I do the go advanced, hit the paper clip, hit browse, click on my file, hit open, and nothing.



JOEu2004, I understand what you are saying, but cannot fix it. All my decimals are down to 2 places. I do however have a convoluted formula and it is to low by 50 plus cents on some og the answers. I wish I could post this here.. but it wont let me attach. It's only like 19 kb in size.

If by "down to 2 places", you mean that you are formatting to display 2 decimal places, that is not sufficient.

Formatting alone only affects how a value appears. It does not affect the actual cell value.

Moreover, as my example with 10.01 - 10 demonstrates, constants might have only 2 decimal places, but calculations with those constants might result in values with more decimal places because of the binary arithmetic anomalies that I alluded to before.

Finally, if results are significantly off (by 50+ cents, for example), the mistake could be in your calculations. Not really a mistake per se; but again, failing to round calculations based on your expectations.

For example, =12.45*10% in A1 is 1.245. That might display 1.25 if you format to 2 decimal places. So you might expect a subsequent calculation like =A1*10 to be 12.50, but in fact it is 12.45. And if you sum a bunch of calculations like my example in A1, the cumulative effect can be a significant "error" -- that is, a deviation from your expectations based on displayed values (appearances).

For all of these examples, the remedy is the same: explicitly round to the precision that you expect calculations to be accurate to. That means: use the ROUND function.

Did you even try it?


As I said, upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website, and post the public/share URL here.

One such website is box.net/files. After uploading the file, be sure to click on Share to get the public URL.

PS.... Arguably, that is not always easy to do. For example, if you use pivot tables, calculations are not rounded by default. I'm not a PT person. But I believe the only work-around is to create "calculated fields" (?) instead of the normal PT set-up.

-----

Another work-around that I do not usually recommend is: set the "Precision As Displayed" Advanced Option.

If you want to experiment with that, be sure to make a backup copy of the Excel file first.

PAD is very dangerous; setting it can cause immediate and irreversible changes to constants throughout the Excel file.

Moreover, setting PAD does not fix all rounding problems. It does not fix my earlier example, for instance: IF(10.01 - 10 = 0.01, TRUE).

But just trying it, then throwing away the Excel file with PAD set, might give you confidence in the need to explicitly round formulas and expressions selectively.