Complex Employee Productivity Table Formula

O

oe23

New Member
Joined
Oct 19, 2022
Messages
3
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
1666212127873.png



Sorry if this sounds easier than it is, I've been trying to figure out a solution for almost 2 days now.

I have this table which shows how many employees produce x amount of units each month.

I have two rows at the bottom of the table. One of them (Additional Employees) lets me hypothetically "add" more employees to the existing amount of employees. The other row (New Units) shows how many units it would be if I would be in total if I added those employees.

The problem is that the new employees would go through training for x amount of months before they can actually work. And even when they begin, they would be at 33% productivity, 66% and finally 100% by month 3.

But we want to modify the Training Months to be a variable that we could change (2 months, 3 months etc).

For example: In the picture above, I have the training set to 2 months. Hence why when I add 2 new employees in July 2022, we don't get new units until September. That's because they undergo 2 months of training.

But I want to be able to enter a different number for Training (I.e. 4 months) so that it doesn't count the employees until they go through 4 months of training.

Any suggestions are extremely apreciated!

Thank you
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Mr_excel_task_assigned.xlsx
ABCDEFGHIJ
1Modifiers
2
3Training months2
4Productivity Month 133%
5Productivity Month 266%
6Productivity Month 3100%
7
8202220222022202220222022202220222022
9JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch
10Units155141155155158158173180181
11Employees424041414141414141
12Units per Employees444444444
13
14Additional Employees23
15New Units155141157160166170190202203
16
17Additional Employees adj for productivity0.00.00.71.32.03.04.05.05.0
180%0.00.00.00.00.00.00.00.00.0
190%0.00.00.00.00.00.00.00.0
2033%0.70.00.01.00.00.00.0
2166%1.30.00.02.00.00.0
22100%2.00.00.03.00.0
Sheet2
Cell Formulas
RangeFormula
B12:J12B12=B10/B11
B15:J15B15=B17*B12+B10
C17:J17F17=SUM(F18:F22)+SUM($B22:E22)
B18:J18F18=$A18*F14
C19:J19F19=$A19*E14
D20:J20F20=$A20*D14
E21:J21F21=$A21*C14
F22:J22F22=$A22*B14
B17B17=SUM(B18:B22)
 
Upvote 0
You just need to create an additional table that allows you to calculate the adjusted number of new employees, which takes into consideration the productivity phase
 
Upvote 0
Mr Kaka said:
Mr_excel_task_assigned.xlsx
ABCDEFGHIJ
1Modifiers
2
3Training months2
4Productivity Month 133%
5Productivity Month 266%
6Productivity Month 3100%
7
8202220222022202220222022202220222022
9JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch
10Units155141155155158158173180181
11Employees424041414141414141
12Units per Employees444444444
13
14Additional Employees23
15New Units155141157160166170190202203
16
17Additional Employees adj for productivity0.00.00.71.32.03.04.05.05.0
180%0.00.00.00.00.00.00.00.00.0
190%0.00.00.00.00.00.00.00.0
2033%0.70.00.01.00.00.00.0
2166%1.30.00.02.00.00.0
22100%2.00.00.03.00.0
Sheet2
Cell Formulas
RangeFormula
B12:J12B12=B10/B11
B15:J15B15=B17*B12+B10
C17:J17F17=SUM(F18:F22)+SUM($B22:E22)
B18:J18F18=$A18*F14
C19:J19F19=$A19*E14
D20:J20F20=$A20*D14
E21:J21F21=$A21*C14
F22:J22F22=$A22*B14
B17B17=SUM(B18:B22)
Click to expand...
Thank you for this! However, the issue is that the training months may vary

Like in the original sheet I had training at 2 months, but management wants it to be a number we can change at any time. Like if they want to enter the number "4" for 4 months of training. Then they won't start counting the employee until they go through 4 months of training.
 
Upvote 0
You can use the offset formula, allowing you to have a variable training period. See below example:


Mr_excel_task_assigned.xlsx
ABCDEFGHIJ
1Modifiers
2
3Training months2
4Productivity Month 133%
5Productivity Month 266%
6Productivity Month 3100%
7
8202220222022202220222022202220222022
9JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch
10Units155141155155158158173180181
11Employees424041414141414141
12Units per Employees3.693.533.783.783.853.854.224.394.41
13
14Additional Employees23
15New Units155141157160166170190202203
16
17Additional Employees adj for productivity0.661.322.002.993.985.005.005.005.00
1833%0.70.00.01.00.00.00.00.00.0
1966%1.30.00.02.00.00.00.00.0
20100%2.00.00.03.00.00.00.0
Sheet2
Cell Formulas
RangeFormula
B12:J12B12=B10/B11
B15:J15B15=B10+IFERROR((OFFSET(B17,0,-$C$3)*B12),0)
C17:J17D17=SUM(D18:D20)+SUM($B20:C20)
B18:J18D18=$A18*D14
C19:J19D19=$A19*C14
D20:J20D20=$A20*B14
B17B17=SUM(B18:B20)
A18:A20A18=C4
 
Upvote 0
Solution
Mr Kaka said:
You can use the offset formula, allowing you to have a variable training period. See below example:


Mr_excel_task_assigned.xlsx
ABCDEFGHIJ
1Modifiers
2
3Training months2
4Productivity Month 133%
5Productivity Month 266%
6Productivity Month 3100%
7
8202220222022202220222022202220222022
9JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch
10Units155141155155158158173180181
11Employees424041414141414141
12Units per Employees3.693.533.783.783.853.854.224.394.41
13
14Additional Employees23
15New Units155141157160166170190202203
16
17Additional Employees adj for productivity0.661.322.002.993.985.005.005.005.00
1833%0.70.00.01.00.00.00.00.00.0
1966%1.30.00.02.00.00.00.00.0
20100%2.00.00.03.00.00.00.0
Sheet2
Cell Formulas
RangeFormula
B12:J12B12=B10/B11
B15:J15B15=B10+IFERROR((OFFSET(B17,0,-$C$3)*B12),0)
C17:J17D17=SUM(D18:D20)+SUM($B20:C20)
B18:J18D18=$A18*D14
C19:J19D19=$A19*C14
D20:J20D20=$A20*B14
B17B17=SUM(B18:B20)
A18:A20A18=C4
Click to expand...
This is perfect, thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Automation for training sheet
2
Replies
13
Views
732
David Clark
D
B
  • Question Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
207
bh24524
B
W
  • Question Question
VBA - Match two values from Table1 to two in Table2 and move/align
Replies
1
Views
46
rpaulson
rpaulson
A
  • Solved
Payroll Forecasting Sheet-How to setup table by acct number and returning values closest to today's date.
Replies
1
Views
338
awiley1
A
L
  • Solved
Converting formula to use spill method
Replies
7
Views
219
Eric W
Eric W

Forum statistics

Threads
1,223,989
Messages
6,175,799
Members
452,670
Latest member
nogarth

Share this page

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
Back
Top