rounding numbers

[Excel Wired]

in one spreadsheet I get total of $96,897.33 and using the same exact figures
in another spreadsheet, I get $96,897.32 and the penny is driving me off the
deep end. Why are the spreadsheets off .01 cent?

Must I round down or up?

 

[RagDyeR]

From J.E. McGimpsey's web site:

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

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

in one spreadsheet I get total of $96,897.33 and using the same exact
figures
in another spreadsheet, I get $96,897.32 and the penny is driving me off the
deep end. Why are the spreadsheets off .01 cent?

Must I round down or up?

 

[David Biddulph]

What are the formulae in the 2 spreadsheets?
What are the data values in the 2 spreadsheets?
Do you have different options set (such as precision as displayed)?

Whether you should round up or down (or neither) depends on what calculation
you are trying to do.

 

[JoeU2004]

in one spreadsheet I get total of $96,897.33 and using the same exact
figures
in another spreadsheet, I get $96,897.32 and the penny is driving me off
the
deep end. Why are the spreadsheets off .01 cent?

That might depend on how you got the "same exact figures" in both worksheet.
My suspicion is: in one worksheet, the figures were calculated; in the
other worksheet, you copied the figures displayed in the first worksheet.

The operative word is "displayed". What you see displayed is not always the
exact value in the cell. For example, if you calculated two values 1.2348
and 1.2349, which sum to 1.4697, they might be displayed as 1.23 and 1.23,
but their sum might be displayed as 2.47. If you were to copy what you see
(1.23 and 1.23) to another worksheet, they would sum to 2.46.

We can only speculate about the many ways such disparities can occur without
seeing the exact figures and without knowing how they were generated in each
worksheet.

Must I round down or up?

I recommend that you almost always do some kind of rounding when dealing
with financial numbers. For a variety of reasons, the value of numbers with
decimal fractions is almost never exactly as it appears.

Generally, I would simply ROUND, which rounds up or down depending on the
circumstances. There might be circumstances where ROUNDDOWN or ROUNDUP is
more appropriate. But that would depend on your application; so only you
can make that decision.

 

[Jerry W. Lewis]

Though less likely, it could still occur in pre-2007 versions if the OP did a
Copy/Paste Special/Values, since prior to 2007 there were certain bit
patterns that could only occur as the result of calculations (Excel
automatically rounded them rather than permitting them in constants). You
could only detect the difference by something like
=(Sheet1!A1=Sheet2!A1)
where the parenthes are essential.

Jerry