Statistical functions
Statistical functions describe a set of numbers with one value. MAX(range) finds the largest, MIN(range) the smallest, AVERAGE(range) the arithmetic mean and MEDIAN(range) the middle value. COUNT counts the numeric cells, and COUNTIF(range, condition) counts the cells that meet a condition, for example =COUNTIF(A1:A6,">80"). GEOMEAN(number1, number2, …) takes the nth root of the product of n positive numbers: GEOMEAN(4,16) = 8. A function can be entered in two ways: typing it straight into the cell (when you type the first letters of the name, Excel shows a list of suggestions) or choosing it from the Statistical category through the fx button. If the data contain a very large or very small outlier, the arithmetic mean is distorted while the median hardly changes: for 3, 4, 5 and 100 the mean is 28 and the median is 4.5.
| Function | Meaning |
|---|---|
| MAX | largest |
| MIN | smallest |
| AVERAGE | arithmetic mean |
| MEDIAN | middle value |
| COUNT | number of numeric cells |
| COUNTIF | number of cells meeting a condition |
| GEOMEAN | geometric mean |
=SUM(A1:A6) = 75
sum: 12, 7, 25, 9, 15, 7
A school weather corner records the temperature every day. Instead of seven numbers, three are enough: the lowest, the highest and the average. On the wall newspaper one line shows how the week went.
Weather specialists, sports statisticians and researchers describe a lot of data with just a few numbers.
For 5 days write in your notebook the pages you read or glasses of water you drink each day, then find the largest, the smallest and the middle value.
| Indicator | Value |
|---|---|
| MAX | 91 |
| MIN | 64 |
| AVERAGE | 79.17 |
| MEDIAN | 81.5 |
| COUNTIF(A1:A6,">80") | 3 |
“Statistics detective”: the teacher hides 8 numbers (no one’s personal data) and gives only the MAX, MIN, AVERAGE and MEDIAN results; groups invent a set that fits those values.