There are two exponential function options in Excel. Like many things in Excel, what looks simple has some traps for the unwary. We’ll explain why the Power() and ^ don’t always give the same or even correct result.
Exponentiation is an operation in mathematics, written as an, involving the base a and an exponent n. Assuming n is a positive integer, exponentiation refers to repeated multiplication of the base a, n times. So, 102 would be the same as 10 x 10.
Excel has two exponent functions. Power () function or the ^ character shortcut. A lot of people use ^ all the time and ignore the full function but that can be a mistake.
For positive (above zero) integers both Power() and ^ work fine. For negative bases and fractions, Power() is the safer and accurate choice.
Here we’ll show you how to apply the calculation using both functions than the ^ trap for the unwary.
Using the Power() function
The syntax for the Power() function is:
=POWER( number , power )
Number – The base number to raise to a power/exponent.
Power – The exponent to raise the base number to.
So, if we are trying to find the value of 910 in excel, we can use the formula =POWER(9,10)
Say we have a column of base numbers and exponents, we can also easily calculate this in Excel using the cell number instead, like so. By simply hovering your mouse in the bottom right-corner of the results cell (C6) until you see the small cross, you will be able to drag the formula down to the other cells. Alternatively, you can also double-click your mouse when the cross appears and Excel will auto-fill the remaining cells in the column.
The fill function will ensure that Excel returns the exponential value based on the corresponding cells.
Using the ^ function
Users will find the ^ function performs the same way as the Power () Formula in Excel.
So, if we are trying to find the value of 910 in excel, we can use the formula =9^10
Here we’ve applied the same format as per our previous Power() example. As you can see it returns the same results.
Negative Exponent
What about if it’s a negative exponent such as minus 10?
Well, the ^ function has a way around it by simply using the formula =9^-10
We had tested this on the Power() function too and both have returned the same results.
Negative Base Problem
But if the Base number is negative, the ^ function has a problem we’ve explained in Excel’s order of calculation and BODMAS . Excel does negation before exponents, which isn’t the standard order, so -10^2 in Excel gives 100 (-10 x -10) not the correct -100 (10 x 10 then negative).
Use the Power() function to avoid that trap e.g Power(-10,2) whenever a negative base is possible.
Fraction Exponent or Base Number
Likewise, if the exponent or Base number is a fraction, both the ^ function and Power() function will calculate within Excel without any trouble.
The ^ function simply uses the formula =9^(2/5) but not =9^2/5 !!!
Safer to use the Power() function for clarity e..g =POWER(9,2/5).
Which to use? Power() or ^
So, which formula should you use? Well, it would be up to personal preference. Both work in similar ways and the Power() function is considered just an alternate option to the ^ function. Some may find the exponent operator (^) more useful during more complicated or longer equations.
If working with negative or fractional numbers, Power() is the better and safer option to avoid any order of calculation issues.
Excel’s order of calculation and BODMAS
Excel Function Keys explained
PERCENTRANK Excel Functions
Random Numbers and more in Excel
