Calculating percentage spread over time

nancydong2012

New member
Joined
May 23, 2014
Messages
2
Reaction score
0
Points
0
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 Date2014201520162017
Q3 20150%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.

Q12014q22014Q32014Q42014Q12015Q22015Q32015Q42015Q12016Q22016Q32016Q42016Q12017Q22017Q32017Q42017
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
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
May 23, 2014
Messages
2
Reaction score
0
Points
0
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!!
 
Top