If(isna...

I am trying to calculate a CAGR and I want the field to be 0 if there is no value to divide by.

I am using the formula:

=IF(ISNA(+DM241/AM241)^(1/3)-1,0,(+DN241/AN241)^(1/3)-1)

Which returns 0%, but I know that the figure should be 70% as this is what I get when I just put the formula below in.

=(+DM241/AM241)^(1/3)-1

Any suggestions?

Thanks.

The second part of your formula has DN and AN rather than DM and AM as column references.

Thanks,

But when I correct it to...

=IF(ISNA(+DM241/AM241)^(1/3)-1,0,(+DM241/AM241)^(1/3)-1)

it still returns 0 when I know that

=(+DM241/AM241)^(1/3)-1

is 70%

Perhaps:

Please Login or Register  to view this content.

I just added parentheses to the first part to get the ISNA part to evaluate correctly. I can't see another reason why this wouldn't work.