Баг в Excel 2007

[veey]

Перемножте 77.1 и 850. В результате получится 100000 вместо ожидаемых 65535. Баг не зависит от способа умножения (перемножение отдельных клеток или формула целиком в одной клетке) и касается не только этой пары чисел.
Получается, что использование Excel в серьезных расчетах (например, в финансовых), может быть просто опасным.

[ulitka]

Re: Баг в Excel 2007

hmm... this is what I get...

[veey]

Ответ: Баг в Excel 2007

Tak это какая версия?

[ulitka]

Re: Ответ: Баг в Excel 2007

Tak это какая версия?

Виноват - это другая версия...

[Вовакрд]

Ответ: Баг в Excel 2007

Excel 2000 v 9.0._ _ _

[ulitka]

Re: Баг в Excel 2007

сегодня на работе у начальника нашелся EXCEL 2007 который шел в комплекте с его sony vaio, так действительно 77,1 если умножить на 850 получается 100000 вместо ожидаемых 65535.
Сначала он не верил, говорил, что такого не может быть, ведь это серьезный продукт... Я ушел с работы, а он все еще сидел и высчитывал разные вариации, очевидно пытаясь понять, в чем же дело и откуда такой бред. Похоже, что EXCEL использует свои внутренние арифметические инструкции, не системные. Лично я склоняюсь к мысли, что это просто очередное "яйцо". Одноко, в чем бы ни была причина - случай, конечно, вопиющий. Скажем, у меня бухгалтерия на этих таблицах, и было продано за месяц 850 HDD за 77 долларов и 10 каждый.... Дурдом, короче.

[veey]

Ответ: Баг в Excel 2007

Это не "яйцо". Это яйца.

[Aldia]

Ответ: Баг в Excel 2007

А что вы возмущаетесь?
Сообщите об обнаруженном непорядочке и потребуйте компенсации, ну вроде, "бухгалтерия у меня пострадала от ваших недоделок"

Интересно, кроме veey кто-нибудь обнаружил это дело?

[ulitka]

Re: Баг в Excel 2007

А вот что в мире говорят об этом:

What's 77.1 x 850? Don't ask Excel 2007 | The Register
A Microsoft manager has confirmed the existence of a serious bug that could give programmers and number crunchers a failing grade when relying on the latest version of Excel to do basic arithmetic.

The flaw presents itself when multiplying two numbers whose product equals 65,535. Fire up your favorite calculator and multiply 850 by 77.1. Through the magic of zeros and ones, you'll quickly get an answer of 65,535. Those using the Excel 2007, however, will be told the total is 100,000. The program similarly fails when multiplying 11 other sets of numbers, including 5.1*12850, 10.2*6425 and 20.4*3212.5, according to this blog post from Microsoft manager David Gainer.

He stressed that the bug, which was introduced when Microsoft made changes to the Excel calculation logic, occurs only in the value Excel displays in a cell. The result stored in memory is correct. "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," Gainer wrote.

Of the 9.214*10^18 different floating point numbers that Excel 2007 can store, six of them are susceptible to the flaw.

We're still at a loss as to why the latest and greatest version of Excel would get tripped up on equations totaling 65,535. The number, of course, is the highest that can be represented by an unsigned 16 bit binary number, so people who muck around with computers encounter it on a daily basis. For example, Internet protocol supports 65,535 TCP and UDP ports.

Microsoft engineers are in the final phase of testing a fix. It should be available for download soon, Gainer said.



Microsoft has confirmed the existence of a serious calculation flaw in Excel 2007 that can cause an incorrect amount to appear in a cell. Gail Perry and John Stokdyk report.

Molham Serry highlighted the problem on Monday afternoon in the microsoft.public.excel group on Google when he suggested Excel 2007 users try entering the multiplication formula =77.1*850.

Excel displays 100000 instead of the correct answer 65535. Other formulae - but not all that produce the same result - have the same effect. Entering =13107*5, for example, produces the correct result of 65535.

Programmers at Microsoft were informed of the problem on Tuesday and are working on a fix. No date for a correcting update has been projected yet, but it will be made available via Microsoft Update.

"Hooray!" quipped Chris Dillaborough on the Microsoft Excel team blog, "I thought I'd spent my Google AdWords budget for the month, but I've actually got $34,465 left!"

Excel team leader David Gainer explained that although the spreadsheet displays 100000, the value of the cell is correct at 65535. So if you use the cell in another formula (for example, if the mistakenly displayed presentation of 100000 appears in cell A1 and you enter the formula =A1*2 in another cell, you will see the correct result of 131070.

According to Gainer, the flaw affects six different floating point numbers between 65534.99999999995 and 65535, and six between 65535.99999999995 and 65536 that cause this problem.

"Excel actually calculates the correct answer, and you can see that if you use VBA to check the value for A1 - it will be 65535. But in the function that takes that value and formats it to be displayed on the screen, for the values described above, there is a bug. Any calculations based off that cell will be accurate too," Gainer wrote.

Respondents to Gainer queried his explanation and pointed out that rounding the affected cell or including an addition in a formula referencing it would propagate the erroneous 100,000 figure.

Respondents to Gainer queried his explanation and pointed out that rounding the affected cell o would propagate the erroneous 100,000 figure. This effect was confirmed by ExcelZone contributor Simon Hurst (see below), who also found that the error was not uniform across all functions. If the result is used in a SUM function, for example, it returns the correct underlying value.

[ulitka]

Re: Explaining the Excel Bug

а это статья с одного из моих любимых техноблогов, ее автор работал как-то в EXCEL team и знает проблему изнутри:

Explaining the Excel Bug


This item ran on the Joel on Software homepage on Wednesday, September 26, 2007

By now you've probably seen a lot of the brouhaha over a bug in the newest version of Excel, 2007. Basically, multiplying 77.1*850, which should give you 65,535, was actually displaying 100,000.

Before I try to explain this, I should disclose that I did work on the Excel team, but that was thirteen years ago. I haven't been there for a long time. I don't even think I know anyone on that team any more. I'm just trying to explain the bug a little bit as a public service.

The first thing you have to understand is that Excel keeps numbers, internally, in a binary format, but displays them as strings. For example, when you type 77.1, Excel stores this internally using 64 bits:

0100 0000 0101 0011 0100 0110 0110 0110
0110 0110 0110 0110 0110 0110 0110 0110

The display is showing you four characters: "7", "7", ".", and "1".

Somewhere inside Excel is a function that converts binary numbers to strings for displaying. This is the code that has the bug that causes a few numbers which are extremely close to 65,535 to be formatted incorrectly as 100,000.

If you use the number further along in calculations, for example, if you add 2 to the results, you'll get the right thing.

=77.1*850 -> displays 100000

=77.1*850+2 -> displays 65537, correctly.

Just to throw people off, this bug also exists for a few numbers which are extremely close to 65,536. They display incorrectly as 100,001.

=77.1*850+1 -> displays 100,001, incorrectly.

This is still only a bug in the number formatting code; if you try to make a chart with that number in it, you'll get a correct chart.

Now... you may have noticed that I said that this bug exists for numbers which are extremely close to 65,535, but not for 65,535 itself. Indeed if you enter 65,535 you see 65,535. But, you notice, 77.1 * 850 should be exactly 65,535, not extremely close to 65,535!

Look closely at the binary representation for 77.1:

0100 0000 0101 0011 0100 0110 0110 0110
0110 0110 0110 0110 0110 0110 0110 0110

See how there's a lot of 0110 0110 0110 there at the end? That's because 0.1 has no exact representation in binary... it's a repeating binary number. It's sort of like how 1/3 has no representation in decimal. 1/3 is 0.33333333 and you have to keep writing 3's forever. If you lose patience, you get something inexact.

So you can imagine how, in decimal, if you tried to do 3*1/3, and you didn't have time to write 3's forever, the result you would get would be 0.99999999, not 1, and people would get angry with you for being wrong.

The same thing happens in binary with numbers ending in 0.1: they are repeating decimals, so when you do mathematical operations on them, very small insignificant errors creep in somewhere way to the right of the decimal point. (PS: same for .2, .3, .4, .6, .7, .8, and .9, but not .5).

The IEEE has a standard, IEEE 754, for how to represent floating point numbers in binary, and this is what almost everybody uses, including Excel, and they have for a really long time, and it means sometimes you get imprecise results when you add a lot of 0.1's together, but if you're rounding the numbers to a reasonable number of decimal points, you won't really care.

Back to the Excel bug, which is a genuine bug, not just an artifact of this IEEE 754 stuff. Since 77.1 has no exact representation, Excel stores it as

0100 0000 0101 0011 0100 0110 0110 0110
0110 0110 0110 0110 0110 0110 0110 0110

and then when you try to multiply it by 850, you get something very close to 65,535, but not exactly 65,535, because of the fact that 77.1 wasn't stored exactly because that would take infinite memory. And this number, which is very close to 65,535, happens to be one of only 12 possible floating point numbers which trigger this bug in Excel.

OK, Q&A.

Q: Isn't this really, really bad?

A: IMHO, no, the chance that you would see this in real life calculations is microscopic. Better worry about getting hit by a meterorite. Microsoft, of course, will be forced to tell everyone "accuracy is extremely important to us" and I'm sure they'll have a fix in a matter of days, and they'll be subjected to all kinds of well-deserved ridicule, but since I don't work there I'm free to tell you that the chance of this bug actually mattering to you as an individual is breathtakingly small.

Q: Shouldn't they be testing for these kinds of things?

A: I'll bet that most of the numeric testing done on the Excel team is done automatically with VBA code. Cells containing this value display as 100,000, but from VBA, they're going to look like 65,535 (since the number would be passed into the Basic runtime in binary, before the display formatting.) I'm sure there's plenty of code to test display formatting, but with a bug like this that only happens on 12 out of 18446744073709551616 possible floating point binary numbers, it's unlikely that any set of black-box tests would cover this case.

Q: What caused the bug?

A: I'm not sure exactly, since I don't have the code. Off the top of my head, I can't think of anything that would cause this behavior. Play around with Quanfei Wen's IEEE-754 calculator, maybe you'll find something.

Q: Why not use "exact" (decimal) arithmetic?

A: It's much slower than floating point arithmetic, since there's no hardware on your CPU chip to do it for you natively.

Over the years, Microsoft got so much heat for floating point rounding artifacts in the Windows Calculator that they rewrote it to use an arbitrary-precision arithmetic library. Since you have to poke at Windows Calculator with a stick, it doesn't have to be as fast as Excel. That said, CPUs have gotten pretty fast. I'll bet an arbitrary-precision version of Excel would perform pretty well these days. Still, the Microsoft Excel support team has spent the last 20 years defending IEEE 754, and it's not surprising that they've started to believe in it.

And let's face it -- do you really want the bright sparks who work there now, and manage to break lots of perfectly good working code -- rewriting the core calculating engine in Excel? Better keep them busy adding and removing dancing paper clips all day long.