Need help with if(and formula..

  • Thread starter Thread starter Raedaan
  • Start date Start date
R

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
 
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)
 
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.
 
Erg, missed a parenthesis in my formula:
=IF(B2=0,IF(C2=0,"Both 0","New Price"),(C2-B2)/B2)
 
Back
Top