SEQanswers In Excel 1.0E-310 is >0.05!
 Register FAQ Members List Calendar Search Today's Posts Mark Forums Read

 Similar Threads Thread Thread Starter Forum Replies Last Post ohsu Bioinformatics 5 01-31-2012 11:33 PM Chuckytah Bioinformatics 1 04-24-2011 08:07 PM Nigel Bioinformatics 1 09-30-2009 07:51 AM

 01-19-2011, 02:02 AM #1 dariober Senior Member   Location: Cambridge, UK Join Date: May 2010 Posts: 311 In Excel 1.0E-310 is >0.05! (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: Code: ```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
 01-19-2011, 02:43 AM #2 ffinkernagel Senior Member   Location: Marburg, Germany Join Date: Oct 2009 Posts: 110 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)
01-19-2011, 03:05 AM   #3
dariober
Senior Member

Location: Cambridge, UK

Join Date: May 2010
Posts: 311

Quote:
 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.

Quote:
 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

 01-19-2011, 01:42 PM #4 steven Senior Member   Location: Southern France Join Date: Aug 2009 Posts: 269 ha ha, yes, i even remember a BMC Bioinformatics paper about that.
01-20-2011, 05:43 AM   #5
pmiguel
Senior Member

Location: Purdue University, West Lafayette, Indiana

Join Date: Aug 2008
Posts: 2,317

Quote:
 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.

Code:
`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:

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