Help with MAX / MIN formula (I think)

[Mark D]

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

 

[Pete_UK]

I'm a bit confused as to what you want in B31:B37, but try this:

=VLOOKUP(A$1,G$5:I$11,3)

It will return the appropriate percentage from column I dependent on
your total in A1.

I assume your currency amounts are proper numbers formatted as
currency, and not text values.

Hope this helps.

Pete

 

[Luke M]

You've actually got your data setup perfectly for use with the LOOKUP function.

=LOOKUP(A1,G5:G11,I5:I11)

 

[Eduardo]

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(A1>10000,A1<=20000),A1*I6,IF(AND(A1>20000,A1<=30000),A1*I7,IF(AND(A1>30000,A1<=40000),A1*I8,IF(AND(A1>40000,A1<=50000),A1*I9,I10)))))

 

[Mark D]

Hi Pete, thanks for the reply, I was hoping my post would make sense but I'll
try and be a bit more specific

Basically the sum of A1 is €100,000 say total Profit for example.

The min - max amounts in the table are saying that for the first €0 -
€10,000 of the profit pay out at 10%, the next €10,001 - €20,000 pay 15% etc
ect.

The last table I want to show that for each banding what would be the %
payout (i.e €0 - €10,000 payout would be €1000, the next €10,001 - €20,000 @
15% = €1500 etc). I need to be able to show a table in B31 to B37 to show
these amounts. Once you hit the first €10,000 the payout of €1000 is capped.

The last calculation shownig that whatever the profit is over €60,000 it
just * by 40%

I have the following calculations but they are manual and changing them to
the to and from amounts doesnt seem to work

Typical example
=MAX(MIN(10000,$A$1-20000)*I12,0), I12 being the 15% payout

I hope this makes more sense with what I am trying to do

Thanks again for the reply

 

[Mark D]

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.

 

[Luke M]

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.

 

[Bernard Liengme]

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

 

[Luke M]

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

 

[Mark D]

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

 

[Mark D]

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.

 

[T. Valko]

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(--(A1>G5:G10),(A1-G5:G10),I5:I10)

The technique is explained here:

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

 

[Mark D]

Crikey that's incredible, thank you so much for this. Another technique to
use.

 

[T. Valko]

You're welcome. Thanks for the feedback!