If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Sliding Commission Formula
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Sliding Commission Formula

#1
June 29th 12, 06:13 AM
 Space Jace Junior Member First recorded activity by ExcelBanter: Jun 2012 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
June 29th 12, 11:41 AM posted to microsoft.public.excel.worksheet.functions
 Ron Rosenfeld[_2_] external usenet poster Posts: 894
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
June 29th 12, 12:05 PM posted to microsoft.public.excel.worksheet.functions
 Vacuum Sealed external usenet poster Posts: 259
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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Sliding commission rates in excel [email protected] Excel Worksheet Functions 4 July 17th 08 09:40 PM Sliding scale commission percentages formulas Margs Excel Worksheet Functions 7 September 8th 07 11:33 PM Percentage Commission on a sliding scale. JonPFP Excel Discussion (Misc queries) 6 April 13th 06 06:24 PM sliding commission calculation [email protected] Excel Programming 3 January 24th 06 09:17 PM calculating commission on sliding scale corrado444 New Users to Excel 4 December 9th 05 05:08 PM

All times are GMT +1. The time now is 04:43 AM.