Excel equation rounding errors

[Ned S]

I am attempting to sum a series of non-sequential cells
that have been calculated as part of a separate
equation. The cell is displayed without decimal
positions. However, when picked as as input to a separate
equation the value used in the second equation has not
been rounded. If the series of cells are added visually
used the displayed cell contents the results are
different than the value calculated. The difference is
in the actual value stored versus the value display. My
goal is to make both totals the same. MROUND or ROUNDUP
only works on stored numbers not embedded equations. Is
there a recommended manner of resolving this problem?

 

[JE McGimpsey]

The usual solution is to round the cells that input to your formula, so
if your formula is

=SUM(A1,J10,M75)

then A1, J10, and M75 should be rounded, for example:

A1: =ROUND(<your formula here>,0)

You can check the Tools/Options/Calculation/Precision as Displayed
checkbox, but that has a global scope, so may not get what you're after.

Obviously, if A1, J10 and M75 are user inputs, you'll need to round each
separately, e.g.:

=SUM(ROUND(A1,0),ROUND(J10,0),ROUND(M75,0))


Sometimes you have to do some very careful planning to make the rounding
come out right. See

http://www.mcgimpsey.com/excel/pennyoff.html

for examples.