stats for scientists in Excel

[uberdonkey5]

Excel has regularly been criticised for producing poor statistics due to the way users apply the functions without understanding what is happening (and Excel is not consistent), but also due to lack of precision in the calculations and incorrect routines.

See this site:
http://www.practicalstats.com/xlsstats/excelstats.html

interestingly it suggests open-office may be better:

"Yalta (ref 1) states that p-values [inverse probability distributions] reported by the free OpenOffice’s Calc spreadsheet and the open-source Gnumeric spreadsheet do not have the same numerical problems as does Excel - their programmers used accurate algorithms. I have not seen a detailed analysis of results from these programs, however, so caution is advised until you check their results against a true statistics package. These two programs, both of which read Excel data spreadsheets, might provide an alternate and more accurate set of the simpler statistical test procedures within a spreadsheet environment than does Excel."

Also, alternatives methods of using Excel with add-ins are detailed.

The site is also interesting because of its discussion on CENSORED DATA and NON-DETECTS.. ie. dealing with data when there are large numbers of values below the limit of detection (I hope no-one out there is using LoD/2 !!)

P.S. if you want to know how to calculate Regression on Order Statistics with Excel or Open-office, please contact me and I can send you a (macro, and thus virus-free) spreadsheet in Excel or open-office format. I have it for both single and multiple LoDs.

[XCan]

Hehe, are scientists truly using Excel for anything worthwhile? :O Not even the biologists I know use Excel. Only people that use Excel that I know of are economicsts, but we all know that's not a real science. ;)

<Disclaimer> The above is of course written with a slight sense of humor, don't be offended. :)

[uberdonkey5]

well, I must admit I recently used Excel to do something as simple as ranking values, but I discovered it even does that incorrectly!

[uberdonkey5]

P.S. reminds me of this joke...

A man takes a balloon ride at a local country fair. A fierce wind suddenly kicks up, causing the balloon to violently leave the fair and carry its occupant out into the countryside. The man has no idea where he is, so he goes down to five meters above ground and asks a passing wanderer: "Excuse me, sir, can you tell me where I am?"

Eyeing the man in the balloon the passer-by says: "You are in a red balloon, five meters above ground."
The balloon's unhappy resident replied, "You must be an economist.

"How could you possible know that?" asked the passer-by.
"Because your answer is technically correct but absolutely useless, and the fact is I am still lost".

"And you must be in management", said the passer-by.
"Thats right! How did you know?"
"You have such a good view from where you are, and yet you don't know where you are and you don't know where you are going. The fact is you are in the exact same position you were in before we met, but now your problem is somehow my fault!"

[samden]

The reality is that most of the scientists I work with use Excel for virtually everything, maybe running their data through GenStat or Minitab as a very final step. Virtually every graph I see is made using Excel.

And most people don't realise there are alternative ways of working with data.

[uberdonkey5]

yes.. I agree. I use it alot myself. I think office is geared to 'the office' but Excel is a good intermediary for organising, visualising and transferring data. Problem is with more more complex analyses we necessarily have to use a variety of software and the way they function is different... I use four different statistical packages with very different methods of producing graphs.

May Excel Add-ins are actually a good idea, though I'm used to what I use now

[XCan]

Well, I won't say anything, I mean there are even people running Monte-Carlo simulations through Excel... In terms of efficiency, it's just horrible, as well as the quality of the plots and figures generated by it. What takes one week of work (data sorting, regression etc) to do in Excel by a biologist took less than 1 day for my colleague in Matlab and I imagine it being the same in other high-level languages.

[samden]

I do all my data manipulation in R now, and I hate to think how long it would take were I struggling away with massively complicated Excel macros like most (well, all) of my colleagues.

[lethalfang]

The reality is that most of the scientists I work with use Excel for virtually everything, maybe running their data through GenStat or Minitab as a very final step. Virtually every graph I see is made using Excel.

And most people don't realise there are alternative ways of working with data.

Only if the data needs no complex processing, and the scientists you're talking about are probably biologists.