Published by Patrick Mutisya · 14 days ago
By the end of this lesson students will be able to use the following spreadsheet functions confidently:
| Function | Purpose | Syntax | Example |
|---|---|---|---|
| SUM | Adds a range of numbers | =SUM(number1, [number2], …) or =SUM(A1:A10) | =SUM(B2:B6) → adds the values in B2 through B6 |
| A \cdot ERAGE | Calculates the mean of a range | =A \cdot ERAGE(number1, [number2], …) or =A \cdot ERAGE(A1:A10) | =A \cdot ERAGE(C1:C5) → \$(\frac{C1+C2+C3+C4+C5}{5})\$ |
| MAX | Returns the largest value | =MAX(number1, [number2], …) or =MAX(A1:A10) | =MAX(D2:D8) |
| MIN | Returns the smallest value | =MIN(number1, [number2], …) or =MIN(A1:A10) | =MIN(E2:E9) |
Syntax: =INT(number)
Example: =INT(4.7) returns 4.
Syntax: =ROUND(number, num_digits)
Example: =ROUND(3.14159, 2) returns 3.14.
| Function | Counts | Syntax | Example |
|---|---|---|---|
| COUNT | Number cells containing numeric values | =COUNT(value1, [value2], …) or =COUNT(A1:A10) | =COUNT(F2:F12) |
| COUNTA | Number cells containing any type of data (numbers, text, logical values) | =COUNTA(value1, [value2], …) or =COUNTA(A1:A10) | =COUNTA(G2:G15) |
Searches for a value in a one‑dimensional range and returns a value from the same position in a second range.
Syntax: =LOOKUP(lookupvalue, lookupvector, [result_vector])
Example: =LOOKUP(85, A2:A10, B2:B10) – finds 85 in column A and returns the corresponding entry from column B.
Searches for a value in the first column of a table and returns a value from a specified column in the same row.
Syntax: =VLOOKUP(lookupvalue, tablearray, colindexnum, [range_lookup])
Example: =VLOOKUP("Smith", A2:D20, 3, FALSE) – looks for “Smith” in column A and returns the value from column C of the same row.
Searches for a value in the first row of a table and returns a value from a specified row in the same column.
Syntax: =HLOOKUP(lookupvalue, tablearray, rowindexnum, [range_lookup])
Example: =HLOOKUP("Q2", A1:E5, 4, FALSE) – finds “Q2” in row 1 and returns the value from row 4 of that column.
Replaces \cdot LOOKUP/HLOOKUP with a single function that can search vertically or horizontally.
Syntax: =XLOOKUP(lookupvalue, lookuparray, returnarray, [ifnotfound], [matchmode], [search_mode])
Example: =XLOOKUP(2023, A2:A10, B2:B10, "Not found") – searches column A for 2023 and returns the matching value from column B.
Tests a condition and returns one value if true, another if false.
Syntax: =IF(logicaltest, valueiftrue, valueif_false)
Example: =IF(D5 >= 50, "Pass", "Fail") – returns “Pass” when D5 is 50 or more, otherwise “Fail”.
Functions can be nested to perform complex calculations.
Example: Calculate the average of the top three scores in a range B2:B10.
=A \cdot ERAGE(LARGE(B2:B10, {1,2,3}))
This uses LARGE (not listed in the objective but useful) to extract the three highest values, then A \cdot ERAGE to find their mean.
range_lookup is set correctly (TRUE for approximate, FALSE for exact).A \cdot ERAGE on an empty range. Use IFERROR or ensure the range contains data.SUM to total monthly sales.A \cdot ERAGE and MAX.INT and ROUND to format monetary values to whole pounds.VLOOKUP and XLOOKUP.IF formula that assigns grades (A‑F) based on percentage scores.