Good afternoon Bernard,

yes I tried that and I got it to work. But It's not what I need it to do in
total

For a ‚¬100,000 payout it needs to pay out based on each of the criteria from
my table.

For example the first ‚¬10k profit is paid @ 10% and capped so that max
payout on threshold 1 is ‚¬1000

The next ‚¬10k paid @ 15% so max payout on threshold 2 is ‚¬1500 right the way
till the end cap where anything over ‚¬50,000 is paid at 45%

So based on my table below a profit of ‚¬100,000 based on the table criteria
would = a total payout of ‚¬34,000 using all 6 criteria.

I am nearly there with the reply from Luke M but am still having trouble
changing the ranges accordingly.

I appreciate everyones help and hope I am not causing too much trouble, Just
this exercise has been killing me

Best Regards

Mark

"Bernard Liengme" wrote:

Mark,
PeteUK has answered your question.
Try this experiment in a new workbook
1) Starting in A1 enter these values in column A (I will return to column B
shortly)
?100,000 45%
?20,000 15%
?30,100 30%
Starting in G4 enter this table
PROFIT FROM PROFIT TO % PAYOUT PROFIT TO?0
?10,000 10%
?10,001 ?20,000 15%
?20,001 ?30,000 20%
?30,001 ?40,000 30%
?40,001 ?50,000 40%
?50,001 45%

In B1 enter the formula =VLOOKUP(A1,$G$6:$I$11,3)
Copy this down the column

To get the actual payouts (as below)
?100,000 ?45,000
?20,000 ?3,000
?30,100 ?9,030
Use the formula =A1*VLOOKUP(A1,$G$6:$I$11,3)
Are these the results you expected?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mark D" wrote in message
...
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