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!D641,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...

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

This should prevent the error message.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nicky" wrote:

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!D641,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...

Hi Nicky,

Try the following:-

=IF(ISERROR(IF(SPLITS_1!D64/ACTUAL!D641,SPLITS_1!D64/ACTUAL!D64-1,1-SPLITS_1!D64/ACTUAL!D64)),"Check
Zero",IF(SPLITS_1!D64/ACTUAL!D641,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.)


New Delhi, India


"Nicky" wrote:

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!D641,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...

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



"Luke M" wrote:

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

This should prevent the error message.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nicky" wrote:

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!D641,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...

HELP...anyone got any suggestions

"Nicky" wrote:

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



"Luke M" wrote:

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

This should prevent the error message.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nicky" wrote:

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!D641,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...

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


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Nicky" wrote in message
...
HELP...anyone got any suggestions

"Nicky" wrote:

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



"Luke M" wrote:

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

This should prevent the error message.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nicky" wrote:

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!D641,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...