Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
I know this is a simple question but I cannot figure it out!
What function would I write if I want to do this? I want to write a formula so that data in Column E will display 0% when there is no value entered in Column B (a forecast ). I also want to keep the existing formula in Column E -calculating the percentage of change from Column A (actual) and Column B (forecast). Sorry - I know this will seem simple to Excel pros. I am not one of you:) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
Chris Cullen wrote:
I know this is a simple question but I cannot figure it out! What function would I write if I want to do this? I want to write a formula so that data in Column E will display 0% when there is no value entered in Column B (a forecast ). I also want to keep the existing formula in Column E -calculating the percentage of change from Column A (actual) and Column B (forecast). Sorry - I know this will seem simple to Excel pros. I am not one of you:) Perhaps, in E2: =IF(ISBLANK(B2),0,B2/A2-1) Or consider: =IF(ISBLANK(B2),NA(),B2/A2-1) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
Hi
I tried both with no luck. When I paste in the formula it says #N/A. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
What is in cell B2? If it is a formula, please state it and the result.
Chris Cullen wrote: Hi I tried both with no luck. When I paste in the formula it says #N/A. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
"Chris Cullen" wrote:
I want to write a formula so that data in Column E will display 0% when there is no value entered in Column B (a forecast ). I also want to keep the existing formula in Column E -calculating the percentage of change from Column A (actual) and Column B (forecast). Apparently you have some formulas ("keep the existing formula"). Please post them. It might help to resolve the ambiguity of your English explanation. No slight intended; English is an imprecise language for explaining most mathematical relationships. Assuming you want to calculate the percentage change row-by-row, and assuming you really want the percentage change of actual over forecast (i.e. "actual is x% of forecast"), then try: =IF(B2="",0,IF(B2=0,SIGN(A2),(A2-B2)/B2)) That also addresses a problem you did not mention, namely: a forecast of zero. There is no correct mathematical answer in that case. I arbitrarily treat any actual positive amount as 100% gain over forecast, actual zero as 0% over forecast, and any actual negative amount as -100% of forecast. PS: Personally, I would treat "no forecast" (B2 is empty or "") the same as a forecast of zero. That is, I would use the arbitrary choices I made above. To that end, the formula might be: =IF(N(B2)=0,SIGN(A2),(A2-B2)/B2) PS: If actual and forecast might have opposite signs(one negative, the other postivie), the better formula might be: =IF(N(B2)=0,SIGN(A2),(A2-B2)/ABS(B2)) ----- original message ----- "Chris Cullen" wrote in message ... I know this is a simple question but I cannot figure it out! What function would I write if I want to do this? I want to write a formula so that data in Column E will display 0% when there is no value entered in Column B (a forecast ). I also want to keep the existing formula in Column E -calculating the percentage of change from Column A (actual) and Column B (forecast). Sorry - I know this will seem simple to Excel pros. I am not one of you:) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
The table:
Column A: Product Group (Verical Axis list) Column B: Forecasted Revenue Column C: Actual Revenue Column D: Difference (=$C5-$B5) Column E: % Change (=$D5/$C5) So when there is a value in Column B (a forecasted revenue for that category) I am all set in Column E. What I need to know is what formula to insert to allow for categories that do not have a forecasted revenue - in this case I want the value in Column E to be 0%. Thanks for any help |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
If I understand what you want correctly, put this formula in Column D (note
the D)... =IF($B5="",0,$C5-$B5) -- Rick (MVP - Excel) "Chris Cullen" wrote in message ... The table: Column A: Product Group (Verical Axis list) Column B: Forecasted Revenue Column C: Actual Revenue Column D: Difference (=$C5-$B5) Column E: % Change (=$D5/$C5) So when there is a value in Column B (a forecasted revenue for that category) I am all set in Column E. What I need to know is what formula to insert to allow for categories that do not have a forecasted revenue - in this case I want the value in Column E to be 0%. Thanks for any help |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
"Chris Cullen" wrote:
What I need to know is what formula to insert to allow for categories that do not have a forecasted revenue - in this case I want the value in Column E to be 0%. Okay, you just want an answer to your question. GIGO. Try: =if($B5="",0,$D5/$C5) It is important to test $B5="", not ISBLANK($B5). The issue is: B5 might be empty, or it might simply appear blank because an IF() expression returns "" (very common). The function name ISBLANK() is a misnomer; it is true only when B5 is empty. Testing $B5="" is more versatile; it is true when B5 is "" as well as when it is empty. But you have other mistakes. Let me know if you would like some help with those, too. They a 1. The formula in D5 needs to change if B5 might be "" as well as empty. 2. The formula in E5 is nonsensical based on the difference computed in D5. (D5 is the right difference to compute, IMHO.) 3. Forecast and actual, which are revenue, can be negative (loss), zero (break even) as well as positive (profit). Why not fix the formula in E5 to accommodate all possibilities? ----- original message ----- "Chris Cullen" wrote in message ... The table: Column A: Product Group (Verical Axis list) Column B: Forecasted Revenue Column C: Actual Revenue Column D: Difference (=$C5-$B5) Column E: % Change (=$D5/$C5) So when there is a value in Column B (a forecasted revenue for that category) I am all set in Column E. What I need to know is what formula to insert to allow for categories that do not have a forecasted revenue - in this case I want the value in Column E to be 0%. Thanks for any help |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
"Rick Rothstein" wrote:
If I understand what you want correctly, put this formula in Column D (note the D)... =IF($B5="",0,$C5-$B5) A better solution than mine insofar as it kills two birds with one stone. But there are still problems with the Chris's computations. The following problems remain (numbered based on my previous reply): 2. The formula in E5 is nonsensical based on the difference computed in D5. (D5 is the right difference to compute, IMHO.) 3. Forecast and actual, which are revenue, can be negative (loss), zero (break even) as well as positive (profit). Why not fix the formula in E5 to accommodate all possibilities? ----- original message ----- "Rick Rothstein" wrote in message ... If I understand what you want correctly, put this formula in Column D (note the D)... =IF($B5="",0,$C5-$B5) -- Rick (MVP - Excel) "Chris Cullen" wrote in message ... The table: Column A: Product Group (Verical Axis list) Column B: Forecasted Revenue Column C: Actual Revenue Column D: Difference (=$C5-$B5) Column E: % Change (=$D5/$C5) So when there is a value in Column B (a forecasted revenue for that category) I am all set in Column E. What I need to know is what formula to insert to allow for categories that do not have a forecasted revenue - in this case I want the value in Column E to be 0%. Thanks for any help |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
Or, if you want to report the $C5-$B5 value in Column D even if $B5 is
blank, then move the IF test to Column E... =IF($B5="",0,$D5/$C5) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If I understand what you want correctly, put this formula in Column D (note the D)... =IF($B5="",0,$C5-$B5) -- Rick (MVP - Excel) "Chris Cullen" wrote in message ... The table: Column A: Product Group (Verical Axis list) Column B: Forecasted Revenue Column C: Actual Revenue Column D: Difference (=$C5-$B5) Column E: % Change (=$D5/$C5) So when there is a value in Column B (a forecasted revenue for that category) I am all set in Column E. What I need to know is what formula to insert to allow for categories that do not have a forecasted revenue - in this case I want the value in Column E to be 0%. Thanks for any help |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering an "IF" formula
LOL... Your postings just popped up on my newsreader and they crossed with
an afterthought posting of mine in which I proposed the same formula as you did (but I qualified the suggestion asking the OP to consider what the values should really be if $B5 is blank). -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "Rick Rothstein" wrote: If I understand what you want correctly, put this formula in Column D (note the D)... =IF($B5="",0,$C5-$B5) A better solution than mine insofar as it kills two birds with one stone. But there are still problems with the Chris's computations. The following problems remain (numbered based on my previous reply): 2. The formula in E5 is nonsensical based on the difference computed in D5. (D5 is the right difference to compute, IMHO.) 3. Forecast and actual, which are revenue, can be negative (loss), zero (break even) as well as positive (profit). Why not fix the formula in E5 to accommodate all possibilities? ----- original message ----- "Rick Rothstein" wrote in message ... If I understand what you want correctly, put this formula in Column D (note the D)... =IF($B5="",0,$C5-$B5) -- Rick (MVP - Excel) "Chris Cullen" wrote in message ... The table: Column A: Product Group (Verical Axis list) Column B: Forecasted Revenue Column C: Actual Revenue Column D: Difference (=$C5-$B5) Column E: % Change (=$D5/$C5) So when there is a value in Column B (a forecasted revenue for that category) I am all set in Column E. What I need to know is what formula to insert to allow for categories that do not have a forecasted revenue - in this case I want the value in Column E to be 0%. Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Format cell to display "Y" or "N"when entering a 1 or zero | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |