Set Cell Values to 2 Decimal Points

[Trevor G]

I have a sheet and columns A to V have values that have more than 2 decimal points how can I strip away the excessive decimal points, there are positive and negative values via VBA over 30000 rows.

 

[obiron]

It depends on what you mean by 'Set'

You can format the cells to numerical 2dp and they will only display (and export) with 2dp but the precision is still stored in the cell.

You can ROUND() the values which will trim the precision using standard rounding rules [ round up if the first significant digit to be trimmed >= 5 or down if 1st s.d. is <=4]

You can TRUNC() which will simply chop off the digits after the precision you specify (i.e. 2.999999999999 will become 2.99, not 3.00)

If you install the analysis toolpak add-in you can also use MROUND() which will apply rounding rules but will round to the nearest multiple (e.g. 0.25) rather than the nearest signigicant digit.

 

[Joe4]

Another way is to format it to show the number of decimals you want, and then selecting the "Set precision as displayed" option (found under the Advanced Excel option in Excel 2007, under the "When calculating this workbook section).

The nice thing about this option is that it will affect your whole worksheet, and no formulas are necessary.

 

[Trevor G]

Thank you Joe.

I looked at this method and was able to automate it so I can proceed.

Obiron grateful for your help as well.