Picking a commission rate based on close rate help

[j_sun]

Hi,

I'm sure this has been asked and is in the forum but I must not be searching on the correct terms.


Basically I need to calculate commission paid. It is paid in a tiered structure. So if their conversion rate was 0-20% they get 2% if it 20.01-30% they get 3% and 30.01%+ 4%.

Because the boss gave us a template we must follow, I have to put each rate in it's own cell (as opposed to putting the rates directly into the formula)

So I have the rates in C7,C8 and C9 respectively.


The formula needs to look at the cell which has conversion rate in it and determine what rate to use to calculate commission. I thought maybe an IF function would work but it only evaluates one test.

Thoughts?


Thanks,

Jason

 

[Luke M]

You'll want to use a lookup table. Left hand column contains min threshold for each level. So, for your setup

[pre]

Level   Rate
0       2%
20.01   3%
30.01   4%

[/pre]
Let's assume this table is in AA2:AB5. Then, your formula to find commision rate is:

=LOOKUP(ConversionRate,$AA$2:$AB$5)

 

[j_sun]

As always, thanks Luke!

 

[j_sun]

But wait...

The commission rate isn't on a 1:1 relationship. It is a range:


More like this:


0-20% = 2%

21-30% = 3%

31%+ = 4%


That is where the complexity comes in.

 

[bobhc]

Good day j_sunh


Luke has come up with a neater method, I was think along this method

[pre]

A               B
Conversion    Rate Commission
20%	      2% commission
10%	      2% commission
15%	      2% commission
25%	      3% commission
30%	      3% commission
35%	      4% commission

[/pre]

This is in B2 and copied down


=IF(A2<=20%,2,IF(A2>20%,3,IF(A2>30%,4)))&"%commission"

 

[Faseeh]

Hi j_sun


Go with Luke's formula and your table as below:

[pre]

0	2%
21	3%
31	4%

[/pre]

Regards,

 

[j_sun]

I just read up on the LOOKUP formula and get how it works. Luke you were right in the beginning. I should have known not to doubt you.


Thanks all!!

 

[SirJB7]

@j_sun

Hi!

If I were you, I'd always doubt... he usually uses blind shots.

Regards!

 

[Luke M]

@j_sun

Glad you got it figured out. Sorry I missed the boundary conditions in my setup and caused confusion.


@SirJB7

Still a doubter huh? You need to let go of your feelings...use the Force.