+ Reply to Thread
Results 1 to 2 of 2

Calculating a Sales Bonus When the Bonus Rate Changes as Sales Change

  1. #1
    Registered User
    Join Date
    04-04-2012
    Location
    Raleigh, NC, USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Calculating a Sales Bonus When the Bonus Rate Changes as Sales Change

    I need to create a spreadsheet to calculate a sales bonus for one person. The parameters are as follows:

    If sales >0<20000 then Bonus = 5% of total sales

    If sales >20000<=400000 then Bonus = 5% of first 20000 and 10% of sales >20000

    If sales >40000<=60000 then Bonus = 5% of first 20000, 10% of next 20000 and 15% of sales >40000

    If sales >60000 then Bonus = 5% of first 20000, 10% of next 20000, 15% of next 20000 and 25% of sales >60000

    Thanks for any advice.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Calculating a Sales Bonus When the Bonus Rate Changes as Sales Change

    Firstly, welcome to the forums! i hope we can help you with any issues you may have.

    secondly, where A1 is your total sales, use this formula

    =(A1*0.05)+IF(A1>20000,((A1-20000)*0.1),0)+IF(A1>40000,((A1-40000)*0.15),0)+IF(A1>60000,((A1-60000)*0.25),0)

    -Dan
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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