Microsoft Office Tutorials and References
In Depth Information
Identifying the maximum and minimum values in a series
Figure 8-28: This chart uses two XY series to highlight the maximum and
minimum data points in the line series.
To create this chart using the data in range A1:B13:
1. Enter the following formula in cell C2:
=IF(B2=MAX(\$B\$2:\$B\$13),B2,NA())
2. Enter this formula in cell D2:
=IF(B2=MIN(\$B\$2:\$B\$13),B2,NA())
3. Copy range C2:D2 down, ending in row 13. These formulas display the
maximum and minimum values in column B, and all other cells display #NA.
4. Select C1:D13 and choose Edit
Copy.
5. Select the chart and choose Edit
Paste Special. In the Paste Special dialog
box, choose New series, Values (Y) in Columns, and Series Names in First
Row. This adds two new series, named Max and Min.
6. Select the Max series and access the Format Data Series dialog box. In
the Patterns tab, set the Line to None, and change the marker to a large
hollow circle. To create a hollow circle, select the circle style, set the
Marker Background to No Color, and increase the Size.
7. Repeat Step 6 for the Min series, but use a large, hollow square for the
marker.
8. Add data labels to the Max and Min series (the #NA values will not appear).
9. Apply other cosmetic formatting as desired.
Search JabSto ::

Custom Search