Need some help.

I have forecasted sales, actual sales and % variance.

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

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: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://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,"unplanned sales"))

format as percentage


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://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 "unplanned sales".
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: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=503708

streetboarder

Personally I think it's a good idea to show "unplanned sales" - 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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://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,"unplanned sales"))

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: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=503708

You could make these alterations

=IF(B38="","",IF(B4,B38/B4-1,IF(B38=0,0,"unplanned sales")))

and

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503708