Sliding Commission Formula

I still have no clue as to how to create a formula from this problem that I have.
I am looking to create variable commission rates for differing price points. Can I get some Help?

If sales amount is equal to or less than $449 then commission is 50% of sales
If sales are greater than $449 but equal to or less than $599 then commission is 60% of sales.
If sales are greater than $599 but equal to or less than $899 then commission is 70% of sales.
If sales are greater than $899 but equal to or less than $1799 then commission is 80% of sales.
If sales are greater than $1799 then commission is 90% of sales.

On Fri, 29 Jun 2012 05:13:50 +0000, Space Jace > wrote:

>
>I still have no clue as to how to create a formula from this problem
>that I have.
>I am looking to create variable commission rates for differing price
>points. Can I get some Help?
>
>If sales amount is equal to or less than $449 then commission is 50% of
>sales
>If sales are greater than $449 but equal to or less than $599 then
>commission is 60% of sales.
>If sales are greater than $599 but equal to or less than $899 then
>commission is 70% of sales.
>If sales are greater than $899 but equal to or less than $1799 then
>commission is 80% of sales.
>If sales are greater than $1799 then commission is 90% of sales.

This is frequently done using a Lookup Table.
For example, in G1:G5, enter the following values:

$ 0.000 50%
$449.001 60%
$599.001 70%
$899.001 80%
$1,799.001 90%

Then, to return the commission percentage, use this formula, with the sales amount in A1:

=VLOOKUP(A1,$F$1:$G$5,2)

To return the commission amount, merely multiply A1 by the percentage:

=VLOOKUP(A1,$F$1:$G$5,2)*A1

On 29/06/2012 3:13 PM, Space Jace wrote:
> If sales amount is equal to or less than $449 then commission is 50% of
> sales
> If sales are greater than $449 but equal to or less than $599 then
> commission is 60% of sales.
> If sales are greater than $599 but equal to or less than $899 then
> commission is 70% of sales.
> If sales are greater than $899 but equal to or less than $1799 then
> commission is 80% of sales.
> If sales are greater than $1799 then commission is 90% of sales.

Hi

If you're happy yo use VBA, the following does what you need without
having nested formulas...

Change sheet name and ranges to suit.

HTH
Mick.

Sub Extract_Commission()

Dim myWsht As Worksheet
Dim mySales As Range
Dim c As Range


Set myWsht = Worksheets("Sheet1")
Set mySales = myWsht.Range("A2:A21")

For Each c In mySales
If c <> "" Then
Select Case c
Case 1 To 449
With c
.Offset(0, 1).Value = (c.Value * 0.5)
End With
Case 450 To 599
With c
.Offset(0, 1).Value = (c.Value * 0.6)
End With
Case 600 To 899
With c
.Offset(0, 1).Value = (c.Value * 0.7)
End With
Case 900 To 1799
With c
.Offset(0, 1).Value = (c.Value * 0.8)
End With
Case Else
With c
.Offset(0, 1).Value = (c.Value * 0.9)
End With
End Select
End If
Next c

End Sub