AVERAGES, without # # # #?

  • Thread starter Thread starter Josh W.
  • Start date Start date
J

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!
 
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)))
 
Back
Top