Easier way to average times, MTD and YTD

jaybird2569

New Member
Joined
Sep 28, 2016
Messages
22
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
ABCDEFGHIJKLMNO
1EmployeeMonthTask #1avg time to complete task (min)Actual TimeTask #2avg time to complete task (min)Actual TimeTask #3avg time to complete task (min)Actual TimeStaffed TimeEfficiency Rating MTDEfficiency Rating YTD
2RickJanuary2690.053114.2839284112210.153.1579:35:2339.03(E2+H2+K2)/L2
3CarlJanuary520.05312.761218472300.154.5120:01:1615.85
4DarylJanuary7200.053138.23212448150.152.25120:01:0017.69
5CarolJanuary24850.0531131.953510440120.151.8107:45:2438.70
6MaggieJanuary5600.053129.7365420180.152.7101:49:1712.36
7GlennJanuary20830.0531110.607316464210.153.1598:55:0043.13
8BethJanuary14380.053176.3578374148190.152.85104:00:0052.43
9MorganJanuary14000.053174.344416120.151.888:38:0024.95
10NeganJanuary15590.053182.78298432110.151.65104:34:3326.72
11LoriJanuary52550.0531279.0405542060.150.9109:35:3765.68
12Totals158211034:55:30
13RickFebruary730.05313.876315460290.154.35104:55:5515.60(E2+H2+K2+E13+H13+K13)/(L2+L13)
14CarlFebruary3760.053119.965617468310.154.65106:43:3220.83
15DarylFebruary530.05312.814318472230.153.4582:55:5322.65
16CarolFebruary14490.053176.941912448190.152.8577:33:0739.55
17MaggieFebruary2580.053113.699814456170.152.55118:03:0214.69
18GlennFebruary1110.05315.894113452160.152.4109:04:2113.27
19BethFebruary1160.05316.15961847250.150.75101:34:0018.65
20MorganFebruary570.05313.026710440140.152.1101:34:0010.66
21NeganFebruary1280.05316.79684416110.151.65122:34:424.79
22LoriFebruary10410.053155.27718432100.151.5144:26:3614.75
23Totals36621069:25:08
24RickMarch830.05314.4073943600.150146:40:376.61(E2+H2+K2+E13+H13+K13+E24+H24+K24)/(L2+L13+L24)
25CarlMarch1010.05315.3631441670.151.05145:00:083.71
26DarylMarch840.05314.46048432140.152.1120:41:427.67
27CarolMarch390.05312.07099436300.154.5139:57:007.30
28MaggieMarch690.05313.66397428170.152.5556:52:0314.44
29GlennMarch1450.05317.6995843260.150.9141:12:496.90
30BethMarch400.05312.12414456240.153.6116:38:5012.70
31MorganMarch130.05310.69036424130.151.95128:00:005.00
32NeganMarch1450.05317.69959436210.153.1598:12:3011.45
33LoriMarch2820.053114.9742642490.151.35142:10:156.81
34Totals10011235:25:54
Sheet2
Cell Formulas
RangeFormula
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)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,632
Members
430,372
Latest member
contentment

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top