Excel is creating a decimal in 9th digit when all inputs are only two decimals (see description)..how can i fix without round()?

[otherstuff]

Below is the data in question, I have expanded the decimals to show it's not the result of our inputs. The third column is a formula that adds the second and subtracts the first from the preceding balance (a rolling bank balance). The row in Bold shows where Excel suddenly creates a value that has more than two decimal places. Why does this happen when all of the inputs are no longer than two decimals? Why does $3,102,035.90 - $2,879,595.16 = $222,440.739999999, and not $222,440.74? As you can see from the preceding numbers there are no other decimals beyond the second digit until the formula.

I would like a solution that does not involve rounding every formula or having to use another function. This happens in many spots throughout our workbook but we aren't able to identify an issue with our inputs. Is there a fix? THANKS!

-	186,597.04000000000000000000	3,041,589.47000000000000000000
10,200.00000000000000000000	-	3,031,389.47000000000000000000
220,037.00000000000000000000	-	2,811,352.47000000000000000000
12,500.00000000000000000000	-	2,798,852.47000000000000000000
-	312,983.43000000000000000000	3,111,835.90000000000000000000
9,800.00000000000000000000	-	3,102,035.90000000000000000000
2,879,595.16000000000000000000	-	222,440.73999999900000000000
6,250.00000000000000000000	-	216,190.73999999900000000000
-	0.29000000000000000000	216,191.02999999900000000000
-	285.46000000000000000000	216,476.48999999900000000000
-	140,178.35000000000000000000	356,654.83999999900000000000

<tbody>
</tbody>

 

[Jonmo1]

It's related to how decimal values cannot always be precicely stored in a Binary system.
https://support.microsoft.com/en-us/kb/78113#/en-us/kb/78113

No real solution I'm aware of, just workarounds using Round or other functions.

 

[ranman256]

multiply by 100, truncate, divide by 100.

 

[joeu2004]

Why does $3,102,035.90 - $2,879,595.16 = $222,440.739999999, and not $222,440.74?
[....]
I would like a solution that does not involve rounding every formula or having to use another function.

Actually, I cannot duplicate your problem, even when I calculate the rightmost column for the entire example.

I suspect the problem arises in an earlier calculation, and you cannot see it because Excel formats only up to the first 15 significant digits, an arbitrary limitation.

In any case, such anomalies are certainly possible. And IMHO, the best solution is indeed to explicitly round every calculation that you expect to be accurate to a specific number of decimal places.

There is an alternative that obviates the need for most explicit rounding. However, I strongly discourage you from using it.

If you choose to experiment, be sure to make a copy of the Excel file first. Doing the following might change some constants in your workbook irreversibly.

You might set the option "Precision as displayed" (PAD), and be sure that all cells are explicitly formatted with 2 decimal places (or whatever you want). Do not rely on the General format, even if displays only 2 decimal places.

Some of the problems with PAD:

1. It affects the entire workbook. You cannot pick-and-choose, as you can with cell formats.

2. It only rounds the final cell value to the formatted number of decimal places. So, for example, setting PAD does not correct the following problem: IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!). We still must write IF(ROUND(10.1 - 10, 2) = 0.1, TRUE).

3. Setting PAD changes constants permanently; and that can change all dependent formulas. For example, suppose you have the interest rate 4.390625%, which you choose to format as 4.39%. When you set PAD, the interest rate is changed to 4.390000%. Even if you unset PAD later, the interest rate continues to be 4.390000%.