Formula Error

[Curtis]

Why am I getting an error #DIV/0!

Note
a9=0
c9=0

=IF(AND(A9>0,C9=0),C9/A9-1,IF(AND(A9=0,C9>0),"100%",C9/A9-1))

Thanks

 

[Luke M]

If A9 = 0, but C9<=0, then you are doing the math operation of C9/A9. (which
leads to division by zero).

Note also that the first part of your formula checks if C9=0 and A9>0, and
if true, to do C9/A9-1. This will always results in -1!

 

[Curtis]

Thanks

SO what can I do in those cases where a9=0> I am calculating growth and if
a9=0 and c9>0 then I need to show growth (100%)rather than the error

 

[Luke M]

To properly define this, need to know all the possible outcomes. So far, the
only two outcomes are 100% if A9 =0, else do the formula C9/A9 -1. If this is
true, your formula can be condensed to:
=IF(A9=0,"100%",C9/A9-1)

What I believe is causing confusion is if the value of C9 makes a difference
in what happens. In which case, formula would be something like:
=IF(A9=0,"100%",IF(Some_Check,C9/A9-1,Other_Possible_Result))

for a guess as to what you'd need, based on the statement about growth:
=IF(A9=0,IF(C9>=0,"100%","-100%"),C9/A9-1)

 

[Curtis]

a9 = previous yr number
c9 =current year number

a9 can be <0 or =0 or >0
c0 can be <0 or =0 or >0

Thanks

 

[JoeU2004]

a9 = previous yr number
c9 =current year number
a9 can be <0 or =0 or >0
c0 can be <0 or =0 or >0

First, it is a very bad idea to quote numeric results, "numeric text" like
"100%" as Luke did. It is often the root cause of many problems later on.

The better simple formulation is:

=if(A9=0, 1, C9/A9 - 1)

formatted as Percentage.

But since A9 and C9 might be negative, I suspect you would be happiest with:

=if(A9=0, SIGN(C9), (C9 - A9) / abs(A9))

This has the following results for various values in A9 and C9:

-50 -50 0.00%
-50 -25 50.00%
-50 0 100.00%
0 0 0.00%
0 50 100.00%
50 0 -100.00%
25 50 100.00%
50 50 0.00%
-50 25 150.00%
25 -50 -300.00%


----- original message -----

 

[Curtis]

thx

First, it is a very bad idea to quote numeric results, "numeric text" like
"100%" as Luke did. It is often the root cause of many problems later on.

The better simple formulation is:

=if(A9=0, 1, C9/A9 - 1)

formatted as Percentage.

But since A9 and C9 might be negative, I suspect you would be happiest with:

=if(A9=0, SIGN(C9), (C9 - A9) / abs(A9))

This has the following results for various values in A9 and C9:

-50 -50 0.00%
-50 -25 50.00%
-50 0 100.00%
0 0 0.00%
0 50 100.00%
50 0 -100.00%
25 50 100.00%
50 50 0.00%
-50 25 150.00%
25 -50 -300.00%


----- original message -----