AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ 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.

FunctionMeaning
MAXlargest
MINsmallest
AVERAGEarithmetic mean
MEDIANmiddle value
COUNTnumber of numeric cells
COUNTIFnumber of cells meeting a condition
GEOMEANgeometric mean
A12A17A225A39A415A57A6=SUM(A1:A6) → 75

=SUM(A1:A6) = 75

sum: 12, 7, 25, 9, 15, 7

💡 Where will I use this?
In real life

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.

Who uses it

Weather specialists, sports statisticians and researchers describe a lot of data with just a few numbers.

Try it today

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.

Worked examples
A1:A6 = 72, 85, 91, 64, 85, 78
IndicatorValue
MAX91
MIN64
AVERAGE79.17
MEDIAN81.5
COUNTIF(A1:A6,">80")3
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?
Story of the Week · The Khwarizmi WayA bacterium and the Earth’s massClose the week: all of this week’s subjects in one storyRead →