Formula to Calculate Scale of Commissions

So our owner made up a new way to do commissions, and it's taking me four days to manually calculate them and to be honest, I'm so afraid of human error, it's going to catch up at some point, so I'm hoping there is some sort of formula that can help!

Our sales team gets a percentage based off the profit margin range, and I need a formula that can meet 8 conditions - which is where the "IF" conditions fail me.

Basically all my "IF's" are:
0-4 = 0%
5-10 = 5%
11-15 = 7%
16-19 = 9%
20-23 = 10%
24-27 = 11%
28-33 = 13%
34-37 = 14%
38-100 = 21%

And that percentage times another field yields their commission.

Help please??

A lookup table is the best option.

Look at VLOOKUP function

Table with 0,5,11,16 etc in column A with % in B (0,5,7 ...)

+VLOOKUP(D1,$A$1:$B$9,2,1) D1 profit margin

bebo021999, I was feeling AWESOME! that formula was good -- THEN I realized it didn't take into account rounding. For example, the report gave me 15.7333333 and when I "decrease decimal" so that a whole number shows, it calculated 7% for 15 instead of 9% for rounding up to 16.

Any ideas?

I was feeling AWESOME! that formula was good -- THEN I realized it didn't take into account rounding. For example, the report gave me 15.7333333 converted into Excel, and when I use the "decrease decimal" icon so that a whole number (rounded up) shows, it calculated 7% for 15 instead of 9% for rounding up to 16.

Any ideas?

PS: Thanks for the quick and super simple formula! If I can get the rounding corrected, I'll be Golden!!!

Eeep! I figured it out.

To explain, the database generates a report that I past into excel. So my numbers look more like this:

15.73333
19.81552
20.77781
30.67675
37.79472

I use the "decrease decimal" icon under the "Number" tab in the ribbon until I get a clean, rounded value, ie:

round to 16
round to 20
round to 21
round to 31
round to 38

The lookup formula I'm using =LOOKUP(I9,{0,5,11,16,20,24,28,34,38},{0,5,7,9,10,11,13,14,21})/100 was calculating the percentages for:

15 (instead of 16)
19 (instead of 20)
20 (instead of 21)
30 (instead of 31)
37 (instead of 38)

So I changed the formula to:
=LOOKUP(I9,{0,4.49,10.49,15.49,19.49,23.49,27.49,33.49,37.49},{0,5,7,9,10,11,13,14,21})/100

It's working perfectly!!!

Thanks to ALL of you for being so quick and helpful!