Peanut butter spread calculation based on Time

N

nld08

New Member
Joined
Mar 14, 2014
Messages
2
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Benefit Start Date[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Q3 2015[/TD]
[TD]0%[/TD]
[TD]40%[/TD]
[TD]40%[/TD]
[TD]20%[/TD]
[/TR]
</tbody>[/TABLE]

I want to develop a formula that essentially populates the following information (the breakdown of % by quarter), where the full 40% for 2015 is realized over Q3 and Q4 for 2015, and 2016 percentage is spread over all 4 quarters evenly (AKA peanut butter spread for the remaining full years)

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Q12014[/TD]
[TD]q22014[/TD]
[TD]Q32014[/TD]
[TD]Q42014[/TD]
[TD]Q12015[/TD]
[TD]Q22015[/TD]
[TD]Q32015[/TD]
[TD]Q42015[/TD]
[TD]Q12016[/TD]
[TD]Q22016[/TD]
[TD]Q32016[/TD]
[TD]Q42016[/TD]
[TD]Q12017[/TD]
[TD]Q22017[/TD]
[TD]Q32017[/TD]
[TD]Q42017[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
It's probably easiest if you split the dates into Year and Quarter components, and use some helper cells.

Here's one way you could do it.

Excel 2010
ABCDEFGHIJKLMNOPQ

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Qtr[/TD]
[TD="align: center"]Benefit %[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Quarter[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]2014[/TD]
[TD="align: center"]2014[/TD]
[TD="align: center"]2014[/TD]
[TD="align: center"]2014[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2017[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Qtr[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]5%[/TD]

</tbody>
1​

Excel 2010
AB
Range Names
BenefitSplitE2:I8
StartYrB2
StartQtrC2
EndYrB3
EndQtrC3
Formulae
E4=IF(AND(E$2*4+$D4>=4*StartYr+StartQtr,E$2*4+$D4<=4*EndYr+EndQtr),1,0)
Copy across and down
E8=SUM(E4:E7) Copy across
B12=IFERROR(HLOOKUP(B10,BenefitSplit,2)*HLOOKUP(B10,BenefitSplit,B11+2)/HLOOKUP(B10,BenefitSplit,7),0)
Copy across

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]

</tbody>
1​
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Spreading Months between Financial Year
Replies
3
Views
674
Mahenderlalwani
M
B
  • Question Question
Simplifying a formula that calculates time.
2
Replies
11
Views
1K
Fluff
Fluff
D
  • Question Question
Formula for Quarter Year
Replies
1
Views
468
Ron Coderre
Ron Coderre
B
  • Question Question
Annualized IRR
Replies
5
Views
20K
joeu2004
J

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top