Replace #DIV/0 error with zeros
 

Hi All

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

Thanks

Shirley Munro:)


--
Shirley Munro
------------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
View this thread: http://www.excelforum.com/showthread...hreadid=515252

Replace #DIV/0 error with zeros
 

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

Replace #DIV/0 error with zeros
 

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

Shirley


--
Shirley Munro
------------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
View this thread: http://www.excelforum.com/showthread...hreadid=515252

Replace #DIV/0 error with zeros
 

Try

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


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


"Shirley Munro"

wrote in message
news:Shirley.Munro.23mo2n_1140606600.9426@excelfor um-nospam.com...

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

Shirley


--
Shirley Munro
------------------------------------------------------------------------
Shirley Munro's Profile:
http://www.excelforum.com/member.php...nfo&userid=836
View this thread:
http://www.excelforum.com/showthread...hreadid=515252

Replace #DIV/0 error with zeros
 

Hi Chip

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

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

Any further help would be much appreciated.

Shirley


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

--
Shirley Munro
------------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
View this thread: http://www.excelforum.com/showthread...hreadid=515252

Replace #DIV/0 error with zeros
 

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

Replace #DIV/0 error with zeros
 

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


--
Shirley Munro
------------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
View this thread: http://www.excelforum.com/showthread...hreadid=515252