Bonus calculations using Nested IF's

Hi,

I am working on a spreadsheet to calculate bonus for sales staff.
The scenario is as follows:

We have 30 Products. These products have 7 categories to sell into. Which are Recruitment, Property, Motors, Classified, LROP, NROP, Ents, Features.

People can sell into any product and any category.

When they do, we calculate the Yield of the space sold, against a target.

Each product and category has its own commission rate.

So if person X sells into Title A, and category recruitment, and they do not meet target they get zero commission, they beat target by 0-15% they get 1% of the revenue, if they beat it by 16-30% they get 2.5%, if they get 31-50% they get 5%, 51-75% they get 7.5%. and 75% plus they get 10% of revenue.

But these bandings can vary for each category and title.

I have got a formula for when the category and title does not vary, but not when it does, and I was hoping that someone can point me in the right direction.

The attached spreadsheet is what I am using. The sheet where I am performing the calculations is called Bonus Calculations, heading is Rate.

Any help is much appreciated.

I am using excel 2007, but even the 64 If’s now allowed by this will not cope with what is required.

Please can you help.

Thank you very much, it seems to be working great.

I need to read about this offset function as its not something I have used before.

Thank you once again!

That for the feedback.
//Ola

Google search for "Excel Offset Video".