Need help with if(and formula..
 

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

Need help with if(and formula..
 

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

Need help with if(and formula..
 

Or:

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

Need help with if(and formula..
 

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)
--
** John C **


"Raedaan" wrote:

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

Need help with if(and formula..
 

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



"Raedaan" wrote:

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

Need help with if(and formula..
 

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.

"Raedaan" wrote:

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


"Raedaan" wrote:

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

Need help with if(and formula..
 

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

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"John C" wrote:

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)
--
** John C **


"Raedaan" wrote:

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