Exponents, square roots, absolute value and MOD in Excel

In short: an exponent is =A2^3 or =POWER(A2,3). A square root is =SQRT(A2), any other root =A2^(1/3). Absolute value is =ABS(A2), and the remainder of a division is =MOD(A2,B2).

Try it now: absolute value with ABS

Some months beat the budget, others miss it. When all you want to know is how far off each month was, the minus sign gets in the way. Turn every variance into a positive number.

Task In D2, calculate the gap between actual and budget as a positive number, no matter which one is bigger. The formula is filled down to D7.

	A	B	C	D
1	Month	Budget $	Actual $	Variance $
2	January	12000	12800
3	February	15000	14100
4	March	14000	14000
5	April	13000	12450
6	May	16000	17200
7	June	15500	15150

Excel math functions at a glance

Function	What it does	Example	Result
^ or POWER	raises a number to an exponent	=2^10 or =POWER(2,10)	1024
SQRT	square root	=SQRT(144)	12
^(1/n)	nth root, for example a cube root	=27^(1/3)	3
ABS	absolute value, the number without its sign	=ABS(-350)	350
MOD	remainder after division	=MOD(17,5)	2
SIGN	1 if positive, -1 if negative, 0 if zero	=SIGN(-350)	-1
INT	rounds down to the next lower integer	=INT(-7.8)	-8
TRUNC	cuts off the decimals	=TRUNC(-7.8)	-7
CEILING.MATH	rounds up to a multiple	=CEILING.MATH(17,5)	20
FLOOR.MATH	rounds down to a multiple	=FLOOR.MATH(17,5)	15

Exponents in Excel: the caret and the POWER function

The caret ^ (Shift+6 on a US keyboard) is Excel's exponent operator. =B2^2 squares B2 and =B2^3 cubes it. POWER is the same thing written as a function, =POWER(B2,3), and returns the same result. Most people use the caret because it is shorter.

Exponents are calculated before multiplication, division, addition and subtraction, so =2*3^2 is 18, not 36. Anything that has to be added first goes in brackets: =(1+B2)^5. That pattern is the heart of every compound growth and CAGR formula.

Square root and cube root

For a square root, use =SQRT(B2). There is no separate function for other roots, and you do not need one: the nth root of a number is the same as raising it to the power of 1/n. So the cube root of 27 is =27^(1/3), and a fourth root uses 1/4. Keep the brackets around the fraction. Without them Excel works out 27 to the power of 1 and then divides by 3.

Absolute value with ABS, direction with SIGN

ABS strips the sign: -900 becomes 900 and 900 stays 900. Use it whenever only the size of a difference matters, for example to flag variances above a tolerance: =IF(ABS(C2-B2)>500,"check","ok"). SIGN tells you the direction instead: 1 means above budget, -1 below, 0 spot on. Together they rebuild any number, because SIGN(x)*ABS(x) is always x.

Practice: the MOD function

MOD returns what is left over after a division. In a warehouse, that is the number of units that will not fill another whole box.

Task The warehouse packs every item into full boxes. In D2, work out how many units are left over that do not fill a whole box. The formula is filled down to D7.

	A	B	C	D
1	Item	Stock (units)	Units per box	Loose units
2	Ballpoint pens	530	50
3	Binders	87	10
4	Sticky notes	146	12
5	Toner	23	4
6	Envelopes	1270	500
7	Printer paper	64	5

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

MOD is also the standard test for even and odd numbers: =MOD(A2,2)=0 is TRUE when A2 is even. As a conditional formatting rule, =MOD(ROW(),2)=0 shades every other row. With negative numbers, Microsoft documents that the result always takes the sign of the divisor: =MOD(-3,2) returns 1 and =MOD(3,-2) returns -1.

INT, TRUNC, CEILING.MATH and FLOOR.MATH

These four turn untidy numbers into tidy ones, each in its own way. INT always goes down to the next lower whole number, which for negatives means away from zero: -7.8 becomes -8. TRUNC simply drops the decimals, so -7.8 becomes -7. For positive numbers the two agree.

CEILING.MATH and FLOOR.MATH round to a multiple you choose. =CEILING.MATH(B2,5) pushes a price up to the next 5 dollars, and =FLOOR.MATH(B2,0.25) cuts hours down to the last quarter hour. For ordinary rounding to a number of decimal places, use ROUND.

Common mistakes

A minus sign in front of a power

=-2^2 returns 4, not -4. In Microsoft's operator precedence table, negation comes before exponentiation, so Excel squares -2. Write =-(2^2) or =0-2^2 if you mean -4.

#NUM! from SQRT

SQRT cannot take the root of a negative number and shows #NUM!. If only the size matters, wrap the value in ABS: =SQRT(ABS(B2)).

Fractional exponent without brackets

=8^1/3 returns 2.67 because Excel calculates 8^1 and then divides by 3. The cube root is =8^(1/3), which is 2.

#DIV/0! from MOD

If the divisor is 0 or empty, MOD returns #DIV/0!, just like a normal division.

Math functions in German Excel

English	German
POWER	POTENZ
SQRT	WURZEL
ABS	ABS
MOD	REST
SIGN	VORZEICHEN
INT	GANZZAHL
TRUNC	KÜRZEN
CEILING.MATH, FLOOR.MATH	OBERGRENZE.MATHEMATIK, UNTERGRENZE.MATHEMATIK

German Excel separates arguments with semicolons and uses a decimal comma: =REST(B2;C2), =KÜRZEN(-7,8). The caret works the same everywhere. The Excel formulas list has more names.

Which Excel versions have these functions?

The ^ operator, POWER, SQRT, ABS, MOD, SIGN, INT and TRUNC are in every current version of Excel. Microsoft lists CEILING.MATH for Microsoft 365, Excel 2024, 2021, 2019 and 2016, including the Mac versions. The older CEILING and FLOOR functions still work.

FAQ