Formula using Vlookup

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hi,

Excel 2000

Every month I need to pay commission based on points that a salesperson has
earnt, they are rewarded points based on sales e.g. $0.00 to $150.99 = zero,
$151.00 to $175.99 = 50, $176.00 to $200.99 = 100, $201.00 to $225.99 = 150
and thereafter they earn 50 points per every extra $25.99.

I first thought of using a lookup table but this could occupy hundreds or
rows as sales vary from $0.00 to $20,000. On the basis that there is a
trend to the base data, I think there's a simple formula but try as I may,
I'm lost.

Any help most welcome.

Rob
 
try' where b11 is the value
=LOOKUP(B11,{0,151,176,201},{0,50,100,150})+IF(B11>201,INT(((B11-201)/25.99)
)*50,0)
 
Hi Rob
one way (A1 stores the sales value)
=(A1-151>=0)*50+(A1-176>=0)*100+(A1-201>=0)*150+INT(MAX(A1-225.99,0)/25
..99)*25
 
Don,

Thanks for the reply which I tried, in the main this works but where I have
say a sale of $1000.00 the returned figure is 50 out. I think this is to do
with the INT element and the / 25.99.

I work on this and Frank's reply to hopefully resolve. Any pointers would
be welcome.

Regards, Rob
 
=(1000-201)/25.99 is only 30.7 so it hasn't reached the next threshold to
qualify for the 25.99 each.
 
Don,

I think I made an error, the scale of reward runs a little different to what
I stated; it goes like: $0.00 to $150.99 = zero, $151.00 to $175.99 = 50,
$176.00 to $200.99 = 100, $201.00 to $225.99 = 150, $226.00 to $250.99 =
200, $251.00 to $275.00 (note 275 and not 275.99) = 250 and thereafter for
every £25 of sales 50 points are earnt.

Therefore I calculate a $1000 sale is $1000-$275 = $725 / $25 = 29 * 50 =
1450 points + the 250 points earnt from the $275.00

If you can add any pointers, I'll be most grateful.

Regards, Rob
 
Don

Thanks for your help and guidance which has solved the query. The formula I
ended up with was:

=LOOKUP(F7,{0,151,176,201,226,251},{0,5,10,15,20,25})+IF(F7>275,((((F7-MOD(F
7,25))-275)/25)*5),0)

Thanks again. Rob
 
If that's the case then why not just something like this?
=IF(F15>150,INT(((F15-125)/25))*50,0)
 
If that's the case then why not something like this?
=IF(F15>150,INT(((F15-125)/25))*50,0)
 
Back
Top