Need help with if(and formula..

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
 
J

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)
 
S

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.
 
J

John C

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top