Re: Excel Calculation issues

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Errata....

I wrote:
"Dana DeLouis" <delouis@xxxxxxxxxxxxx> wrote:
I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware [...].
I suspect the latter [....]

Wrong again! VB Format() does the right thing with 40179.00440002. So the problem is specific to the algorithm used by Excel. No idea what it could be.

(Arguably, VB does make better use of the 80-bit flt pt registers. But on second thought, I think that's a misdirection anyway.)


----- original message -----

"JoeU2004" <joeu2004@xxxxxxxxxxx> wrote in message news:%23jtnMwjGKHA.1252@xxxxxxxxxxxxxxxxxxxxxxx
"Dana DeLouis" <delouis@xxxxxxxxxxxxx> wrote:
Numbers ending in the following digits also have this "issue."
{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

Can you point me to credible documentation of this defect online?


They are all offset by 1/8.

Hmm, 0.00440002 is not an integral multiple of 0.125 from 0.098. I doubt the rule is anything quite that simple, based on what my experiments for this thread.


I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

Probably differences in the software or even hardware used for converting from binary to text.

I suspect the latter because of the similarities that I see in examples that have different results (correctly v. incorrectly formatted). The Intel FPU uses 80-bit registers for floating point arithmetic. Those results must be crammed into the standard 64-bit form when they are stored into memory. The Intel FPU provides several alternatives for rounding the 80-bit value into a 64-bit. That might be a factor. But that's a WAG.

But software is another real possibility. Apple engineers are renowned for paying attention to detail, hacking algorithms to make them behave more humanlike. They might also be mindful of differences, if any, between the PowerPC and Intel FPUs. I can imagine that the Mac conversion routines tweak the result as needed, as I do manually. Adding 1 to the least-signifcant bit sometimes make the necessary difference for Windows/Excel conversion routines. Of course, that is also a WAG.

It is difficult for me to reverse-engineer this and explore variations of the algorithm. My own method of converting to an exact value is probably very different from "typical" algorithms. And I no longer have access to a credible (read: Unix) implementation that I could leverage.

I can work on this on the back-burner over time. But I wonder if someone like Jerry has always figured this out to a "T".


----- original message -----

"Dana DeLouis" <delouis@xxxxxxxxxxxxx> wrote in message news:eG5Cy4iGKHA.4620@xxxxxxxxxxxxxxxxxxxxxxx
> I suspect that is not the only decimal fraction that is problematic.
> But I'm not seeing a pattern.

Hi. Yes, there are more. I remember that the more common problem occurs between 2^15 and 2^16.
(ie 32768 to 65536)

Numbers ending in the following digits also have this "issue."

{0.098, 0.223, 0.348, 0.473, 0.598, 0.723, 0.848, 0.973}

They are all offset by 1/8.

So, a number like 30000.098 will display ok.
40000.098 will not display properly.

I noticed that in Excel for Mac, this is not a problem.
Hmmmm. Wonder why?

= = = = = = = = =
Dana DeLouis



JoeU2004 wrote:
"Dana DeLouis" <delouis@xxxxxxxxxxxxx> wrote:
>> I would think that it would return 40179.00440002.
>> But what I see instead is 40179.0044000199.

I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

Okay. But what exactly is the specification of the defect?

I can see that this misbehaves for xxxxx.00440002 where 4096<=xxxxx<=65535.

I suspect that is not the only decimal fraction that is problematic. But I'm not seeing a pattern.

For example, 4096.00440002 is displayed incorrectly as 4096.0044000199. Its internal value ends in 1999,9693496..., and its hex representation ends in &h205C1600. (Digits to the left of the comma are last of the first 15 significant digits.)

But 4096.00440007 displays correctly as 4096.00440007. Its internal value ends with 6999,997422099..., and its hex representation ends in &h205CECC0.


----- original message -----

"Dana DeLouis" <delouis@xxxxxxxxxxxxx> wrote in message news:uBl7$BgGKHA.4436@xxxxxxxxxxxxxxxxxxxxxxx
>> I would think that it would return 40179.00440002. But what I see
>> instead is 40179.0044000199.

Hi. I think this is the same bug that's been around for a while.
You don't get it if the number is > 2^16 (65536)

So, 65000.00440002 has its display changed, but
66000.00440002 should stay the same.

= = = = =
Dana DeLouis

Ron Rosenfeld wrote:
On Mon, 10 Aug 2009 10:45:54 -0700 (PDT), BaileyPouter
<mdgabriel@xxxxxxxxxxxxx> wrote:

I'm getting confusing results from Excel. The real issue is in a
SumIf formula that I have where values that should match the criteria
I specify are not being found. But I've simplified the issue into a
relatively simple situation that seems to show a real flaw in Excel's
calculations.

If you format a cell as a number showing 10 decimal places, what would
you expect the following formula to return?

=ROUND(40179.0044000199,8)

I would think that it would return 40179.00440002. But what I see
instead is 40179.0044000199.

Does anyone have any idea what could be going on here? Precision as
displayed is unchecked.

Thanks for any thoughts...

You don't even have to round it.

Just type 40179.00440002 into some cell. It changes to 40179.0044000199

--ron



.

Relevant Pages

  • Re: Re: selecting with thousands separator
    ... have it display in this format xx,xxx? ... I suspect you're thinking of DBFORMAT, but that is only supported by ... Roland Wintgen (Systemadministrator) ...
    (comp.databases.informix)
  • Re: Tapble top fusion generator
    ... >increased my monitor format to 1280x1024 and display it on a Mitsubishi ... >using a screen format less than 1280x1024 and the low -resolution LCD ... >Since the poster writing about the character overlap, ... is, when you make one web site viewable, you can render others ...
    (sci.physics.fusion)
  • 2 Ques regarding dates (matching and subtracting)
    ... The end-user enters a date in cell a2, validated to format DD/MM/YYYY. ... In K30, I want to display the number of days until the end of the month, ... "Jacob" wrote: ... > I want to use a subtraction or matching formula. ...
    (microsoft.public.excel.misc)
  • Re: Dividing hours
    ... you can display these in a format that mimics time by making use of the ... The divisional manager will enter in the annual planned hrs on the ... worksheets Each workbook also has a calculation worksheet. ...
    (microsoft.public.excel.misc)
  • Re: Create Dynamic Controls and Displaying Page by Page on the for
    ... My computer is client and other is server. ... I want to display all recevied values for parameters ... > I'm saying this because dynamically creating a few thousand controls might ... >> row data format will be same. ...
    (microsoft.public.dotnet.languages.vc)