Sum values in a column depending on start time in another column

[Morgan]

Hi there,

My spreadsheet tracks and measures the performance of tasks. In Column B, I
have a list of start times in the time format hh:mm in two other columns I
have the total time for that work session (Column D) and the output in units
in (Column M), I am after a formula that will show the average hourly output
for work beginning in each of the 24 hours of a day, it doesn't matter how
long the work sessions are. I want to compare the output in an hourly rate
for people starting work at various times in the day, to gauge when the most
productive time to start is.

The results would be in a column with twenty four cells 0h to 24h and the
average hourly output for based on when the person started in the day, eg 3am
or 7pm etc. Any help would be fantastic, thank you

 

[Per Jessen]

Hi

This formula assume that total time in column D is not entered as Time, but
as a regular number. Column N is the start time to calculate average for.

=SUMIF($B$2:$B$5,N3,$M$2:$M$5)/SUMIF($B$2:$B$5,N3,$D$2:$D$5)

Regards,
Per

 

[Per Jessen]

If total time in column D is entered as time use this formula:

=SUMIF($B$2:$B$10,N2,$M$2:$M$10)/SUMIF($B$2:$B$10,N2,$D$2:$D$10)/24

Regards,
Per

 

[Morgan]

Thank You Per Jessen, the formula works great and i was using time as a
regular number in column D, but i was hoping to incorporate any possible
start time within an hour, eg from 16:00 to 17:00 people could be clocking in
at 16:07, 16:48 etc, and i was hoping that the results column could display
the average hourly output for people starting at any time within the hour not
just on the hour or at a specific time.

In Column B, the start times would have entries like 13:10, 18:21, 05:43,
18:33 and so on, and for all the start times beginning in the hour 18:00 -
19:00, i would like to find the average hourly rate, if possible?

Thank you again for your help

 

[Per Jessen]

Thanks for your reply.

I was not sure if people started at the hour, so I gave you the 'simple'
formula first, as the foumula you actually need is a bit more time
comsuming. The formula has to pasted as one line:

=(SUMIF($B$2:$B$10,">=" & N2,$M$2:$M$10)-SUMIF($B$2:$B$10,">=" &
(N2+TIMEVALUE("1:00")),$M$2:$M$10))/(SUMIF($B$2:$B$10,">=" &
N2,$D$2:$D$10)-SUMIF($B$2:$B$10,">=" & (N2+
TIMEVALUE("1:00")),$D$2:$D$10))/24

Regards,
Per

 

[Morgan]

Thank you Per, you've been a great help!