Excel Math Functions
Excel includes a set of built-in math functions that handle calculations you would otherwise do manually or with a calculator. These functions work on single numbers, ranges of cells, or combinations of both. Knowing the right function for the right task saves time and reduces calculation errors.
SUM — Add Numbers Together
SUM adds all numbers in a range. It is the most-used function in Excel.
=SUM(A1:A10) → Adds all values from A1 to A10 =SUM(A1, B1, C1) → Adds three specific cells =SUM(A1:A5, C1) → Adds a range plus one extra cell
SUM Diagram
A1: 100
A2: 250
A3: 80
A4: 120
↓
=SUM(A1:A4) = 550
ROUND — Round to a Specific Decimal Place
ROUND takes a number and rounds it to the number of decimal places you specify.
=ROUND(number, num_digits) =ROUND(3.7654, 2) → 3.77 (round to 2 decimal places) =ROUND(3.7654, 0) → 4 (round to nearest whole number) =ROUND(345, -1) → 350 (round to nearest 10) =ROUND(345, -2) → 300 (round to nearest 100)
Related Rounding Functions
| Function | What It Does | Example |
|---|---|---|
| ROUND | Rounds to nearest (up or down) | =ROUND(2.45, 1) → 2.5 |
| ROUNDUP | Always rounds up, away from zero | =ROUNDUP(2.41, 1) → 2.5 |
| ROUNDDOWN | Always rounds down, toward zero | =ROUNDDOWN(2.49, 1) → 2.4 |
| MROUND | Rounds to the nearest multiple | =MROUND(17, 5) → 15 |
| CEILING | Rounds up to the nearest multiple | =CEILING(17, 5) → 20 |
| FLOOR | Rounds down to the nearest multiple | =FLOOR(17, 5) → 15 |
ABS — Absolute Value
ABS removes the negative sign from a number. It returns the distance of a number from zero, regardless of direction.
=ABS(-45) → 45 =ABS(45) → 45 =ABS(-7.3) → 7.3
Use ABS when calculating differences where the direction (positive or negative) does not matter — for example, the difference between a budget and actual spending.
MOD — Remainder After Division
MOD returns the remainder left over after dividing one number by another.
=MOD(10, 3) → 1 (10 ÷ 3 = 3 remainder 1) =MOD(15, 5) → 0 (15 ÷ 5 = 3 remainder 0) =MOD(7, 2) → 1 (7 ÷ 2 = 3 remainder 1)
Practical Uses of MOD
| Task | Formula | Logic |
|---|---|---|
| Identify even/odd rows | =MOD(ROW(),2)=0 | Result is 0 for even rows |
| Alternate shading | Used in conditional formatting | Highlight every other row |
| Check divisibility | =MOD(A1,3)=0 | True if A1 divides evenly by 3 |
INT and TRUNC — Remove the Decimal Part
INT rounds a number down to the nearest integer. TRUNC removes the decimal part without rounding.
=INT(3.9) → 3 (rounds DOWN always) =INT(-3.1) → -4 (rounds DOWN, which is more negative) =TRUNC(3.9) → 3 (simply cuts off the decimal) =TRUNC(-3.9) → -3 (cuts off the decimal, keeps sign)
POWER and SQRT — Exponents and Square Roots
=POWER(2, 10) → 1024 (2 to the power of 10) =POWER(9, 0.5) → 3 (square root of 9) =SQRT(25) → 5 (square root of 25) =SQRT(144) → 12
PRODUCT — Multiply a Range
PRODUCT multiplies all values in a range together.
=PRODUCT(A1:A4) A1: 2 A2: 3 A3: 4 A4: 5 Result: 2 × 3 × 4 × 5 = 120
SUMPRODUCT — Multiply Then Add
SUMPRODUCT multiplies corresponding values in two or more ranges, then adds the results. This is useful for calculating weighted totals.
Quantity (A) Price (B)
3 100
5 80
2 150
=SUMPRODUCT(A1:A3, B1:B3)
= (3×100) + (5×80) + (2×150)
= 300 + 400 + 300
= 1000
This single formula replaces a column of multiplication formulas plus a SUM at the bottom.
QUOTIENT — Integer Division Result
=QUOTIENT(10, 3) → 3 (only the whole number part of 10÷3) =QUOTIENT(15, 4) → 3
LCM and GCD — Least Common Multiple and Greatest Common Divisor
=LCM(4, 6) → 12 (smallest number divisible by both 4 and 6) =GCD(12, 8) → 4 (largest number that divides both 12 and 8)
FACT — Factorial
=FACT(5) → 120 (5! = 5 × 4 × 3 × 2 × 1 = 120) =FACT(3) → 6 (3! = 3 × 2 × 1 = 6)
Common Math Functions Summary Table
| Function | Syntax | Purpose |
|---|---|---|
| SUM | =SUM(range) | Add all values |
| ROUND | =ROUND(number, digits) | Round to decimal places |
| ROUNDUP | =ROUNDUP(number, digits) | Always round up |
| ROUNDDOWN | =ROUNDDOWN(number, digits) | Always round down |
| ABS | =ABS(number) | Remove negative sign |
| MOD | =MOD(number, divisor) | Remainder after division |
| INT | =INT(number) | Round down to integer |
| TRUNC | =TRUNC(number) | Cut off decimal |
| POWER | =POWER(base, exponent) | Calculate exponent |
| SQRT | =SQRT(number) | Square root |
| PRODUCT | =PRODUCT(range) | Multiply all values |
| SUMPRODUCT | =SUMPRODUCT(array1, array2) | Multiply then add |
| QUOTIENT | =QUOTIENT(numerator, denominator) | Whole number from division |
| LCM | =LCM(n1, n2) | Least common multiple |
| GCD | =GCD(n1, n2) | Greatest common divisor |
| FACT | =FACT(number) | Factorial |
These math functions work in all modern Excel versions. Combine them with other functions — for example, use ROUND with SUM to get a clean total, or use MOD with IF to apply logic based on divisibility. Each function is a building block, and combining them gives you powerful calculation tools without complex programming.