Calculating savings depending on value of columns E & F

Good afternoon

I was kindly provided with the formula in column G.

The formula assumes:
• If column E = 0, it returns 0, otherwise
• It takes column F and multiples this by F, up to a maximum of 100
• After this 100, it takes the remaining of the number (above 100) and multiples this by half of F
• It then removes case spend (H)

What I now need the formula to do, is in the second part of the formula (where is multiples by half, only multiple up a maximum of 90 days and then stop. Effectively it should not calculate anything above and beyond 190 days.

Hope this makes sense, but any questions please let me know.

Try

in G2

=IF(E2=0,0,F2*(MIN(100,E2)+MAX(0,MIN(E2-90,90))*0.5)-H2)

Thanks for the response both

John, can you please explain to me what the changes to the second part of the formula do:

MAX(0,MIN(E2-90,90))

Thank you

=MAX(0,MIN(E2-90,90))

The MIN finds the MINIMUM value of E2-90 and 90 so if E2=216 then 216-90=126 so MIN(126,90) returns 90.

MAX then returns (0,90) =90

if E2=80, then E2-90= -10 so MIN(-10,90) returns -10

MAX(0,-10) returns 0

Thank you so much as always, very helpful

So talking through the whole formula

Formula
=IF(E2=0,0,F2*(MIN(100,E2)+MAX(0,MIN(E2-90,90))*0.5)-H2)

How it works ...

if E2=0, result is 0 else ...

=IF(E2=0,0,F2*(MIN(100,E2)+ MAX(0,MIN(E2-90,90))*0.5)-H2)

...Selects the minimum value of 100 (or the value of E2 if below 100)

=IF(E2=0,0,F2*(MIN(100,E2)+MAX(0,MIN(E2-90,90))*0.5)-H2)

… finds the MINIMUM value of E2 – 90. E.g. if E2 = 216, 216-90=126
… Therefore MIN(126,90) returns 90 because 90 is the minimum of the two numbers
… MAX then finds the largest number by comparing 0 and the result of MIN(E2-90,90) or MIN(0,90). The MAX = 90 is the above example
… Therefore if, E2 = 80, MIN(80-90,90) provides the result MIN(-10,90)
… The formula then looks for the MAX(0,-10) and returns, 0 because 0 is the largest number. This therefore means nothing I added to the overall calculation

=IF(E2=0,0,F2*(MIN(100,E2)+MAX(0,MIN(E2-90,90))*0.5)-H2)
.....selects the maximum value of 0 or the MIN(E2-90,90), so if E2 < 90, you will get a negative number ([E2=]80-100 = -20) so 0 will be selected: otherwise result is E2-90 (this means if the amount is above 100, you will always end with a positive). The result is multiplied by 0.5 (equivalent to 50%)

=IF(E2=0,0,F2*(MIN(100,E2)+MAX(0,E2-100)*0.5)-H2)
... finally H2 is subtracted from the above

What I can’t quite get my head around (and this may just me being dense), is what happens if the value in E2 = 101

Theoretically, the formula should multiply 100, by the full benefit rate (F2) and then multiply 1, by half (0.5) of F2. However, I don’t see how this would work with the new formula unless I’m missing something?

Thanks for your continue help.

So:

MAX(0,MIN(E2-100,90))
Ok so I think that works, for example:
E2 = 101
1. finds the MINIMUM value of E2 – 100 (101-100 = 1)
2. Therefore MIN(1,90) returns 1 because 1 is the minimum of the two numbers
3. MAX then finds the largest number by comparing 0 and MIN(1,90). The MAX = 1 in the above example
4. Therefore 1 is multiplied by 0.5

So I guess, just to finalise, if:

E2 = 300
1. finds the MINIMUM value of E2 – 100 (300-100 = 200)
2. Therefore MIN(200,90) returns 90 because 90 is the minimum of the two numbers
3. MAX then finds the largest number by comparing 0 and MIN(200,90). The MAX = 90 in this example
4. Therefore 90 is multiplied by 0.5

The second example, if in effect, the control, to make sure there is never a multiplication over the value of 90 correct?

That is it: simply ensures you cannot have 50% value > 90

Thanks, John - you are my hero! Unfortunately, I can't give you more rep for some reason?

No problem: just glad it is solved.

Please mark as SOLVED.