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.

=5*int(sum(b2:b6)/5)
--
Wag more, bark less


"Joe Murphy" wrote:

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.

On May 9, 2:47*pm, Brad wrote:
=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.

=SUMPRODUCT(5*INT(C3:C6/5))
This should do it.
--
Wag more, bark less


"Joe Murphy" wrote:

On May 9, 2:47 pm, Brad wrote:
=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.

On May 9, 3:21*pm, Brad wrote:
=SUMPRODUCT(5*INT(C3:C6/5))
This should do it. *

It totally did. You're a star.

(and man, I have to go read about SUMPRODUCT)

Joe.

You're welcome

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

--
Wag more, bark less


"Joe Murphy" wrote:

On May 9, 3:21 pm, Brad wrote:
=SUMPRODUCT(5*INT(C3:C6/5))
This should do it.

It totally did. You're a star.

(and man, I have to go read about SUMPRODUCT)

Joe.

On May 9, 3:50*pm, Brad wrote:
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.