#div/0!

I created a statistical worksheet for our scientists. Each scientist has an individual sheet where they enter values into cells. Other cells on their sheet have formulas that automatically calculate numbers that our directors use for progress reports. The first sheet is an overall where all of the calculated numbers for each individual are combined to an overall stat sheet. One of the calculated values is turnaround time. They enter an start date and ending date and the turnaround time is calculated. Then the average turnaround time is calculated per yearly quarter. The overall sheet averages all of the turnaround times for each person in each quarter. Here's my issue:

If a person does not add anything to their sheet, meaning they do not have any cases to have a turnaround time (we have scientists that work on my team periodically), the cells where the automatic calculations are made have "#DIV/0!". I need a way on my overall sheet for the formula to average all values but ignore this error. I looked at "Conditional Formatting" in the Home tab but it did not work. Any ideas?

Thanks!

You can use AVERAGEIF

=AVERAGEIF(A1:A10,">=0")

That's assuming there are no negative values in the range to average.

Didn't work but I don't know if I coded it wrong.

This is the formula for the Average Turnaround Time cell in Scientist A's individual sheet for the first quarter:

=AVERAGEIFS($E$24:$E$502,$D$24:$D$502,">"&Q2,$D$24:$D$502,"<"&Q3)

The E column is the average turn around times for the individual cases. The D column are report dates for each case. Q2 has 9/30/2014 in it which >Q2 is October 1st (the first day of our fiscal year and the start of the first quarter). Q3 has 1/1/2015 which <Q3 is the end of the quarter (12/31/2014).

This is the formula for the Average Turnaround Time cell in the Overall sheet (which averages scientists A-I) for the first quarter:

=AVERAGE(A!D3,B!D3,C!D3,D!D3,E!D3,F!D3,G!D3,H!D3,I!D3). This is where I get the #DIV/0! error.

This is what I coded after your reply:

=AVERAGEIF((A!D3:B!D3:C!D3:D!D3:E!D3:F!D3:G!D3:H!D3:I!D3),">=0")

It no longer shows the #DIV/0! but the #VALUE! error. I've know that the only error I can get in this cell is the #DIV/0! error, so thankfully that's all I have to worry about. Did I code the AVERAGEIF formula wrong?