DIV/0 error

N

Nicky

Hi Experts

I want to produce figures for our sales team to show how accurate the
forecasts are...
I have 2 tables - SPLITS_1 which has the forecast in
ACTUAL which has the sales in

I am using the following formula

=IF(SPLITS_1!D64/ACTUAL!D64>1,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64)

this works fine unless there is a zero in one of the tables. I need to be
able to return that if the forecast said 50, and the sales were 0, then it
was 100% wrong also visa versa, otherwise do the calculation... to give the %

Many thanks for your help as always...
 
L

Luke M

Use another IF statement like so:
=IF(ACTUAL!D64=0,"100%
wrong",IF(SPLITS_1!D64/ACTUAL!D64>1,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64))

This should prevent the error message.
 
D

DILipandey

Hi Nicky,

Try the following:-

=IF(ISERROR(IF(SPLITS_1!D64/ACTUAL!D64>1,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64)),"Check
Zero",IF(SPLITS_1!D64/ACTUAL!D64>1,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64))

It will show you the message "Check Zero", if either of the sheets contains
zero. If you don't want this, then you can replace "Check Zero" with "" in
the formula.
Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
[email protected]
[email protected]
New Delhi, India
 
N

Nicky

This partly worked but does not capture all senarios...
ie;


F/Cast Actual Answer
0 110 0.00% this should be 100% wrong as there is no forecast
90 0 "100% > wrong" correct
0 0 "100% > wrong" this should be 0% as there is no forecast or sales

Do I need more If statements... I'm getting a bit lost, or should I be
tackling it a different way?

Many thanks for the help..
 
N

Nicky

HELP...anyone got any suggestions

Nicky said:
This partly worked but does not capture all senarios...
ie;


F/Cast Actual Answer
0 110 0.00% this should be 100% wrong as there is no forecast
90 0 "100% > wrong" correct
0 0 "100% > wrong" this should be 0% as there is no forecast or sales

Do I need more If statements... I'm getting a bit lost, or should I be
tackling it a different way?

Many thanks for the help..
Click to expand...
 
Top