☰ Contents · Computing

Statistical functions

Lessons 15–16 · 2 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
15

Statistical functions

Textbook: pp. 37–39
GoalEnters the statistical functions MAX, MIN, AVERAGE, MEDIAN, COUNT, COUNTIF and GEOMEAN in two ways and describes a set of data.
New words
arithmetic mean (AVERAGE): the sum of the values divided by their number · o‘rtacha arifmetikmedian (MEDIAN): the middle value of the sorted data, or the mean of the two middle ones · medianageometric mean (GEOMEAN): the nth root of the product of n positive numbers · o‘rtacha geometrikCOUNTIF: counts the cells that meet a condition · shartli sanash
Explanation

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.

Worked examples
Scores are in A1:A6: 72, 85, 91, 64, 85, 78. MAX = 91, MIN = 64, AVERAGE = 475/6 ≈ 79.17. MEDIAN: the sorted row is 64, 72, 78, 85, 85, 91; the two middle values are 78 and 85, so the median is (78 + 85)/2 = 81.5. =COUNTIF(A1:A6,">80") = 3 (85, 91, 85).
The range of the scores (the difference between the largest and the smallest): =MAX(A1:A6)-MIN(A1:A6) = 91 – 64 = 27. The geometric mean: =GEOMEAN(2,8): 2·8 = 16, √16 = 4. A square root is used for two numbers, because n = 2.
Class activity

“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.

Practice
1
What is =AVERAGE(10,20,30,40)?
2
What is the median of 5, 7, 7, 9, 12?
3
What is =GEOMEAN(2,8) and how is it calculated?
4
Why is the median sometimes more convenient than the mean for pay data?