# Calculating percentage spread over time

#### nancydong2012

##### New member
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

##### Super Moderator
Staff member
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

#### nancydong2012

##### New member
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

Amazing!!! Thank you so much!!