Formula Result - Strange!!

[tlc53]

Hi there,

I have a basic formula of F230 - F232 = F234

Pasting the values of each cell returns the following results;

+ F230 421,028.9
- F232 396,028.9
= F234 24,999.9999999999


Why on earth is it giving the result 24,999.999999999 instead of 25,000 ???

I've tried changing all the number formats but that's not doing it. Completely stumped!

 

[joeu2004]

Is there a way I can attach a spreadsheet so you can view it?

I described several "tools" for you to see what doesn't "seem" to be there.

But sure, I would be happy to look at the Excel file. Upload a file that duplicates the problem to a file-sharing website, and post the public/share URL in a response here. Test the download URL, being careful to log out of the file-sharing website first to ensure that there are no permission issues. I like to use box.net/files. Alternatively, you can send the file directly to me at joeu2004 "at" hotmail.com.

However, the purpose would only be to confirm the explanation of the problem that we probably already know. And you have the solution, which you said works for you, namely: explicit use of ROUND.

Nevertheless, I am happy to provide a more specific explanation, if you want to take the trouble of sharing the Excel file with me.

 

[pedro-egoli]

Out of interest if you format G235 as "general" would that fix your conditional formatting?

 

[joeu2004]

@tlc53.... In the Excel file that you sent to me, F228 appears to have the value 421,028.90, F230 contains the constant 396,028.90, F232 has the formula =F228-F230, and G232 has the formula =F232-25000, which results in about -5.82E-11 when formatted as Scientific.

Your problems begin in F228, which has the formula =F160+F173-F197-F212+F226. Despite appearances (due to limitations of Excel formatting), its value differs from 421,028.90 by about -5.82E-11.

At a minimum, it would be prudent to change F228 to:

=ROUND(F160+F173-F197-F212+F226, 2)

Although that is sufficient to correct your immediate problem, these infinitesmal arithmetic "errors" can arise elsewhere with a different set of numbers.

So it would be prudent to add similar explicit rounding to all cells that do arithmetic with non-integers. Specifically:

F173: =ROUND(SUM(F163:F172), 2)
F197: =ROUND(SUM(F176:F196), 2)
F199: =ROUND(F160+F173+F197, 2)
F212: =ROUND(SUM(F202:F211), 2)
F226: =ROUND(SUM(F216:F225), 2)
F232: =ROUND(F228-F230, 2)
G232: =ROUND(F232-25000, 2)

Caveat: Someone might note that you can avoid the explicit use of ROUND (in these instances) by setting an Advanced Option called "Precision as displayed". I deprecate the use of PAD because of the many problems it can cause. But if you insist on experimenting, be sure to save a copy of your Excel file first, because in general, setting PAD can change some constants irreversibly. Arguably, I don't see any such danger in the file that you sent to me. But better "save" than sorry. (wink)

-----

I think you might appreciate a drill-down on the original of the infinitesimal binary arithmetic "errors" in your example. Beware: It might get a little "deep".

As noted, F228 has the formula =F160+F173-F197-F212+F226. The problem is with F160+F173-F197. F160+F173 is 433,576.72, and F197 has the value 37,547.82. Those values are indeed "exact". But when we subtract them, F160+F173-F197 differs from 396,028.90 by about -5.82E-11.

The reason is: 433,576.72 and 37,547.82 cannot be represented exactly in binary. The exact decimal representation of their binary approximations is:

433576.72: 433576.719999999,97206032276153564453125
 37547.82:  37547.8199999999,997089616954326629638671875

I use period for the decimal point and comma to demarcate the first 15 significant digits. Excel formats only the first 15 significant digits, rounded.

When we subtract the two values and compare with the corresponding constant, the exact decimal representations are:

433576.72-37547.82: 396028.899999999,9650754034519195556640625
396028.90:          396028.900000000,023283064365386962890625

Note the difference from the constant 396,028.90.

Sometimes, subsequent arithmetic "corrects" such differences. But not in this case. Consequently, the exact decimal representation of the result in F228 is:

F160+F173-F197-F212+F226: 421028.899999999,9650754034519195556640625
421028.90:                421028.900000000,023283064365386962890625

Also, sometimes Excel tries to hide some infinitesimal differences. For example, =F228-ROUND(F228,2) results in exactly zero (0.00E+00). That might suggest (incorrectly) that the binary representations are the same.

But =F228-ROUND(F228,2)-0 displays the true arithmetic difference, about -5.82E-11 when formatted as Scientific.

Likewise, =F228-ROUND(F228,2)=0 returns FALSE, and =MATCH(ROUND(F228,2),F228,0) returns #N/A, indicating a difference in the binary representation.

 

[tlc53]

Wow, that is very interesting! Thank you for taking the time to explain it to me in such detail.
I don't think I'll attempt to alter PAD and will apply the ROUND function instead, as you suggest.

Thanks again!