Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sliding Commission Formula
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sliding Commission Formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sliding commission rates in excel | Excel Worksheet Functions | |||
Sliding scale commission percentages formulas | Excel Worksheet Functions | |||
Percentage Commission on a sliding scale. | Excel Discussion (Misc queries) | |||
sliding commission calculation | Excel Programming | |||
calculating commission on sliding scale | New Users to Excel |