Forum Discussion

Sudheer_Mambra's avatar
Sudheer_Mambra
Copper Contributor
Nov 13, 2020

How to remove decimal places permanently

Hi all,

 

I am new to the community. A Google search for a solution to my problem landed me here! Thanks.

 

I have a worksheet where various values are displayed as follows:

 

14369.1666666667

 

how can I delete permanently  the decimal places after the 3rd decimal place to make it 14369.167?

I am not looking for a formula like round, or trim, but a  solution that I can apply to an entire sheet.

 

Thanks  a ton in advance.

Charting
excel
Formulas and Functions
Macros and VBA

7 Replies

    • Sudheer_Mambra's avatar
      Sudheer_Mambra
      Copper Contributor
      Nov 15, 2020

      Hello SergeiBaklan,

       

      Thanks a lot. As the system gives some warning that the accuracy of the data would be lost, I am now scared to use. How serious is that issue?

       

      BR

      • JoeUser2004's avatar
        JoeUser2004
        Bronze Contributor
        Nov 16, 2020

        Sudheer_Mambra  wrote:  ``the system gives some warning that the accuracy of the data would be lost [....] How serious is that issue?``

         

        Very serious, IMHO.  But note that the warning of permanent loss of precisions applies only to constants, not formulas.

         

        So if you have a formula like =100/3 that has unintended consequences after setting Precision As Displayed (PAD), you can reverse the misbehavior simply by deselecting PAD again.

         

        However, suppose you have an interest rate like 2.345678%, which you display as 2.35% for aesthetic reasons.  When you set PAD, the interest rate actually becomes 2.35%.  If (and when) you discover that many dependent calculations are affected adversely, so you deselect PAD again, the interest rate remains 2.35%.

         

        For that reason, it is imporant to heed the following warning:  Before setting PAD in an existing workbook, make a back-up copy of the Excel file.  If (and when) you discover untended consequences of setting PAD, you can revert to the back-up Excel file.

         

        That said, setting PAD is the only method for achieving your requirement, to wit:  ``not looking for a formula like round, [...] but a solution that I can apply to an entire sheet [sic]``.  (Setting PAD applies to the entire workbook, not selected worksheets.)

         

        IMHO, you should abandon that requirement.  The decision of what and when to round and to what precision should always be done on a case-by-case basis, IMHO.

         

         

Resources