Two decimal places.

[J.McQ]

Does anybody know if there is a way to format a spreadsheet so that regardless of what is entered onto it, it will be rounded to two decimal places?

The gist of this is that a reporting tool I use returns results - excel based, but then need to be paste specialed as values. The result of doing that is that the values come out at the likes of 789.8278257253 even though the value in the actual cell would say 789.83.

So, the value in the cell rounds to two decimal places on the face of it, but underlying it is the rest of the decimal places and it's that part that I need rid of.

Any ideas or solutions would be much appreciated.

[xLJer]

I think you would have more confidence in your data
by doing the calculation in a separate column rather
than using a solution that magically and invisibly
removes all but two trailing decimal places as soon as
the data is copied to a cell.

Try this formula with your numeric value in cell A1, and
the formula in B1:

Please Login or Register  to view this content.

[Pete_UK]

You can use the Precision as Displayed option (although use it with care, as you can't undo it). Click on the Office button, then Excel Options (bottom of panel), then Advanced, then scroll down to the section "When calculating this workbook", then check against Precision as Displayed.

You might like to have a look at some of the other settings that control how Excel works for you, then click OK when finished.

Hope this helps.

Pete

[Armando Montes]

Maybe this short macro:

Please Login or Register  to view this content.

[J.McQ]

Thanks very much for sharing your skills folks! I think precision as displayed is going to be perfect for this one, so thanks very much for that Pete.

I've already thought of another use for that macro you given me, Armando, so thanks very much for that too. I've been using the round function in the manual way, but that macro is going to work brilliant instead!

Can't test them out until Monday, but I think it's going to be sorted.

Thanks again