SOLVED

Rounding

Copper Contributor

How can I round an entire section of numbers at one time without going into each formula and adding the Round function?  I want the calculated number to end up with only 2 decimal points and not just display 2 decimal points.  I don't want to use the number formatting as that will just change the way the numbers are displayed. 

 

For instance:

8543.236985 - want the calculated number to be 8543.24

8542.48792 - want the calculated number to be 8542.49

6877.1 - want the calculated number to be 6877.10

 

4 Replies
best response confirmed by ChrisM1222 (Copper Contributor)
Solution

@ChrisM1222 

One possible solution:

  • Select File > Options.
  • Select Advanced.
  • Scroll down to the section 'When calculating this workbook'.
  • Tick the check box 'Set precision as displayed'.
  • Click OK.
  • Set the number format of the cells to Number with 2 decimal places.
  • Excel will round the stored values of the cells to 2 decimal places.
  • Warning: this will affect all formatted cells in the workbook. It won't affect other workbooks.

 

Another option would be VBA code.

@ChrisM1222 

Assuming Excel 2021 or 365

Sample2.png

then format the cells with 2 decimals

this only displays the number as 2 decimal places but the actual number in the cell extends past that.

@ChrisM1222 

Not the case here or I don't understand what you mean with "the actual number in the cell extends past that"

1 best response

Accepted Solutions
best response confirmed by ChrisM1222 (Copper Contributor)
Solution

@ChrisM1222 

One possible solution:

  • Select File > Options.
  • Select Advanced.
  • Scroll down to the section 'When calculating this workbook'.
  • Tick the check box 'Set precision as displayed'.
  • Click OK.
  • Set the number format of the cells to Number with 2 decimal places.
  • Excel will round the stored values of the cells to 2 decimal places.
  • Warning: this will affect all formatted cells in the workbook. It won't affect other workbooks.

 

Another option would be VBA code.

View solution in original post