I need some opinions on precision of calculation

[Dan Wilson]

Good day. I am using Excel 2003 on Win XP Pro. This issue may take some time to detail, so please read on.

Over the last five years I have labored to create workbooks that will track, resolve and report the activities of the cash register in my business. In that time I have encountered many problems regarding the issue of floating point arithmetic. I have resolved most of the issues by using the ROUND function and lately, have been using FORMAT to show negative numbers in RED.

In perusing through the OPTIONS, I stumbled across a flag called Precision as Displayed. This looks like it might help my issue a LOT!

All of the calculations that I use in my workbooks all relate to money, thus no decimal calculation beyond 2 points is needed.

Please reply with your opinions about the pros and cons of setting the Precison as Displayed flag. My workbooks are reaching the point of being extremely large due to the number of calculations being done on the data.

Thanks in advance, Danno...

 

[xapie128]

Precision as Displayed will work if you format all cells to .00, otherwise it will still use 15 decimal point precision even if you only enter two digits after the decimal point.

 

[Dan Wilson]

Precision display

Good day xapie. Thank you for the quick response. I was hoping for a little more detail. Some of the columns on my worksheet are formatted to Number with 0 decimal points. Will this be affected? If I set the flag in the Options, will all of my worksheets be affected or just the one that is open? I have other workbooks that rely on 3 decimal points for accuracy.

Thanks, Danno...

 

[xapie128]

0 (zero) will work too.
The help for Excel says of that setting: "Permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed."