Need help with if(and formula..

[Raedaan]

Doing a percentage change column and can't get the formula to give me
100% change as answer basically if column b=0 and column c=0 then I
can make the formulas give nothing...but if column b=0 and column c<>0
then the change should come out as 100% because there is a difference
from the previous year.... I'm need correct formula...here's what
hasn't worked:

=IF(AND(B2=0,C2<>"0",100),C2/B2-1,NA())
=IF(B2="0", "", IF(B2=0, 0,(C2/B2-1)))

Help.... column b represents current year numbers and column c
represents next years budget

 

[TomPl]

=IF(A2=0,IF(B2<>0,100,B2/A2),B2/A2)

 

[TomPl]

Or:

=IF(AND(A7=0,B7<>0),100,B7/A7)

 

[John C]

Your formula could be as simple as:
=(C2-B2)/B2
The problem you are encountering is that, mathematically, an increase from 0
to any number (50 for example) is an infinite percentage. Let's look at 2
examples:
Ex1: C2=50, B2=10, result is 400%, an increase of 4 times the original amount
Ex2: C2=50, B2=0, result is #DIV/0, but you want it to be an increase of
100%, despite the fact it was a larger increase than Ex1:
If you are trying to show a column of percentage changes, you could just
keep it blank for that reference, or even put in a text saying New Price or
something.
i.e.:
=IF(B2=0,IF(C2=0,"Both 0","New Price"),C2-B2/B2)

 

[Tanya M]

=IF(ISERROR(IF(AND(B2=0,C2>0),100%,C2/B2-1)),"",IF(AND(B2=0,C2>0),100%,C2/B2-1))

 

[Sheeloo]

Try
=IF(C1=0,0,IF(B1=0,100,(C1-B1)*100/B1))

You may change (C1-B1)*100/B1 to what you want to reflect the number you want.

 

[John C]

Erg, missed a parenthesis in my formula:
=IF(B2=0,IF(C2=0,"Both 0","New Price"),(C2-B2)/B2)