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

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) * B1This formula would look up the value in A1 (5%) in the

lookup_tableand 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.

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

Ask Your Question

Weekly Poll