How do I determine

[Hastey]

I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to £1,000 worth of products, I will pay them 3% of the
value.

Between £1,000 & £2,000, I will pay them 5% of the value

Over £2,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

(e-mail address removed)

 

[Bill Ridgeway]

I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to £1,000 worth of products, I will pay them 3% of the
value.

Between £1,000 & £2,000, I will pay them 5% of the value

Over £2,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

(e-mail address removed)

You need to be aware of a perennial trap when calculating days difference.
The answer lies in if you are counting gross or net days.



In a simple example, if a person starts work on 1 January and ends work on 2
January, 2 days pay would be due. Calculating the number of days pay by
taking the last day less the first day would result in an underpayment by
one day. It is, therefore, important when calculating gross days to add one
day (i.e. =date2-date1+1)



Bill Ridgeway

Computer Solutions

 

[Bill Ridgeway]

Oops! apologies

Regards.

Bill Ridgeway
Computer Solutions

 

[Pete_UK]

Try this:

=IF(A2<=1000,3%,IF(A2<=2000,5%,10%))*A2

Hope this helps.

Pete

 

[Mais qui est Paul]

Bonsour® Hastey avec ferveur ;o))) vous nous disiez :

I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:
If they sell up to £1,000 worth of products, I will pay them 3% of the
value.
Between £1,000 & £2,000, I will pay them 5% of the value
Over £2,000, I will pay them 10% of the value
The amount paid to them in commission will be show in column (A3).

=A2*CHOOSE(MATCH(A2,{0;1000;2000},1),3%,5%,10%)

HTH

 

[Pete_UK]

Bill,

I think your reply relates to another post.

Pete

 

[Bob Phillips]

=MIN(A3,1000)*3%+(MIN(MAX(A3-1000,0),1000)*5%)+(MAX(A3-2000,0)*7%)

--
__________________________________
HTH

Bob

I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to £1,000 worth of products, I will pay them 3% of the
value.

Between £1,000 & £2,000, I will pay them 5% of the value

Over £2,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

(e-mail address removed)

 

[Harald Staff]

Yet another approach, just for the fun of it:

=A2*(0.03+(A2>1000)*0.02+(A2>2000)*0.05)

Best wishes Harald

"Hastey" <[email protected]> skrev i melding
I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to £1,000 worth of products, I will pay them 3% of the
value.

Between £1,000 & £2,000, I will pay them 5% of the value

Over £2,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

(e-mail address removed)

 

[Mais qui est Paul]

Bonsour® Hastey

Yet another approach, just for flexibility :
define :
a range named : Amounts
0, 1000, 2000, 5000
and a range named : Bonus
2%, 3%, 5%, 10%

as many Amounts as Bonus

=A2*INDEX(Bonus,MATCH(A2,Amounts,1),1)

HTH

 

[Pete_UK]

The OP emailed me directly with further details - he gives 5% of the
sales value if sales are between 1000 and 2000, and 10% of the sales
value if over 2000, not on the sliding scale you imply.

Actually his sales values which trigger the different percentages are
a lot less than those quoted in the example, but I've just used them
here for continuity.

Pete

 

[Bob Phillips]

I'm glad about that, everyone else was going another route and I thought I
was off-key.