Beyond the big five, a handful of math functions clean up and shape numbers.
Precise numeric calculations on a finance worksheet
| Function | Effect | =fn(3.146, 2) |
|---|---|---|
ROUND | Round to N decimals | 3.15 |
ROUNDUP | Always up | 3.15 |
ROUNDDOWN | Always down | 3.14 |
MROUND | To nearest multiple | MROUND(23,5) → 25 |
Formatting to 2 decimals is not the same as rounding. A cell formatted to show 3.15 still holds 3.146 and will calculate with the full value. Use ROUND when the actual value must change (e.g. currency, invoices).
1=INT(7.9) → 7 (rounds DOWN to integer)
2=INT(-7.9) → -8 (down = more negative!)
3=TRUNC(7.9) → 7 (just chops decimals)
4=TRUNC(-7.9) → -7 (chops, no rounding)1=ABS(-250) → 250Great for "difference regardless of direction": =ABS(Actual-Budget).
MOD returns what's left after division. It's secretly one of the most useful functions.
1=MOD(10, 3) → 1 (10 ÷ 3 = 3 remainder 1)
2=MOD(ROW(), 2)→ 0 or 1 (even/odd row → banded coloring)Is a number even? =MOD(n, 2) = 0
Every 3rd row? =MOD(ROW(), 3) = 0
1=POWER(2, 10) → 1024 (same as 2^10)
2=SQRT(144) → 12
3=PRODUCT(B2:B5)→ multiplies the range