Replace #DIV/0 error with zeros

[Shirley Munro]

Hi All

I am creating a worksheet which will monitor the performance of
manufacturing machine each week. The machine is generally used Monda
to Friday but can also be used on Saturdays and/or Sundays. As
result, some of the formulas that I am using, particularly those fo
averages, show up as #DIV/0 because there is no data for that day but
have to allow for there possibly being values some weeks. Is there
way that I can replace all #DIV/0's with an actual zero (0) and thi
way I can then use an array formula to calculate the average where th
cell value is greater than 0.

Thanks

Shirley Munro:

 

[Pete_UK]

If your formula for average is something like:

= a / b

then you should change this to:

=IF(b=0, 0, a/b)

to get rid of the #DIV/0 errors. Obviously, a and b would be cell
references.

Hope this helps.

Pete

 

[Shirley Munro]

Thanks for the reply but I am using the average function rather tha
dividing one cell by another and this function is being used over 5-
cells depending on how many days in the week have been worked.

Shirle

 

[Chip Pearson]

Try

=IF(COUNT(A1:A10)=0,"",AVERAGE(A1:A10))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Shirley Munro"
<[email protected]>
wrote in message
news:[email protected]...

 

[Shirley Munro]

Hi Chip

Thanks for the reply but this doesn't work either. The problem is tha
the cell range does not contain zero's, it contains #DIV/0's therefor
the if part of the statement doesn't work and I just get another #DIV/
error. I need to replace the #DIV/0 errors with zeros and then I ca
use an array formula to calculate the average but I don't know how t
replace the #DIV/0's with actual 0's.

I am attaching a screen shot showing the formulas. In this instance i
is Saturday that has a #DIV/0 error since no hours were worked on thi
particular week but I have to allow for Saturday and/or Sunday bein
worked some weeks.

Any further help would be much appreciated.

Shirle

+-------------------------------------------------------------------
|Filename: DivZero.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4392
+-------------------------------------------------------------------

 

[Pete_UK]

You are getting the error because somewhere you are trying to divide by
zero. What you need to do is to track back to discover where the #DIV/0
error is first being introduced. Your screen shot shows that you are
getting data from columns G, L, Q etc for something that is labelled as
a percentage, so go back to those cells, where you will probably find
something like:

= a / b

to get the percentage. Then you can try my suggestion:

=IF(b=0, 0, a / b)

Hope this helps.

Pete

 

[Shirley Munro]

Hi again

Yes, you are correct some of the cells in the screenshot contain
#Div/0's but this is because some weeks Saturday and/or Sundays are not
worked. To give you more of an explanation. I am monitoring the weekly
perfomance of a manufacturing machine. I need to have 7 sections in the
worksheet - one for each day of the week regardless of whether Saturday
and/or Sundays are worked. I have to work out the average production
for each part and then a daily average overall. It is some of these
cells that have a #DIV/0 error as there has been no production that
particular day. I then want the weekly average and again this may have
some #DIV'0 error due to a particular day not being worked. Either way,
I either have to change the #DIV/0 errors to zeros or else I need
further formulas to ignore these errors.

Any further suggestions would be much appreciated.

Shirley