+ Reply to Thread
Results 1 to 9 of 9

Calculate Sliding Scale

  1. #1
    Registered User
    Join Date
    01-26-2012
    Location
    Campbell River, BC
    MS-Off Ver
    Excel 2003
    Posts
    6

    Calculate Sliding Scale

    Does anyone know how to make a sliding scale? It is a commission sliding scale. The scale is 60% for 1st $100, 70% for 2nd $100, 75% for 3rd $100, 20% for 4th $100, 15% over $400.

    If something sold for $580:

    $60 of the 1st $100

    $70 of the 2nd $100

    $75 of the 3rd $100

    $80 of the 4th $100

    $153 of the Remainder after $400 ($180)

    Total=$438

    So basically I need to be able to punch in the Price then it would put the commission and the remainder into the next 2 cells.

    Thanks
    Daniel

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculate Sliding Scale

    Daniel

    Don't really follow your example as 60+70+75+80 = 285, 180*.15 = 27, so 285+27 = 312.

    Set up a table in the range F3:H7 with the following values
    0,0,.6
    100,60,.7
    200,130,.75
    300,205,.4
    400,285,.15

    If you have your 580 in A1 then try
    =VLOOKUP(A1,F3:H7,2)+((A1-VLOOKUP(A1,F3:H7,1))*VLOOKUP(A1,F3:H7,3))

    rylo

  3. #3
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Calculate Sliding Scale

    You could use a UDF - place the following in a module
    Please Login or Register  to view this content.
    The in A2 place a value e.g. 580 then in B2 place the following formula =comm(A2)
    The only condition I am not clear on is what if the remainder is LESS than $153?
    See attached workbook as example- Formula in B2
    Attached Files Attached Files
    Last edited by smuzoen; 01-26-2012 at 11:59 PM.

  4. #4
    Registered User
    Join Date
    01-26-2012
    Location
    Campbell River, BC
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Calculate Sliding Scale

    Hi rylo,

    Basically the example is: If something sells for say $580 that $580 would be split up into $100@40%, $100@30%, $100@25%, $100@20% and $180@15%. So if something sold for $100 even I would get $40. If something sold for $200 I would get $70 ($40+$30). If I sold something for $320 I would get ($40+$30+$25+$4).

    Hope that helps. I will give you a link to my website. If you go under the 'About' section it will show you how the commission is based.

    crconsignments.com

    Thanks
    Daniel

  5. #5
    Registered User
    Join Date
    01-26-2012
    Location
    Campbell River, BC
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Calculate Sliding Scale

    Hi rylo,

    Basically the example is: If something sells for say $580 that $580 would be split up into $100@40%, $100@30%, $100@25%, $100@20% and $180@15%. So if something sold for $100 even I would get $40. If something sold for $200 I would get $70 ($40+$30). If I sold something for $320 I would get ($40+$30+$25+$4).

    Hope that helps. I will give you a link to my website. If you go under the 'About' section it will show you how the commission is based.

    crconsignments.com

    Thanks
    Daniel

  6. #6
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Calculate Sliding Scale

    If you would use a UDF then
    Please Login or Register  to view this content.
    Formula in workbook =comm(A2)

    Hope this helps.
    Anthony
    “Confidence never comes from having all the answers; it comes from being open to all the questions.”
    PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculate Sliding Scale

    Daniel

    OK, same locations as post 2, but values are
    0,0,.4
    100,40,.3
    200,70,.25
    300,95,.2
    400,115,.15

    Put your value into A1 and the lookups will bring back the result.

    rylo

  8. #8
    Registered User
    Join Date
    01-26-2012
    Location
    Campbell River, BC
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Calculate Sliding Scale

    rylo,

    Works great so far! I tried a couple of numbers and they worked. Sweet!

    Thank you very much

  9. #9
    Registered User
    Join Date
    01-26-2012
    Location
    Campbell River, BC
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Calculate Sliding Scale

    smuzoen,

    I am fairly new to speadsheets. I am not sure of how to use UDF or what UDF is. Would be cool to know though.


    Thanks
    Daniel

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1