Nov 13 2020 04:32 AM
Nov 13 2020 04:32 AM
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:
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.
Nov 15 2020 01:09 PM
How serious it depends on concrete data.
For example, for $100/3 result will be $33.33 (instead of $33.33333333333...) if you set precision on 2 digits. Multiplying back $33.33 x 3 = $99.99. Other words you lost one cent from $100 on such simple operations.
Nov 15 2020 04:34 PM
@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.