Currency Values Aren't Two Decimal Places 2

[Hakala]

Hi, Tek-tippers! I am trying to reconcile a massive currency nightmare in an Excel workbook. However, some of the values I get (there are four sources) are not true currency numbers and have many decimal places, usually in the six to eight range.

If I format to currency, of course, it only *looks* like it's two decimal places. When I have a long column of these numbers, the total at the bottom can be off by up to 45 cents from the total if the decimal places are actually two.

Fixing it manually works, but is time-consuming.

Is there a way to actually round my data up to two decimal places without touching each cell?

Michelle Hakala

http://www.winebird.com/

 

[SkipVought]

hi,

Is there a way to actually round my data up to two decimal places without touching each cell?

In an empty column enter your rounding function. Then COPY that column and Edit > Paste Special -- VALUES in the column you want to change.

BTW, to restate your conclusion for the benefit of other members, formatting changes NOTHING. The underlying value must be changed. Hence the process in the prior paragraph.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Hakala]

That worked! I didn't know there was a rounding function in Excel. Thanks, Skip!

Michelle Hakala

http://www.winebird.com/

 

[macropod]

Hi Michelle,

Another approach, which doesn't involve rounding off the underlying data, is to use the 'precision as displayed' option. Just be aware that this affects all calculations in the workbook, not just those in a particular range.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Hakala]

Yet another great tip. Thank you!


Michelle Hakala

http://www.winebird.com/