#DIV/0 error help!

DAWND470

New Member
Joined
Sep 19, 2011
Messages
8
HELP! I've been trying to get an error corrected on a workbook since last week to no avail. I have a large workbook with a separate worksheet for each day of the month, then a "totals" worksheet at the end. Everything is fine EXCEPT for the cells that require averages.

My workbook is used in a hospital to calculate the time it takes to transport patients from point a to point b. On each daily worksheet, a dispatcher logs each patient transport and we get a total transport time for that run. At the bottom of that worksheet, I need to average the times for that day. On the "totals" worksheet, I need to get a monthly average, using the daily averages for each day.

For each daily worksheet, the formula I used to calculate the average for the day is =AVERAGEIF(X2:X309,"<>0"). This will calculate as long as there is data in at least one of the fields, but if all of them are 0, I get the #DIV/0 error.

For the monthly average, I'm using the formula =AVERAGEIF('1:31'!W310,">0"). This comes up with a #VALUE! error.

Does anyone have any suggestions to help with these errors and to get rid of these error messages?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi

You could use IFERROR to return a specified value if the formula returns an error eg:

=IFERROR(AVERAGEIF(X2:X309,"<>0"),"")

will return an empty string if the AVERAGEIF returns a DIV0! error.
 
Upvote 0
Hi Dawn,

On the daily sheet, you could try
IF(SUM(x2:c309)=0,0,AVERAGEIF(x2:x309,"<>0")).

Regards,
Peg
 
Upvote 0
Thanks everyone! One of my coworkers finally came in and found a formula that worked......for the daily sheets, we went with
=IF(SUM(W2:W309)=0,"0:00",AVERAGEIF(W2:W309,"<>0")) and for the monthly totals we went with
=IF(SUM('1:31'!W310)=0,"0:00",AVERAGE('1:31'!W310))

These worked perfectly. Thanks for all the input!

Dawn
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,710
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top