Calculating percentage spread over time

[nancydong2012]

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.

Benefit Start Date	2014	2015	2016	2017
Q3 2015	0%	40%	40%	20%

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.

Q12014

q22014

Q32014

Q42014

Q12015

Q22015

Q32015

Q42015

Q12016

Q22016

Q32016

Q42016

Q12017

Q22017

Q32017

Q42017

20%

20%

10%

10%

10%

10%

5%

5%

5%

5%

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

[NBVC]

Assuming your first table is in A1:E2 and your second table is in A5:P6

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

[nancydong2012]

Assuming your first table is in A1:E2 and your second table is in A5:P6

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

Amazing!!! Thank you so much!!