How to use the POWER function

What is the POWER function?

The POWER function calculates a number raised to a power. You can also use the caret ^ character to perform a exponentiation in an Excel formula.

1. Introduction

What is exponentiation?

Exponentiation is a mathematical operation involving exponents. It means raising a number, known as the base, to a specified power. The exponent indicates how many times to multiply the base by itself.

base^exponent

Roots are the inverse operation, giving the base when the power and number are known. For example,

x⁵ = 3125

3125^(1/5) = 5

x = 5

5⁵ = 3125

This makes it possible to calculate roots using the POWER function, however, to calculate square roots you can also use the SQRT function.

How are roots and logarithms related?

Roots and logarithms are inverse mathematical operations:

A root "undoes" exponentiation, finding the base number when given the power and result.
For example, √16 = 4, since 4² = 16. The root reveals the base of 4. √16 = 16^(1/2)

A logarithm is the inverse of exponentiation as well, returning the exponent that a base must be raised to in order to produce a number.
For example, =log(8,2) = 3, since 2³ = 8. The logarithm returns the exponent 3.

Logarithms calculate the exponent and roots calculate the base. This example demonstrates how to calculate the exponent:

5^x = 3125

LOG5(3125) = 5

x = 5

5⁵ = 3125

What are the other logarithmic functions in Excel?

Excel function	Description
LOG	Returns the logarithm with a given base.
LN	Returns the natural logarithm (base e) of a number
LOG10	Returns the base-10 logarithm of a number

The corresponding inverse functions for the logarithm functions in Excel:

Logarithm	Power to
LOG	Arbitrary base, basex or POWER(number, power)
LN	EXP(number)
LOG10	10x or POWER(10,x)

What are the exponentiation rules?

Product Rule: x^m * xⁿ = x^(m+n)

Exponents with the same base are multiplied when multiplying terms.

Power Rule: (x^m)ⁿ = x^(m*n)

When raising a power to a power, the exponents are multiplied.

Power of a Product: (ab)ⁿ = aⁿ * bⁿ

Exponents distribute to all factors in a product.

Quotient Rule: x^m / xⁿ = x^(m-n)

Exponents with the same base subtract when dividing terms.

Power of Quotient: (a/b)ⁿ = aⁿ / bⁿ

Exponents distribute to the numerator and denominator when raising a quotient.

Any base raised to the 0 power equals 1.

Negative Exponent Rule: x^-n = 1 / xⁿ

2. Syntax

POWER(number, power)

number	Required. The number you want to raise.
power	Required. The exponent to which the number is raised.

3. Example 1

The first example has the number in cell B3 and the power in cell C3. They are 5 and 2 respectively. The formula is in cell E3:

The result of 5² is 25, here is how:
5² = 5 * 5 = 25

The second example has the number 2 in cell B4 and the power 8 in cell C4. The formula is in cell E4:

The result of 2⁸ is 256, here is how:
2⁸ = 2 * 2 * 2* 2 * 2 * 2 * 2 * 2 = 256

The third example has the number in cell B5 and the power in cell C5. They are 10 and 2 respectively. The formula is in cell E5:

The result of 10² is 100, here is how:
10² = 10 * 10 = 100

The fourth example has the number 10 in cell B6 and the power 6 in cell C6. The formula is in cell E6:

The result of 10⁶ is 256, here is how:
10⁶ = 10 * 10 * 10 * 10 * 10 * 10 = 1,000,000

The fifth example has the number in cell B7 and the power in cell C7. They are 3 and 3 respectively. The formula is in cell E5:

The result of 3³ is 27, here is how:
3³ = 3 * 3 * 3 = 9 * 3 = 27

The sixth example has the number 4 in cell B6 and the power 3 in cell C6. The formula is in cell E6:

The result of 4³ is 64, here is how:
4³ = 4 * 4 * 4 = 64

4. Example 2

What is the area of a circle if the radius is 5 units?

The area of a circle is found by multiplying the mathematical constant pi (π) by the square of the circle's radius.
A = π * r²
A = the area of the circle
r = the radius of the circle
π = pi

Formula in cell C23:

The area is approx. 78.54 square units based on a radius of 5 units. The PI function lets you calculate number pi, it has no arguments. The POWER function takes 5 to the power of 2 which is 25.

Lets plug the values and calculate the area manually. Here is how:
A = π * r²
A = 3.14159265358979 * 5²
A = 3.14159265358979 * 25
A = 78.5398163397448

5. Example 3

A bacteria population doubles ever 1.5 hour. How many cells will you find after 7 hours if the start population is 50?

We need to find a pattern in the growth of the bacteria population over time and then use it to calculate the population after 7 hours.
Given information:

• The bacteria population doubles every 1.5 hours.
• The initial population is 50 cells.

The math formula has to be: Population = 50 * 2^(t/1.5)

Formula in cell C21:

The formula returns 1269 cells after 7 hours with a start population of 50. The image above shows a chart illustrating an exponential blue cure, the x-axis title is hours and ranges from 0 to 7 hours. The y-axis title is population and ranges from 0 to 1400 cells.

Go to value 7 on the x-axis, follow an imaginary vertical line up  until it intersects with the blue curve. The follow an imaginary horizontal line to the left until you find the y-axis. The y-axis value is near 1300 which corresponds to the calculated value in cell C21.

6. Example 4

If you invest $1,000 today in an account that pays 8% annual interest compounded annually, what will be the future value of your investment after 20 years?

We need to model a math function that follows the growth of the investment over time and then use it to calculate the amount after 20 years.
Given information:

• The investment compounds annually with an rate of 8%.
• The initial investment is 1,000.

The math formula has to be: Amount= 1000 * 1.08^y

Formula in cell C21:

The formula returns 4,660 after 20 years with an initial investment of 1,000. The image above shows a chart illustrating an exponential blue cure, the x-axis represents years and ranges from 0 to 20 years. The y-axis title represents the amount and ranges from 1,000 to 5,000.

Go to year 20 on the x-axis, follow an imaginary vertical line up until it intersects with the blue curve. The follow an imaginary horizontal line to the left until you find the y-axis. The y-axis value is near 4600 which corresponds to the calculated amount in cell C21.

Comments

The ^ operator is easier to use, example 10^2 = 100, number^power.

7. Function not working

The POWER function returns

• #VALUE! error if any of the arguments is non-numeric.
• #NAME? error if you misspell the function name.
• propagates errors, meaning that if the input contains an error (e.g., #VALUE!, #REF!), the function will return the same error.

7.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

7.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue..

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference B3 converted to hard-coded value using the F9 key. The POWER function requires numerical values which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

7.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

'POWER' function examples

Functions in 'Math and trigonometry' category

The POWER function function is one of 62 functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Leave a Comment