SEQanswers

Go Back   SEQanswers > Bioinformatics > Bioinformatics



Similar Threads
Thread Thread Starter Forum Replies Last Post
from excel file to genomic features ohsu Bioinformatics 5 01-31-2012 11:33 PM
Excel file with Blast info Chuckytah Bioinformatics 1 04-24-2011 08:07 PM
Export HCDiff file from ReferenceMapper as an Excel file Nigel Bioinformatics 1 09-30-2009 07:51 AM

Reply
 
Thread Tools
Old 01-19-2011, 02:02 AM   #1
dariober
Senior Member
 
Location: Cambridge, UK

Join Date: May 2010
Posts: 311
Default 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
dariober is offline   Reply With Quote
Old 01-19-2011, 02:43 AM   #2
ffinkernagel
Senior Member
 
Location: Marburg, Germany

Join Date: Oct 2009
Posts: 110
Default

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)
ffinkernagel is offline   Reply With Quote
Old 01-19-2011, 03:05 AM   #3
dariober
Senior Member
 
Location: Cambridge, UK

Join Date: May 2010
Posts: 311
Default

Quote:
Originally Posted by ffinkernagel View Post
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
dariober is offline   Reply With Quote
Old 01-19-2011, 01:42 PM   #4
steven
Senior Member
 
Location: Southern France

Join Date: Aug 2009
Posts: 269
Default

ha ha, yes, i even remember a BMC Bioinformatics paper about that.
steven is offline   Reply With Quote
Old 01-20-2011, 05:43 AM   #5
pmiguel
Senior Member
 
Location: Purdue University, West Lafayette, Indiana

Join Date: Aug 2008
Posts: 2,317
Default

Quote:
Originally Posted by ffinkernagel View Post
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
pmiguel is offline   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off




All times are GMT -8. The time now is 08:04 PM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2021, vBulletin Solutions, Inc.
Single Sign On provided by vBSSO