Descriptive Statistics

With many built-in functions, Excel can be used for statistical calculation.

Here is a list of frequently used statistical functions.

Function |
Description |
---|---|

AVERAGE function | Returns the average of its arguments |

MAX function | Returns the maximum value in a list of arguments |

MEDIAN function | Returns the median of the given numbers |

MIN function | Returns the minimum value in a list of arguments |

NORM.DIST function | Returns the normal cumulative distribution |

NORM.INV function | Returns the inverse of the normal cumulative distribution |

NORM.S.DIST function | Returns the standard normal cumulative distribution |

NORM.S.INV function | Returns the inverse of the standard normal cumulative distribution |

PERCENTILE.EXC function | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. |

PERCENTRANK.EXC function | Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set |

QUARTILE.EXC function | Returns the quartile of the data set, based on percentile values from 0..1, exclusive |

STANDARDIZE function | Returns a normalized value |

STDEV.S function | Estimates standard deviation based on a sample |

**Example:** The following table shows the test scores
of 20 students.

Test |
---|

80 |

66 |

72 |

76 |

78 |

73 |

74 |

80 |

79 |

80 |

94 |

77 |

65 |

82 |

89 |

92 |

96 |

97 |

88 |

77 |

- Find the mean and standard deviation.
- Find the 5-number summary for the test.

**Solution:** Suppose the test scores are
`A2:A21`

.

The mean is given by the function `AVERAGE(A2:A21)`

. To
calculate the standard deviation, you can use the method introduced in
class or use the function `STDEV.S(A2:A21)`

.

To find the 5-number summary, **sort the data from small to
large first**. Then depends on the convetion, you may use the
Excel to find them. See the table below for the functions used in two
conventions.

5-number summary | Class convetion | Excel convention |
---|---|---|

minimum | `MIN(A2:A21)` |
`MIN(A2:A21)` |

lower quartile | `MEDIAN(A2:A11)` |
`QUARTILE.EXC(A2:A21, 1)` |

median | `MEDIAN(A2:A21)` or
`QUARTILE.EXC(A2:A21, 2)` |
`MEDIAN(A2:A21)` or
`QUARTILE.EXC(A2:A21, 2)` |

upper quartile | `MEDIAN(A12:A21)` |
`QUARTILE.EXC(A2:A21, 3)` |

maximum | `MAX(A2:A21)` |
`MAX(A2:A21)` |

**Example:** Create a box plot for the test scores in
the above example.

**Solution:** Suppose the test scores are
`A2:A21`

.

**Step 1:** Select the scores in
`A1:B21`

.

**Step 2:** Under the Insert tab, click
`Insert Statistical Chart`

.

**Step 3:** Click the picture below
`Box and Wisker`

. A boxplot will be then created.

*Note the boxplot created by Excel uses the convention of weighted
mean for the first and third quartiles.*

The following data set contains scores of a uniform final from randomly selected 20 students.

Final |
---|

72 |

88 |

82 |

82 |

88 |

77 |

78 |

77 |

76 |

76 |

68 |

82 |

89 |

92 |

96 |

97 |

88 |

77 |

95 |

88 |

- Find the mean and standard deviation.
- Find the five-number summary.
- Create a boxplot for the data.
- Do your think the uniform final exam is easy or hard?