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