Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
ABS / INT / MOD
Math & roundingThree small math tools that punch above their weight — magnitude, whole part, remainder.
Difficulty
1What is it?
ABS returns a number's magnitude, dropping the sign — so -4,000 and 4,000 both become 4,000, which is how you compare the size of a variance regardless of direction. INT strips the decimals down to the whole number (it floors, so it rounds negatives away from zero). MOD returns the remainder after division — the leftover — which is the trick behind 'every nth row' patterns, converting a running count into a repeating cycle, or splitting a total into whole units and a remainder.
2What it looks like
MOD(number, divisor)- number
- The value to operate on (for ABS and INT, the only argument).
- divisor
- For MOD: what to divide by — the remainder of number ÷ divisor is returned.
3When you use it
- Compare variance size regardless of sign: =ABS(actual - budget).
- Whole units and leftover: =INT(total/12) and =MOD(total, 12).
- Flag every nth row or alternate: =MOD(ROW(), 2) for stripes.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
ABS drops the sign, INT drops the decimals, MOD returns the remainder after dividing by B3. Edit the number and switch functions.
=ABS(B2)2,450| A | B | C | |
|---|---|---|---|
| 1 | Field | Value | Result |
| 2 | Number | 2,450 | |
| 3 | Divisor |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
INT vs TRUNC on negativesYou expected INT(-2.5) to give -2.Fix: INT floors, so INT(-2.5) is -3; use TRUNC(-2.5) to just chop the decimal and get -2.
#DIV/0! from MODThe divisor is zero.Fix: MOD can't divide by zero — guard it, or check the divisor isn't blank.
MOD sign surpriseMOD with a negative divisor returned an unexpected sign.Fix: MOD's result takes the divisor's sign — keep the divisor positive for the usual behavior.
6Better functions & alternatives
- ROUND / ROUNDUP / ROUNDDOWN — When you need to round to a precision, not just drop decimals.
- CEILING / FLOOR / MROUND — Round to a multiple — the nearest 25, 0.05, or case pack.
- QUOTIENT — The whole-number result of a division — the partner to MOD's remainder.
Want ABS / INT / MOD already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →