# Excel formula for a dynamic ramp plan based on job and recruitment

#### Hemar5

##### New Member
Hi All,

I have attached spreadsheet with 3 tabs
Details: Please refer employees recruited number for each job per week (Column O to BV)
Summary : This is where the formula should go
Refer: This is reference of a value used in formula

There are 10 Jobs for which employees would be recruited within 60 weeks randomly. (Details tab: Column O to BV)
Summary Tab:

1. You can select each job in B1
2. Onboarded employees will be reflected in Row 2 from Column V: Column BL
3. Ramp% will get reflected from Refer tab
4. Production of one employee= Target(always 96)*Ramp%
5. First week employee will do 5%
6. Next week 10%
...likewise
7. Formula should be written in Row#8 of Summary.
8. This should reflect the total production by all employees for the week
9. if that week has 2 employees in Ramp2 week and 3 employees from Ramp3 week, then it should say 10%*96*2 + 15%*96*3
10. I have written the formula which works if all job starts in Week1. But, each job gets its employee in different weeks and ramp% changes for each change

Thank you so much

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Attachments

• 2.PNG
9.3 KB · Views: 9

#### CA_Punit

##### Well-known Member
It is very difficult to estimate the column reference from the picture.

#### Hemar5

##### New Member
Ramp Up Plan.xlsx
ABCDEFGHIJKLMNOPQRST
1Process Job1 Week --->Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16
2Total Monthly 1,000 Onboarded Employees 3 3 3 3 3 3 3 3 2 10 10 10 10 10 10 10
3Per day53 Ramp 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
4Target per day 96 Ramp% 5%10%15%20%30%40%50%60%70%80%90%100%100%100%100%100%
5Required FTE1 Ramp Target per Employee 5 10 14.4 19.2 28.8 38.40 48.00 57.60 67.20 76.80 86.40 96 96 96 96 96
6 Number of Business Days 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
7 Weekly Ramp per Employee 5 10 14 19 29 38 48 58 67 77 86 96 96 96 96 96
8 Volume per Week 14.4 43.2 86.4 144.0 230.4 345.6 489.6 662.4 859.2 1,118.4 1,440.0 1,824.0 2,236.8 2,716.8 3,264.0 3,878.4
9
Summary
Cell Formulas
RangeFormula
E2:T2E2=VLOOKUP(\$B\$1,Table1,COLUMN()+13,0)
E3E3=IF(E2>0,1,0)
F3:T3F3=IF(E3<1,IF(F2>0,E3+1,0),E3+1)
E4,N4:T4E4=IF(E3>0,VLOOKUP(E3,Refer!\$A:\$B,2,0),0)
F4:M4F4=IF(E3>1,VLOOKUP(F3,Refer!\$A:\$B,2,0),IF(F3>0,VLOOKUP(F3,Refer!\$A:\$B,2,0),0))
E5:T5E5=E\$4*\$B\$4
B2B2=VLOOKUP(\$B\$1,Details!\$A:\$N,5,0)
B3B3=B2/19
B5B5=B3/B4
E7:T7E7=E5*E6
E8E8=(\$E\$2*E5*E6)
F8F8=(\$E\$2*F5*F6)+(\$F\$2*E5*E6)
G8G8=(\$E\$2*G5*G6)+(\$F\$2*F5*F6)+(\$G\$2*E5*E6)
H8H8=(\$E\$2*H5*H6)+(\$F\$2*G5*G6)+(\$G\$2*F5*F6)+(\$H\$2*E5*E6)
I8I8=(\$E\$2*I5*I6)+(\$F\$2*H5*H6)+(\$G\$2*G5*G6)+(\$H\$2*F5*F6)+(\$I\$2*E5*E6)
J8J8=(\$E\$2*J5*J6)+(\$F\$2*I5*I6)+(\$G\$2*H5*H6)+(\$H\$2*G5*G6)+(\$I\$2*F5*F6)+(\$J\$2*E5*E6)
K8K8=(\$E\$2*K5*K6)+(\$F\$2*J5*J6)+(\$G\$2*I5*I6)+(\$H\$2*H5*H6)+(\$I\$2*G5*G6)+(\$J\$2*F5*F6)+(\$K\$2*E5*E6)
L8L8=(\$E\$2*L5*L6)+(\$F\$2*K5*K6)+(\$G\$2*J5*J6)+(\$H\$2*I5*I6)+(\$I\$2*H5*H6)+(\$J\$2*G5*G6)+(\$K\$2*F5*F6)+(\$L\$2*E5*E6)
M8M8=(\$E\$2*M5*M6)+(\$F\$2*L5*L6)+(\$G\$2*K5*K6)+(\$H\$2*J5*J6)+(\$I\$2*I5*I6)+(\$J\$2*H5*H6)+(\$K\$2*G5*G6)+(\$L\$2*F5*F6)+(\$M\$2*E5*E6)
N8N8=(\$E\$2*N5*N6)+(\$F\$2*M5*M6)+(\$G\$2*L5*L6)+(\$H\$2*K5*K6)+(\$I\$2*J5*J6)+(\$J\$2*I5*I6)+(\$K\$2*H5*H6)+(\$L\$2*G5*G6)+(\$M\$2*F5*F6)+(\$N\$2*E5*E6)
O8O8=(\$E\$2*O5*O6)+(\$F\$2*N5*N6)+(\$G\$2*M5*M6)+(\$H\$2*L5*L6)+(\$I\$2*K5*K6)+(\$J\$2*J5*J6)+(\$K\$2*I5*I6)+(\$L\$2*H5*H6)+(\$M\$2*G5*G6)+(\$N\$2*F5*F6)+(\$O\$2*E5*E6)
P8P8=(\$E\$2*P5*P6)+(\$F\$2*O5*O6)+(\$G\$2*N5*N6)+(\$H\$2*M5*M6)+(\$I\$2*L5*L6)+(\$J\$2*K5*K6)+(\$K\$2*J5*J6)+(\$L\$2*I5*I6)+(\$M\$2*H5*H6)+(\$N\$2*G5*G6)+(\$O\$2*F5*F6)+(\$P\$2*E5*E6)
Q8Q8=(\$E\$2*Q5*Q6)+(\$F\$2*P5*P6)+(\$G\$2*O5*O6)+(\$H\$2*N5*N6)+(\$I\$2*M5*M6)+(\$J\$2*L5*L6)+(\$K\$2*K5*K6)+(\$L\$2*J5*J6)+(\$M\$2*I5*I6)+(\$N\$2*H5*H6)+(\$O\$2*G5*G6)+(\$P\$2*F5*F6)+(\$Q\$2*E5*E6)
R8R8=(\$E\$2*R5*R6)+(\$F\$2*Q5*Q6)+(\$G\$2*P5*P6)+(\$H\$2*O5*O6)+(\$I\$2*N5*N6)+(\$J\$2*M5*M6)+(\$K\$2*L5*L6)+(\$L\$2*K5*K6)+(\$M\$2*J5*J6)+(\$N\$2*I5*I6)+(\$O\$2*H5*H6)+(\$P\$2*G5*G6)+(\$Q\$2*F5*F6)+(\$R\$2*E5*E6)
S8S8=(\$E\$2*S5*S6)+(\$F\$2*R5*R6)+(\$G\$2*Q5*Q6)+(\$H\$2*P5*P6)+(\$I\$2*O5*O6)+(\$J\$2*N5*N6)+(\$K\$2*M5*M6)+(\$L\$2*L5*L6)+(\$M\$2*K5*K6)+(\$N\$2*J5*J6)+(\$O\$2*I5*I6)+(\$P\$2*H5*H6)+(\$Q\$2*G5*G6)+(\$R\$2*F5*F6)+(\$S\$2*E5*E6)
T8T8=(\$E\$2*T5*T6)+(\$F\$2*S5*S6)+(\$G\$2*R5*R6)+(\$H\$2*Q5*Q6)+(\$I\$2*P5*P6)+(\$J\$2*O5*O6)+(\$K\$2*N5*N6)+(\$L\$2*M5*M6)+(\$M\$2*L5*L6)+(\$N\$2*K5*K6)+(\$O\$2*J5*J6)+(\$P\$2*I5*I6)+(\$Q\$2*H5*H6)+(\$R\$2*G5*G6)+(\$S\$2*F5*F6)+(\$T\$2*E5*E6)
Cells with Data Validation
CellAllowCriteria
Q5:T5Text length=0
Q7:T8Text length=0
B1List=Details!\$A\$4:\$A\$13

#### Hemar5

##### New Member

Ramp Up Plan.xlsx
ABCDEFGHIJKLMNO
1Process Job4 Week --->Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11
2Total Monthly 1,003 Onboarded Employees - - - - - 3 3 3 - 4 -
3Per day53 Ramp - - - - - 1 2 3 4 5 6
4Target per day 96 Ramp% 0%0%0%0%0%5%10%15%20%30%40%
5Required FTE1 Ramp Target per Employee - - - - - 4.80 9.60 14.40 19.20 28.80 38.40
6 Number of Business Days 1 1 1 1 1 1 1 1 1 1 1
7 Weekly Ramp per Employee - - - - - 5 10 14 19 29 38
8 Volume per Week - - - - - - - - - - 14.4
Summary
Cell Formulas
RangeFormula
E2:O2E2=VLOOKUP(\$B\$1,Table1,COLUMN()+13,0)
E3E3=IF(E2>0,1,0)
F3:O3F3=IF(E3<1,IF(F2>0,E3+1,0),E3+1)
E4,N4:O4E4=IF(E3>0,VLOOKUP(E3,Refer!\$A:\$B,2,0),0)
F4:M4F4=IF(E3>1,VLOOKUP(F3,Refer!\$A:\$B,2,0),IF(F3>0,VLOOKUP(F3,Refer!\$A:\$B,2,0),0))
E5:O5E5=E\$4*\$B\$4
B2B2=VLOOKUP(\$B\$1,Details!\$A:\$N,5,0)
B3B3=B2/19
B5B5=B3/B4
E7:O7E7=E5*E6
E8E8=(\$E\$2*E5*E6)
F8F8=(\$E\$2*F5*F6)+(\$F\$2*E5*E6)
G8G8=(\$E\$2*G5*G6)+(\$F\$2*F5*F6)+(\$G\$2*E5*E6)
H8H8=(\$E\$2*H5*H6)+(\$F\$2*G5*G6)+(\$G\$2*F5*F6)+(\$H\$2*E5*E6)
I8I8=(\$E\$2*I5*I6)+(\$F\$2*H5*H6)+(\$G\$2*G5*G6)+(\$H\$2*F5*F6)+(\$I\$2*E5*E6)
J8J8=(\$E\$2*J5*J6)+(\$F\$2*I5*I6)+(\$G\$2*H5*H6)+(\$H\$2*G5*G6)+(\$I\$2*F5*F6)+(\$J\$2*E5*E6)
K8K8=(\$E\$2*K5*K6)+(\$F\$2*J5*J6)+(\$G\$2*I5*I6)+(\$H\$2*H5*H6)+(\$I\$2*G5*G6)+(\$J\$2*F5*F6)+(\$K\$2*E5*E6)
L8L8=(\$E\$2*L5*L6)+(\$F\$2*K5*K6)+(\$G\$2*J5*J6)+(\$H\$2*I5*I6)+(\$I\$2*H5*H6)+(\$J\$2*G5*G6)+(\$K\$2*F5*F6)+(\$L\$2*E5*E6)
M8M8=(\$E\$2*M5*M6)+(\$F\$2*L5*L6)+(\$G\$2*K5*K6)+(\$H\$2*J5*J6)+(\$I\$2*I5*I6)+(\$J\$2*H5*H6)+(\$K\$2*G5*G6)+(\$L\$2*F5*F6)+(\$M\$2*E5*E6)
N8N8=(\$E\$2*N5*N6)+(\$F\$2*M5*M6)+(\$G\$2*L5*L6)+(\$H\$2*K5*K6)+(\$I\$2*J5*J6)+(\$J\$2*I5*I6)+(\$K\$2*H5*H6)+(\$L\$2*G5*G6)+(\$M\$2*F5*F6)+(\$N\$2*E5*E6)
O8O8=(\$E\$2*O5*O6)+(\$F\$2*N5*N6)+(\$G\$2*M5*M6)+(\$H\$2*L5*L6)+(\$I\$2*K5*K6)+(\$J\$2*J5*J6)+(\$K\$2*I5*I6)+(\$L\$2*H5*H6)+(\$M\$2*G5*G6)+(\$N\$2*F5*F6)+(\$O\$2*E5*E6)
Cells with Data Validation
CellAllowCriteria
B1List=Details!\$A\$4:\$A\$13

#### Hemar5

##### New Member
Formula should be entered into the Volume per week row(Row 8).

Thanks

#### Fluff

##### MrExcel MVP, Moderator
Cross posted Dynamic formula to calculate ramp production based on recruitment

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Replies
2
Views
155
Replies
2
Views
49
Replies
1
Views
101
Replies
9
Views
243
Replies
1
Views
47

1,128,001
Messages
5,628,063
Members
416,290
Latest member
antoniokobe

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