Calculating bonuses - but not a VLOOKUP or CEILING?

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.

Brad
Fri May 09 08:47:01 CDT 2008

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

Joe
Fri May 09 09:04:54 CDT 2008

On May 9, 2:47=A0pm, Brad <B...@discussions.microsoft.com> wrote:
> =3D5*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=3D20 euro.

Joe.

Brad
Fri May 09 09:21:02 CDT 2008

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


"Joe Murphy" wrote:

> On May 9, 2:47 pm, Brad <B...@discussions.microsoft.com> 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.
>

Joe
Fri May 09 09:30:42 CDT 2008

On May 9, 3:21=A0pm, Brad <B...@discussions.microsoft.com> wrote:
> =3DSUMPRODUCT(5*INT(C3:C6/5))
> This should do it. =A0

It totally did. You're a star.

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

Joe.

Brad
Fri May 09 09:50:01 CDT 2008

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 <B...@discussions.microsoft.com> 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.
>

Joe
Fri May 09 09:59:47 CDT 2008

On May 9, 3:50=A0pm, Brad <B...@discussions.microsoft.com> wrote:
> You're welcome
>
> Would you please click the button that says that the response answered the=

> question. =A0I 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.