SOLVED

Rounding

Copper Contributor

Rounding

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

Re: Rounding

One possible solution:

• Select File > Options.
• 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.

Re: Rounding

Assuming Excel 2021 or 365

then format the cells with 2 decimals

Re: Rounding

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

Re: Rounding

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

Re: Rounding

One possible solution:

• Select File > Options.