+ Reply to Thread
Results 1 to 5 of 5

Formula help for bonus calculation with minimum threshold?

  1. #1
    Registered User
    Join Date
    01-07-2015
    Location
    London
    MS-Off Ver
    London
    Posts
    3

    Formula help for bonus calculation with minimum threshold?

    Hi All, I am trying to work out the best way to do enter a formula into a spreadsheet that calculates driver bonuses. My challenge for this is that the drivers have to do a minimum of 5 loads before they get anything then on the fifth they get a £15 bonus and then £15 per load thereafter (or part thereof). At first I thought:

    =MAX(0,C21-4)*15 ..Where C21 is the input of the number of jobs done that day.

    Of course that doesn't work because it would pay out on 4.25 loads or 4.5 loads...

    Any help you could give me would be much appreciated! Thank you in advance.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula help for bonus calculation with minimum threshold?

    Please Login or Register  to view this content.
    If someone has helped you then please add to their Reputation

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula help for bonus calculation with minimum threshold?

    Try

    =IF(C21<5,0,MAX(0,C21-4)*15)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    01-07-2015
    Location
    London
    MS-Off Ver
    London
    Posts
    3

    Re: Formula help for bonus calculation with minimum threshold?

    Thank you for your very quick assistance on this. I have tried it and it works however how can I do it so that the minimum value is zero and not a negative value?

  5. #5
    Registered User
    Join Date
    01-07-2015
    Location
    London
    MS-Off Ver
    London
    Posts
    3

    Re: Formula help for bonus calculation with minimum threshold?

    Quote Originally Posted by Ace_XL View Post
    Try

    =IF(C21<5,0,MAX(0,C21-4)*15)
    That is it! Perfect! Thank you so much for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 9
    Last Post: 11-25-2014, 06:07 PM
  2. Help required - formula to check minimum and maximum threshold
    By ismailm in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-18-2014, 08:33 AM
  3. [SOLVED] Bonus Calculation
    By AZ-XL in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-14-2014, 06:56 AM
  4. Forecast Bonus Calculator - Require a formula to calculate the bonus' due
    By The_Snook in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2013, 07:32 PM
  5. Excel 2007 : How to automate calculation of threshold value?
    By sandyjune1988 in forum Excel General
    Replies: 1
    Last Post: 12-28-2011, 02:50 AM

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