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

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

--
thanks

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

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

"Morgan" skrev i meddelelsen
...
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

--
thanks

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

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

"Per Jessen" skrev i meddelelsen
...
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

"Morgan" skrev i meddelelsen
...
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

--
thanks

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

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
--
thanks


"Per Jessen" wrote:

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

"Morgan" skrev i meddelelsen
...
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

--
thanks

.

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

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" skrev i meddelelsen
...
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
--
thanks


"Per Jessen" wrote:

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

"Morgan" skrev i meddelelsen
...
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

--
thanks

.

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

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


"Per Jessen" wrote:

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" skrev i meddelelsen
...
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
--
thanks


"Per Jessen" wrote:

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

"Morgan" skrev i meddelelsen
...
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

--
thanks

.


.