Cube rooting a negative number does not work?

I tried using this formula for cube rooting but it doesn't seem to work.

=ROUND(C38^(1/3))

The value of C38 is -2.

I'm getting the following error:

Imaginary numbers are not supported in real sheets.

Shouldn't cube roots logically work without being imaginary, whether it's negative or positive?

• Could you share the spreadsheet with us? Or another spreadsheet demonstrating the problem? Jan 21 '14 at 8:48

I am not sure why it's not supported but you can bypass it this way:

=ROUND(IF(C38 < 0,-1*ABS(C38)^(1/3),C38^(1/3)))

That would be your formula for rounding a cube root.

• Wow it actually works thanks :) Jan 21 '14 at 22:46

Use the following notation to perform these types of calculations.

=minus(a^(b/c))

Research

I thought it was a rather odd behavior of Google Spreadsheets and therefore I started to investigate the situation.

It started by performing the calculation in the Google Calculator: There, the notation

-2^(1/3)

was re-written to

-(2^(1/3))

This solves your problem immediately. Entering the last formula (with ROUND()) in Google Spreadsheets, will give you : -1. But why?

Why

In the first reference, Dr. Math is explaining the matter in detail. Bottom line is that the calculation is returning an imaginary number, like the cube root of -1.

To avoid these calculations, the minus is simply extracted from the calculation and "added" afterwards.