+ Reply to Thread
Results 1 to 6 of 6

overtime total is diferent when made by excel than when made by hand

  1. #1
    Registered User
    Join Date
    04-14-2012
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    30

    overtime total is diferent when made by excel than when made by hand

    hello , working on the payroll worksheet i need to compute overtime pay and when using a formula on excel it says its 10.00 but when calculated by hand its 10.05 , so its 5 cents less for the employee , so would like to know if you can fix out the formula or tell me whats wrong and how to fix it , thanks : ) ,

    ps. attached is copy of the worksheet of the topic
    Attached Files Attached Files
    Last edited by tecnico; 04-16-2012 at 05:13 PM.

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: overtime total is diferent when made by excel than when made by hand

    the difference that i see is due to the following:

    0.66*15 = 9.99 which is rounded to 10

    0.67*15 = 10.05 even

    if you used the formula - =IF(E3>8,A3*1.5*ROUNDUP((E3-8),2),0) - you would get 10.05.

    instead of decimals, if you used fraction (2/3 for 40 minutes), your answer would be an even 10.

  3. #3
    Registered User
    Join Date
    04-14-2012
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: overtime total is diferent when made by excel than when made by hand

    ok made that corection and the 5 cents issue was ok , but when made some adjustments on the hour on one of the cells then th employee had 15 cents more vs the equation by hand : ( , included is the worksheet with this data, would apreciate your help , thanks in advanced
    Attached Files Attached Files

  4. #4
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: overtime total is diferent when made by excel than when made by hand

    tecnico, it is probably better to ROUNDUP the column where you deduct OUT hours from IN hours. see attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-14-2012
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: overtime total is diferent when made by excel than when made by hand

    icestationzbra thanks for your responses : ) , but would like to know ,

    ok with the 9.54 would be ok on both ways excel and by calculator BUT how is 9.533 rounded up ?????? Cause with excel

    9.533 to 2 decimal places gives 9.53 not 9.54 : ( , by hand could make it 9.54 but with excel it came up as when making it to 2 decimal places 9.53 , so could you
    explain if posible how the roundup function converts the 9.533 to 9.54 ?

    included is the worksheet with the data
    Attached Files Attached Files

  6. #6
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: overtime total is diferent when made by excel than when made by hand

    take a look at the attached example (highlighted section) to see if this makes sense...
    Attached Files Attached Files

+ Reply to Thread

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