You're on the right track. Here is complete formula, with values included for
clarity:

=MIN(A1,10000)*10%+MIN(MAX(0,A1-10000),10000)*15%+MIN(MAX(0,A1-20000),10000)*20%+MIN(MAX(0,A1-30000),10000)*30%+MIN(MAX(0,A1-40000),10000)*40%+MAX(0,A1-50000)*45%

You'll notice that there are 3 basic patterns. The top and bottom use
Min/Max respectively. The middle ranges use a pattern on
MIN(MAX(0,A1-Threshold),Size_of_Threshold)*Percentage

You can either leave the formula as is, or replace it with cell references.
It really depends on if you need to be able to change your limits/variables.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Mark D" wrote:

Hi everyone

Thanks for the replies but maybe I am confusing myself and maybe in turn you
guys. Apologies if I have or if it's something I'm just not understanding

Basically I am trying to calculate employees commission payouts based on
profit earned

Let me put it this way if it helps. I have amended the table

Cell A1 = ‚¬100,000 PROFIT

PAYOUT PARAMETERS

PROFIT FROM PROFIT TO % PAYOUT
(G5) ‚¬0 (H5) ‚¬10,000 (I5)10%
(G6) ‚¬10,001 (H6) ‚¬20,000 (I6)15%
(G7) ‚¬20,001 (H7) ‚¬30,000 (I7)20%
(G8) ‚¬30,001 (H8) ‚¬40,000 (I8)30%
(G9) ‚¬40,001 (H9) ‚¬50,000 (I9)40%
(G10)‚¬50,001 (I10)45%

The amounts are capped as you hit each milestone amount

I have to show the calculations seperately as per the 6 criteria levels above)

so the amounts will come to (in seperate cells B
(B31) PAYOUT 1 (between ‚¬0 - ‚¬10,000 of the ‚¬100,000 @ 10%) = ‚¬1000
(B32) PAYOUT 2 (between ‚¬10,001 - ‚¬20,000 of the ‚¬100,000 @ 15%) = ‚¬1500
(B33) PAYOUT 3 (between ‚¬20,001 - ‚¬30,000 of the ‚¬100,000 @ 20%) = ‚¬2000
(B34) PAYOUT 4 (between ‚¬30,001 - ‚¬40,000 of the ‚¬100,000 @ 30%) = ‚¬3000
(B35) PAYOUT 5 (between ‚¬40,001 - ‚¬50,000 of the ‚¬100,000 @ 40%) = ‚¬4000
(B36) PAYOUT 6 (anything above ‚¬50,001 of the ‚¬100,000 @ 45%) = ‚¬22,500


TOTAL PAYOUT = ‚¬34,000

I need it formula based so can change the ‚¬ payout parameters and it change
the amounts accodingly. I can run the calculation entering manual entries
such as =MAX(MIN(10000,$A$1-20000)*I6,0)

I tried the lookup formula and I just get the total amount.

Again many thanks for looking.


"Eduardo" wrote:

Hi ,
I didn't complety understand what you want to achieve, I think you want to
multiply the amount in cell A1 by the % in column I and have the value
populated in B1.

=IF(A1<=10000,A1*I5,IF(AND(A110000,A1<=20000),A1* I6,IF(AND(A120000,A1<=30000),A1*I7,IF(AND(A13000 0,A1<=40000),A1*I8,IF(AND(A140000,A1<=50000),A1*I 9,I10)))))

"Mark D" wrote:

Morning all

I am sorry if this request seems quite basic but I am absolutely stuck on
how to enter this forumla and am really hoping someone can help me. I think I
need to use a MAX / MIN formula

If I have a total figure in A1 which say has ‚¬100,000 in it.

Then i have some TO & FROM amounts in other columns starting at G5 and
working down

TO FROM % AMOUNT APPLIED
G5 - G11 H5 - H11 I5 - I11
‚¬0 ‚¬10,000 10%
‚¬10,001 ‚¬20,000 15%
‚¬20,001 ‚¬30,000 20%
‚¬30,001 ‚¬40,000 30%
‚¬40,001 ‚¬50,000 40%
‚¬60,000 45%

Then from B31 down to B37 based on the figure in A1 show the value in B31 -
B37 that applies based on the criteria in columns G, H & I

G11 actually has no end value so anything higher than ‚¬60,000 will always be
at 45%

Again thanks for anyone that can help me with this. I have tried everything
and am going out of my mind

Kind Regards

Mark