Forum Discussion

CJNed's avatar
CJNed
Copper Contributor
May 16, 2023
Solved

Need help with formula evaluating progressive rebate against 2 criteria

Hi - I'm trying to model what a tiered, progressive, rebate would look like against two different contracts (old/new) rebate percentages.  The new contract (higher rebate %) would benefit from the ti...
Formulas and Functions
  • mtarler's avatar
    mtarler
    May 18, 2023

    SnowMan55 alternative option:

     

    =SUMPRODUCT($B$12:$B$19*(D30>=$D$12:$D$19)
             -$B$12:$B$19*(B30>=$D$12:$D$19)
             -(B30-$C$12:$C$19+1)*(B30<$D$12:$D$19)*(B30>=$C$12:$C$19)
             +(D30-$C$12:$C$19+1)*(D30>=$C$12:$C$19)*(D30<$D$12:$D$19),
            $I$12:$I$19)

     

    line 1 add tier size if total > tier max

    line 2 subtract tier size if Old > tier max

    line 3 subtract portion left from Old on that tier

    line 4 add portion from total on that tier

    line 5 multiply by %

     

    Note that I agree with SnowMan55  that the numbers you have for the Old Contr. are not right.  My formula for that portion is 

     

    =SUMPRODUCT($B$12:$B$19*(B29>=$D$12:$D$19)
             +(B29-$C$12:$C$19+1)*(B29<$D$12:$D$19)*(B29>=$C$12:$C$19),
            $H$12:$H$19)

     

    similar in concept to the above.

    And lots of kudos to SnowMan55  for re-creating that sheet (although I tweaked a couple formulas I thought weren't done how intended)

    Lastly I added a number 1 for the min on the first Tier for consistency and for making calculations easier (i.e. that -$C$12:$C$19+1 in my formulas) but could have used +$B$12:$B$19-$D$12:$D$19 instead.

     

    BTW i realized it would be a huge help here to define those ranges with names so the updated formula (I only used in the 2025 part of the table is:

    =SUMPRODUCT(TierSize*(J29>=TierMax)
             -TierSize*(H29>=TierMax)
             -(H29-TierMin+1)*(H29<TierMax)*(H29>=TierMin)+
             (J29-TierMin+1)*(J29>=TierMin)*(J29<TierMax),
            NewPercent)
SnowMan55's avatar
SnowMan55
Bronze Contributor
May 18, 2023

CJNed As there are only 8 tiers, how about using "brute force-calculation" formulas to calculate rebates?  Specifically for cell L29:

=$I$12   * MAX( 0, MIN($B$12, $B$12-MAX(0, MIN($B$12, B29-0)), C29, C29+B29-0 ) )
 + $I$13 * MAX( 0, MIN($B$13, $B$13-MAX(0, MIN($B$13, B29-$D$12)), C29, C29+B29-$D$12 ) )
 + $I$14 * MAX( 0, MIN($B$14, $B$14-MAX(0, MIN($B$14, B29-$D$13)), C29, C29+B29-$D$13 ) )
 + $I$15 * MAX( 0, MIN($B$15, $B$15-MAX(0, MIN($B$15, B29-$D$14)), C29, C29+B29-$D$14 ) )
 + $I$16 * MAX( 0, MIN($B$16, $B$16-MAX(0, MIN($B$16, B29-$D$15)), C29, C29+B29-$D$15 ) )
 + $I$17 * MAX( 0, MIN($B$17, $B$17-MAX(0, MIN($B$17, B29-$D$16)), C29, C29+B29-$D$16 ) )
 + $I$18 * MAX( 0, MIN($B$18, $B$18-MAX(0, MIN($B$18, B29-$D$17)), C29, C29+B29-$D$17 ) )
 + $I$19 * MAX( 0, MIN($B$19, $B$19-MAX(0, MIN($B$19, B29-$D$18)), C29, C29+B29-$D$18 ) )

Copy the formula as needed.


The inner MAX and MIN functions are used to calculate the Old Contract Spend amounts by tier (the same calculations as in cells F12 through F19). The outer MIN functions imitate the calculations in cells G12 through G19; their arguments ensure that the New Contract Spend amounts by tier are no greater than:

  1. the Tier Size (a better name than Tier Range);
  2. the Tier Size minus the Old Contract Spend amount in that tier;
  3. the entire New Contract Spend Amount; or
  4. the sum of the Spend Amounts minus all the monies spent or spendable in lower tiers (so this could be a negative number, but the outer "MAX( 0, …" keeps that from being a problem).

Your sample calculations for Old Contract Rebates in 2023 and 2024 apparently do not follow the same calculation rules as in the upper block.  Especially note that the rebates in cells K30 and K31 have the same values, but are based on Old Contract Spends that differ by $500,000.  See the attached workbook for my calculations.

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 18, 2023

    SnowMan55 alternative option:

     

    =SUMPRODUCT($B$12:$B$19*(D30>=$D$12:$D$19)
             -$B$12:$B$19*(B30>=$D$12:$D$19)
             -(B30-$C$12:$C$19+1)*(B30<$D$12:$D$19)*(B30>=$C$12:$C$19)
             +(D30-$C$12:$C$19+1)*(D30>=$C$12:$C$19)*(D30<$D$12:$D$19),
            $I$12:$I$19)

     

    line 1 add tier size if total > tier max

    line 2 subtract tier size if Old > tier max

    line 3 subtract portion left from Old on that tier

    line 4 add portion from total on that tier

    line 5 multiply by %

     

    Note that I agree with SnowMan55  that the numbers you have for the Old Contr. are not right.  My formula for that portion is 

     

    =SUMPRODUCT($B$12:$B$19*(B29>=$D$12:$D$19)
             +(B29-$C$12:$C$19+1)*(B29<$D$12:$D$19)*(B29>=$C$12:$C$19),
            $H$12:$H$19)

     

    similar in concept to the above.

    And lots of kudos to SnowMan55  for re-creating that sheet (although I tweaked a couple formulas I thought weren't done how intended)

    Lastly I added a number 1 for the min on the first Tier for consistency and for making calculations easier (i.e. that -$C$12:$C$19+1 in my formulas) but could have used +$B$12:$B$19-$D$12:$D$19 instead.

     

    BTW i realized it would be a huge help here to define those ranges with names so the updated formula (I only used in the 2025 part of the table is:

    =SUMPRODUCT(TierSize*(J29>=TierMax)
             -TierSize*(H29>=TierMax)
             -(H29-TierMin+1)*(H29<TierMax)*(H29>=TierMin)+
             (J29-TierMin+1)*(J29>=TierMin)*(J29<TierMax),
            NewPercent)
    • CJNed's avatar
      CJNed
      Copper Contributor
      May 18, 2023
      SnowMan55 mtarler

      Thank you, both, for your help!! I was spinning on this for so long! Additionally, thank you for catching my error with the old contract calculation! mtarler, I am using named ranges in my master file, I actually removed them so to share the formula since I couldn't figure out how to attach my sample file. 🙂

      Thanks again!!

Resources