How do I correct rounding errors in Excel formulas?

[C. Van Dam]

I am working with spreadsheets in Excel 2000 and Excel 2002, and find that
although I'm using the same formulas throughout differing cells in the
spreadsheet, some cells round the results correctly and some do not. Has
anyone encountered this problem, and if so, does anyone know of a fix for it?
The data I'm working with rounds answers that result in currency (dollars
and cents) and it's imperative that I get the numbers to round correctly to
the next penny following the standard rule of up for .5 cents or more and
down for .4 cents or less.

I have even tried copying and pasting the formulas from cells that are
rounding the results correctly into the cells that are rounding incorrectly
to no avail. No matter what I've tried, some cells will round correctly (in
both up or down directions) and some will not (they truncate the answer
instead of rounding it).

Thanks in advance for any help!

 

[Ron Rosenfeld]

On Mon, 28 Aug 2006 19:44:01 -0700, C. Van Dam <C. Van

I am working with spreadsheets in Excel 2000 and Excel 2002, and find that
although I'm using the same formulas throughout differing cells in the
spreadsheet, some cells round the results correctly and some do not. Has
anyone encountered this problem, and if so, does anyone know of a fix for it?
The data I'm working with rounds answers that result in currency (dollars
and cents) and it's imperative that I get the numbers to round correctly to
the next penny following the standard rule of up for .5 cents or more and
down for .4 cents or less.

I have even tried copying and pasting the formulas from cells that are
rounding the results correctly into the cells that are rounding incorrectly
to no avail. No matter what I've tried, some cells will round correctly (in
both up or down directions) and some will not (they truncate the answer
instead of rounding it).

Thanks in advance for any help!

It's hard to tell without seeing the data and the formulas, which you have not
chosen to share with us.

But the most likely explanation is that you don't understand what is going on.

Excel will add the values that are in the cells. You are probably confusing
what you see in the cell with what is actually in the cell. What you see is
based on the result of the calculation as formatted. So you could see, for
example $12.46 when the contents is really $12.46497812.

But formatting does NOT change the value in the cell, and when excel performs
an operation on the contents of the cell, it ignores the formatting.

It that doesn't help you solve the problem, you will have to post back with
more detail.
--ron