Entries:
Posts:

Something went wrong getting user information from Channel 9

Latest Achievement:

Something went wrong getting user information from MSDN

Visual Studio Achievements

Latest Achievement:

Something went wrong getting the Visual Studio Achievements

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

• Oops, something didn't work.

Getting "follow" information
• 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

85000*.771 = 65535
8500*7.71 = 65535
850*77.1 = 100000
85*771 = 65535
8.5*7710 = 65535
(850*7.71)*10 = 65535
850*(7.71*10) = 100000

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.