Math and Rounding Functions
| Function | Syntax | Example and result | Use |
|---|---|---|---|
| SUM | =SUM(number1, [number2], ...) | =SUM(C2:C9) → 572 | Adds numbers. Ignores text and blanks in a range. |
| AutoSum | Alt + = | Inserts SUM for the neighbouring cells. | |
| PRODUCT | =PRODUCT(2,3,4) | 24 | Multiplies. |
| SUMPRODUCT | =SUMPRODUCT(array1, array2) | =SUMPRODUCT(C2:C9,D2:D9) → 1715000 | Multiplies matching items, then adds. Works as a multi-condition counter and weighted average. |
| SUMSQ | =SUMSQ(3,4) | 25 | Sum of squares. |
| ABS | =ABS(-7) | 7 | Absolute value. |
| SIGN | =SIGN(-9) | -1 | -1, 0 or 1. |
| SQRT | =SQRT(144) | 12 | Square root. Negative gives #NUM!. |
| POWER | =POWER(2,10) | 1024 | Same as 2^10. |
| EXP, LN, LOG, LOG10 | =LOG(100,10) | 2 | Exponent and logarithms. |
| MOD | =MOD(17,5) | 2 | Remainder. Sign follows the divisor: =MOD(-7,3) → 2. |
| QUOTIENT | =QUOTIENT(17,5) | 3 | Integer part of division. |
| INT | =INT(-4.5) | -5 | Rounds down to the next lower integer. |
| TRUNC | =TRUNC(-4.5) | -4 | Cuts off the decimals, no rounding. |
| FACT | =FACT(5) | 120 | Factorial. |
| COMBIN / PERMUT | =COMBIN(5,2) / =PERMUT(5,2) | 10 / 20 | Combinations / permutations. |
| GCD / LCM | =GCD(12,18) / =LCM(4,6) | 6 / 12 | Highest common factor / lowest common multiple. |
| PI | =PI() | 3.14159... | The constant pi. |
| RAND | =RAND() | Random decimal between 0 and 1 | Changes at every recalculation. |
| RANDBETWEEN | =RANDBETWEEN(1,100) | Random whole number from 1 to 100 | |
| SUBTOTAL | =SUBTOTAL(9, C2:C9) | 572 | Totals that ignore other SUBTOTALs and obey filters. |
Rounding functions
| Function | Example | Result | Rule |
|---|---|---|---|
| ROUND | =ROUND(2.567,2) | 2.57 | Normal rounding to the given digits. |
=ROUND(1234,-2) | 1200 | A negative digit count rounds to hundreds, tens and so on. | |
| ROUNDUP | =ROUNDUP(2.341,1) | 2.4 | Always away from zero. |
| ROUNDDOWN | =ROUNDDOWN(2.99,0) | 2 | Always towards zero. |
| MROUND | =MROUND(17,5) | 15 | Nearest multiple of 5. |
| CEILING | =CEILING(17,5) | 20 | Up to the next multiple. |
| FLOOR | =FLOOR(17,5) | 15 | Down to the previous multiple. |
| EVEN / ODD | =EVEN(3) / =ODD(4) | 4 / 5 | Round up to the next even or odd number. |
SUBTOTAL function numbers
=SUBTOTAL(function_num, range). Numbers 1 to 11 include rows hidden by hand; 101 to 111 leave out hidden rows. Filtered-out rows are always left out.
| Number | Function | Number | Function |
|---|---|---|---|
| 1 / 101 | AVERAGE | 5 / 105 | MIN |
| 2 / 102 | COUNT | 6 / 106 | PRODUCT |
| 3 / 103 | COUNTA | 7 / 107 | STDEV |
| 4 / 104 | MAX | 9 / 109 | SUM |
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.