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