working out % of correctness gives #DIV/0!
 

Hi,
can nayone help please?(using excel 2003)
I am using a table, part of which is shown below, which needs to show the %
of correctness of predictions given for a period of time. The problems come
if I need to divide by 0 or if both figures are 0.
currently I am using: =IF((E7E8),E8/E7,E7/E8) which gives the correct
answer as long as there are no zeros.
I have tried using =if(iserror(formulae),0,formulae) but it does not work.
I know that I need to check for zeros first but not sure how to check in
both E8 and E7 or how to get the correct answer if only 1 is a zero.

International Trade Advisor
G. Symonds J. Dorman R. Partos M. Brodin R Harvey
April Prediction 500 2500 1000 3400 1500
Actual 3000 2000 1500 2300 1000
% correct 600% 80% 150% 68% 67%
May Prediction 500 500 1000 500 1000
Actual 400 0 500 0 500
% correct 80% 0% 50% 0% 50%
June Prediction 1 0 1000 500 1000
Actual 400 0 500 0 500
% correct 0% #DIV/0! 50% 0% 50%

--
any help gratefully received
thanks
carrach

working out % of correctness gives #DIV/0!
 

Hi,
I assume if the cell has 0 you want to show 0

=IF(or(E7=0,E8=0),0,if(E7E8),E8/E7,E7/E8))

"Carrach" wrote:

Hi,
can nayone help please?(using excel 2003)
I am using a table, part of which is shown below, which needs to show the %
of correctness of predictions given for a period of time. The problems come
if I need to divide by 0 or if both figures are 0.
currently I am using: =IF((E7E8),E8/E7,E7/E8) which gives the correct
answer as long as there are no zeros.
I have tried using =if(iserror(formulae),0,formulae) but it does not work.
I know that I need to check for zeros first but not sure how to check in
both E8 and E7 or how to get the correct answer if only 1 is a zero.

International Trade Advisor
G. Symonds J. Dorman R. Partos M. Brodin R Harvey
April Prediction 500 2500 1000 3400 1500
Actual 3000 2000 1500 2300 1000
% correct 600% 80% 150% 68% 67%
May Prediction 500 500 1000 500 1000
Actual 400 0 500 0 500
% correct 80% 0% 50% 0% 50%
June Prediction 1 0 1000 500 1000
Actual 400 0 500 0 500
% correct 0% #DIV/0! 50% 0% 50%

--
any help gratefully received
thanks
carrach

working out % of correctness gives #DIV/0!
 

Hi Eduardo,
that works - many many thanks
--

thanks
carrach


"Eduardo" wrote:

Hi,
I assume if the cell has 0 you want to show 0

=IF(or(E7=0,E8=0),0,if(E7E8),E8/E7,E7/E8))

"Carrach" wrote:

Hi,
can nayone help please?(using excel 2003)
I am using a table, part of which is shown below, which needs to show the %
of correctness of predictions given for a period of time. The problems come
if I need to divide by 0 or if both figures are 0.
currently I am using: =IF((E7E8),E8/E7,E7/E8) which gives the correct
answer as long as there are no zeros.
I have tried using =if(iserror(formulae),0,formulae) but it does not work.
I know that I need to check for zeros first but not sure how to check in
both E8 and E7 or how to get the correct answer if only 1 is a zero.

International Trade Advisor
G. Symonds J. Dorman R. Partos M. Brodin R Harvey
April Prediction 500 2500 1000 3400 1500
Actual 3000 2000 1500 2300 1000
% correct 600% 80% 150% 68% 67%
May Prediction 500 500 1000 500 1000
Actual 400 0 500 0 500
% correct 80% 0% 50% 0% 50%
June Prediction 1 0 1000 500 1000
Actual 400 0 500 0 500
% correct 0% #DIV/0! 50% 0% 50%

--
any help gratefully received
thanks
carrach