Nov 13 2020 04:32 AM
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.
Nov 13 2020 04:55 AM - edited Nov 13 2020 07:09 AM
We can apply a format to the entire worksheet so only the 3 decimals display in numbers,
Nov 13 2020 05:29 AM
Thanks a lot@Craig_Hatmaker .
I am actually looking for a solution for permanently deleting the decimal places.
Nov 13 2020 07:00 AM
You may Set rounding precision , with that every number will be automatically rounded to number of digits as in applied format.
Nov 15 2020 05:56 AM
Hello @Sergei Baklan,
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
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.
Nov 16 2020 09:40 AM
Everything depends on goals. Workbook to multiply Qty*Price and sum result will work fine with precision as displayed, decision to use it is made for this concrete workbook. Why not.