commission spreadsheet, need help

[jumboh]

Please help this noob. If someone is kind enough or just want some practice, I will be grateful...

I have a commission project. sales reps will have about 300 accts per month listed in rows. each account will have a starting rate code (in column F), and reps will try to upgrade each account to another rate code (in column J). reps will also have a monthly save percentage (in cell Q1). depending on what rate code the acct starts and ends up with, along with what their monthly save percentage is, reps will get a certain commission (in column K). soo...

if acccount is in rate code 'F', and is changed to rate code 'J', and if save
percentage = 'Q1' then commission = 'K'

'F', 'J', 'K' will be in every row, the save percentage 'Q1' will be in only one
cell.

'F' could equal the following: 99.00, 114.99, 126.99, 129.99, 159.99
'J' could equal the following: 129.99, 159.99
'Q1' could equal the following: 90, 91, 92, 93, 94, 95.

this is the current commission grid:

if F=99.00 and J=129.99 and Q1=90, then K=2.00
if F=99.00 and J=129.99 and Q1=91, then K=2.50
if F=99.00 and J=129.99 and Q1=92, then K=3.00
if F=99.00 and J=129.99 and Q1=93, then K=3.50
if F=99.00 and J=129.99 and Q1=94, then K=4.00
if F=99.00 and J=129.99 and Q1=95, then K=4.50

if F=99.00 and J=159.99 and Q1=90, then K=6.75
if F=99.00 and J=159.99 and Q1=91, then K=7.25
if F=99.00 and J=159.99 and Q1=92, then K=7.75
if F=99.00 and J=159.99 and Q1=93, then K=8.25
if F=99.00 and J=159.99 and Q1=94, then K=8.75
if F=99.00 and J=159.99 and Q1=95, then K=9.25

if F=114.99 and J=129.99 and Q1=90, then K=1.50
if F=114.99 and J=129.99 and Q1=91, then K=2.00
if F=114.99 and J=129.99 and Q1=92, then K=2.50
if F=114.99 and J=129.99 and Q1=93, then K=3.00
if F=114.99 and J=129.99 and Q1=94, then K=3.50
if F=114.99 and J=129.99 and Q1=95, then K=4.00

if F=114.99 and J=159.99 and Q1=90, then K=5.00
if F=114.99 and J=159.99 and Q1=91, then K=5.50
if F=114.99 and J=159.99 and Q1=92, then K=6.00
if F=114.99 and J=159.99 and Q1=93, then K=6.50
if F=114.99 and J=159.99 and Q1=94, then K=7.00
if F=114.99 and J=159.99 and Q1=95, then K=7.50

if F=126.99 and J=129.99 and Q1=90, then K=1.00
if F=126.99 and J=129.99 and Q1=91, then K=1.50
if F=126.99 and J=129.99 and Q1=92, then K=2.00
if F=126.99 and J=129.99 and Q1=93, then K=2.50
if F=126.99 and J=129.99 and Q1=94, then K=3.00
if F=126.99 and J=129.99 and Q1=95, then K=3.50

if F=126.99 and J=159.99 and Q1=90, then K=3.50
if F=126.99 and J=159.99 and Q1=91, then K=4.00
if F=126.99 and J=159.99 and Q1=92, then K=4.50
if F=126.99 and J=159.99 and Q1=93, then K=5.00
if F=126.99 and J=159.99 and Q1=94, then K=5.50
if F=126.99 and J=159.99 and Q1=95, then K=6.00

if F=129.99 and J=159.99 and Q1=90, then K=2.50
if F=129.99 and J=159.99 and Q1=91, then K=3.00
if F=129.99 and J=159.99 and Q1=92, then K=3.50
if F=129.99 and J=159.99 and Q1=93, then K=4.00
if F=129.99 and J=159.99 and Q1=94, then K=4.50
if F=129.99 and J=159.99 and Q1=95, then K=5.00

if F=136.99 and J=159.99 and Q1=90, then K=2.50
if F=136.99 and J=159.99 and Q1=91, then K=3.00
if F=136.99 and J=159.99 and Q1=92, then K=3.50
if F=136.99 and J=159.99 and Q1=93, then K=4.00
if F=136.99 and J=159.99 and Q1=94, then K=4.50
if F=136.99 and J=159.99 and Q1=95, then K=5.00

if F=159.99 and J=159.99 and Q1=90, then K=3.00
if F=159.99 and J=159.99 and Q1=91, then K=3.50
if F=159.99 and J=159.99 and Q1=92, then K=4.00
if F=159.99 and J=159.99 and Q1=93, then K=4.50
if F=159.99 and J=159.99 and Q1=94, then K=5.00
if F=159.99 and J=159.99 and Q1=95, then K=5.50

reps will enter starting rate in column 'F' and new rate in column 'J' and save percentage in cell 'Q1'. I want the formula to automatically figure their commission in column 'K'. So i guess I need the formula for cells in column K.

Thanks a million in advance!

 

[iliace]

'F' could equal the following: 99.00, 114.99, 126.99, 129.99, 159.99

if F=136.99 and J=159.99 and Q1=90, then K=2.50
if F=136.99 and J=159.99 and Q1=91, then K=3.00
if F=136.99 and J=159.99 and Q1=92, then K=3.50
if F=136.99 and J=159.99 and Q1=93, then K=4.00
if F=136.99 and J=159.99 and Q1=94, then K=4.50
if F=136.99 and J=159.99 and Q1=95, then K=5.00

136.99 is not in your list of values.

I will make the assumption that each increase in Q over 90 results in an increase in K of 0.5, so if this will always be the case, I recommend you setup a lookup table for F and J values that lists the K values for the Q=90, then do this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
2	J / F	99.00	114.99	126.99	129.99	159.99		J	F	Q	K
3	129.99	2.00	1.50	1.00				129.99	114.99	92	2.50
4	159.99	6.75	5.00	3.50	2.50	3.00
Sheet3

 

[Smitty]

Welcome to the Board!

If you build a table just like the one you reference above you can add a helper column to concatenate the values and create unique items (e.g. =A1&B1&C1) and pull a vlookup off of it. Like so:

=VLOOKUP(G2&K2&R2,D2:E63,2,FALSE)

If you PM me your e-mail address I'll send you an example.

HTH,

 

[jumboh]

136.99 is not in your list of values.

I will make the assumption that each increase in Q over 90 results in an increase in K of 0.5, so if this will always be the case, I recommend you setup a lookup table for F and J values that lists the K values for the Q=90, then do this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
2	J / F	99.00	114.99	126.99	129.99	159.99		J	F	Q	K
3	129.99	2.00	1.50	1.00				129.99	114.99	92	2.50
4	159.99	6.75	5.00	3.50	2.50	3.00
Sheet3

sorry, yes...
'F' could equal the following: 99.00, 114.99, 126.99, 129.99, 136.99, 159.99.

The table below it has the correct info though, it is the same info as the 129.99 value.

this being said, everything you said still apply correct? I will give it a try and report back.

THANK YOU!

 

[jumboh]

Welcome to the Board!

If you build a table just like the one you reference above you can add a helper column to concatenate the values and create unique items (e.g. =A1&B1&C1) and pull a vlookup off of it. Like so:

=VLOOKUP(G2&K2&R2,D2:E63,2,FALSE)

If you PM me your e-mail address I'll send you an example.

HTH,

I pm'ed you my email, thanks for your help too!