Math Functions

Beyond SUM and AVERAGE, Excel has a deep library of mathematical functions that handle everything from rounding and remainders to weighted calculations and absolute values. These come up constantly in finance, operations, and data analysis work. This lesson covers the ones you will actually use — with a special focus on SUMPRODUCT, which is one of the most versatile and powerful functions in the entire Excel toolkit.

ABS — Absolute Value

ABS returns the absolute value of a number — that is, the distance from zero, always positive. It strips the negative sign from negative numbers and leaves positive numbers unchanged.

=ABS(number)

=ABS(-450)       →  450
=ABS(450)        →  450
=ABS(A2-B2)      →  Difference between two values, always positive

A common real-world use is calculating variance between actual and budget: =ABS(Actual-Budget) gives you the magnitude of the gap regardless of whether you went over or under. You can then use SUMIF to total all overspend or underspend separately.

INT and TRUNC — Removing Decimal Places

Both functions remove the decimal portion of a number, but they behave differently with negative numbers.

INT rounds down to the nearest integer — always toward negative infinity. TRUNC simply cuts off the decimal part without rounding — always toward zero.

=INT(number)
=TRUNC(number, [num_digits])

=INT(7.9)         →  7      (rounds down)
=INT(-7.9)        →  -8     (rounds toward negative infinity)

=TRUNC(7.9)       →  7      (cuts off decimal — same as INT for positives)
=TRUNC(-7.9)      →  -7     (cuts toward zero — different from INT)

=TRUNC(3.14159, 2)  →  3.14  (keeps 2 decimal places, no rounding)

For most positive-number scenarios they are interchangeable. The difference matters when working with negative numbers — choose TRUNC if you want to always move toward zero, INT if you want to always move toward the floor.

MOD — The Remainder

MOD returns the remainder after dividing one number by another. It sounds niche but it has several genuinely useful applications — identifying even and odd numbers, highlighting every Nth row, grouping data into cycles, and working with time calculations.

=MOD(number, divisor)

=MOD(10, 3)      →  1     (10 ÷ 3 = 3 remainder 1)
=MOD(12, 4)      →  0     (12 ÷ 4 = 3 remainder 0 — evenly divisible)
=MOD(7, 2)       →  1     (odd number — remainder 1 when divided by 2)
=MOD(8, 2)       →  0     (even number — remainder 0 when divided by 2)

The classic use: =IF(MOD(ROW(),2)=0,"Even","Odd") — tags every row as even or odd, which is how conditional formatting creates alternating row colours when you want to do it manually. Also useful in finance for identifying payment cycles: =MOD(MONTH(A2),3)=0 flags the last month of each quarter.

CEILING and FLOOR — Rounding to a Multiple

ROUND rounds to a number of decimal places. CEILING and FLOOR round to the nearest multiple of a value you specify — up or down respectively. These are essential for pricing, scheduling, and inventory calculations where you need values to land on specific increments.

=CEILING(number, significance)   Always rounds UP to nearest multiple
=FLOOR(number, significance)     Always rounds DOWN to nearest multiple

=CEILING(47, 10)    →  50    (round up to nearest 10)
=FLOOR(47, 10)      →  40    (round down to nearest 10)

=CEILING(2.3, 0.5)  →  2.5   (round up to nearest 0.5)
=FLOOR(2.3, 0.5)    →  2.0   (round down to nearest 0.5)

=CEILING(13, 5)     →  15    (next multiple of 5 above 13)
=FLOOR(13, 5)       →  10    (previous multiple of 5 below 13)

POWER and SQRT — Exponents and Square Roots

=POWER(number, power)      Raises number to a power
=SQRT(number)              Returns the square root

=POWER(2, 10)     →  1024    (2 to the power of 10)
=POWER(B2, 1/3)   →  Cube root of B2  (any root using fractional exponent)
=SQRT(144)        →  12
=SQRT(B2)         →  Square root of B2

You can also use the ^ operator as a shortcut for POWER — =2^10 is identical to =POWER(2,10). SQRT is available as a shortcut for the specific case of square roots, and it is generally cleaner to read than =POWER(B2, 0.5).

RAND and RANDBETWEEN — Random Numbers

Sometimes you need random data — for testing, simulations, or generating sample datasets. RAND returns a random decimal between 0 and 1. RANDBETWEEN returns a random integer between two values you specify. Both recalculate every time the workbook recalculates.

=RAND()                  →  Random decimal between 0 and 1 (e.g. 0.7342)
=RANDBETWEEN(1, 100)     →  Random integer from 1 to 100
=RANDBETWEEN(1000, 9999) →  Random 4-digit number (useful for test IDs)
=RAND()*100              →  Random decimal between 0 and 100

To lock a random number so it stops changing, copy the cell and Paste Special → Values. This replaces the formula with the static number it generated.

SUMPRODUCT — The Swiss Army Knife

SUMPRODUCT is one of the most powerful functions in Excel. At its simplest it multiplies corresponding elements of two arrays together and sums the results — which is exactly how you calculate a weighted total or a weighted average. But it goes much further than that — it can replace COUNTIFS and SUMIFS, handle complex multi-condition calculations, and work with criteria in ways that other functions cannot match.

Start with the basic use — calculating revenue from a quantity and price list:

=SUMPRODUCT(array1, array2, ...)

Products table:
  B2:B5 = Quantities sold  {120, 85, 200, 60}
  C2:C5 = Unit prices      {£12, £25, £8, £45}

=SUMPRODUCT(B2:B5, C2:C5)
  = (120×12) + (85×25) + (200×8) + (60×45)
  = 1440 + 2125 + 1600 + 2700
  = 7,865

Notice there is no helper column needed — SUMPRODUCT does the row-by-row multiplication and the final sum in a single formula. This is what makes it so powerful for financial calculations.

SUMPRODUCT With Conditions

SUMPRODUCT becomes even more powerful when you add conditions. By including a TRUE/FALSE test as one of the arrays, you can replicate SUMIFS behaviour — but with more flexibility, including handling text patterns and calculated criteria that SUMIFS cannot handle.

Sales data: A=Rep name, B=Region, C=Sales, D=Quarter

Conditional SUM — North region sales only:
=SUMPRODUCT((B2:B10="North") * C2:C10)

Conditional COUNT — how many North reps:
=SUMPRODUCT((B2:B10="North") * 1)

Two conditions — North region, Q1 only:
=SUMPRODUCT((B2:B10="North") * (D2:D10="Q1") * C2:C10)

Weighted average — average sales weighted by units:
=SUMPRODUCT(C2:C10, D2:D10) / SUM(D2:D10)

The key insight: when you multiply a TRUE/FALSE array by a number array, Excel treats TRUE as 1 and FALSE as 0. So (B2:B10="North") creates an array of 1s and 0s, and multiplying that by the sales values zeroes out every non-North row. The final SUM only adds up the North rows. Clean, fast, and no helper columns needed.

A Quick Reference — All Math Functions

Next up — Lookup Functions, where you will learn VLOOKUP, HLOOKUP, and INDEX/MATCH — the classic tools for pulling data from one table into another.