AVERAGES, without # # # #?

[Josh W.]

I've made a template relying heavily on averages. The problem is,
when an employee didn't work on a certain day, and therefore had no
data, all I end up with is this: # # # #, which messes with other
formulas.

For example: Yesterday Jeff drove a tour with a 3 rating, and a tour
with a 2 rating. (AVERAGE: 2.5) Today Jeff had the day off. (AVERAGE:
# # # #). The Average for the two days: # # # #.

I want Jeff's day off NOT to count in the average. SO, average of day 1
would be 2.5, average of day 2 would be N/A, and total average would
be 2.5. How to do this?

Thanks!

 

[David McRitchie]

Hi Josh,
=IF(COUNTA(C5:F5)<>0,SUM(C5:F5)/COUNTA(C5:F5),"")

 

[Guest]

Thanks!!!

 

[2rrs]

one of these will do the job; they are not affected by zeros, blanks nor text

(cse) =AVERAGE(IF(A120:A129<>0,A120:A129))

=SUM(A120:A129)/SUMPRODUCT(--(A120:A129<>0)*(ISNUMBER(A120:A129)))