DIV/0 error

[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...

 

[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.

 

[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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[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..

 

[Nicky]

HELP...anyone got any suggestions

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..

 

[Ashish Mathur]

Hi,

I cant read your question very well - it is all jumbled up. Please repost
your question or else mail me your workbook (a sample of the problem) at
(e-mail address removed)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com