Precision as displayed
Excel stores numeric data as " Double Precision Floating Point" which is a design requirement but can result inaccuracies in certain exceptional cases.
Consider you have the expression =103/7 in one cell and =99/6 in the other. Number that excel would be storing as the answer would be 14.7142857142857000...... for first cell and 16.500000000...... for the second cell.
Consider you have selected to display all amounts as number format with no decimal places. The amount would be appearing as 15 and 17 now if you add both cells in another cell, excel would give the answer as 31 which is correct mathematically but not if someone performs casting by simply adding the visible numbers i.e 15+17 = 32. This problem arises frequently while making financial statements where amounts are rounded to thousands or millions.
One solution to this problem is to round the source amounts down to zero decimal places using the round function. The other option is the use of "Precision as displayed" feature. To use it, simply go to Office button > Excel options > Advanced > When calculating this workbook > Set precision as displayed.
For Excel 2003 and earlier versions, go to Tool > Options > Calculation > workbook options > Precision as displayed.
However there is one problem i.e the changes made by this setting cannot be undone, so preparing a copy of the document may be considered.
Excel stores numeric data as " Double Precision Floating Point" which is a design requirement but can result inaccuracies in certain exceptional cases.
Consider you have the expression =103/7 in one cell and =99/6 in the other. Number that excel would be storing as the answer would be 14.7142857142857000...... for first cell and 16.500000000...... for the second cell.
Consider you have selected to display all amounts as number format with no decimal places. The amount would be appearing as 15 and 17 now if you add both cells in another cell, excel would give the answer as 31 which is correct mathematically but not if someone performs casting by simply adding the visible numbers i.e 15+17 = 32. This problem arises frequently while making financial statements where amounts are rounded to thousands or millions.
One solution to this problem is to round the source amounts down to zero decimal places using the round function. The other option is the use of "Precision as displayed" feature. To use it, simply go to Office button > Excel options > Advanced > When calculating this workbook > Set precision as displayed.
For Excel 2003 and earlier versions, go to Tool > Options > Calculation > workbook options > Precision as displayed.
No comments:
Post a Comment