Hourly AVG formula confusion

[jakeisbill]

I made a sheet to track employees hourly production of widget wiring. I came across an instance that has me stumped. If an employee misses the morning hours my formula doesn't compute the hourly average accurately. I could use a hand. Thanks

ps The Orange box is the morning average from 7:30-12:50. the purple one is the afternoon average 1:15-5:15.

Hourly wiring graph helper.xls

[daddylonglegs]

Are you trying to exclude the zeroes? Try AVERAGEIF, i.e.

=AVERAGEIF(E2:E6,">0")

[jakeisbill]

thanks Daddylonglegs, I will use that formula, but my formula in the E column isn't correct. My formula is ("total units made" / 1) for the first hour, ("total units made" / 2) for the second hour... etc. If an employee is 3 hours late and starts at 10:30 then their hourly average would not be correct.

[6StringJazzer]

How can you tell that they missed hours? If HOUR is 0? In your example, there is only one HOUR that is not 0. Is it when TOTAL is blank? I'll assume that but what does it look like if they miss afternoon hours?

What is the AVG column supposed to indicate? It looks to me like a running average, and so the figure in the orange box should be the same as the AVG in the same row.

If so I have attached a possible solution. The column AVG shows the running average for the entire day. The orange box shows the average for the morning, and the purple box shows the average for the afternoon.

jakeisbill=Hourly wiring graph helper.xls

[jakeisbill]

Thanks 6String I could have explained it better, but your formula does exactly what I needed. SOLVED by 6StringJazzer