Need help with a formula for commission at different values

[SSM]

I want to do this:
I have the following arrangement to program:
Income Commission
Up to $1,000 20%
From 1,000 to 3000$ $200 + 30% of excess of Commission over $1,000
Above $3,000 $200 + $600 + 40% of excess of Commission
over $3,000

I want to put this into a table which I can change later on, e.g. I want to
change the $1,000 limit to, say, $2,000, or to change the 40% rate to 35%.
These changes should be reflected in the results...

Now, how do I do this?
(Hope I have been clear w/this description of my problem...)

Thanks in advance,

SSM

 

[Don Guillett]

Try here
http://tinyurl.com/oyha

You can use Ron's google add in to get what I just got.

http://www.rondebruin.nl/

 

[J.E. McGimpsey]

One way:

Set up your table in, say, J1:J4:

J K
1 Threshold Differential Commission Rate
2 0 20%
3 1000 10%
4 3000 10%

Note that the rate column is the *differential* rate for the bracket
in the threshold column, so 1000->3000 = 20%+10% = 30% and 3000+ is
20%+10%+10% = 40%. If your commission scale decreases, just use a
negative differential rate. In your example, if you changed the 40%
rate to 35%, K4 would become 5%.

Then, with your Income in A1, put this in B1:

=SUMPRODUCT(--(A1>J2:J4),(A1-J2:J4),K2:K4)

 

[SSM]

Thanks for the answer, Don, but I haven't been able to find a good enough
answer to my query in those pages u've mentioned. Thanks for your
reply...anyways.
I can of course do this using an "If Then" statement, but then I will have
to change the formula a lot to account for any changes in the values or
percentages. I want to form a table and program it so that I can change the
rates/% or values...I hope this explains what I have in mind...
TIA,
SSM

 

[L. Howard Kittle]

Hi SSM,

I used a vlookup against this sort of table.

A B C D E
1
2 0 20% =IF(K2<A3,K2*B2,"")
3 1001 30% 200 1000 =IF(K2<A4,C3+(K2-D3)*B3,"")
4 3001 40% 800 3000 =IF(K2>A4,C4+(K2-D4)*B4,"")

And any other cell: =VLOOKUP(K2,A2:E4,5,1)

Where K2 is the income cell and the commissions are returned in E2, E3, E4.
Change any of the table values but the lookup formula remains constant.

Hope the table format comes through intact.

HTH
Regards,
Howard

 

[_Bigred]

You would probably want to read about IF statements.

this type of formula would be the type you are looking for (but I am not
sure how to do the $ and % in the if statement).:


=IF(A1<1001,(A1)*0.2+(A1)) <----------- this would give you the
up to $1000 item below.
=IF(A1<3001,(A1)*0.3+(A1)+200) <------------- this would give you
the $200 + 30% of excess of $1000
=IF(A1>3000,(A1)*0.4+(A1)+200+600) <------------- this woudl give you the
$200 + $600 +40%

If you want to have this ALL run in (1) cell you would do something like:

=IF(A1<1001,(A1*0.2+(A1),IF(A1<3001,(A1)*0.3+(A1)+200,IF(A1>3000,(A1)*0.4+(A
1)+200+600))))

Basically you might have to go into the help file and read up on IF
Statements (with nested functions) excel allows you to nest up to 7 nested
functions in 1 cell. The formula I put above basically checks the first
criteria and if the "data cell" doesn't match it goes to the next criteria
and so on.

Let me know how it goes,
_Bigred

 

[BlackBlade]

suppose
A | B | C
8 income | commision
9 0 | 1000 | 20%
10 1001 | 3000 | 30%
11 3001 | | 40%

put in cell E11 the actual ammount ex. 3780
in cell f11 type the formula :
=IF(E11<=B9;E11*C9;IF(E11<=B10;(B9*C9)+((E11-B9)*C10);IF(E11>=A11;(B9*C9)+(B
10*C10)+(E11-B10)*C11;0)))

so whenever you want to change any parameter you can do it ....