Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUM of column values depending on the heading (month) | Excel Discussion (Misc queries) | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
How do I highlight a column depending on other column value? | Excel Worksheet Functions | |||
Sum values depending in values next column | Excel Discussion (Misc queries) | |||
how do i sum up a column of time values but ignore negative values | New Users to Excel |