I am trying to create a spreadsheet for commission structure which
adds amounts based on different percentages found in a lookup table. 
Find the range for the sales number given and subtract the number
found in the lookup from sales number, then add all lower number
calculations in the range together to get the commission. Example:
$12,890 in sales.  The table has 4 levels of percentages in the
lookup. From $14000-15000; 18%. $12000-14000; 15%. $10000-12000; 10%. 
$8500-$10000; 5%.  In this case the lookup would find $12000 in the
table.  Commision would be $890*15% + $2000*10% + $1500*5% or $408.50.

---

Request for Question Clarification by maniac-ga on 13 Jan 2005 18:37 PST

Hello Jb23,

If you are satisfied with the comment made by Jack_Of_Few_Trades, I
suggest you close the question. If you want a more complete answer,
please indicate in a question clarification.

For example, I could provide an example of using the table look up
(instead of hard coding the values in the formula) from the
spreadsheet.

  --Maniac

---

Clarification of Question by jb23-ga on 14 Jan 2005 05:19 PST

I started out with a lookup table.  If you have a solution using a
table, please provide that as an answer.  It would be easier to
modify.

The best way to do this is to first add an extra column to the lookup
table to sum up the total comission for all of the rate brackets below
each level

Here is how the lookup table should be set up:

   A                 B       C
1  Threshold Floor   Rate    Base Comission
2  0                 0%      0
3  8500              5%      0
4  10000             10%     75
5  12000             15%     275
6  14000             18%     575
7  15000             0%      755

The "threshold floors" and "rates" would be manually placed in the
lookup table by you.  The "base commission" cells, however, would be
filled by formula.

The formula in C3 would be:
     =B2*(A3-A2)+C2

You should enter that in C3 and then highlight from C3 to C7 and then
press CTRL-D (or go Edit: Fill: Down) to copy the formula down into
all the column-C cells.

Call your lookup table spreadsheet "rates" (you can do this by
double-clicking that spreadsheet's tab at the bottom of the screen and
renaming it)

Then, on your main spreadsheet, let's assume it looks something like this:

   A         B
1  Sales     Commission
2  12890.00  408.50
3  16000.00  755.00

etc.

The values in A would be placed by you.  B would be filled by lookup
formula.  In B2, place the following formula:
  =VLOOKUP(A2,rates!A:C,2)*(A2-VLOOKUP(A2,rates!A:C,1))+VLOOKUP(A2,rates!A:C,3)

This is what it does:
    1. Using the sales number in A2, it . . .
    2. Finds the correct percentage to use, then
    3. Multiplies it by the threshold bottom subtracted from the sales, then
    4. Adds in the total commission for the thresholds below.

With this example, here is what the formula pieces provide for B2:

  =.15*(12890-12000)+275
  =408.50

Please let me know if you have any questions.

--Joey

Strategy:
     Personal knowledge of Excel

For more information on VLOOKUP, search Google for:
     vlookup excel

Thanks! This is what I was looking for.

Here's a way to do it:

Put this formula in any cell other than A1:
=IF(A1>8500,IF(A1<=10000,(A1-8500)*0.05,IF(A1<=12000,1500*0.05+(A1-10000)*0.1,IF(A1<=14000,1500*0.05+2000*0.1+(A1-12000)*0.15,IF(A1<=15000,1500*0.05+2000*0.1+2000*0.15+(A1-14000)*0.18)))))

Then input the sales into A1.

**this will not work for values over $15,000 as that's not specified
in the questions, but you can add another IF statement for that if
you'd like.

This works.  Thanks!

Better (prettier & more flexible) to use the vlookup function

http://www.dicks-blog.com/archives/2004/10/06/vlookup-with-a-variable-column/

Don't stray from the ways of hard coding!  You know you love it!!!

I mean...  flexible tables is a far better way to go unless the
numbers are forever set in stone.