Results 1 to 6 of 6

Help with commission formula

Threaded View

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    UK
    MS-Off Ver
    Excel for MAC 2011
    Posts
    3

    Post Help with commission formula

    Hi - I'm completely stumped with this - I am trying to work out a formula for the following commission scheme:

    £ 0-1999 - 0 commission
    £ 2000-2001 - 2.5% commission (ie. flat £50 bonus)
    £ 2001-3000 - 4% commission
    £ 3001-4000 - 6% commission
    £ 4001-5000 - 7% commission
    £ 5001-6000 - 8% commission

    The figure I'm using as an example is £3576.01 and the commission payable should be £ 124.56. All of the formulas I've tried give me the wrong answer as they are putting 4% commission on the whole amount, and it should be 2.5% on the first £2000, 4% on the next £1000, and 6% on £576.01 etc. I have tried a SUMPRODUCT formula, a MAX formula (which gets the closest answer) and a LOOKUP formula.

    Thanks!
    Last edited by rebeccapw; 04-23-2013 at 05:21 AM. Reason: typo

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