Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account


Photo

EXCEL 2007 CALCULATION ERROR

- - - - -

  • Please log in to reply
8 replies to this topic

#1
Thauzar

Thauzar

    4th Star Learner

  • Member
  • PipPipPip
  • 415 posts
In windows, if you open calculator and multiply 850*77,1=65535
In Excel 2007, if you multiply 850*77,1=62065,5

some people, in a legit Excel 2007 Enterprise edition, get a result of 100000!!!

What's up with that? Can anyone here confirm this? How can we make sure Excel is calculating right?

Btw my version is up-to-date, I can't tell for the enterprise one but still it's both answers are far from the right answer.

:wacko: :blink:
5. Images in signatures shall NOT exceed a total of 300x100 and 80kb. -- Tarun


How to remove advertisement from MSFN

#2
tecknomage

tecknomage

    Newbie

  • Member
  • 14 posts
  • OS:XP Pro x86
  • Country: Country Flag

In windows, if you open calculator and multiply 850*77,1=65535
In Excel 2007, if you multiply 850*77,1=62065,5

some people, in a legit Excel 2007 Enterprise edition, get a result of 100000!!!

What's up with that? Can anyone here confirm this? How can we make sure Excel is calculating right?

Btw my version is up-to-date, I can't tell for the enterprise one but still it's both answers are far from the right answer.

:wacko: :blink:


Please clearify your calculation. What do you mean by "1=65535" or "1=62065,5" :huh:

For me, in both Calculator & Excel: 850*77 = 65450
Tecknomage
Computer System Specialist
IT Technician

#3
Thauzar

Thauzar

    4th Star Learner

  • Member
  • PipPipPip
  • 415 posts
it reads 77,1 it's in decimal... 77,1
5. Images in signatures shall NOT exceed a total of 300x100 and 80kb. -- Tarun

#4
jaclaz

jaclaz

    The Finder

  • Developer
  • 14,197 posts
  • OS:none specified
  • Country: Country Flag
Cannot say about Excel 2007, but something similar is common in every release of spreadsheet apps.

It depends on the separator you use, in US and Britain are in use the comma as separator for thousands and the period or full stop to separate decimal numbers.
In Europe generally it is the other way round.
Be aware that there TWO separate settings for this, usually spreadsheet apps use the default "International" settings, but some have also an "internal" setting.

Instead of putting "=850*77,1" in a cell, try the following:
Input in cells A1 and A2 "850" (without quotes)
Input in cell B1 "77.1"
Input in cell B2 "77,1"
Input in cell C1 "=A1*B1"
Input in cell C2 "=A2*B2"

Now copy the six cells a couple of rows below, and set cell formats "Number" to "General", you should see why it happens. ;)

To check results, when in doubt, always enter integers, like:
=850*771/10

This has nothing to do with Excel (or any other spreadsheet for that matter) with any versions: you are simply giving to it data in a way that the app parses as being NOT the number you think you typed.

jaclaz

#5
cluberti

cluberti

    Gustatus similis pullus

  • Supervisor
  • 11,252 posts
  • OS:Windows 8.1 x64
  • Country: Country Flag
Yes, this is a documented issue and a fix is in the works. Nothing more I know about it than that, although if you multiply the results of the initial calculation by 2, you'll get the desired number, so the calculation itself is fine, but the display logic is flawed.
MCTS Windows Internals, MCITP Server 2008 EA, MCTS MDT/BDD, MCSE/MCSA Server 2003, Server 2012, Windows 8
--------------------
Please read the rules before posting!
Please consider donating to MSFN to keep it up and running!

#6
jaclaz

jaclaz

    The Finder

  • Developer
  • 14,197 posts
  • OS:none specified
  • Country: Country Flag
I take back my previous post (it is correct but not related to the issue at hand). :blushing:

It appears to be a real BUG specific to Excel 2007:
http://bink.nu/news/...lation-bug.aspx
http://www.accountin...e...1023&f=1026

jaclaz

P.S.:
judging from some of the reports here:
http://blogs.msdn.co...sue-update.aspx

# Any calculations based off that cell will be accurate too? Is that so?
Wednesday, September 26, 2007 2:59 AM by cmart02

David,

You said "Any calculations based off that cell will be accurate too. Hope that helps."

But that's not entirely correct. At least not from what I have seen. If you happen to be rounding your calculations (=ROUND(850*77.1,2)*2), it also rounds it to 100K making that permanent.

So, while it is true that most cases Excel treats the value as correct except for the visual side of it, in others it actually DOES treat it as 100K.

Rob


# re: Calculation Issue Update
Thursday, September 27, 2007 11:35 PM by wlandrum

I think that I have found a second related bug that is in both the 2003 version of Excel and the 2007 version. Namely:

If you key in the following:

=DEC2HEX(2^16), it correctly gives 10000 (hex)

=DEC2HEX(2^16-1), it correctly gives FFFF (hex)

=DEC2HEX(65535), it correctly gives FFFF (hex)

=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)


Unlike the original statement, the "wrong" result can be "propagated" in a sheet, so it is not just a "display" problem, and everyone using Excel for financial calculations in his right mind will use the ROUND() function, as simply put by user Jerome:

# re: Calculation Issue Update
Thursday, September 27, 2007 5:24 AM by Jerome

That's why most of us are staying with Office 2000 and Windows XP.

Remember my boss was so mad when his wireless keyboard missed a key stroke. One hundred thousand became ten thousand could be a very serious mistake.

If you send out an Excel order form to order 850 pieces of $77.1 parts. The recipient open it with Excel 2007 and charge you $100,000. This is no fun.

:blink: :ph34r:

Edited by jaclaz, 29 September 2007 - 04:33 AM.


#7
Thauzar

Thauzar

    4th Star Learner

  • Member
  • PipPipPip
  • 415 posts
Thanks for all the replies, with links and all, I'll take time to read everything. This seem like a HUGE problem. I just hope they fix this asap because here ppl are making a push to switch all Office suites to OpenOffice :angry: and I love the ribbon :blushing:

Thanks
5. Images in signatures shall NOT exceed a total of 300x100 and 80kb. -- Tarun

#8
jaclaz

jaclaz

    The Finder

  • Developer
  • 14,197 posts
  • OS:none specified
  • Country: Country Flag
Just to close this topic, a hotfix has been released:
http://support.micro...kb/943075/en-us

Though not very much publicized. :whistle:

The basic statement hinting that it is a minor bug as it involves the "showing" of result, as the calculatin is performed correctly, which has been already proven to be false :realmad: is still there:

INTRODUCTION
Issue that the hotfix package fixes
This hotfix package fixes the following issue that was not previously documented in a Microsoft Knowledge Base article:
• When you perform a calculation in Excel 2007, the following behavior occurs:
• The result of the calculation is a number from 65534.99999999995 to 65535. The calculation is performed correctly. However, the result is incorrectly shown as 100000.
• The result of the calculation is a number from 65535.99999999995 to 65536. The calculation is performed correctly. However, the result is incorrectly shown as 100001.


and is contradicted by the subsequent statement :rolleyes: :

Back to the top
MORE INFORMATION
To resolve this problem, apply this hotfix. After you apply the hotfix, recalculate the spreadsheets that are in manual calculation mode. When you do this, the values in the spreadsheet will be updated to the correct values.

Why, IF the calculation is done correctly, one should "recalculate the spreadsheets that are in manual calculation mode" :w00t:, really escapes me and is the final (unneeded) confirmation that the bug affected calculation and not display of wrong values only.

As a reader plainly put it here:
http://blogs.msdn.co...sue-update.aspx

# re: Calculation Issue Update
Thursday, October 11, 2007 5:51 PM by ScottB

@humanbeing:

>I´m a doctor, yesterday I calculated a dosage
>for my patient (yes, it´s your son!) in excel
>as 77.1kg*850=100000mg. He´s dead, you don´t
>know it yet. 65535mg would save his life.

I'd be more worried about being off by a factor of a million (kg/mg) than I would by being off by around a factor of 1.5.


It seems to me a very serious bug, and I find the "official silence" or "understatements" by MS, definitely inappropriate.

Another thing that really makes me wonder is the sheer size of the hotfix, about 34 Mbytes and the number of files involved! :blink:

jaclaz

#9
spacesurfer

spacesurfer

    Pharmassist

  • Patrons
  • 1,668 posts
  • OS:Windows 7 x86
  • Country: Country Flag
Nothing beats the good old long hand multiplication and division.

Then again, we would be calculating forever!

Oh, the benefits vs disadvantages.
_____________________________________________________________________
[Modify Office 2007 / 2010 Ribbon UI] [Javascript Guide] [Methods of Typing in Gujarati] [My Math Pages]
[Boot Windows 7 from VHD] [Multi-boot Vista/XP and other OSes with Grub Menu] [Boot XP and Vista Independently]


Gigabyte GA-P35-DS3L, Intel Core 2 Duo E6550 2.33 GHz, ASUS Radeon EAH3450 256 MB, 6 GB Corsair RAM, Maxtor 300 GB + Seagate 400 GB HDDs, Windows 7 Ultimate 64-bit
IBM T42 Intel Pentium M 1.7 GHz, 1.5 GB RAM, Radeon Mobility 7500, 160 GB HDD, Windows 7 Ultimate 32-bit




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users



How to remove advertisement from MSFN