+ Reply to Thread
Results 1 to 14 of 14

Payroll, various rates simple formula

  1. #1
    Registered User
    Join Date
    08-08-2018
    Location
    NZ
    MS-Off Ver
    10
    Posts
    9

    Payroll, various rates simple formula

    First Time poster.

    Asking for a friend

    After a formula or plan:


    Need to enter a number of hours into one cell.

    What I need is it to calculate from the input of this CELL A to generate result in CELL B:

    If CELL A is between and including 0 and 7.6 it needs to display [true value entered]
    If CELL A is >7.61 and < 10.61 it needs to display [true value of 7.6] + [1.5* the remainder of the difference of 10.6]
    If CELL A is > 7.61 and < 16.00 it needs to display [true value of 7.6] + [3*1.5 true value] + [2.0*The remaining difference of 16]
    Notes:
    >16.10 needs to error
    < 0.00 needs to error

    best way I can explain it.
    hope it makes sense.

    Thanks in advance.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,184

    Re: Payroll, various rates simple formula

    What does this actually mean?

    [true value of 7.6] + [1.5* the remainder of the difference of 10.6]

    And this?

    [true value of 7.6] + [3*1.5 true value] + [2.0*The remaining difference of 16]

    A couple of worked examples, please.

    Asking for a friend
    Maybe a dating site for this bit???
    Last edited by AliGW; 08-08-2018 at 05:00 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-08-2018
    Location
    NZ
    MS-Off Ver
    10
    Posts
    9

    Re: Payroll, various rates simple formula

    Hi AliGW

    Thanks for the swift reply. I expected my explanation would be difficult to interpret.


    1.
    workers are paid 1.0 x their rate if they work 4 to 7.6 hours (minimum 4 hours)

    2.
    if a worker worker, works up to 10.6 hours then they get 7.6 hours at 1.0 times their rate
    then
    they get paid 1.5 times their rate for 3 hours (maximum 3 hours at 1.5 times their rate)

    3.
    if a worker, works up to 16 hours they get:
    7.6 hours x 1.0 their rate
    plus
    3.0 hours x 1.5 their rate
    plus
    2.0 times their rate for the remaing hours up to 16.0 hours.

    Note: Rate isnt important but if I could enter their rate as well that would be just a bonus.

    Hope that helps clarify.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,184

    Re: Payroll, various rates simple formula

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  5. #5
    Registered User
    Join Date
    08-08-2018
    Location
    NZ
    MS-Off Ver
    10
    Posts
    9

    Re: Payroll, various rates simple formula

    sorry the explanation is all I have at the moment.

    I'd like to say this if I could make my own formula and it would understand.

    assume A1 is the only input cell. with one output cell.

    IF A1 is <7.60 then display the true value of A1
    IF A1 is >7.61 but < 10.60 then calculate (7.6*1.0) + [(A1 - 7.6)*1.5]
    IF A1 is >10.61 but < 16.01 then calculate (7.6*1.0) + (3.0*1.5) + [(A1-10.6)*2.0]

    does that help at all?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,184

    Re: Payroll, various rates simple formula

    IF A1 is >7.61 but < 10.60 then calculate (7.6*1.0) + [(10.60 - 7.6)*1.5]
    Why 7.6? Do you really mean the value in A1???

  7. #7
    Registered User
    Join Date
    08-08-2018
    Location
    NZ
    MS-Off Ver
    10
    Posts
    9
    Quote Originally Posted by AliGW View Post
    Why 7.6? Do you really mean the value in A1???
    Yes sorry.
    You're on to it.
    But with a minimum value of 4

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,184

    Re: Payroll, various rates simple formula

    IF A1 is <7.60 then display the true value of A1
    IF A1 is >7.61 but < 10.60 then calculate (7.6*1.0) + [(10.60 - 7.6)*1.5]
    IF A1 is >10.61 but < 16.01 then calculate (7.6*1.0) + (3.0*1.5) + [(A1-10.6)*2.0]
    Try this:

    =IF(A1<4,"",IF(A1<7.6,A1,IF(A1<10.6,7.6+((10.6-A1)*1.5),IF(A1<16.01,7.6+4.5+((A1-10.6)*2),""))))

  9. #9
    Registered User
    Join Date
    08-08-2018
    Location
    NZ
    MS-Off Ver
    10
    Posts
    9

    Re: Payroll, various rates simple formula

    thanks for your reply again.
    I'm impressed.

    It doesnt seem to calculate correctly in the middle part.

    I'll put some scenarios to hopefully give the 'work back'

    Example 1:
    If someone does 6 hours.

    6 * 1.0
    =
    6

    The Formula gives
    6 CORRECT!!!



    Example 2:
    If someone does 10 hours.

    7.6 * 1 (7.6)
    +
    2.4 * 1.5 (4.5)
    =
    12.1


    The formula gives:
    8.5 INCORRECT



    -------------------

    Example 3:
    If someone does 14 hours

    7.6 * 1 (7.6)
    +
    3 * 1.5 (4.5)
    +
    3.4 * 2.0 (6.8)
    =
    18.9

    The formula gives:
    18.9...CORRECT!!!


    so good. I'm not game to mess with your handy work

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,184

    Re: Payroll, various rates simple formula

    Provide a sample workbook with a few lines of dummy data. Put in your expected results - manually calculated. I'm not going to build a dummy workbook for you - that's your job if you want any further help with this.

  11. #11
    Registered User
    Join Date
    08-08-2018
    Location
    NZ
    MS-Off Ver
    10
    Posts
    9

    Re: Payroll, various rates simple formula

    thanks for your help.
    I would be asking for the donation page (and still may) if I can work out the malfunction.
    I'll see what I can do from here.
    WOW by the way.

    thanks heaps. very good at what you do. and quick.

    cheers

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,184

    Re: Payroll, various rates simple formula

    There is no need for a donation - this is a free forum!!! Those of us who help here do so voluntarily and because we enjoy it. All I need to resolve the final issue is the sample workbook I've asked for. It should take you no more than five minutes to set it up and post it here for me.

    PS If you really want to, you can give somebody who has helped you a virtual 'pat on the back' by clicking on their reputation star under their profile to the left of their post. You can also leave a 'thank you' message that way. If you do that, it's very nice, but it's completely at your discretion. I am just happy to be thanked within the thread in question.
    Last edited by AliGW; 08-08-2018 at 06:28 AM.

  13. #13
    Registered User
    Join Date
    08-08-2018
    Location
    NZ
    MS-Off Ver
    10
    Posts
    9

    Re: Payroll, various rates simple formula

    Good reply.

    Given you did all the hard work I think I got it now.

    This is what i edited the formula to and it seems to work.

    =IF(A1<4,"",IF(A1<7.6,A1,IF(A1<10.6,7.6+((10.6-A1)*1.5),IF(A1<16.01,7.6+4.5+((A1-10.6)*2),""))))

    =IF(A1<4,"",IF(A1<7.6,A1,IF(A1<10.6,7.6+((A1-7.6)*1.5),IF(A1<16.01,7.6+4.5+((A1-10.6)*2),""))))


    So good of you.

    Thanks very much.
    I'll check out your recommendations.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,184

    Re: Payroll, various rates simple formula

    If it works for you ...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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: 2
    Last Post: 08-16-2017, 04:26 PM
  2. help needed making a simple payroll calculator
    By aphatmc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2017, 02:55 PM
  3. Formula for payroll with varrious pay rates
    By William B 1965 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-30-2015, 06:03 PM
  4. [SOLVED] Payroll Start and End Dates, need Payroll Period fix
    By colarguns in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-12-2014, 08:03 PM
  5. Replies: 0
    Last Post: 01-10-2013, 03:07 PM
  6. Adding payroll stubs payroll calculator
    By Sable in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-05-2006, 12:40 PM
  7. Replies: 0
    Last Post: 01-29-2005, 06:06 PM

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