AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Contents · Computing

Using mathematical operations and functions in MS Excel

Lessons 21–22 · 2 lessons · B. Boltayev, A. Azamatov, A. Asqarov, M. Sodiqov, G. Azamatova. Fundamentals of Informatics and Computer Technology, Grade 8. Second edition. “O‘zbekiston milliy ensiklopediyasi” State Scientific Publishing House, Tashkent, 2015
21

Using mathematical operations and functions in MS Excel

Textbook: pp. 98–101
GoalKnows the structure of Excel functions (mathematical, logical, statistical, text) and uses them in formulas.
New words
function: a ready-made calculation with a name, called as NAME(argument; argument; ...) · funksiyaargument: a value, cell address or range given to a function · argumentfill handle: the small square in the corner of a cell that is dragged to copy a formula · to‘ldirish markeriTRUE / FALSE: the two values of a logical function · ROST / YOLG‘ON
Explanation

Excel has more than 400 ready-made functions, divided into mathematical, logical, statistical, text, date and financial kinds. A function has its own name, followed by arguments in brackets; arguments are separated by a semicolon (a comma in English settings): =SUM(A1:A5; C1). Function names differ with the language of Excel; English names are used here. Mathematical: ABS(number) – absolute value; SIGN(number) – sign (–1, 0 or 1); SQRT(number) – square root; MOD(number; divisor) – remainder; POWER(number; exponent) – power; INT(number) – rounds down to the nearest whole number; SUM(range) – total. Statistical: MAX, MIN, AVERAGE (arithmetic mean), COUNTIF(range; condition) – the number of cells that meet the condition. Logical: AND (TRUE if all are true), OR (TRUE if any is true), NOT, IF(condition; if true; if false). Text: LEN – length, LEFT and RIGHT – characters from the left/right, MID(text; start; count) – a piece from the middle, CONCATENATE (or &) – joining, REPLACE – replacing. To copy a formula down or sideways, drag the fill handle.

KindFunctions
mathematicalABS, SIGN, SQRT, MOD, POWER, INT, SUM
statisticalMAX, MIN, AVERAGE, COUNTIF
logicalAND, OR, NOT, IF
textLEN, LEFT, RIGHT, MID, CONCATENATE, REPLACE
💡 Where will I use this?
In real life

Pupils put 25 notebooks equally into 4 boxes: each box got 6 and 1 was left over. The pupils found a separate place for the extra notebook.

Who uses it

Store keepers, event organisers and programmers count the groups and the remainder.

Try it today

Work out on paper how to share 17 things equally among 5 groups: how many in each, how many are left?

Worked examples
MOD(29; 6) = 5, because 29 = 4 · 6 + 5. POWER(5; 3) = 125. SQRT(144) = 12. INT(7.9) = 7, but INT(–7.2) = –8 (rounded down to the smaller whole number). SIGN(–9) = –1. AVERAGE(4; 8; 12) = 8.
Text: LEN("Toshkent") = 8; LEFT("Informatika"; 4) = "Info"; MID("Informatika"; 3; 4) = "form"; REPLACE("Toshkent"; 1; 4; "Yash") = "Yashkent". Function: for x = –4, =(A1^2+ABS(A1))/(A1+6) gives (16 + 4) : 2 = 10.
Class activity

“Function cards”: each pair matches cards with function names and examples (for example MOD(45; 7) → 3), then gives its own example to a rival pair.

Practice
1
Find the values of MOD(47; 6), POWER(2; 8) and INT(–3.5).
2
What does =IF(A1>=60; "Pass"; "Fail") give for A1 = 55 and A1 = 60?
3
Find the results of LEFT("Samarqand"; 3) and RIGHT("Samarqand"; 4).
4
Why is INT(–7.2) = –8 and not –7?
Story of the Week · The Khwarizmi WayA red lamp for the observing clubClose the week: all of this week’s subjects in one storyRead →