850 * 77.1 is 65535... unless you're Microsoft Excel.

JonesJ wrote:

As a front office developer at an investment bank this scares the crap out of me.

MS fix this and fix it VERY quickly!!!

tribalcactus wrote:

There has already been a bug filed on this.  My guess is that it will be addressed with SP1.

I've been sent the Google groups link discussing this link twice today from two seperate sources at two seperate investment banks.

I'd suggest they fix it damn damn quick.

65535 is 2^16-1.

According to Excel 2007

Something stinks in the state of Washington (Redmond area).

It amusses me that you can do

A1 => 850*77.1    = 100000
A2 => A1 - 1      = 65534
A3 => A1 + 1      = 100001



EDIT: opps, see this is posted right at the top of the thread...

What I don't get is why it's base 10 "100000". It's clear that the problem is that the floating point multiplication has overflown the two-byte box that the number has, but it's bizarre to say the least that it chose to overflow to 100000. I could even understand it going to 0x100000 (overwrite on the buffer - potential hackable hazard).

Back in yonder days of old, when I was less lazy than I am now, I'd attack Excel with the stick of debug and give you an actual answer, but Excel is big and I am lazy, so I can't be bothered.

At this site an investigator performed a simple loop to calculate (65535/x)*x for x = 1, ..., 65535.  For over 10,000 values of x, it gave 100,000 rather than 65,535 as the answer.

If you have a stock whose value is fluctuating close to 77, keep watching the ticker to see when to sell 850 shares of it!

Wil wrote:

At this site an investigator performed a simple loop to calculate (65535/x)*x for x = 1, ..., 65535.  For over 10,000 values of x, it gave 100,000 rather than 65,535 as the answer.

If you have a stock whose value is fluctuating close to 77, keep watching the ticker to see when to sell 850 shares of it!

Sadly progrommatic access through excel shows 65535 when it autocasts to a float, double or int, and most of the big financial companies are more prudent than to upgrade their software if the old one works.

That being said, it's unacceptable since grandpa joe might be paying for a bottom-line that has been incorrectly computed by davy-smith and sons local accountants firm ltd.

For n = 1 to 1000, there are 137 "bugs":

n	65535/n	n*(65535/n)
13	5041.153846153850	100000
26	2520.576923076920	100000
49	1337.448979591840	100000
52	1260.288461538460	100000
81	809.074074074074	100000
87	753.275862068965	100000
93	704.677419354839	100000
98	668.724489795918	100000
104	630.144230769231	100000
107	612.476635514019	100000
115	569.869565217391	100000
117	560.128205128205	100000
119	550.714285714286	100000
123	532.804878048780	100000
162	404.537037037037	100000
169	387.781065088757	100000
173	378.815028901734	100000
174	376.637931034483	100000
186	352.338709677419	100000
196	334.362244897959	100000
208	315.072115384615	100000
214	306.238317757009	100000
230	284.934782608696	100000
233	281.266094420601	100000
234	280.064102564103	100000
235	278.872340425532	100000
238	275.357142857143	100000
243	269.691358024691	100000
246	266.402439024390	100000
249	263.192771084337	100000
324	202.268518518519	100000
338	193.890532544379	100000
339	193.318584070796	100000
346	189.407514450867	100000
348	188.318965517241	100000
349	187.779369627507	100000
372	176.169354838710	100000
379	172.915567282322	100000
385	170.220779220779	100000
392	167.181122448980	100000
393	166.755725190840	100000
395	165.911392405063	100000
415	157.915662650602	100000
416	157.536057692308	100000
421	155.665083135392	100000
425	154.200000000000	100000
428	153.119158878505	100000
439	149.282460136674	100000
457	143.402625820569	100000
460	142.467391304348	100000
466	140.633047210300	100000
468	140.032051282051	100000
470	139.436170212766	100000
476	137.678571428571	100000
486	134.845679012346	100000
489	134.018404907975	100000
492	133.201219512195	100000
498	131.596385542169	100000
499	131.332665330661	100000
574	114.172473867596	100000
578	113.382352941176	100000
587	111.643952299830	100000
597	109.773869346734	100000
610	107.434426229508	100000
617	106.215559157212	100000
621	105.531400966184	100000
642	102.079439252336	100000
648	101.134259259259	100000
653	100.359877488515	100000
663	98.846153846154	100000
671	97.667660208644	100000
676	96.945266272189	100000
678	96.659292035398	100000
685	95.671532846715	100000
687	95.393013100437	100000
689	95.116110304790	100000
692	94.703757225434	100000
693	94.567099567100	100000
696	94.159482758621	100000
698	93.889684813754	100000
711	92.172995780591	100000
721	90.894590846047	100000
723	90.643153526971	100000
727	90.144429160935	100000
729	89.897119341564	100000
731	89.651162790698	100000
744	88.084677419355	100000
751	87.263648468708	100000
758	86.457783641161	100000
761	86.116951379764	100000
770	85.110389610390	100000
783	83.697318007663	100000
784	83.590561224490	100000
786	83.377862595420	100000
789	83.060836501901	100000
790	82.955696202532	100000
797	82.227101631117	100000
807	81.208178438662	100000
830	78.957831325301	100000
832	78.768028846154	100000
837	78.297491039427	100000
841	77.925089179548	100000
842	77.832541567696	100000
845	77.556213017752	100000
850	77.100000000000	100000
856	76.559579439252	100000
865	75.763005780347	100000
878	74.641230068337	100000
883	74.218573046433	100000
885	74.050847457627	100000
887	73.883878241263	100000
889	73.717660292463	100000
897	73.060200668896	100000
899	72.897664071190	100000
901	72.735849056604	100000
914	71.701312910285	100000
915	71.622950819672	100000
920	71.233695652174	100000
921	71.156351791531	100000
932	70.316523605150	100000
936	70.016025641026	100000
939	69.792332268371	100000
940	69.718085106383	100000
952	68.839285714286	100000
955	68.623036649215	100000
959	68.336809176225	100000
967	67.771458117890	100000
971	67.492276004120	100000
972	67.422839506173	100000
973	67.353545734841	100000
977	67.077789150461	100000
978	67.009202453988	100000
979	66.940755873340	100000
984	66.600609756098	100000
996	65.798192771084	100000
997	65.732196589769	100000
998	65.666332665331	100000

I have heard a rumor this is an bug resurfaced again.  As in previous versions of Excel throughout the ages have had this problem and once again it appears to be a valid bug?  Can anyone back this up?h

j0217995 wrote:



I have heard a rumor this is an bug resurfaced again.  As in previous versions of Excel throughout the ages have had this problem and once again it appears to be a valid bug?  Can anyone back this up?h

Does not happen in Office 2003.

Official Excel Blog comes with an explanation and promise of an upcoming fix:

Official Excel Blog Thingie wrote:

So what, specifically, are the values that cause this display problem?  Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem.  You can’t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell.  All other calculation results are not affected.

Yggdrasil wrote:

Official Excel Blog comes with an explanation and promise of an upcoming fix:

Official Excel Blog Thingie wrote: So what, specifically, are the values that cause this display problem?  Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem.  You can’t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell.  All other calculation results are not affected.

1 is between 0.99999999995 and 1
2 is between 1.99999999995 and 2
3 is between 2.99999999995 and 3
etc...

And he is wrong about "only a display bug"...

BlackTiger wrote:


And he is wrong about "only a display bug"...

I think it means that it is post-processing where they decide how to display the value that is causing the problem, not the calculation of the actual value - I could be (and probably am) wrong.  I don't however think that saying "This'll only happen with a few thousand number" really mitigates the situation.

I'm taking bets on how long before-
1. A lawsuit
2. An update

Hmmm... Interesting...

A1: =850*77.1
A2: =A1+1
A3: =A2+1

Result in A3 = 65537

A1: =850*77.1
A2: =ROUND(A1,1)+1
A3: =A2+1

Result in A3 = 100002

A1: =850*77.1
A2: =A1+1
A3: =A2*2

Result in A3 = 131072


Crazy stuff...

Rossj wrote:



BlackTiger wrote:  And he is wrong about "only a display bug"...

I think it means that it is post-processing where they decide how to display the value that is causing the problem, not the calculation of the actual value - I could be (and probably am) wrong.  I don't however think that saying "This'll only happen with a few thousand number" really mitigates the situation.

I'm taking bets on how long before-
1. A lawsuit
2. An update

Do Northern Rock use Office 2007?

LOL

Are you sure it's not some puzzling with , (comma) and . (dot)?

BlackTiger wrote:


And he is wrong about "only a display bug"...

Errr.. he has access to the code and debugger. I'm gonna go with his version of events, rather than your "superior knowledge" on the subject.

evildictaitor wrote:



BlackTiger wrote:  And he is wrong about "only a display bug"...

Errr.. he has access to the code and debugger. I'm gonna go with his version of events, rather than your "superior knowledge" on the subject.

It's actually provable that it's not just a display bug:

A1: =850*77.1
A2: =ROUND(A1,1)+1
A3: =A2+1

Result in A3 = 100002

Minh wrote:


It's actually provable that it's not just a display bug:

A1: =850*77.1
A2: =ROUND(A1,1)+1
A3: =A2+1

Result in A3 = 100002

Since that only seems to happen when rounding, it suggests there is another subtle bug in ROUND that only surfaces when the displayed value doesn't match the stored value.