Replace #DIV/0 error with zeros

  • Thread starter Thread starter Shirley Munro
  • Start date Start date
S

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:
 
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
 
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
 
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
+-------------------------------------------------------------------
 
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
 
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
 
Back
Top