Powers and Roots in Excel 2007: The POWER and SQRT Functions

By Greg Harvey from Excel 2007 All-In-One Desk Reference For Dummies

In Excel 2007, you use the POWER function to raise a number to a certain power, and SQRT to find the square root of a number. These functions are located with the Math & Trig functions on the Ribbon’s Formulas tab or in the Select a Category list in the Insert Function dialog box.

POWER

Although you can use the caret (^) operator to build a formula that raises a number to any power, the POWER function accomplishes the same thing. For example, to build a formula that raises 5.9 to the third power (that is, cubes the number), you can use the exponentiation operator as in:

=5.9^3

You can have Excel perform the same calculation with the POWER function by entering this formula:

=POWER(5.9,3)

In either case, Excel returns the same result, 205.379. The only difference between using the exponentiation operator and the POWER function occurs on that rare occasion when you have to raise a number by a fractional power. In that case, you need to use the POWER function instead of the caret (^) operator to get the correct result. For example, suppose that you need to raise 20 by the fraction 3/4; to do this, you build the following formula with the POWER function:

=POWER(20,3/4)

To use the exponentiation operator to calculate the result of raising 20 by the fraction 3/4, you can convert the fraction into decimal form as in:

=20^0.75

You would also use the POWER function when you need to use an IF statement to determine the exponent, as in this formula:

=POWER(10,IF(A1="Decrease",1/2,2))

SQRT

The SQRT calculates the square root of any number that you specify as its sole number argument. For example, if you use the SQRT function to build the following formula in a cell:

=SQRT(144)

Excel returns 12 to that cell.

The SQRT function can’t deal with negative numbers, so if you try to find the square root of a negative value, Excel returns a #NUM! error value to that cell. To avoid such a nuisance, you need to use the ABS (for absolute) math function, which returns the absolute value of a number (that is, the number without a sign). For example, suppose that cell A15 contains –$49.00, showing that it’s something you owe, and you want to return the square root of this number in cell A16. To avoid the dreaded #NUM! error, you nest the ABS function inside the SQRT function. The ABS function returns the absolute value of the number you specify as its sole argument (that is, the value without its sign). To nest this function inside the SQRT function, you create the following formula:

=SQRT(ABS(A15))

Excel then returns 7 instead of #NUM! to cell A16 because the ABS function removes the negative sign from the 49.00 before the SQRT function calculates its square root (remember that Excel always performs the calculations in the innermost pair of parentheses first).