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.