Entering an "IF" formula

[Chris Cullen]

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

 

[Chris Cullen]

Hi

I tried both with no luck. When I paste in the formula it says #N/A.

 

[JoeU2004]

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]

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

 

[Rick Rothstein]

If I understand what you want correctly, put this formula in Column D (note
the D)...

=IF($B5="",0,$C5-$B5)

 

[JoeU2004]

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 are:

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

 

[JoeU2004]

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]

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 Rothstein]

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