commission calulation in excel

May 11, 2012 at 06:14:55
Specs: Windows 7
I am looking for a way to calculate commissions. We currently calculate profit margins and pay commission based on rate listed below. The problem I am having is writing a formula in excel that pays on margin “after” commission. Any ideas?

Margin 5.00% 6.25% 7.50% 8.75% 10.00% 11.25% 12.50% 13.75% 15.00% 15.62% 16.25% 16.39% 17.50% 18.00% 18.50% 19.25% 20.00%
Commission 1.00% 1.25% 1.50% 1.75% 2.00% 2.25% 2.50% 2.75% 3.00% 3.25% 3.50% 3.75% 4.00% 4.25% 4.50% 4.75% 5.00%
Frank



See More: commission calulation in excel

Report •


#1
May 11, 2012 at 07:54:08
Try using VLOOKUP. Read up on that function in the Excel Help files or via Google.

Set up a table with your margin values and corresponding commissions.
Then let VLOOKUP look up the commission for a given margin.

Something like:

A1 contains a profit margin value, e.g. 5%
B1 contains a total sales value, e.g $10,000
C1 contains something like:

=VLOOKUP(A1, lookup_table, 2,0) * B1

This formula would look up the value in A1 (5%) in the lookup_table and pull the corresponding commission value and then multiply the $10,000 by that commission.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 13, 2012 at 05:57:52
Thank you, i have been trying but i am not that good in excel. how much would you changre me to send me the formulas? wethfam at bellsouth dot net
Frank

Report •

Related Solutions


Ask Question