# Thread: What the??! Incorrect Totals... (2002)

1. ## 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

2. ## 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:

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

Cheers

3. ## 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:

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.

4. ## 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.

5. ## 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. ## Re: What the??! Incorrect Totals... (2002)

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