+ Reply to Thread
Results 1 to 6 of 6

Help Please! SUMMING up numbers calculated by formula vs. input manually

  1. #1
    Registered User
    Join Date
    10-03-2006
    Posts
    3

    Help Please! SUMMING up numbers calculated by formula vs. input manually

    When I SUM up five decimals that have been calculated by using a formula (converting time into decimals), the SUM is not correct. However, if I manually add up the numbers and SUM them, the total is correct. I'm SUMMING up 2.17 five times. The column in which 2.17 has been reached via formula comes up with a total of 10.83. However, if I manually input 2.17 and SUM it up, the total is correct - 10.85. HELP PLEASE! Thanks, Marta

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803
    Why do you say that 10.83 isn't correct? If I take 2.165, which would correctly display as 2.17 to 2 decimal places, it would correctly add 5 times to result in 10.825, which would correctly display as 10.83 to two decimal places.

    Basically, it's a function of rounding. If a function results in 2.17, that means that the actual value returned from the function could be anything from 2.165-2.175. If you really want your spreadsheet to result in 2.170000000000 * 5, then you'll need to nest your formula inside of the ROUND function: =ROUND(formula,2).

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Thumbs up

    Quote Originally Posted by petite10252
    When I SUM up five decimals that have been calculated by using a formula (converting time into decimals), the SUM is not correct. However, if I manually add up the numbers and SUM them, the total is correct. I'm SUMMING up 2.17 five times. The column in which 2.17 has been reached via formula comes up with a total of 10.83. However, if I manually input 2.17 and SUM it up, the total is correct - 10.85. HELP PLEASE! Thanks, Marta
    Hi Marta,

    Check the format are your five cells are they set to time?

    oldchippy

  4. #4
    Registered User
    Join Date
    10-03-2006
    Posts
    3

    SUMMING numbers reached via forumula

    Thanks for the replies. I just want to make sure I get this right...

    If I manually input 2.17 (not a "rounded" number), and SUM up the five rows 2.17 is manually entered in, I get 10.85. This is the number the person I'm helping wants - 10.85.

    However, the 2.17 which is arrived at by formula returns 10.83. The forumla which returns the 2.17 is: =(G14-INT(G14))*24. See below. This is a timesheet someone is working on, and she wants the SUM of 2.17 to return 10.85. Should I use the ROUND function always? For instance, if the TOT HRS ends up being 2.19, I'm assuming the person making this time sheets wants a return of 10.95, but I won't get that unless I round, correct?

    Row G Row H
    IN OUT HRS/MIN TOT HRS
    12:00 PM 2:10 PM 2:10 2.17
    12:00 PM 2:10 PM 2:10 2.17
    12:00 PM 2:10 PM 2:10 2.17
    12:00 PM 2:10 PM 2:10 2.17
    12:00 PM 2:10 PM 2:10 2.17
    X 10.83 = 10.83

    Thanks much!!!! Marta

  5. #5
    Registered User
    Join Date
    10-03-2006
    Posts
    3

    SUMMING up Decimals

    Sorry, I posted my reply to your replies, but there is on formatting.
    Row G is 2:10. Row H is 2.17. I hope this helps. Thanks again for your help.

    Marta

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803
    Should I use the ROUND function always? For instance, if the TOT HRS ends up being 2.19, I'm assuming the person making this time sheets wants a return of 10.95, but I won't get that unless I round, correct?
    If that's what you/they want, then, yes, you will always need to use the round function. Converting times to tot hours will almost always yield repeating decimals (2:10=2 10/60 hours=2.1666666666... hours). If you want subsequent calculations to treat this to the nearest 0.01 of an hour, then you will have to use the ROUND function.

+ 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