☰ 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
16

Practical lesson for consolidation

Textbook: p. 39
GoalUses statistical and mathematical functions together in a table task, checks the result and shows it with a chart.
New words
summary row: the row with MAX, MIN, AVERAGE and other results · natijaviy qatorCOUNTIF: counts cells that meet a condition · shartli sanashtable of values: inputs and the results calculated from them · qiymatlar jadvalichart: a graphic that shows the numbers of a table · diagramma
Explanation

In this practical lesson three kinds of table are built: a table of statistical indicators, a table of function values and a chart of them. In each table the starting data should be in one place and the formulas in another, so that when the data change the result updates by itself. The summary row uses MAX, MIN, AVERAGE, MEDIAN and COUNTIF; also calculate the results by hand on a small list of 10 numbers. In calculations such as the circumference of a circle take π with the PI() function and round the result with ROUND. A chart must have a title, axis titles and units. Save the finished file with a meaningful name.

Worked examples
Task 1. Test scores are in A1:A10: 56, 72, 88, 91, 64, 77, 88, 95, 49, 80. AVERAGE = 760/10 = 76; MAX = 95; MIN = 49; MEDIAN = (77 + 80)/2 = 78.5; =COUNTIF(A1:A10,">=60") = 8, so 8/10 = 80% of the students scored 60 or more.
Task 2. The circumference of a circle: r = 1 to 5 in A2:A6, and B2 gets =ROUND(2*PI()*A2,2). Result: 6.28; 12.57; 18.85; 25.13; 31.42. A scatter chart gives a straight line through the origin, because the length is directly proportional to the radius.
Class activity

“Indicators in a minute”: each pair calculates four statistical indicators for 10 given numbers in Excel at the same time; whoever is quick and correct wins.

Practice
1
The scores are 56, 72, 88, 91, 64, 77, 88, 95, 49, 80. If the sum is 760, what is the mean?
2
How many of these scores are 60 or more?
3
What is the circumference for r = 4 by =ROUND(2*PI()*A2,2)?
4
Why is it good to keep the starting data and the formulas in separate places?