Forumla Help

[streetboarder]

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

 

[daddylonglegs]

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

 

[streetboarder]

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

 

[daddylonglegs]

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.

 

[streetboarder]

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

 

[daddylonglegs]

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)

 

[streetboarder]

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

 

[daddylonglegs]

Which one?

This formula

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

is the equivalent of

=IF(C26<>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.

 

[streetboarder]

Thanks. I can't tell you how much I have learned from this site since
this weekend!

So then the original formula
=IF(B38="","",IF(B4,B38/B4-1,IF(B38=0,0,"unplanned sales"))) 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]

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

 

[daddylonglegs]

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

Yes, your assumptions are almost all correct, except in the above. The
comma is simply part of the syntax

If you use an expression like

=IF(B4,do this, do that)

in excel 0 is equivalent to FALSE so if B4 is equal to zero we get "do
that", if B4 is any other number we get "do this"

It may be more transparent written as

=IF(B4=0,do that, do this)

 

[streetboarder]

Ok. got that part thanks. So does excel just assume that the following
is true?

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

Just the cell name followed by comma it just assumes: C26=0?

Thanks again,
Josh

 

[daddylonglegs]

No

with that formula

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

It depends on the value in C26. If C26 is not zero that is deemed to be
TRUE so the first operation is performed, i.e. C60/C26-1

If C26 is zero that is deemed to be FALSE so 0 is returned.

This way you never divide by zero so you never get a #DIV/0! error