How to Stop #DIV/0! from showing

[posfog]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82, align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
Morning All,
I have a number of different formulas within a work book which look at a data set which sometimes shows zero's or no information, So as you would expect the formula's bring back #DIV/0!

Is there a way that the formulas could infact then show nothing or a single zero and not the #DIV/0!


Here is examples of the formulas in question

=ABS(SUM($R$8/$R$15)-100%)
=VLOOKUP($R$4,'Off Load Data'!$A:$D,3,FALSE)


Kind Regards
P

 

[steve the fish]

Try it like this:

=IFERROR(formula,0)

 

[Rick Rothstein]

=ABS(SUM($R$8/$R$15)-100%)

When you have a definite division such as the one above, simply test for the divisor being 0...

=IF($R$15=0,"",ABS(SUM($R$8/$R$15)-100%))

=ABS(SUM($R$8/$R$15)-100%)
=VLOOKUP($R$4,'Off Load Data'!$A:$D,3,FALSE)

The following method will work for the first formula as well as the one immediately above, but note that it will stop all errors, not just #DIV/0! errors...

=IFERROR(VLOOKUP($R$4,'Off Load Data'!$A:$D,3,FALSE),"")

 

[posfog]

Try it like this:

=IFERROR(formula,0)

Fantastic! Steve the Fish Worked a treat

 

[posfog]

Morning All,
Thanks again for the help so far but i have come up with an issue along the same lines.

So i am using the following Formula
=IFERROR(ABS(SUM($R$9/$R$16)-100%),1)

In todays data R9 is 11 and R16 is 0
The formula still thinks that the scoring should be 100% but i wanted it to show the true negative score.


Is this possible?


Regards and Thanks in advance

 

[steve the fish]

Depends what you mean by true negative score??? Whats that meant to be and whats the logic behind the result.

 

[posfog]

Morning Steve,

Problem i have is the formula will work for:
Example A Where someone has an output of work and any errors raised against it as shown below
Example B Where no output or errors has been raise
[TABLE="width: 269"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Errors[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Output[/TD]
[TD]476[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Percentage[/TD]
[TD]98.11%[/TD]
[TD]100%[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]


But it wont work for example C where errors have been found against that person but no work has been carried out on that day (errors can be raised due to error checking is delayed)


Hope this makes sense?

 

[steve the fish]

Ok so what is the ?? meant to be?

 

[posfog]

where i would need a negative % answer or even a zero

 

[steve the fish]

I dont really see how you can have an either this or that answer but maybe try this:

=IF(B1="",1,IF(B2="",-1,1-B1/B2))