Results 1 to 11 of 11

2 Tiered Commisions Formula

Threaded View

  1. #1
    Registered User
    Join Date
    12-01-2009
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    8

    2 Tiered Commisions Formula

    Hiya,

    I'm Looking for help with a formula used to calculate commisions.

    The problem is that the commisions are 2 tiered depending on type of product sold (Low end or high end)
    Low end products are identifed by the fact that their stockcode starts with either C,F,G,H,J,T eg: F-4050
    High end products are identified by the fact that their stockcode starts with either A,E,K,w eg: E-2074

    First commision is determined by summing the ValuetoDate column for each AccountCode (Low end and highend included)
    The same AccountCode can appear multiple times in the table.
    This total is then compared to a commisions table with the following criteria....see table1

    Low end product valuetoDate are summed to Give lowend Total

    Low end commision is then = lowend total x low end commision %

    Second commision is also determined by summing the ValuetoDate column for each AccountCode (Low end and highend included)
    This same total is then compared to a commisions table with the following cirteria....see table2

    High end product valuetoDate are summed to yield highend Total

    High end commision is = highend total x high end commision %


    The 2 commisions are then added together to give a total commison per Accountcode..

    can anyone help me with the forumala in either access or excel or SQL.
    I have attached some example data containing info on 2 accountcodes

    Any help greatlty appretiated..

    Ray


    Table1

    Total Sales Value Commission %
    0.00 - 300 0%
    301 - 1000 5%
    1001 - 2000 7.5%
    2001 - 3500 10%
    3501 - 4500 12%
    4501 - 7500 15%
    7501+ 20%



    Table2

    Total Sales Value Commission %
    0.00 - 300 0%
    300 - 1000 5.0%
    1001+ 7.5%
    Attached Files Attached Files

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