

Subject:
Excel  Calculating totals using a lookup table
Category: Computers > Software Asked by: jb23ga 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 $1400015000; 18%. $1200014000; 15%. $1000012000; 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.  
 


Subject:
Re: Excel  Calculating totals using a lookup table
Answered By: joeyga 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*(A3A2)+C2 You should enter that in C3 and then highlight from C3 to C7 and then press CTRLD (or go Edit: Fill: Down) to copy the formula down into all the columnC cells. Call your lookup table spreadsheet "rates" (you can do this by doubleclicking 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)*(A2VLOOKUP(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*(1289012000)+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 
jb23ga
rated this answer:
Thanks! This is what I was looking for. 

Subject:
Re: Excel  Calculating totals using a lookup table
From: jack_of_few_tradesga 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,(A18500)*0.05,IF(A1<=12000,1500*0.05+(A110000)*0.1,IF(A1<=14000,1500*0.05+2000*0.1+(A112000)*0.15,IF(A1<=15000,1500*0.05+2000*0.1+2000*0.15+(A114000)*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: jb23ga on 13 Jan 2005 13:23 PST 
This works. Thanks! 
Subject:
Re: Excel  Calculating totals using a lookup table
From: shockandawega on 13 Jan 2005 17:14 PST 
Better (prettier & more flexible) to use the vlookup function http://www.dicksblog.com/archives/2004/10/06/vlookupwithavariablecolumn/ 
Subject:
Re: Excel  Calculating totals using a lookup table
From: jack_of_few_tradesga 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. 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 