Math
POWER function: calculate exponents, reciprocals, and Nth roots
Raise numbers to positive, zero, negative, and fractional (1/N) powers with POWER—and guard even roots of negative numbers.
One function for powers, roots, and reciprocals
POWER(number, power) computes number^power—identical to the caret operator (^) but often easier to read inside nested engineering or CAGR formulas.
Passing a fraction like 1/3 computes the cube root (POWER(27, 1/3) = 3), passing 0 returns 1, and passing a negative exponent computes the reciprocal (POWER(4, -2) = 1/16 = 0.0625).
| Base (A) | Exponent (B) | POWER(A, B) | Meaning |
|---|---|---|---|
| 2 | 5 | 32 | 2 multiplied 5 times |
| 27 | 1/3 | 3 | Cube root of 27 |
| 4 | -2 | 0.0625 | Reciprocal 1 / 4^2 |
| 95 | 0 | 1 | Zero exponent identity |
Calculate Compound Annual Growth Rate (CAGR) with POWER
Growing from 800 to 1,064.80 over 3 years equals POWER(1064.8 / 800, 1/3) - 1 = POWER(1.331, 1/3) - 1 = 0.10 (10.0% per year).
=ROUND(POWER(D2 / C2, 1 / E2) - 1, 4)Guard fractional roots of negative bases
Raising a negative number to a fractional power such as 0.5 (square root of -4) has no real-number result. Checking AND(A2<0, MOD(B2,1)<>0) prevents non-real domain failures.
This guard is deliberately conservative: some negative-base fractional powers, such as an odd root, do have a real mathematical result. The label here means the POWER calculation is unsupported by this pattern, not that every fractional power of a negative number is non-real.
=IF(AND(A2<0, MOD(B2, 1)<>0), "Non-real root", POWER(A2, B2))Try the example
Use the original values or change them to test your understanding.