dividing 0 by 0 in excel 2003

[Carrach]

Hi, I have a spreadsheet where I am working out the correctness of an
expected result in % but have a problem with dividing 0 by 0.
example from my spreadsheet:
if E10 is the predicted value and E11 is the actual value achieved the % of
correctness is shown in E12. E12 is currently holding the formulae below:
=IF((E10>E11),E11/E10,E10/E11)
The problem arrises if both E10 and E11 are showing a zero value as the
result is #DIV/0! I want it to show 0% which it does if only one of them is a
zero.
Most of the threads I have found on the website suggest checking for the
zero in the divisor before dividing but I cant work out how to check for a
zero in both E10- & E11 and then incorporate it into the formulae above.
probably really simple but been at it so long cant see the wood for the trees.
any help gratefully received
thanks
carrach

 

[JudithJubilee]

Hi Carrach,

Try this:

=IF(AND(E11=0,E20=0),0,if(E10>E11),E11/E10,E10/E11))

 

[Bob Phillips]

Try

=IF(AND(E10=0,E11=0),0,IF((E10>E11),E11/E10,E10/E11))

 

[John]

Hi Carrach
Maybe something like this :
=IF(AND(E10=0,E11=0),0,IF(E10>E11,E11/E10,E10/E11))
HTH
John

 

[Dana DeLouis]

Hi. If 'x' is the name of two adjacent cells, maybe...
(Excel 2007)

=IFERROR(MIN(x)/MAX(x),0)


= = = = = = =
HTH :>)
Dana DeLouis

 

[Joe User]

The problem arrises if both E10 and E11 are showing a
zero value as the result is #DIV/0! I want it to show 0% [....]
Most of the threads I have found on the website suggest
checking for the zero in the divisor before dividing but I
cant work out how to check for a zero in both E10- & E11

You could write:

=if(E10*E11=0, 0, min(E10,E11)/max(E10,E11))

But....

if E10 is the predicted value and E11 is the actual value
achieved the % of correctness is shown in E12.

So if E12 is 110%, how can anyone know if actual is 110% of predicted or
predicted is 110% of actual?

I think most people would prefer E12 to indicate just one or the other, and
typically that actual is x% of predicted, for example 110% or 91% of
predicted.

So you can simply write:

=if(E10=0, 1, E11/E10)

formatted as Percentage.

Note that I chose 1, not zero, when predicted is zero. This is an arbitrary
choice; mathematically, there is no correct number. But if you predicted 0
and actual is any positive number, I doubt that people would express actual
as 0% of predicted. I choose 100% of predicted arbitrarily.

PS: All of this assumes that neither E10 nor E11 is negative. If either or
both can be negative, your formula needs more work.


----- original message -----

 

[Carrach]

thank you so much, that works, sorry for the delay in replying as I am new to
this and couldn't find my thread.

 

[Carrach]

thank you bob, that worked
new to this so bear with me please
thanks

carrach

 

[Carrach]

-thanks for the help but working in 2003

thanks
carrach

 

[Carrach]

Everybody is so helpful
many thanks for this - it works

 

[Carrach]

Hi John, thanks for the reply
I agree that it does not give the best results but this is exactly what they
want to see in the hope that it will drive behaviour and eventually they will
all be at over 90% correct.
thanks again
--

thanks
carrach

The problem arrises if both E10 and E11 are showing a
zero value as the result is #DIV/0! I want it to show 0% [....]
Most of the threads I have found on the website suggest
checking for the zero in the divisor before dividing but I
cant work out how to check for a zero in both E10- & E11

You could write:

=if(E10*E11=0, 0, min(E10,E11)/max(E10,E11))

But....

if E10 is the predicted value and E11 is the actual value
achieved the % of correctness is shown in E12.

So if E12 is 110%, how can anyone know if actual is 110% of predicted or
predicted is 110% of actual?

I think most people would prefer E12 to indicate just one or the other, and
typically that actual is x% of predicted, for example 110% or 91% of
predicted.

So you can simply write:

=if(E10=0, 1, E11/E10)

formatted as Percentage.

Note that I chose 1, not zero, when predicted is zero. This is an arbitrary
choice; mathematically, there is no correct number. But if you predicted 0
and actual is any positive number, I doubt that people would express actual
as 0% of predicted. I choose 100% of predicted arbitrarily.

PS: All of this assumes that neither E10 nor E11 is negative. If either or
both can be negative, your formula needs more work.


----- original message -----

Hi, I have a spreadsheet where I am working out the correctness of an
expected result in % but have a problem with dividing 0 by 0.
example from my spreadsheet:
if E10 is the predicted value and E11 is the actual value achieved the % of
correctness is shown in E12. E12 is currently holding the formulae below:
=IF((E10>E11),E11/E10,E10/E11)
The problem arrises if both E10 and E11 are showing a zero value as the
result is #DIV/0! I want it to show 0% which it does if only one of them is a
zero.
Most of the threads I have found on the website suggest checking for the
zero in the divisor before dividing but I cant work out how to check for a
zero in both E10- & E11 and then incorporate it into the formulae above.
probably really simple but been at it so long cant see the wood for the trees.
any help gratefully received
thanks
carrach