Excel: How to Calculate Odd and Even Negative Nth Roots

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

When you raise a negative value to a fractional exponent in Microsoft Excel, you may receive a #NUM error value. This error value may occur because the solution may not be defined in the real number system. To calculate the nth root of a negative number, use the appropriate formula below.

Note: The formulas are different for even (1/2, 1/4,...) and odd (1/3, 1/5,...) roots.

MORE INFORMATION

Calculating Even Roots of Negative Numbers

Although even roots of negative numbers are undefined in the real number system, they do have solutions in the complex number system. To calculate these roots in Microsoft Excel, use complex number functions. The following example demonstrates how to calculate the complex formula -25^(1/8):

 A1: =COMPLEX(-25,0)               B1: convert to a complex number
 A2: =IMSQRT(IMSQRT(IMSQRT(a1)))   B2: same as raising to power of 1/8

Note: This formula only works for roots that are a power of 2 (that is, 2, 4, 8,...).

For even roots that are not necessarily powers of 2, use the following formula:

  A1: <Real part>
  A2: <Imaginary part>
  A3: =COMPLEX(A1,A2)
  A4: =IMABS(A3)
  A5: =IMARGUMENT(A3)
  A6: <desired root> (2, 4, 6, 10,...)
  A7: <number of iterations>
  A8: <Constant K> goes from 0,1,...,(root-1)
  A9: =COS(A5/A6+A7)*(A4^(1/A6))
 A10: =SIN(A5/A6+A7)*(A4^(1/A6))
 A11: =COMPLEX(A9,A10)

1. Type the real part of you number in cell A1 and type the imaginary part of your number in A2 (if your number does not contain any imaginary parts, type 0 (zero) in cell A2).

2. In cell A6, type the desired root.

3. To get all possible results, enter values for K ranging from 0 to the desired root minus 1 (root-1).

   Note: Each time you enter a new value for K in cell A8, Microsoft Excel calculates the result. For this formula to work correctly, the Calculation option must be set to automatic (from the Options menu, choose Calculation and select the Automatic option under Calculation).

Calculating Odd Roots of Negative Numbers

Odd roots do exist in a real number system and can be calculated by taking the odd root of the absolute value of the negative number and multiplying the result by -1 (negative one). For example, to calculate the formula - 25^(1/3), follow the steps below:

1. Find the absolute value of the negative number.

2. Find the nth root of the absolute value.

3. Multiply the answer by -1.

   A1: =(ABS(-25)^(1/3))*-1

If you have a column containing mixed positive and negative numbers, and you want to calculate the nth odd roots for each of them, use the following formula

 =IF(ISERROR(CellRef^(1/3)),(ABS(CellRef)^(1/3)*-1),CellRef^(1/3))

REFERENCES

"Mathematical Applications," Ronald J. Harshbarger and James J. Reynolds, D.C. Heath and Company, 1985, Massachusetts.

"Microsoft Excel Function Reference," version 4.0, pages 60, 228-235.