# Calculate a list of Averages from a Timesheet

#### LWLSKOP

##### New Member
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

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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Replies
3
Views
471
Replies
5
Views
3K
Replies
6
Views
753
Replies
1
Views
387
Replies
3
Views
419

1,195,628
Messages
6,010,771
Members
441,568
Latest member
abbyabby

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back