I wonder if Microsoft is going to do anything about the =850*77,1 being
equal 100000 instead of 65535 error in Excel 2007. Any news of patch,
anyone?

Mantvydas

They are certainly aware, I would expect something today or tomorrow

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(E-Mail Removed)DTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"Mantvydas" <mantvydas at gmail dot com> wrote in message
news:CED5E24B-3831-4B6D-BC67-(E-Mail Removed)...
>I wonder if Microsoft is going to do anything about the =850*77,1 being
>equal 100000 instead of 65535 error in Excel 2007. Any news of patch,
>anyone?
>
> Mantvydas

"Nick Hodge" <nick_ho...@zen.co.uk> wrote...
>They are certainly aware, . . .

No doubt, but will they publicly acknowledge this bug, er, feature?

>I would expect something today or tomorrow

Optimist.

Not if it's deeply buried in the formula parser's code. If this is
flawed bit bashing, it may not be the easiest thing to FIND much less
fix. OTOH, if this is due to some flaw in the interface between the
formula parser and how parsed values are stored in memory, it could
take even longer.

Regardless, it seems clear Excel doesn't just parse formula values,
dispatch them to the FPU, perform arithmetic in the FPU, and store
double precision results in memory. At the very least it seems they
could use a remedial seminar on the KISS principle.

"Harlan Grove" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Optimist.
>
> Not if it's deeply buried in the formula parser's code. If this is
> flawed bit bashing, it may not be the easiest thing to FIND much less
> fix. OTOH, if this is due to some flaw in the interface between the
> formula parser and how parsed values are stored in memory, it could
> take even longer.
>
> Regardless, it seems clear Excel doesn't just parse formula values,
> dispatch them to the FPU, perform arithmetic in the FPU, and store
> double precision results in memory. At the very least it seems they
> could use a remedial seminar on the KISS principle.

Actually the bug appears to show this is exactly what excel is doing. It's
just having trouble converting the double result into text.

Michael

"Michael C" <(E-Mail Removed)> wrote...
>"Harlan Grove" <(E-Mail Removed)> wrote in message
....
>>Regardless, it seems clear Excel doesn't just parse formula values,
>>dispatch them to the FPU, perform arithmetic in the FPU, and store
>>double precision results in memory. At the very least it seems they
>>could use a remedial seminar on the KISS principle.
>
>Actually the bug appears to show this is exactly what excel is doing. It's
>just having trouble converting the double result into text.

If so, then it'd only be the cell's .Text property that was screwed up, and
all calculations using these false-appearing 100000s would use the cell's
actual value, 65535. However, that appears not to be the case since, as
others have reported, with A1 containing a problem formula returning a
false-appearing 100000, the formulas =A1-1 and =A1+1 use DIFFERENT values
for A1, and I believe others have reported that SUM and AVERAGE functions
treat such A1 values differently. That lack of consistency in downstream
calculations is a very strong indication that this is more than just a
rendering problem.

But let's assume it were just rendering. Why is 65535 a special case? As
others have shown, 65534 and 65536 are occasionally displayed correctly, as
are noninteger values between those two integers. How about another test to
see if this were only a rendering issue: try the formula

=(850*77.1)

Also, does changing the number format change the value displayed?

"Harlan Grove" <(E-Mail Removed)> wrote in message
news:e%23XNAD%23$(E-Mail Removed)...
> If so, then it'd only be the cell's .Text property that was screwed up,

It is. The cells Value and Value2 properties report the correct result,
always.

> and all calculations using these false-appearing 100000s would use the
> cell's actual value, 65535. However, that appears not to be the case
> since, as others have reported, with A1 containing a problem formula
> returning a false-appearing 100000, the formulas =A1-1 and =A1+1 use
> DIFFERENT values for A1, and I believe others have reported that SUM and
> AVERAGE functions treat such A1 values differently. That lack of
> consistency in downstream calculations is a very strong indication that
> this is more than just a rendering problem.

This is quite interesting and I'm not sure I fully understand what is going
on but let me explain what I think is happening. In EVERY situation the
actual value in the cell is correct. And in EVERY function I have tested
(only the ones you mentioned here, including A1+1 and A1-1) the correct
value is used (ie 65535 is used, not 100,000). The reason some functions
show the same problem is that they have the same bug converting the value to
text. Here's my explanation for each:

MAX:
This uses the value of the cell correctly and calculates the correct result.
It just displays the result wrong. This is no suprise as the result of
Max(A1) is going to be 65535 and hence the same bug will appear. If you get
the Max of 65535 (which is showing 100,000) and 70,000 then the result is
70,0000. if it was using the text of A1 then the result would be 100,000.

SUM and Average:
Same applies here as applies to max. Sum(A1) will result in 65535 which will
display as 100,000. Same for average. If you Sum 65535 (shown as 100,000)
and 65 then you get 65600, not 100,100.

A1-1:
This uses the value of A1 and sutracts 1 correctly

A1+1:
This is the odd one in that it makes it look like excel is using a different
method to add 1 than it is to subtract, but that is not the case. If you do
A1+2 then you get the correct result of 65537. The reason A1+1 shows 100,001
is just that it happens to have hit on the same bug. The sequence of events
would be 1) Get value from A1, which is 65535 2) Add 1 to that value to get
65536 3) Convert that value to text to display on screen. At that point the
same bug would be encountered.

> But let's assume it were just rendering. Why is 65535 a special case?

It's just a bug. Why is impossible to answer without looking at the source
code. Possibly they used a < sign when they should have used <=. It's likely
the bug is only an issue with a very small range of values, or possibly even
just one single value, eg It's possible that 65535.00000000001234 renders as
100,000 where 65535.00000000001235 does not.

> as are noninteger values between those two integers. How about another
> test to see if this were only a rendering issue: try the formula
>
> =(850*77.1)

This still gives the problem. Any formulae that returns the same result will
show the bug I believe.

> Also, does changing the number format change the value displayed?

No, it still displays incorrectly.

Michael

850*77 + 850*0,1 = 65535

There's at least one other thread (with 125 messages!) on this
subject.

Here's the word from the Microsoft Excel blog:

http://blogs.msdn.com/excel/archive/...ue-update.aspx

Mark Lincoln

On Sep 25, 4:06 pm, "Nick Hodge" <nick_ho...@zen.co.uk> wrote:
> They are certainly aware, I would expect something today or tomorrow
>
> --
>
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHIS...@zen.co.ukANDTHIS
> web:www.nickhodge.co.uk
> blog (non tech):www.nickhodge.co.uk/blog
>
> "Mantvydas" <mantvydas at gmail dot com> wrote in messagenews:CED5E24B-3831-4B6D-BC67-(E-Mail Removed)...
>
>
>
> >I wonder if Microsoft is going to do anything about the =850*77,1 being
> >equal 100000 instead of 65535 error in Excel 2007. Any news of patch,
> >anyone?
>
> > Mantvydas- Hide quoted text -
>
> - Show quoted text -

"Michael C" <(E-Mail Removed)> wrote in message
news:e47$TP$$(E-Mail Removed)...

> This is quite interesting and I'm not sure I fully understand what is
> going on but let me explain what I think is happening. In EVERY situation
> the actual value in the cell is correct. And in EVERY function I have
> tested (only the ones you mentioned here, including A1+1 and A1-1) the
> correct value is used (ie 65535 is used, not 100,000).

Interesting. I entered the calculation "850*77.1" in A1 and got the 100000
answer as expected. In cell C1 I entered the formula "=A1-1" and got the
correct answer, 65534. In cell E1 I entered the formula "=A1+1" and got the
unexpected answer, 100001!

"Gordon" <(E-Mail Removed)> wrote:

>"Michael C" <(E-Mail Removed)> wrote in message
>news:e47$TP$$(E-Mail Removed)...
>
>> This is quite interesting and I'm not sure I fully understand what is
>> going on but let me explain what I think is happening. In EVERY situation
>> the actual value in the cell is correct. And in EVERY function I have
>> tested (only the ones you mentioned here, including A1+1 and A1-1) the
>> correct value is used (ie 65535 is used, not 100,000).
>
>Interesting. I entered the calculation "850*77.1" in A1 and got the 100000
>answer as expected. In cell C1 I entered the formula "=A1-1" and got the
>correct answer, 65534. In cell E1 I entered the formula "=A1+1" and got the
>unexpected answer, 100001!
>

"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."

When you do 850*77.1, the error is in the first set of numbers.
Adding 1, you jump to the second set. Add .5, 2, or 1.1 and you
won't see the problem.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? - http://mvp.support.microsoft.com/