Excel math functions
Math functions add, multiply and round numbers. SUM handles most totals. The rounding family (ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING and FLOOR) decides how numbers are cut to size for prices, quantities and time. SUMPRODUCT turns two columns, such as units and price, into one total without a helper column.
- Functions
- 22
- With a graded exercise
- All 22
- Start with
SUM
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Add up a column or block of numbers | SUM | Skips text and blanks, so a header inside the range does no harm. |
| Total units × price without a helper column | SUMPRODUCT | Multiplies row by row, then adds the results. |
| Round money to cents for an invoice | ROUND | Changes the stored value, not just what the cell displays. |
| Always round up: boxes, packs, staff | ROUNDUP | Never leaves you one short. |
| Round to the nearest 5, 15 or 0.25 | MROUND | Rounds to any multiple, up or down, whichever is closer. |
| Find leftovers, odd and even, or every Nth row | MOD | Returns the remainder after division. |
Try SUM live
The most useful math function, on a sample sheet you can edit. Change a value and watch the results update.
E2=SUM(B2:D2) $2,175 Adds across a row: everything spent in JanuaryB5=SUM(B2:B4) $5,400 Adds down a column: rent for the quarterE5=SUM(B2:D4) $6,500 A block of rows and columns in one range: the quarter's total
All 22 math functions
| Function | What it does | Practice exercise |
|---|---|---|
ABS function | ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250. | Forecast error |
CEILING function | CEILING rounds a number up to the next multiple you choose: the next 5, the next dozen, the next quarter hour. | Order in full packs |
EXP function | EXP returns e (about 2.71828) raised to a power. | Continuous compounding |
FLOOR function | FLOOR rounds a number down to the previous multiple you choose: down to the nearest 10, the nearest quarter, or the start of a price band. | Price break quantity |
INT function | INT rounds a number down to the nearest whole number. | Whole weeks of work |
LN function | LN returns the natural logarithm of a number: the power you would raise e to in order to get it. | Log returns |
LOG function | LOG returns the logarithm of a number in the base you choose: the power the base must be raised to in order to reach the number. | Years to reach the target |
LOG10 function | LOG10 returns the base-10 logarithm of a number: how many times 10 is multiplied to reach it. | Amplifier gain in decibels |
MOD function | MOD returns the remainder after dividing one number by another. | Loose units |
MROUND function | MROUND rounds a number to the nearest multiple of another number. | Bill in six-minute blocks |
PI function | PI returns the constant π, accurate to 15 digits (3.14159265358979). | Tank volumes |
POWER function | POWER raises a number to a power, the same as the ^ operator. | Grow each deposit |
PRODUCT function | PRODUCT multiplies all of its arguments together. | Price each part |
ROUND function | ROUND rounds a number to the number of digits you choose. | Round to cents |
ROUNDDOWN function | ROUNDDOWN always rounds toward zero, dropping the extra digits whatever they are. | Full cases to ship |
ROUNDUP function | ROUNDUP always rounds away from zero, whatever the next digit is. | Boxes needed |
SIGN function | SIGN tells you which side of zero a number is on: 1 for positive, -1 for negative and 0 for zero. | Which way did prices move? |
SQRT function | SQRT returns the square root of a number: the value that, multiplied by itself, gives the number. | Straight-line distance |
SUM function | SUM adds numbers. | Weekly total |
SUMPRODUCT function | SUMPRODUCT multiplies matching items in two or more ranges and adds the results. | Total revenue |
SUMSQ function | SUMSQ squares each number and adds the squares. | Sum of squared errors |
TRUNC function | TRUNC cuts a number off at a set number of decimals without rounding. | Commission to the cent |
Head-to-head
- ROUND vs ROUNDUP vs ROUNDDOWNROUND goes to the nearest value. ROUNDUP always moves away from zero and ROUNDDOWN always moves toward it, whatever the next digit is.
Learn them in order
These functions are taught step by step in the Analysis track. Each lesson ends with graded exercises on real-looking data.
- SUM, AVERAGE, MIN, MAX & COUNT7 min
- FILTER10 min · Pro
- ROUND, ROUNDUP & ROUNDDOWN7 min · Pro
- SUMPRODUCT9 min · Pro