You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mark D" wrote in message
...
Crikey that's incredible, thank you so much for this. Another technique to
use.

"T. Valko" wrote:

Try this....

Set up your table like this:

...........G.............H..........I
5....0...............10%.....formula
6...10000........15%.....formula
7...20000........20%.....formula
8...30000........30%.....formula
9...40000........40%.....formula
10.50000........45%.....formula

I5 formula: =H5
I6 formula: =H6-H5
Copy the formula in I6 down to I10

Your table will look like this:

...........G.............H..........I
5....0...............10%......10%
6...10000........15%......5%
7...20000........20%......5%
8...30000........30%......10%
9...40000........40%......10%
10.50000........45%......5%

Now, to get the total commission:

A1 = 100,000

=SUMPRODUCT(--(A1G5:G10),(A1-G5:G10),I5:I10)

The technique is explained he

http://mcgimpsey.com/excel/variablerate.html

--
Biff
Microsoft Excel MVP


"Mark D" wrote in message
...
Luke M if I could have your babies I would. You really helped me out
here.
As
I usually do I made it more complicated then it need be.

Thanks so much.

"Luke M" wrote:

Formula rewritten using all cell references:

=MIN(A1,H5)*I5+MIN(MAX(0,A1-H5),H6-H5)*I6+MIN(MAX(0,A1-H6),H7-H6)*I7+MIN(MAX(0,A1-H7),H8-H7)*I8+MIN(MAX(0,A1-H8),H9-H8)*I9+MAX(0,A1-H9)*I10
--
Best Regards,

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


"Luke M" wrote:

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