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.
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.