Formula help for bonus calculation with minimum threshold?

1. 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...

2. Re: Formula help for bonus calculation with minimum threshold?

``Please Login or Register  to view this content.``

3. Re: Formula help for bonus calculation with minimum threshold?

Try

=IF(C21<5,0,MAX(0,C21-4)*15)

4. 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. Re: Formula help for bonus calculation with minimum threshold?

Originally Posted by Ace_XL
Try

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

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

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