Lookup or If Statement?

[lcfierro]

Hello all!

I'd like to ask for some quick answers to the question below So, any assistance you can provide would be WONDERFUL!

The spreadsheet I'm working with contains commission line items for a salesperson over one quarter. I'm trying to determine a commission percentage based on the type of service the salesperson is selling. Then, based on the entire quarter sales, once they reach a threshold, the commission based on the type of service should increase.

Here, is the matrix for the commission. As you can see, services will have a code letter to reference on the matrix.

<a href="http://s62.photobucket.com/albums/h100/lcfierro/?action=view&current=Picture2-1.png" target="_blank"><img src="http://i62.photobucket.com/albums/h100/lcfierro/Picture2-1.png" border="0" alt="Photobucket"></a>

Here, I'll need to populate a formula in the column 'I', that relates to the matrix above. (Disregard the Commission Amount, this column has copied information in it.)

ideas?

 

[mosiki]

CELL I10 FORMULA:


=IF(G10>1200000,VLOOKUP(B10,I2:L6,4,FALSE),IF(G10<=600000,VLOOKUP(B10,I2:L6,2,FALSE),VLOOKUP(B10,I2:L6,3,FALSE)))

copy down

Mosiki

 

[mosiki]

Sorry, forgot to set range to absolute, below is correct I think.


=IF(G10>1200000,VLOOKUP(B10,$I$2:$L$6,4,FALSE),IF(G10<=600000,VLOOKUP(B10,$I$2:$L$6,2,FALSE),VLOOKUP(B10,$I$2:$L$6,3,FALSE)))


Mosiki

 

[lcfierro]

CELL I10 FORMULA:


=IF(G10>1200000,VLOOKUP(B10,I2:L6,4,FALSE),IF(G10<=600000,VLOOKUP(B10,I2:L6,2,FALSE),VLOOKUP(B10,I2:L6,3,FALSE)))

copy down

Mosiki

that would work out wonderfully, if the threshold, target, excellence were based on the single line item. BUT, the commission % will increase only after the sum of all the commissions for the quarter meet the sales goals.

 

[mosiki]

To clarify, the commission % is based on the total of column G ??

 

[lcfierro]

this may help:

So, as you can see, the commission % in column J is working, thanks so much!

What I've done next, is sort the items by date, and added a running total column (I).

I can do the next step manually, but of course would rather it be automatic. Now that the items are sorted in date order with the running total.... let me give more thought to it... LOL

 

[lcfierro]

To clarify, the commission % is based on the total of column G ??

Column H, the booked value.

 

[mosiki]

Just a tip.

I would have my commision thresholds and targets listed separately.

Eg. A1 = Commission Level 1 B1=600,000
A2 = Commission Level 2 B2 = 1,200,000

Instead of using the numbers in the formulas i give you before you can link them to the cells B1 and B2.

This way if the threshold changes you can just change cells B1 & B2 and all the formulas will update automatically.

Mosiki

 

[mosiki]

Duped

 

[mosiki]

Duped.