Excel maintains 15 digits of internal precision; however, we may choose any format of our choice to display the number in the worksheet. Suppose, all the cells in my worksheet are formatted with “comma style”; i.e., it will display only two digits after decimal. Now, if we calculate 10% of 1,144.44 we get 114.44, further multiplying it by 2, excel will show result as 228.89. Why so? Whereas it should be 228.88, is something wrong, let’s find out.
As I mentioned in very beginning Excel maintains 15 digits of internal precision; hence, when we calculated 10% of 1,144.44, Excel internally records it as 114.444. Again when we multiply it by 2 It becomes 228.888 but as we have selected “comma style” it displays it as 228.89.
One solution to this problem is select “Set precision as displayed” option. For this follow these steps:
- Go to file and choose options
- Select advanced from the options available
- Go to “When calculating this workbook”
- Check the option – “Set precision as displayed”
- Click “OK”
When we select this option Excel does the calculation using the numbers as displayed in cells; hence, in the above example your value will be changed to 228.88.
CAUTION: When we choose the “Set precision as displayed” option Excel deletes the extra digits. While choosing this option Excel will give you a warning message “Data will permanently lose accuracy”.
Instead of choosing above option we may select one of Excel’s built-in function based on our requirement as shown below:
In case you have any query regarding above, you may send the same as comment to this blog.
Health is the greatest gift, contentment the greatest wealth, faithfulness the best relationship.
By Rajesh K Saraogi
Rajesh is a hardcore finance person with a penchant for latest technology. His passions is to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!