Calculating bonuses - but not a VLOOKUP or CEILING?


J

Joe Murphy

I have a big PivotTable of sales of specific products against
salespeople.

Product Salesperson1 Salesperson2
product1 1 5
product2 0 11
product3 6 2
product4 3 6

Salespeople are paid a bonus if sales are in multiples of 5 - they get
no bonus for selling 0-4 units. A 5 euro bonus for selling 5, 6, 7, 8
or 9 units. Then 10 euro for shifting 10, 11, 12, 13, or 14. Then a 15
euro bonus, etc. So in the above examples, Salesperson1 would get 5
euro total. Salesperson2 would get 20.

I can copy out the pivot results to another sheet and insert a column
for each salesperson that runs a CEILING formula against each adjacent
cell. Then sum the ceiling column for each salesperson, getting their
total bonus. This works fine. I also ran a VLOOKUP, which worked a-ok
too. But there are a lot of columns to insert.

What single cell formula would I enter underneath each salesperson's
column that could calculate their total bonus?

Joe.
 
Ad

Advertisements

J

Joe Murphy

=5*int(sum(b2:b6)/5)

That's not it, alas. I could have been clearer in my example.

It's not the multiples of 5 that create the bonus, it's multiples of 5
_of each product_ that get the bonus.

So for Salesperson1, he doesn't get a 10 euro bonus for shifting 10
units total. He gets a 5 euro bonus for shifting 6 units of one
product, and the rest are ignored.

Salesperson2 gets a 5+10+0+5=20 euro.

Joe.
 
B

Brad

You're welcome

Would you please click the button that says that the response answered the
question. I would appreciate it.
 
Ad

Advertisements

J

Joe Murphy

You're welcome

Would you please click the button that says that the response answered the
question.  I would appreciate it.

I'd love to, but I'm just browsing Usenet through Google. Are you
viewing the groups through some portal? Let me know if that's the case
and I'lll click anything you like.

Joe.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top