Using mathematical operations and functions in MS Excel
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.
| Kind | Functions |
|---|---|
| mathematical | ABS, SIGN, SQRT, MOD, POWER, INT, SUM |
| statistical | MAX, MIN, AVERAGE, COUNTIF |
| logical | AND, OR, NOT, IF |
| text | LEN, LEFT, RIGHT, MID, CONCATENATE, REPLACE |
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.
Store keepers, event organisers and programmers count the groups and the remainder.
Work out on paper how to share 17 things equally among 5 groups: how many in each, how many are left?
“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.