Excel 2007 : Commission Rate from Multiple Criteria

**angle** · 08-19-2009, 08:33 AM

I am at a loss! I'm trying to determine a commission rate based off of four criteria:

1) Renewal Written Premium
2) New Life Commission
3) Retained Policy Count
4) Persistency Rate

Each of those categories has a range for the possible data that could be entered from (i.e., Persistency Rate could be a SINGLE number between 0 - 69.99, or 70 - 74.99) and by falling into one of those ranges, a different commission rate would be possible. I've included a picture of what the table / range(s) looks like:

\1

The commission rate is based off of the LOWEST factor from the four categories.

So, let me show you a possible example:

\1

In this instance, since the lowest factor is 16 (in the Retained Life Policy Count column), then the commission rate would be 1.5%.

Now, what I'm trying to do is create a formula that will check those ranges and produce the commission rate. I created those pictures merely for examples, not actually what I'll be using (however I'm sure I could find a way to adapt a formula to work if one is created from those pictures).

I've examined the idea of nested IF statements, but A) I don't think I can nest enough of them for it to work, and B) it would be ridiculously dirty looking and cumbersome. I'm not super familiar with VLOOKUP, but my guess is that's what I have to use to determine the lowest factor in the various ranges.

Any help you guys / gals can provide would be greatly appreciated! Even just a point(er) in the right direction will give me a leg to stand on. Thanks in advance for any help you can provide!

~Michael

**NBVC** · 08-19-2009, 08:38 AM

Is it possible to attach the actual spreadsheet with this data here?

Click the paperclip icon in the Reply box to attach a sheet.

Also:

You have duplicate ranges with different bonuses.. how do you know which to use if Min falls in that range... e.g. in your example, how do you choose 1.5 or 1 which both fall within the 15 to 19 range for that category?

**angle** · 08-19-2009, 11:28 AM

Thanks for the fast reply!

I've attached the spreadsheet that I'm working with, and highlighted the columns that would be used.

To answer your question, Even though there are multiple ranges, ALL ranges have to be factored and then the the lowest range is used. In the example I provided, all other ranges were above the 1.5 % EXCEPT for the Life Policy Count. Hence, why the 1.5% rate.

**NBVC** · 08-19-2009, 11:33 AM

This doesn't look like previous example

Can you elaborate by giving some expected results and based on what factors?

**angle** · 08-19-2009, 12:40 PM

Sorry about the confusion! I tried to provide a more simplified version of what I was working on.

The spreadsheet that I uploaded as five columns highlighted. The first four of the highlighted columns ("12/31/2008 New Life Commission", "12/31/2008 Inforce and Retained Policy Count", "12/31/2008 Persistency %", and "12/31/2008 General Lines Renewal Premium") are the ones that would have data entered into them. The fifth highlighted column (2009 General Lines Bonus Comission Rate %") is where the expected result would appear.

What I would like is to plug in the numbers for the first four, and have that generate the number for the fifth. The number that is generated here is based off of a series of ranges of for each of the other four columns.

The formula I've been trying to create needs to examine the numbers entered in the first four highlighted columns, and determine what number to place in the fifth based off of the the criteria that is met.