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

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History