Excel: How to Calculate Odd and Even Negative Nth Roots

ID: Q93733

The information in this article applies to:

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

Cell A2 returns the value 1.381522133 + 0.572245204i.

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)

To use the above formula:

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).

In cell A6, type the desired root.

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).

The result of each iteration appears in cells A9 to A11.

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:

Find the absolute value of the negative number.

Find the nth root of the absolute value.

Multiply the answer by -1.

Your formula should resemble the following example:


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

This formula will return -2.92402.

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))

where "CellRef" is the reference to the cell that contains the number. In this formula, CellRef should reference the first cell in your column of negative and positive numbers. To get results for each of the numbers in your column of numbers, fill the formula down as needed.

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.

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.00a 5.0 calculation error


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 30, 1999