Ramp up calculation of sales depending on staff hire date

Dear all,

I am trying to come up with a formula that will allow me to calculate units sold per sales rep depending on a ramp up model that takes into consideration the start date of the sales rep.
For example, in month 1 we expect the new hired sales rep to reach 5% of the targeted unit sales. In month 2, we expect that sales rep to reach 20% of the targeted unit sales, etc..
I have a ramp up model that calculates the number of units sold taking the % into account (lines 1-3).

In lines 21-28, I am trying to calculate how many units each sales rep will sell in any given month depending on when he started. I started keying in the formulas adding IF depending on when the staff started, but I believe my formula is too complicated and i am sure there must be an easier way to get there. Especially, coz sales rep can start anytime, meaning if they start in month 6, I also want to use the ramp up factor of month 1, then in month 7 I want to use the ramp up factor of month 2, etc...

I am struggling to come up with such a formula that I can drag.

Any idea how to do this?

Any help is greatly appreciated
Thanks much
Kind regards
S

Dear John,
thanks much that is super helpful already.

Sorry to trouble again.... but a few questions:

I've seen in your formula you are referring to my table where the sales units are as "COLUMNS($A7:A7)". Why are you starting this is A, if my numbers only start in Column E?

=IFERROR(IF($E$22:E$22="TOTAL","",IF(COUNTIF($E14:E14,"x"),INDEX($E$6:$P$6,0,COLUMNS($A7:A7)-MATCH("x",$E14:$AB14,0)+IF(COUNTIF($E$22:E$22,"TOTAL"),IF(COUNTIF($E14:E14,"x"),1,0),1))*$C25,"")),$C25)

Also, I have seen that you have replaced the number of new hires by X and when a cell has an X, then the formula matches the respective sales unit. I had put numbers in there because in some months, there will be 1 new hire but in other month it will be 3 or more, so if I put an "X", i cannot calculate the proper number of sales units. Is there a possibility to keep the numbers in lines 14-17 etc to reflect the number of new hires and still get the calculation below automated with a formula that I can drag?

Thanks so much!