+ Reply to Thread
Results 1 to 13 of 13

rounding issues

  1. #1
    Registered User
    Join Date
    12-12-2007
    Posts
    14

    rounding issues

    Ok, This is really confusing for me to try and explain but I will do it the best I can.


    I have a formula that looks like this:

    =IF(ISERROR((C101/G119)*G104)," ",((C101/G119)*G104))

    There are about 15 cells with formulas similar to this. They are calculating a value for a row and at the end all of them should add up should equal C101.

    The problem is the value that formula returns has to be two digits and I know it almost never is. But it ALWAYS equils C101. However because it only shows two digits somtimes if you manulally add up the value that you see it does not its off by +/-.01.

    I need to fix it so that it does equal C101 AND if you were to add up the digits (with 2 decimal places) it will also equil C101.

    The formula I have above is complicated as it is How would I make it to account for this error?

    Thank you in advance for your help
    Tom

    P.S. I am sorry if it is confusing.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Tom

    One way would be to use the ROUND function to round the results of 14 of the 15 formulas to 2 places, and have the last cell be C101 - the sum of the 14 other cells.

    HTH

    rylo

  3. #3
    Registered User
    Join Date
    12-12-2007
    Posts
    14
    That would work, unfortunately I forgot to mention that most days people will only do three out of the 15 tasks and it is impossible to know which tasks they will be doing.


    Thank you,
    Tom

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Tom

    Hmmm. Makes things more interesting.

    Can you put up a sample sheet with some representative examples. May be able to work out the last filled item and use that as the balancing item.


    rylo

  5. #5
    Registered User
    Join Date
    12-12-2007
    Posts
    14
    Here ya go.

    Sorry it took so long I have inserted comments to hopefully make it a bit easier. I have also only used 3 departments for now also but just remember that whatever solution is found has to work even if one of the departments entered is blank.


    Thank you again for your help. I will be playing around with it some more.

    Tom
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-12-2007
    Posts
    14
    I can tell if it is going to give an error if there is an odd number of odd numbers. (after multiplying by 100 of course).


    =IF(ISODD(ROUND(B4/C11*C9,2)*100), ROUND(B4/C11*C9,2)+(IF(ROUND(B9, 1)>B9, -0.01, 0.01)), ROUND(B4/C11*C9,2))

    I think I may need to add that formula to every single cell... I will spend then next 45 minutes seeing if that works lol.



    After all is said and done that does not work. It makes it worse.
    Last edited by emt3000gt; 01-04-2008 at 02:19 AM.

  7. #7
    Registered User
    Join Date
    12-12-2007
    Posts
    14
    Bump for my frustration

  8. #8
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    I found the example file now too small. Can you update it to have about 10 rows and columns, and have items missing in a representative fashion.

    Then show what you would expect to see where for each scenario, with descriptions where relevant.


    rylo

  9. #9
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi Tom

    Excel's accuracy is limited to 15 digits, calculations can be affected by this and the displayed results can appear to be different too...

    One option is to save a copy of your workbook, open it in a new instance of Excel (ie have NO other files open - as the following may (?) affect other files too) & see if changing the "precision as displayed" option under Tools-Options in the copy of your file helps with the rounding difference (check out the Help files or Google for more details).

    I'm not sure if the above will actually help you & I think that any adjustment should be made to B5, the "Hours Worked" cell, rather than the calculated cells. Cell B5 is hardcoded in the example file as 7.5 ie only one decimal place (dp), so there is always the potential for a difference. Where is cell B5 sourced from/can this cell be displayed as 2 dp?
    If cell B5 can't be changed then I'd include a balancing cell or formula as Rylo suggested (although to an extent this defeats the purpose of providing a total for comparison/cross-casting).


    Also, I realise this is just an example file but I would suggest maintaining a consistent layout for both of the tables in your actual file rather than having dept's using columns in one table & rows in the other. For example, have the "Tally Hours" & "Hours" in cells A5 & A6 with the dept results in the respective columns or even the other way around with the departments all going down the page with the other info listed across the columns. This will make maintainance of the formulae easier (b/c you'll be able to copy formulae all the way across/down with the use of absolute/relative referencing) & possibly improve readability as well.


    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  10. #10
    Registered User
    Join Date
    12-12-2007
    Posts
    14
    I am posting a larger example. If you manually add the values in Tally Hours it does not equil 7.5.

    They NEED to equal 7.5 (or whatever the person enters there) because this is used on a time sheet so the total has to equal the amount of hours the person worked. Its ok if the values for each department is off by a little bit.


    I am sorry if the layout here is a bit confusing but there are two different programs used to count how much work is done and they have different layouts thats why it is layed out the way it is, to make it easier for the user to copy the information.

    And just remember that normaly there are only about 3 or 4 different departments processed but it is impossible to know which ones they will be.

    Again thank you for your help.
    Tom
    Attached Files Attached Files

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    In B8 and copy down,

    =IF(C8=0, 0, ROUND(C8 * ($B$4 - SUM($B$7:B7) ) / ($C$23 - SUM($C$7:C7)), 2) )

    If hours were recorded instead in tenths, then

    =IF(C8=0, 0, ROUND(C8 * ($B$4 - SUM($B$7:B7) ) / ($C$23 - SUM($C$7:C7)), 1) )
    Last edited by shg; 01-06-2008 at 04:48 PM.

  12. #12
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Another option

    B8: =IF(COUNTIF($C$8:C8,"<>0")=COUNTIF($C$8:$C$22,"<>0"),$B$4-SUM($B$8:B8),ROUND(B4/C23*C8,2))
    C8: =IF(COUNTIF($C$8:C9,"<>0")=COUNTIF($C$8:$C$22,"<>0"),$B$4-SUM($B$8:B8),ROUND($B$4/$C$23*C9,2))

    Copy down from C8 as required.


    rylo

  13. #13
    Registered User
    Join Date
    12-12-2007
    Posts
    14
    Got it working! Thank you all for your help this problem has beein a PITA.

    Again thanks for your help!

    Tom

+ 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