Results 1 to 3 of 3

Thread: Calculating percentage spread over time

  1. #1

    Calculating percentage spread over time



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    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


  3. #3
    Quote Originally Posted by NBVC View Post
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •