"Curtis" wrote:
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" wrote in message
...
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

"Luke M" wrote:

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Curtis" wrote:

Thanks

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

"Luke M" wrote:

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
A90, and
if true, to do C9/A9-1. This will always results in -1!

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Curtis" wrote:

Why am I getting an error #DIV/0!

Note
a9=0
c9=0

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

Thanks