Easier way to average times, MTD and YTD

[jaybird2569]

I have a spreadsheet to track employee efficiency over 16 different tasks. I am currently using formulas to determine their overall efficiency for the month by multiplying their time spent on each task (variable) by the amount of time it takes to do each task (constant). One of the issues is I can't add straight across the column because I have the number of tasks, constant and actual time. I then add all of the actual times across the column and divide by the number of hours worked for that month.
The formulas are ridiculous and get even more complicated as the months go on because I'm also trying to keep YTD totals but I can't just average all of the averages. I'm looking for an easier way to calculate this either VBA to run through the sheet by employees name and adding the columns for each person or keep using formulas that I have to create every month that's starting to take several hours to write. I currently have a formula that is adding 16 "totals" for each month divided by the total hours worked to get the YTD efficiency. For November that will be 176 totals (16x11) divided by 11 total hour cells. There has to be a better way. Maybe I need to set up the spreadsheet differently.
Thank you in advance.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Employee	Month	Task #1	avg time to complete task (min)	Actual Time	Task #2	avg time to complete task (min)	Actual Time	Task #3	avg time to complete task (min)	Actual Time	Staffed Time	Efficiency Rating MTD	Efficiency Rating YTD
2	Rick	January	269	0.0531	14.2839	28	4	112	21	0.15	3.15	79:35:23	39.03		(E2+H2+K2)/L2
3	Carl	January	52	0.0531	2.7612	18	4	72	30	0.15	4.5	120:01:16	15.85
4	Daryl	January	720	0.0531	38.232	12	4	48	15	0.15	2.25	120:01:00	17.69
5	Carol	January	2485	0.0531	131.9535	10	4	40	12	0.15	1.8	107:45:24	38.70
6	Maggie	January	560	0.0531	29.736	5	4	20	18	0.15	2.7	101:49:17	12.36
7	Glenn	January	2083	0.0531	110.6073	16	4	64	21	0.15	3.15	98:55:00	43.13
8	Beth	January	1438	0.0531	76.3578	37	4	148	19	0.15	2.85	104:00:00	52.43
9	Morgan	January	1400	0.0531	74.34	4	4	16	12	0.15	1.8	88:38:00	24.95
10	Negan	January	1559	0.0531	82.7829	8	4	32	11	0.15	1.65	104:34:33	26.72
11	Lori	January	5255	0.0531	279.0405	5	4	20	6	0.15	0.9	109:35:37	65.68
12	Totals		15821									1034:55:30
13	Rick	February	73	0.0531	3.8763	15	4	60	29	0.15	4.35	104:55:55	15.60		(E2+H2+K2+E13+H13+K13)/(L2+L13)
14	Carl	February	376	0.0531	19.9656	17	4	68	31	0.15	4.65	106:43:32	20.83
15	Daryl	February	53	0.0531	2.8143	18	4	72	23	0.15	3.45	82:55:53	22.65
16	Carol	February	1449	0.0531	76.9419	12	4	48	19	0.15	2.85	77:33:07	39.55
17	Maggie	February	258	0.0531	13.6998	14	4	56	17	0.15	2.55	118:03:02	14.69
18	Glenn	February	111	0.0531	5.8941	13	4	52	16	0.15	2.4	109:04:21	13.27
19	Beth	February	116	0.0531	6.1596	18	4	72	5	0.15	0.75	101:34:00	18.65
20	Morgan	February	57	0.0531	3.0267	10	4	40	14	0.15	2.1	101:34:00	10.66
21	Negan	February	128	0.0531	6.7968	4	4	16	11	0.15	1.65	122:34:42	4.79
22	Lori	February	1041	0.0531	55.2771	8	4	32	10	0.15	1.5	144:26:36	14.75
23	Totals		3662									1069:25:08
24	Rick	March	83	0.0531	4.4073	9	4	36	0	0.15	0	146:40:37	6.61		(E2+H2+K2+E13+H13+K13+E24+H24+K24)/(L2+L13+L24)
25	Carl	March	101	0.0531	5.3631	4	4	16	7	0.15	1.05	145:00:08	3.71
26	Daryl	March	84	0.0531	4.4604	8	4	32	14	0.15	2.1	120:41:42	7.67
27	Carol	March	39	0.0531	2.0709	9	4	36	30	0.15	4.5	139:57:00	7.30
28	Maggie	March	69	0.0531	3.6639	7	4	28	17	0.15	2.55	56:52:03	14.44
29	Glenn	March	145	0.0531	7.6995	8	4	32	6	0.15	0.9	141:12:49	6.90
30	Beth	March	40	0.0531	2.124	14	4	56	24	0.15	3.6	116:38:50	12.70
31	Morgan	March	13	0.0531	0.6903	6	4	24	13	0.15	1.95	128:00:00	5.00
32	Negan	March	145	0.0531	7.6995	9	4	36	21	0.15	3.15	98:12:30	11.45
33	Lori	March	282	0.0531	14.9742	6	4	24	9	0.15	1.35	142:10:15	6.81
34	Totals		1001									1235:25:54
Sheet2

Cell Formulas
Range	Formula
E2	=C2*D2
E3	=C3*D3
E4	=C4*D4
E5	=C5*D5
E6	=C6*D6
E7	=C7*D7
E8	=C8*D8
E9	=C9*D9
E10	=C10*D10
E11	=C11*D11
E13	=C13*D13
E14	=C14*D14
E15	=C15*D15
E16	=C16*D16
E17	=C17*D17
E18	=C18*D18
E19	=C19*D19
E20	=C20*D20
E21	=C21*D21
E22	=C22*D22
E24	=C24*D24
E25	=C25*D25
E26	=C26*D26
E27	=C27*D27
E28	=C28*D28
E29	=C29*D29
E30	=C30*D30
E31	=C31*D31
E32	=C32*D32
E33	=C33*D33
H2	=F2*G2
H3	=F3*G3
H4	=F4*G4
H5	=F5*G5
H6	=F6*G6
H7	=F7*G7
H8	=F8*G8
H9	=F9*G9
H10	=F10*G10
H11	=F11*G11
H13	=F13*G13
H14	=F14*G14
H15	=F15*G15
H16	=F16*G16
H17	=F17*G17
H18	=F18*G18
H19	=F19*G19
H20	=F20*G20
H21	=F21*G21
H22	=F22*G22
H24	=F24*G24
H25	=F25*G25
H26	=F26*G26
H27	=F27*G27
H28	=F28*G28
H29	=F29*G29
H30	=F30*G30
H31	=F31*G31
H32	=F32*G32
H33	=F33*G33
K2	=I2*J2
K3	=I3*J3
K4	=I4*J4
K5	=I5*J5
K6	=I6*J6
K7	=I7*J7
K8	=I8*J8
K9	=I9*J9
K10	=I10*J10
K11	=I11*J11
K13	=I13*J13
K14	=I14*J14
K15	=I15*J15
K16	=I16*J16
K17	=I17*J17
K18	=I18*J18
K19	=I19*J19
K20	=I20*J20
K21	=I21*J21
K22	=I22*J22
K24	=I24*J24
K25	=I25*J25
K26	=I26*J26
K27	=I27*J27
K28	=I28*J28
K29	=I29*J29
K30	=I30*J30
K31	=I31*J31
K32	=I32*J32
K33	=I33*J33
M2	=(E2+H2+K2)/L2
M3	=(E3+H3+K3)/L3
M4	=(E4+H4+K4)/L4
M5	=(E5+H5+K5)/L5
M6	=(E6+H6+K6)/L6
M7	=(E7+H7+K7)/L7
M8	=(E8+H8+K8)/L8
M9	=(E9+H9+K9)/L9
M10	=(E10+H10+K10)/L10
M11	=(E11+H11+K11)/L11
M13	=(E13+H13+K13)/L13
M14	=(E14+H14+K14)/L14
M15	=(E15+H15+K15)/L15
M16	=(E16+H16+K16)/L16
M17	=(E17+H17+K17)/L17
M18	=(E18+H18+K18)/L18
M19	=(E19+H19+K19)/L19
M20	=(E20+H20+K20)/L20
M21	=(E21+H21+K21)/L21
M22	=(E22+H22+K22)/L22
M24	=(E24+H24+K24)/L24
M25	=(E25+H25+K25)/L25
M26	=(E26+H26+K26)/L26
M27	=(E27+H27+K27)/L27
M28	=(E28+H28+K28)/L28
M29	=(E29+H29+K29)/L29
M30	=(E30+H30+K30)/L30
M31	=(E31+H31+K31)/L31
M32	=(E32+H32+K32)/L32
M33	=(E33+H33+K33)/L33
C12	=SUM(C2:C11)
C23	=SUM(C13:C22)
C34	=SUM(C24:C33)
L12	=SUM(L2:L11)
L23	=SUM(L13:L22)
L34	=SUM(L24:L33)

 

[CalcSux78]

Sounds like a job for PivotTable.