Different bonus rates, different values to multiply

**PIR** · 03-18-2009, 09:12 PM

http://i42.tinypic.com/2606e7p.jpg

Have included worksheet too.

So i got further in my assignment, when i had to add data and extend the calculations for the new data. However, I now have a problem from one of the first formulas i had to do:

Staff Bonus based on Profit/Sales Staff over 3 year period - rates given in the Bonus Rates table

At first i did it manually, as you can see from the pic, there was only data for Liverpool. E.g. In cell "M8," I saw that "L8" was more than 350 and so the formula was

Please Login or Register  to view this content.

But now i have alot more data, and its harder to do each lookup individually.

My question:

How do i make it so that each cell under column "M", will automatically lookup the bonus rates given, given that whatever the value in column "L" will be inbetween the ranges of 0-200, 200-350, 350-450, and thus multiplied by the percentage next to the range it falls under?