Forum Discussion

ChrisM1222's avatar
ChrisM1222
Copper Contributor
Jun 14, 2023

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

 

  • 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's avatar
      ChrisM1222
      Copper Contributor
      this only displays the number as 2 decimal places but the actual number in the cell extends past that.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor

        ChrisM1222 

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

  • 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.

Resources