Sales V commission

C

Carauto

I need to know how to calculate the following sales commissions as shown
below:

Sales Payment per order
1-10 £10
11-20 £15
21-30 £20
31-40 £25

How can I create a formula so that whenever I say, sales for Mr "A" are
36 then it shows the result in a cell.

Hope someone can help me and thanks.

Martin
 
S

Sandy Mann

Martin,

Assuming that the series carries on in the same arithmetic progression try:

=CEILING(A1,10)-(CEILING(A1,10)/10-1)*5*(A1>0)

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
R

Richard Buttrey

I need to know how to calculate the following sales commissions as shown
below:

Sales Payment per order
1-10 £10
11-20 £15
21-30 £20
31-40 £25

How can I create a formula so that whenever I say, sales for Mr "A" are
36 then it shows the result in a cell.

Hope someone can help me and thanks.

Martin
Click to expand...


This is exactly what Vlookup is for. Particularly if unlike in this
example the relationship between sales and payments are not directly
proportional.

Create a table in A1:B5

0 0
1 10
11 15
21 20
31 25


Then with 36 in say A8, enter in B8
=VLOOKUP(A8,A1:B5,2)


HTH
Richard Buttrey
__
 
Top