Forumla Help

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

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

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

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.

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

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)

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

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.

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.

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

Originally Posted by streetboarder

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)

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

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