[Solved] Determine sum with two rate brackets and a maximum

[perilous1979]

Hello everyone!

I need to create a formula with which i can type a numerical value in one cell, then if this numerical value is under 6.500 (for example) it calculates the 1% percentage in a new cell (i think i got this one =IF(A9<6500; A9*0,01)

but if the value is greater than 6.500 i want it to calculate the 1% percentage for the first 6.500 euro and for the surplus value i want it to calculate the 0,5% percentage for a maximum of 145

For example if the numerical value is 9000 i want it to calculate the 1% for the 6.500 = 65 and the 0,5% for the rest 3.500 - 17,5 and sum it up = 82,5

But if the numerical value is 70.000 with the above example the sum would be 1% for the 6.500 = 65 and the 0,5% for the rest 63.500 = 317,50 sum= 382,50 which exceeds the maximum of 145 and in that case i would want the sum not to exceed the 145 value.

Many thanks in advance

Edit: Changed subject, was Need help with IF formula  Make your post understandable by others  -- MrProgrammer, forum moderator

[lader]

The checked value is in cell A1, then

Code: Select all

=MIN(IF(A1<6500; A1*0.01; 65+(A1-6500)*0.005); 145)

[perilous1979]

Thank you for your input but using this formula didn't work out the way i intented

If i put 8000 as an example then the result should be 6500*0,01 + 1500*0,005 = 65 + 7,5 = 72,5 instead with this formula the result is 145

[lader]

Check the correct notation of the formula, is there really MIN? According to the result, I would guess the function 'MAX.

[MrProgrammer]

… for the surplus value i want it to calculate the 0,5% percentage for a maximum of 145

This says to me that the maximum for ½% of the surplus is 145€. Thus the maximum of the sum is 65€+145€ = 210€. In that case the formula is:
=SUMPRODUCT(A9>{0;6500;35500};A9-{0;6500;35500};{1E-2;-5E-3;-5E-3})

Edit: Constants 1E-2 and -5E-3 are locale-independent.

I would want the sum not to exceed the 145 value.

Perhaps you want the maximum for ½% of the surplus to be 80€ and therefore the maximum of the sum to be 65€+80€ = 145€. In that case the formula would be:
=SUMPRODUCT(A9>{0;6500;22500};A9-{0;6500;22500};{1E-2;-5E-3;-5E-3})

[Tutorial] The SUMPRODUCT function, especially example X23

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach. And you must clarify whether 145€ is the maximum of the excess or the maximum of the sum.

If the numerical value is 9000 I want it to calculate the 1% for the 6.500 = 65 and the 0,5% for the rest 3.500 [=] 17,5 and sum it up = 82,5

9000€ minus 6500€ is 2500€. I ignored your paragraph since the computation there is bogus.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know