Hi Guys
I want formulas that will calculate a summary of averages from the timesheet below(actual spreadsheet has over 50,000 rows with 12 employee). My aim is to be able to see, when I expand each subtotal/month, the average hours worked per day/week/month for the month/subtotal and in the same manner as the number of counts for all employees - summarised below the table (highlighted yellow).
In other words, each subtotal will have its summary of averages below the table when I collapse it. Cells without data should be ignored in the formula
Many thanks
<colgroup><col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 1962;">
<col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 1678;">
<col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 2531;">
<col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 1934;" span="2">
<col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 2588;">
<col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2076;">
<col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2048;">
<col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 1962;">
<col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 2702;">
<col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2133;">
<col width="181" style="width: 136pt; mso-width-source: userset; mso-width-alt: 5148;">
<col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 2531;">
<tbody>
</tbody>
I want formulas that will calculate a summary of averages from the timesheet below(actual spreadsheet has over 50,000 rows with 12 employee). My aim is to be able to see, when I expand each subtotal/month, the average hours worked per day/week/month for the month/subtotal and in the same manner as the number of counts for all employees - summarised below the table (highlighted yellow).
In other words, each subtotal will have its summary of averages below the table when I collapse it. Cells without data should be ignored in the formula
Many thanks
FELIX | RICHARD | GRAND TOTAL | ||||||||||
MONTH | WEEK | DATE | START | END | HRS WKED | COUNTS | START | END | HRS WKED | COUNTS | TOTAL HRS WKD | COUNT |
1 | 2 | 08/04/2014 | - | - | - | - | 12:56:05 | 15:42:30 | 2:46:25 | 95 | 2:46:25 | 95 |
1 | 2 | 09/04/2014 | - | - | - | - | 10:20:26 | 15:45:52 | 5:25:26 | 87 | 5:25:26 | 87 |
1 | 2 | 10/04/2014 | 9:59:13 | 13:52:33 | 3:53:20 | 100 | - | - | - | - | 3:53:20 | 100 |
1 | 2 | 11/04/2014 | 9:20:09 | 10:18:30 | 0:58:21 | 60 | - | - | - | - | 0:58:21 | 60 |
1 | 3 | 14/04/2014 | - | - | - | - | 12:25:45 | 13:37:04 | 1:11:19 | 77 | 1:11:19 | 142 |
1 | 3 | 15/04/2014 | - | - | - | - | 10:25:35 | 11:38:00 | 1:12:25 | 54 | 1:12:25 | 271 |
1 | 5 | 28/04/2014 | - | - | - | - | 11:05:02 | 14:25:40 | 3:20:38 | 79 | 3:20:38 | 79 |
1 | 5 | 29/04/2014 | - | - | - | - | 13:40:21 | 15:10:56 | 1:30:35 | 76 | 1:30:35 | 76 |
1 | 5 | 30/04/2014 | 8:05:57 | 10:34:43 | 2:28:46 | 114 | - | - | - | - | 2:28:46 | 167 |
1 Total | 7:20:27 | 274 | 15:26:48 | 468 | 22:47:15 | 1077 | ||||||
2 | 5 | 01/05/2014 | - | - | - | - | 9:51:10 | 10:31:51 | 0:40:41 | 26 | 0:40:41 | 26 |
2 | 6 | 06/05/2014 | 7:48:15 | 9:30:25 | 1:42:10 | 89 | - | - | - | - | 1:42:10 | 89 |
2 | 8 | 19/05/2014 | 9:25:05 | 11:02:22 | 1:37:17 | 85 | - | - | - | - | 1:37:17 | 85 |
2 | 8 | 20/05/2014 | 8:36:57 | 9:23:18 | 0:46:21 | 42 | - | - | - | - | 0:46:21 | 42 |
2 | 8 | 21/05/2014 | 9:36:35 | 11:26:12 | 1:49:37 | 54 | 9:30:50 | 13:11:51 | 3:41:01 | 91 | 5:30:38 | 145 |
2 | 8 | 22/05/2014 | 8:56:13 | 9:03:00 | 0:06:47 | 3 | - | - | - | - | 0:06:47 | 3 |
2 Total | 6:02:12 | 273 | 4:21:42 | 117 | 10:23:54 | 390 | ||||||
Average Time Spent/Day | ? | |||||||||||
Average Time Spent/Week | ? | |||||||||||
Average Time Spent/Month | ? | |||||||||||
Average Counts/Day | ? | |||||||||||
Average Counts/Week | ? | |||||||||||
Average Counts/Month | ? | |||||||||||
Average Start Time | ? | |||||||||||
Average End Time | ? |