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