"Carrach" wrote:
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" wrote:
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((E10E11),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