How to calculate with #div/0! error?

[nagje]

Hey there,

I'd like to get the average of a row filled with percentages. But because
the percentages will only be filled in when the month has started I have some
div/0! values. How can I calculate the average? And please try to keep it
simple

Hope I explained it clearly, if not let me know, see an example below.
thx
Nagje

example (yes i know this isn't a row ;-) ):
Jan 95.2%
Feb 100.0%
March 93.2%
April 95.0%
may 96.9%
June 97.6%
July #div/0!
Aug #div/0!
Sept #div/0!
Okt #div/0!
Nov #div/0!
Dec #div/0!

Average year = ???????

 

[Gary''s Student]

Instead of something like:

=C1/D1
use
=IF(D1="","",C1/D1)

The #div/0 errors will vanish and the average will be over the available
values.

 

[George Nicholson]

The customary practice is to test for 0.

Rather than:
A1 = x/y
(which would generate Div by zero errors)

Restructure the formula to:
A1= If(y=0, 0, x/y)

I'm sure your formula is more complicated than x/y, but the principle
remains the same.

 

[Bernard Liengme]

The easiest way is to change the formula tat computes the average to
something like =IF(D10>0,D10/C1,""). Then your AVERAGE formula will work

Alternatively, use the array formula
=AVERAGE(IF(ISNUMBER(B1:B12),B1:B12,""))
which must be entered with SHIFT+CTRl+ENTER not just ENTER
best wishes

 

[nagje]

Thx for the quick reply, but this is not the solution to my problem I'm
afraid (or I simply don't get it ).
I try to get the average of a row, in that row there can be several #div/0!
errors. Not just one. I tried =IF(C11="","",C1/D1) but that didn't work as
well.

 

[nagje]

Thx...this helps a lot.
But, why is the answer from this calculation different then when I do a
manual count of month results divided by the number of months where results
have been entered?

 

[Gaurav]

=IF(ISERROR(A1/B1),"",(A1/B1))

 

[Bernard Liengme]

Most likely it is rounding. For example you may see 14.4% in a cell but the
actual stored values could be 14.35789%

Show us the data from which the percentages are calculated.
best wishes

 

[nagje]

The data is obtained from several other sheets (it are calculations based
upon lab experiments with good/not good results). Here is a row of data (all
percentages), starting in january and going to december:
100.00 92.86 100.00 88.89 100.00 93.33 91.67 87.50 100.00 100.00
100.00 #div/0!

Manual calculation over 11 months gives me: 95.84% The formula you gave me
gives me 96.11%. A difference of 0.27%. Not that big but there are also rows
where the difference is 1.00%.

Hope this is what you asked for. Thanx again for helping.

 

[nagje]

Um...not sure what you mean with this. Can you explain it a bit more?

 

[Bernard Liengme]

To exactly show you how the discrepancy occurs I would need not the
percentage values but the values used to compute the percentages. Take the
second figure 92.86. You are seeing only two decimal places. The actual
stored values could be 92.85999999999 or 92.863999999999999999999 or any
value in between

Select all the values and use the decimal increase tool to see what I am
taking about
Try this: suppose the non error values are in A1:A11. Write a formula
=SUM(A1:A11) Do you get 'your' 95.84 or 'my' 96.11 ?
If more help need please send me a copy of the file (remove confidential
stuff)
best wishes

 

[joeu2004]

Manual calculation over 11 months gives me: 95.84%[.]
The formula you gave me gives me 96.11%.

To exactly show you how the discrepancy occurs I would need not the
percentage values but the values used to compute the percentages. Take the
second figure 92.86. You are seeing only two decimal places. The actual
stored values could be 92.85999999999 or 92.863999999999999999999 or
any value in between

More to the point, what appears to be 92.86 could be as big as
92.8649...9 (enough 9s to fill 15 significant digits). But when I
append 49...9 to all of the OPs 11 numbers, the largest average is
still only 95.845909 -- not even close to 96.11.

On the other hand, I cannot duplicate the OP's claim that your formula
results in 96.11.

So I think the problem is not simply rounded displayed values v.
actual value, but the fact that the OP's numbers no longer match what
was posted earlier -- or the OP has a typo in the range that specified
for AVERAGE(), or the OP has misinterpreted what you suggested that
the OP do.

I suggest that the OP post the formulas that the OP is now using after
applying your suggestion.

 

[nagje]

*is suddenly very ashamed* I found the problem....a typo in the formula that
you gave me made me take the average of more values. Fixing it gave me the
correct values.

Thx for all the help. If we'd be in a pub now I'd buy you a beer