I am trying to make a finance calculation which works perfectly in Excel. Here is the formula in Cell C5 and proof that it is working in excel giving me a result of -163.49%

But when I make exactly the same calculation in Google Sheets, it gives #NUM error and says that POWER evaluates to an Imaginary Number. See below:

I don't know what imaginary numbers are and can't figure out how to get rid of this error although its working perfectly in excel.

CodePudding user response：

I do not know if CAGR is well defined when the initial value and the end value are of different signs (one negative and the other positive). It is usually applied to metrics such as revenue or the number of registered users which are always positive.

As noted by user11222393, Google Sheets documentation tells:

If base is negative, exponent must be an integer.

In your case, the exponent 1/(C2-F2) === 1/3 which is not an integer.

I think you can make the formula simpler like this:

=(C3 / F3) ^ (1 / (C2 - F2)) - 1

...but that will still not work when the initial value and the end value are of different signs. You can force it like this:

=sign(C3 / F3) * abs(C3 / F3) ^ (1 / (C2 - F2)) - 1

That will give the -163.49% result you wanted, but I am afraid that the result is nonsensical.

(See real number and imaginary number)