Calculating percentage spread over time

All,

I need some help creating a dynamic formula that calculates a percentage based on when a benefit is supposed to begin.

For example, let's say we receive the following data about an "Awesome Benefit" that we will start realizing in Q3 2015. 100% will be realized by end of 2017.


I want to develop a formula that essentially populates the following information, where the full 40% for 2015 is realized over Q3 and Q4 for 2015, and 2016 percentage is spread over all 4 quarters evenly.


I would very very much appreciate if someone could help...I have been trying to make nested if statements based upon Quarter and year, but Excel cannot process that many if(and()s.

Thank you thank you!!!

Assuming your first table is in A1:E2 and your second table is in A56

Then in A6 enter formula:

=IF(--(RIGHT(A5,4)&MID(A5,2,1))>=--(RIGHT($A$2,4)&MID($A$2,2,1)),LOOKUP(--RIGHT(A5,4),$B$1:$E$2)/SUMPRODUCT(--(RIGHT($A$5:$P$5,4)=RIGHT(A5,4)),--(--(RIGHT($A$5:$P$5,4)&MID($A$5:$P$5,2,1))>=--(RIGHT($A$2,4)&MID($A$2,2,1)))),"")

copied right

NBVC said:

Assuming your first table is in A1:E2 and your second table is in A56

Then in A6 enter formula:

=IF(--(RIGHT(A5,4)&MID(A5,2,1))>=--(RIGHT($A$2,4)&MID($A$2,2,1)),LOOKUP(--RIGHT(A5,4),$B$1:$E$2)/SUMPRODUCT(--(RIGHT($A$5:$P$5,4)=RIGHT(A5,4)),--(--(RIGHT($A$5:$P$5,4)&MID($A$5:$P$5,2,1))>=--(RIGHT($A$2,4)&MID($A$2,2,1)))),"")

copied right

Click to expand...

Amazing!!! Thank you so much!!