Help with MAX / MIN formula (I think)

  • Thread starter Thread starter Mark D
  • Start date Start date
M

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
 
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
 
You've actually got your data setup perfectly for use with the LOOKUP function.

=LOOKUP(A1,G5:G11,I5:I11)
 
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)))))
 
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
 
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.
 
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.
 
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
 
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
 
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
 
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.
 
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
 
Back
Top