If you are an Excel 2007 user, try this:
Type =77.1*850 in any cell and press Enter. Excel 2007 would return a value of 100,000 instead of 65,535.
Any combination of numbers that should calculate to 65535 will show 100000 in Excel 2007.
Excel Team says the issue is actually not in the calculation itself (the result of the calculation stored in Excel’s memory is correct), but only in the result that is shown in the sheet. Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).
I am not sure if that's correct because when I type =B1+1 in the cell C1, I get 100001 and not 65536 as per the official explanation.
Anyway, David Gainer confirmed that this bug was introduced in the Excel 2007 version and a fix for the calculation issue would be available on Microsoft Download Center very soon. It's already under testing.
Reader Comments
Interesting find :)
That said I disagree with the statement "Any combination of numbers that should calculate to 65535 will show 100000 in Excel 2007". Correct, 77.1*850 is shown as 100000 - but 771*85 is shown as 65535 in my Excel 2007; also 13107*5 is shown correctly as 65535.
I guess the challenge here is to find the logic behind the incorrect calculation
Cheers,
H Jaeger
Written on 26/9/07 9:37 PM
For the example you use adding one will keep it in problematic range. Try adding two to result and you will see correct result
Written on 27/9/07 2:39 PM
This is a class B subnet.This number is also the max number of ports and the old max number of rows allowed in excel.
Its 2^16 aka 16 bit dont you love how Microsoft still uses OOOOLLLLDDD code
0 - 65535 = 65536 numbers
Written on 12/10/07 2:41 AM