Sales V commission

[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

[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


"Carauto" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Carauto
> ------------------------------------------------------------------------
> Carauto's Profile:
> http://www.excelforum.com/member.php...o&userid=29500
> View this thread: http://www.excelforum.com/showthread...hreadid=492032
>

[Richard Buttrey]

On Thu, 8 Dec 2005 16:58:10 -0600, Carauto
<[email protected]> wrote:

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


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
__