Exponential calc choices and trap in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address