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

On Jul 27, 10:45*am, Mark D <Mark
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

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

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

--
Best Regards,

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


"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

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

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



"Pete_UK" wrote:

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

On Jul 27, 10:45 am, Mark D <Mark
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

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

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

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

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

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

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

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

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

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