Microsoft Office Tutorials and References

In Depth Information

**Creating a box plot**

Figure 8-49: This box plot summarizes the data in columns A through D.

The raw data appears in columns A:D. The data is summarized in range F1:J7,

with simple formulas. The following table lists the formulas for Group 1 (G3:G7).

These formulas were copied to the three columns to the right.

Cell

Calculation

Formula

G3

Minimum

=MIN(A$2:A$26)–

G4

25th Percentile

=PERCENTILE(A$2:A$26,0.25)

G5

Median

=MEDIAN(A$2:A$26)

G6

75th Percentile

=PERCENTILE(A$2:A$26,0.75)

G7

Maximum

=MAX(A$2:A$26)

The summary data must be transformed in order to create the box plot. This is

done in the section labeled Chart Data (G11:J15). This range contains simple

formulas that calculate the difference between the row values in the Summary Range.

For example, the formula in cell G12 is

=G4-G3