software

Need some help.

I have forecasted sales, actual sales and % variance.

The forumla I have currently is: =IF(B38=0,quot;quot;,B38/B4). I need it to
show the following: if forecasted amount is o and actual is greater
than 1.00 it should read quot;unplanned salesquot;. If actual is 0 and Forecast
is greater than 1 I need it to read quot;-100%quot;.

Otherwise I need to dived actual sales by forecast amount to get the
actual % variance.

Does this make sense? Can someone help me?

Thanks,
Josh--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=503708
I would have thought that variance would be actual/forecast -1 so that
you get zero when actual and forecast are the same, therefore I suggest
this

=IF(B4,B38/B4-1,IF(B38=0,0,quot;unplanned salesquot;))

format as percentage--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=503708
Works Perfect.

Before I was having to look at 100% anything above was a variance
either way but this makes much more sense.

Just out of courisity does it make sense to put in quot;unplanned salesquot;.
Since I have little experience with this would you recommend something
else or is it fine as I have it.

Thanks again,
Josh--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=503708
streetboarder

Personally I think it's a good idea to show quot;unplanned salesquot; - there
isn't really any correct numerical result in that situation so I think
it's eminently sensible to tell it like it is - there were some sales
when none were forecast.--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=503708
Ok. Everything is working great but this has led me to two new follow up
questions (both on the same sheet)

Question 1:
=IF(B4,B38/B4-1,IF(B38=0,0,quot;unplanned salesquot;))

Is there a way in the above formula to say if B38 is empty to input a
blank cell but If cell B38 = $0 or greater than complete the equation?I am currently working on January’s sales and have copied the above
formula over 12 months. So in February I have empty cells for Actual
Sales but it is still computing a percentage when I would like the cell
to be blank. Once February starts I will input 0.00 so that the formulas
will begin to work.

Does that make any sense?

Ok next question:

I have sub-totals for different categories of sales. For instance
Distributors, Retailers, Direct, etc. that I am tracking. I take the
total of Actual Sales for the category and divide by Forecasted Sales –
1. However I have months where I will have zero in the forecasted
category so I am getting the “#DIV/O!” error. I am sure there is a
simple change I can make to the formula to account for this. Any
takers?

=C60/C26-1

Found this site today and already at three questions…sorry everyone and
thanks for the input.

Josh--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=503708
You could make these alterations

=IF(B38=quot;quot;,quot;quot;,IF(B4,B38/B4-1,IF(B38=0,0,quot;unplanned salesquot;)))

and

=IF(C26,C60/C26-1,0)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=503708
Perfect!

If you would can you please explain the forumla in english so I
understand what it is doing? This will help me finish some other fomula
questions on my own...

Thanks!
Josh--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=503708
Which one?

This formula

=IF(C26,C60/C26-1,0)

is the equivalent of

=IF(C26lt;gt;0,C60/C26-1,0)

If C26 is not zero C60/C26-1 otherwise 0

The usual way you get a #DIV/0! error is, unsurprisingly, when you try
to divide by zero so this means that the calculation is carried out
except when C26 is zero, in which case a zero is returned.--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=503708
Thanks. I can't tell you how much I have learned from this site since
this weekend!

So then the original formula
=IF(B38=quot;quot;,quot;quot;,IF(B4,B38/B4-1,IF(B38=0,0,quot;unplanned salesquot;))) would be
equivalent to:

If B38 is empty put an empty cell if B38 has anything move to next if,

If B4 is not equal to zero (is that what the comma means?) then dived
B38 by B4 minus 1. If that statement is not true than move to the final
if

If B38 equals zero than input zero

Is that correct?

Thanks again.--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=503708
Can someone please verify my question above? Just want to make sure I
have it right before I get all If statement crazy...

Thanks,
Josh--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=503708