3

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? – Vidar S. Ramdal Jan 21 '14 at 8:48
4

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 :) – puretppc Jan 21 '14 at 22:46
1

Use the following notation to perform these types of calculations.

Formula

=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: enter image description here

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.

References

  1. Dr. Math
  2. There's no bug

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.