Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What the??! Incorrect Totals... (2002)

    I am totally stumped. I have been staring at this worksheet for hours and still can't figure out what is going on. See attached.

    You will notice there are three sheets (July, August & Total).

    I am trying to take the total of two figures from two sheets, July E51 and August E51, and put that total figure into sheet 3, Total E51, but for some unknown reason, when it totals, the figure is one cent less! It is not rounding down and I do not have figures to three decimal places (only two) so I can't work out why it would do this. I have highlighted the offending sections. Also, in column J, the total is increased by one cent - again, and for no obvious reason. Is there something I'm not seeing?

    Any assistance would be GREATLY appreciated!

    Kristy
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: What the??! Incorrect Totals... (2002)

    Hi Kristy,

    You do indeed have some cells with values to 3 decimal places in both your July and August worksheets.

    If you take a look at cell E8, for example, you'll see that both sheets have a value to 2 decimal place multiplied by 1.1. Those, and others like them, are what are generating the values to 3 decimal places. You only see the first 2 decimal places because that's all your spreadsheet is formatted to show.

    If you need the cells in your July and August worksheets to return values to 2 decimal places, you should surround such cells with:
    =round( your formula ,2)

    Taking July E8 as an example:
    =round(12636.67*1.1,2)

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: What the??! Incorrect Totals... (2002)

    Hi again,

    Just to make life that little bit easier, here's a macro to wrap your existing formulae with the rounding function:

    Sub AddRounding()
    For Each UnRoundedCell In Selection
    CellString = UnRoundedCell.Formula
    If Left(CellString, 1) = "=" Then
    CellString = Right(CellString, Len(CellString) - 1)
    CellString = "=round(" & CellString & ",2)"
    UnRoundedCell.Formula = CellString
    End If
    Next UnRoundedCell
    End Sub

    To use the macro, press <Alt-F11> to open the Visual Basic Editor
    Now, double-click 'ThisWorkbook' from the left pane, then paste the above code into it.
    Close the Visual Basic Editor.
    Next, select a range of cells to process on the July or August Worksheets (eg: E5:E50).
    Finally, press <Alt-F18>, select your macro and click 'Run'.
    Now any of the selected cells that have formulae in them will be wrapped in the rounding function.
    WARNING: Don't run the macro on the same range of cells more than once - doing so will add another rounding wrapper to what's already there!

    Cheers

    PS: Don't run the macro on cells that have formulae working with text strings or you'll get an error.

    PPS: After adding the macro, Excel will give you macro warning whenever you open the workbook. Unless you need to keep the macro for future use, I'd suggest going back into the Visual Basic Editor and deleting the code after you've finished running it.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What the??! Incorrect Totals... (2002)

    Your cells are formatted to display two decimal places, however, since they contain floating point numbers Excel keeps up to 15 decimal places. If you format those cells to display 4 decimal places you will see the following:

    <pre>Cell Contains Displayed
    July!E51 108710.6370 108710.64
    August!E51 36237.2270 36237.23
    Total!E51 144847.864 144847.86
    </pre>


    The easiest way to fix, this problem for this workbook, looks like it would be to select Options from the Tools menu. In the dialog box click on the Calculation tab, and then in the Workbook options section click on Precision as displayed. This will cause Excel to automatically round the result of all calculations do the number of decimal places displayed in the cell. You need to be careful with this option. If you have cases where you need the extra percision, you must use the ROUND function in formulas instead of this option. Since it looks like this workbook is dealing with currency values so that should not be a problem in this workbook.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What the??! Incorrect Totals... (2002)

    Thanks for all your help with my 'totalling problem' - I must be very blonde to have missed the 1*1 calculation! [img]/forums/images/smilies/smile.gif[/img]

    Regards

    Kristy

  6. #6
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What the??! Incorrect Totals... (2002)

    Thanks Legare! I have tried your suggestion as well and it works beautifully.

    Thanks for all your help!

    Kristy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •