More Science

Once I started analysing the data, discussed in the last post, I was immediately reminded of something important.

Your choice of tools matters.

If you use a tool for analysis that is not completely predictable in its behaviour, then any conclusions you draw, which depend upon that tool, are inherently untrustworthy. You are human and will make mistakes - the tools you use should all help you to manage that, not amplify that!

This is why I don't like Excel on the Mac

Microsoft Excel is very useful for what I would call business statistics, or money calculations, but there are several reasons why it is a dud in some respects for the careful scientist.

1. You can't immediately tell, by looking at a cell, what the real data value is because the visual representation dominates. To see the underlying value, you have to convert away from the display format. Subtract one date from another and you will get a weird date based on the difference in the underlying values, expressed as an offset from the epoch. This is not the way that most people think about data. 2007 - 2006 = 1, not 1 Jan 1902 (or whatever it gets set to). This isn't a problem for money because of things like "$" in front. I don't want to be able to subtract 2006 (in days) from 2007 (in years) and get a weird answer that looks like an answer but is, in fact, wronger than a wrong thing on wrongoids.
2. Excel will do what you tell it to do with setting precision, even if what you tell it to do is dumb. If you have 123124/0.23432 and then decide to drop to a single decimal place for precision, exporting this data will save that single decimal place for posterity, with no warnings, despite the rampant loss of significance. (This happened to me with seconds as fractions of hours and I got 0.01 saved as my smallest element, which was NOT what I actually wanted.) This is, again, not as much of a problem in inventory management or a large amount of financial planning because the usable quantum is often fixed.
3. There is no easy way to semantically (or logically) link cells except by using functions. This means that if I have two different calculations, one of which uses formula X to get the positive values, and the other uses formula Y to get the negative values, I cannot tell Excel to use the same formats and representations for these unless I copy and paste them. Assuming that I remember to do so. (This is why my positive values had a minimum delta of 0.01, when my negative values were reals to the higher precision - stored in different cells, with no association, they needed me to copy the format and I didn't.)
4. Excel guesses what you want to do based on the structure of what you type into a cell. This is often less accurate than you want to be, especially when Excel presumes precision or type semantics from structure. Once again, enter money with a $ and the right thing tends to happen.
5. Excel's behaviour, features and usability varies from version to version. Visual Basic (VB) disappeared from the Mac Excel a couple of versions ago. The date handling has changed its default behaviour (according to me, YMMV) as Excel tries harder and harder to help me, but this means that data sets that were read in one way last year are read in a different way this year. The removal of VB has removed one of the easiest ways to automate consistent spreadsheet data mangling. You can still do it with *shudder* macros, but given how often we switch these off to minimise virus transfer, it's not a great way to do it. A lot of businesses still use Windows, which has an Excel with VB and so many other virus vectors that leaving macros on is probably fine. (A little snark at the end but the VB statement is true. It's much easier to use Excel on Windows.)
6. Excel is GUI-based and assumes that the best way to achieve a task is by using the GUI. This is true of many single-use spreadsheets where a simple template can be copied as a basis for a new version. Scientific experiments are nowhere near as standardised in their data handling requirements and the creation of a new spreadsheet set, especially with graphs, is painful.

A large amount of this is, of course, user error territory but user error should have some checks and balances. When I write code to carry out manipulations that I could carry out in Excel, I have to be explicit in my choice of formats, for both input and output, and this is the level of detail that I should ALWAYS be carrying out, no matter how pretty or easy the GUI.

I want something like ABS breaking - yes, I can stop the car by slamming on the hand brake but it requires skill to control the car. I want a tool that allows me to achieve the best and most correct result, in this case stopping the car, but limits my ability completely stuff it up!

Most of this comes down to me, and I accept that, but a good tool would be fit for purpose and limit my errors in useful ways, rather than doing things like allowing me to paste in an incorrect value due to an autofilling pop-up that requires me to SLOW down my data input to read and select correctly. Of course, I can turn all of this auto stuff off but, really, if I'm doing that, why aren't I working with text, grep, awk, Perl, Python, R and Matlab?

Which is why, now, I'm working outside of Excel. I want to be able to collect data and apply the same set of transformations to it every time, with predictable results, without having to cut and paste data, formats, functions and worksheets using a mouse and keyboard.

End of whinge. Time for more coding.

(Hey, anyone who has some sweet days of solving all of the above problems on Excel Mac, I welcome your suggestions! I'm all about productivity. And NC BBQ. Mostly NC BBQ.)