Dynamic formula to calculate ramp production based on recruitment

[Hemar5]

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 Ramp% 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


Please help on the formula

Thank you so much

 

[Chihiro]

Cross post. Please read forum rule and follow it.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Hemar5]

Thank you for notifying me on the Cross post rules. This would be taken care moving forward. Thanks

 

[vletm]

Hemar5
If You thanked for notifying,
then You also should follow those Rules,
before waiting any replies.
Please, reread those Forum Rules.