Numbers formula for conditional maximum

Hi

If I understand your question correctly, this may work.

Formula in C2 is IF(B2>87500,B2−87500,150000−87500)

In English, if value is greater than 87,500, then show value minus 87,500, else show 62,500

150000-87500 is 62,500

Regards,

Ian.

> The LET function can help with that but it would still be complicated.

Agreed... the rules are complex, but I think this is something that LET() can help with, especially with the reuse of values.

Here's my run at it using LET()

=LET(sales,B3,

     tier1_floor,87500,

     tier2_floor,150000,

     tier3_floor,200000,
	


     tier1_eligible,MIN(tier2_floor,sales)−tier1_floor,

     tier1,MAX(0,tier1_eligible)×0.2,
	


     tier2_eligible,MIN(tier3_floor,sales)−tier2_floor,

     tier2,MAX(0,tier2_eligible)×0.25,


     tier3_eligible,MAX(0,sales−tier3_floor),

     tier3,MAX(0,tier3_eligible)×0.3,


     tier1+tier2+tier3)

It looks complex, but the idea is actually pretty simply.

LET() starts by defining a couple of variables, so we can use names rather than cell references for some of the values. This gives a central place to define the bonus boundaries. I used hardcoded values, but these could easily be lookups or references to other cells.

The formula then works out how much of the sales are eligible for the tier 1 bonus, by calculating the lower of tier2's floor (150,000) and the sales. This is then multiplied by 0.2 to calculate the amount of Tier 1 bonus. (note this could be done in one line, but I prefer to break it out for clarity.

     tier1_eligible,MIN(tier2_floor,sales)−tier1_floor,

     tier1,MAX(0,tier1_eligible)×0.2,
	


A similar method is used to calculate the tier 2 bonus - subtract the sales from the tier3 floor and multiply it by 0.25 to calculate the tier2 bonus

Then we do a similar thing again with the tier3 bonus (although this time I don't cap it - I assumed that everything above 200,000 is rated at 0.3, whereas Badunit capped at 400,000 (a good problem to have? :) )

At the end, it's a simple matter of adding the various tierx bonus values to get the answer you're looking for.

Here is a formula to replicate the results of that table. I think there is an easier way to do this with MOD and stuff or it could be done with a lookup table. The problem I have with my formula, aside from its length, is that it repeats some constants a few times which makes it easy to mess up if something has to change (like changing the 0.2 multiplier to 0.21 would require changing it in 3 places). The LET function can help with that but it would still be complicated.

=IFS(B3<87500,0,B3≤150000, 0.2×(B3−87500),B3≤200000,0.2×(150000−87500)+0.25×(B3−150000),B3≤400000, 0.2×(150000−87500)+0.25×(200000−150000)+0.3×(B3−200000),TRUE, "what about if over 400K?")

Here is a lookup table version of it

B2=(B2−XLOOKUP(B2,Table 2::A,Table 2::A,"",−1))×XLOOKUP(B2,Table 2::A,Table 2::B,"",−1)+XLOOKUP(B2,Table 2::A,Table 2::C,"",−1)

The running total amounts of the lower tiers is what I called "residual" in the table below.