Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with if(and formula..
=IF(A2=0,IF(B2<0,100,B2/A2),B2/A2)
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with if(and formula..
Or:
=IF(AND(A7=0,B7<0),100,B7/A7) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|