In Excel 1.0E-310 is >0.05!

[dariober]

(This is just a curiosity, possibly a warning, only loosely related to bioinformatics)

I accidentally found that according to Excel the number 1.0E-310 *is not* smaller than 0.05 (at least, on my Windows XP, 32 bit, Excel 2007).
Try the following in Excel:

1.0E-305  "=A1<0.05"  Gives TRUE
1.0E-310  "=A2<0.05"  Gives FALSE!
1.0E-310  "=A3>0.05"  Gives TRUE!

All the best

Dario

[ffinkernagel]

which is not suprising since Excel stores floating point numbers of double which has a range of only around 1.7E308 (and I suspect excel stores your 1.0E-310 as a string... but that's excel for you).

Unfortunately the pitfalls of floating point arithmetic is not commonly taught, even less so than the usual "don't use Excel if you can avoid it" guidance (there are a variety of other dangerous issues for example "Sep0-8" will become september 2008, open office calc will happily plot 1..n vs Y instead of X vs Y if there are Err: values in X instead of throwing an error, etc. that all lead to almost unnoticeable corruption)

[dariober]

Originally Posted by ffinkernagel

even less so than the usual "don't use Excel if you can avoid it" guidance

I know I know...! I steer clear of Excel as much as possible. In a moment of laziness I got back to it and I got burned! Along with the text-to-date quirks one my favorites is '007' becoming '7'. I just found this one I posted particularly amusing.

and I suspect excel stores your 1.0E-310 as a string

I also suspected that but the funny thing is that the cell property for 1.0E-310 says 'number scientific'.

Dario

[steven]

ha ha, yes, i even remember a BMC Bioinformatics paper about that.

[pmiguel]

Originally Posted by ffinkernagel

which is not suprising since Excel stores floating point numbers of double which has a range of only around 1.7E308 (and I suspect excel stores your 1.0E-310 as a string... but that's excel for you).

If you type if "1.0E-400" excel lists the format as "scientific", but obviously leaves the string as text. (That is, it remains aligned against the left border of the cell, whereas numbers are aligned against the right border of the cell.)

If you type "=1E-400" in a cell you get an error. If you type "=1E-300/1E+100", excel displays "0".

(Excel 2007 on a 32 bit XP box.)

So the actual behavior is a result of whatever parsing engine excel uses to perform the conversions of entered text to other formats. That and whatever dictum specifies that text is greater than numbers. But that is just DWIMery gone wrong.

perl -e 'print "X\n" if ("foo" < 100)'

does print "X". Meaning "foo" is being evaluated somehow and is judged to be less than 100? If you add "use warnings;" perl will warn:

Argument "foo" isn't numeric in numeric lt (<) at -e line 1.

--
Phillip