 View Question
Q: Excel - Calculating totals using a lookup table ( Answered ,   4 Comments ) Question
 Subject: Excel - Calculating totals using a lookup table Category: Computers > Software Asked by: jb23-ga List Price: \$20.00 Posted: 13 Jan 2005 07:56 PST Expires: 12 Feb 2005 07:56 PST Question ID: 456608
 ```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.``` Subject: Re: Excel - Calculating totals using a lookup table Answered By: joey-ga on 15 Jan 2005 00:37 PST Rated: ```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```
 jb23-ga rated this answer: `Thanks! This is what I was looking for.` Subject: Re: Excel - Calculating totals using a lookup table From: jack_of_few_trades-ga on 13 Jan 2005 12:05 PST
 ```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.```
 Subject: Re: Excel - Calculating totals using a lookup table From: jb23-ga on 13 Jan 2005 13:23 PST
 `This works. Thanks!`
 Subject: Re: Excel - Calculating totals using a lookup table From: shockandawe-ga on 13 Jan 2005 17:14 PST
 ```Better (prettier & more flexible) to use the vlookup function http://www.dicks-blog.com/archives/2004/10/06/vlookup-with-a-variable-column/```
 Subject: Re: Excel - Calculating totals using a lookup table From: jack_of_few_trades-ga on 14 Jan 2005 06:17 PST
 ```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.``` 