Ramp up formula with variable column start

[akatsuki]

I am building an implementation model and I have most of it done, the revenue table looks like this:

REVENUE TABLE

Opportunity	Start	Apr	May	Jun	Jul	Aug
Opp 1	May	0	10,000	10,000	10,000	10,000
Opp 2	Apr	5,000	5,000	5,000	5,000	5,000

Basically the table will show the calculated run rate based on the start date.

But, what I want to do on top of this is add a ramp.

RAMP TABLE

1st month	2nd month	3rd month	4th month	5th month
25%	50%	75%	100%	100%

So the table should look like this:

REVENUE TABLE WITH RAMP APPLIES

Opportunity	Start	Apr	May	Jun	Jul	Aug
Opp 1	May	0	2,500 (10,000 x 25%)	5,000 (10,000 x 50%)	7,500 (10,000x75%)	10,000 (10,000 x100%)
Opp 2	Apr	1,250 (5,000 x 25%)	2,500 (5,000 x 50%)	3,750 (5,000 x 75%)	5,000 (5,000 x 100%)	5,000 (5,000 x 100%

So what I am trying to do is have a draggable formula but it needs to apply the 25% of the ramp table to the start date even though that column varies.

 

[Cubist]

Which XL version are you using?

Would you ever have a 0 in the middle?

Book1
	A	B	C	D	E	F	G
1	Opportunity	Start	Apr	May	Jun	Jul	Aug
2	Opp 1	May	0	10,000	0	10,000	10,000
3	Opp 2	Apr	5,000	5,000	5,000	5,000	5,000
Sheet2

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[akatsuki]

Which XL version are you using?

Would you ever have a 0 in the middle?

Book1
	A	B	C	D	E	F	G
1	Opportunity	Start	Apr	May	Jun	Jul	Aug
2	Opp 1	May	0	10,000	0	10,000	10,000
3	Opp 2	Apr	5,000	5,000	5,000	5,000	5,000
Sheet2

Yes, we can have account churn or interruptions that drop it to zero.

Since I have a column identifying the start date, that part is easy. The part that I find hard is how to have the formula start at the first part of the ramp no matter which column has the start date.

 

[akatsuki]

OK, I solved it with the use of an offset

=OFFSET(Ramp Table 25% cell,0,(MONTH(Revenue table month)-MONTH(Revenue Table start column))