Microsoft Office Tutorials and References
In Depth Information
Chart Examples
To specify how to deal with missing data for a chart, choose Chart Tools➜Design➜Data➜Select
Data. In the Select Data Source, click the Hidden and Empty Cells button. Excel displays its
Hidden and Empty Cell Settings dialog box. Make your choice in the dialog box. The option that
you select applies to the entire chart, and you can’t set a different option for different series in
the same chart.
Normally, a chart doesn’t display data that’s in a hidden row or columns. You can use the Hidden
and Empty Cell Settings dialog box to force a chart to use hidden data.
Table 17-1: Formulas Used to Create a Box Plot
Cell
Calculation
Formula
G2
25th Percentile
=QUARTILE(A2:A26,1)
G3
Minimum
=MIN(A2:A26)
=AVERAGE(A2:A26)
G4
Mean
=QUARTILE(A2:A26,2)
G5
50th Percentile
G6
Maximum
=MAX(A2:A26)
G7
75th Percentile
=QUARTILE(A2:A26,3)
Follow these steps to create the box plot:
1. Select the range F1:J7.
2. Choose Insert
Charts
Line, and select the fourth subtype, Line with Markers.
3. Choose Chart Tools
Design
Data
Switch Row/Column to change the orientation of
the chart.
4. Choose Chart Tools
Up/Down Bars to add up/down
bars that connect the first data series (25th Percentile) with the last data series (75th
Percentile).
Layout
Analysis
Up/Down Bars
5. Remove the markers from the 25th Percentile series and the 75th Percentile series.
6. Choose Chart Tools
Hi-Lo Lines to add a vertical line between
each point to connect the Minimum and Maximum data series.
Layout
Analysis
Lines
7. Remove the lines from each of the six data series.
8. Change the series marker to a horizontal line for the following series: Minimum,
Maximum, and 50th Percentile.
9. Make other formatting changes as required.

Search JabSto ::

Custom Search