I need to create a formula for a tier pay system

[guy0nthec0uch]

Hey guys, I really try not to bother you all but this one is driving me nuts.

I work at a car dealership and I'm tasked with paying our BDC reps based on the units they sell.

So, I've factored a spreadsheet to calculate each of their sold but I need to pay them based on the following parameters.

15 SOLD = 250
20 SOLD = 350
25 SOLD = 500
30 SOLD = 750
35 SOLD = 1000

So, I need a formula that can take the cell used to calculate the sold and base the commission on the total.

I need this total to reflect under the DEALER SPIFF for each. Ideas?

 

[Dave Patton]

What version of Excel are you using? Please edit your profile.
What is the expected result for sales of say 10 and 22?

 

[awoohaw]

can you post some examples of the units sold and the expected results? It looks like in your example above the results would all be 0.00.

 

[Candyman8019]

You can try something like the following...assuming your Units sold is in D5:

=LOOKUP(D5,{15,20,25,30,35},{250,350,500,750,1000})

 

[guy0nthec0uch]

What version of Excel are you using? Please edit your profile.
What is the expected result for sales of say 10 and 22?

Just updated it. I'm using EXCEL 10 currently. I normally 365 at work.

They only receive a bonus if they eclipse specific numbers. So 10 garners no bonus, 22 would fall under the bonus for 20.

 

[guy0nthec0uch]

You can try something like the following...assuming your Units sold is in D5:

=LOOKUP(D5,{15,20,25,30,35},{250,350,500,750,1000})

That is correct. It calculates the sold amount. But I tried the formula and it gave me an ERROR message.

 

[Dave Patton]

22 would fall under the bonus for 20.

So what would the bonus be for 22? $xxxxx

 

[guy0nthec0uch]

22 would fall under the bonus for 20.

So what would the bonus be for 22? $xxxxx

that's $350 for anything between 20 and 24.

 

[Candyman8019]

What error message are you getting?

 

[Dave Patton]

You are not answering the question.
Either you need a tiered calculation of you need a lookup of the rate.
If you provide the result that you expect for a few examples such as 22 and 28, I will provide a suggestion.
Otherwise good luck.