Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You shouldn’t use a spreadsheet for important work

lemire.me

Post reply on HN
Print view View original

181–190 of 200 posts

Re: You shouldn’t use a spreadsheet for important work

#181

Post by rahimnathwani »

sheetjs wrote: post #149
dragonwriter wrote: post #147

Earlier quoted context omitted.

> Efforts to move them into databases or whatever be damned. A spreadsheet is != to a database table except maybe in the most abstract possible sense in some cases. The problem is that very often, spreadsheets are used as databases because they are the tool the user is familiar with, not because the application is inherently more suited to a spreadsheet. > They really are different things and I agree that issues with…

The excel database functions (DAVERAGE, DCOUNT, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSUM, DVAR) give you about 90% of what you want in a database with a query "language" that's simpler than SQL.

Pivot Tables are even easier to construct and use than these functions. They calculate things like averages, counts and sums for lots of dimensions really quickly.

If you don't like the Pivot Table layout, you can use easily construct formulae to pull the relevant values from a Pivot Table into whatever format you want. You still get the speed of calculation/refresh and, if you label your fields well, have formulae which you can copy-paste across a large area, making the sheet easy to inspect and reason about.

Re: You shouldn’t use a spreadsheet for important work

#182

Post by rahimnathwani »

frandroid wrote: post #117
andrewfong wrote: post #98

Earlier quoted context omitted.

This little tidbit from Chris Granger seems relevant: "Excel is inherently observable since it doesn't have any hidden state and all values are there for you to see and manipulate. It's also direct. You change values in the grid, drag drop things, do calculations on selections, and so on. And it manages to sidestep a lot of incidental complexity; spreadsheets are timeless, without setup, and don't even have a notion…

Saying Excel doesn't have any hidden state is stretching things a bit--your formulas don't show up until you are directly on them, so mistakes (formula results that got turned into static data, for example) are hidden until you put your cursor directly onto them.

"formula results that got turned into static data"

This hints at one of the sources of problems with Excel. One good approach to using Excel is to always be in one of two modes: changing the structure (equivalent to coding) and entering values (equivalent to using software). Of course, at the early stages of designing something you will be entering lots of dummy values (e.g. ARPU) to check that your formulae work.

However, many people don't distinguish between collections (rows, columns or other contiguous areas) of cells which _should_ contain values, and those which should contain formulae. I, like many, prefer to indicate input cells with a yellow or orange background, so that I know everything else is a formula.

You can do even better:

- If you're done designing a spreadsheet, and expect to use it for a production process, then mark the input cells as unlocked, and protect all the sheets. Then the end user won't be able to mess up the formulae.

- Always write formulae in a way that they can be copied across or down whilst pointing to the right places. This can be achieved through use of one or more $ signs to fix a reference. I've seen a fair number of spreadsheets where there are a large number of similar formulae, but they have been entered/adjusted manually. This is fine only if you never make mistakes, and if no one else needs to change the formulae later or verify they are correct.

My point is that "formula results that got turned into static data" can be avoided with just a few easy rules. However, most people aren't taught these rules :(

Re: You shouldn’t use a spreadsheet for important work

#183

Post by rahimnathwani »

dragonwriter wrote: post #121
andrewfong wrote: post #98

Earlier quoted context omitted.

This little tidbit from Chris Granger seems relevant: "Excel is inherently observable since it doesn't have any hidden state and all values are there for you to see and manipulate. It's also direct. You change values in the grid, drag drop things, do calculations on selections, and so on. And it manages to sidestep a lot of incidental complexity; spreadsheets are timeless, without setup, and don't even have a notion…

> Excel is inherently observable since it doesn't have any hidden state Except that it does -- even in terms of simple numerical values what you see is not necessarily the actual value, but instead the result of passing the value through a format string that can vary by cell.

Sadly, most people don't use this feature, and are content to show values using the default format, even if it results in values displayed as 12904819.23 (which might be better displayed as 12.9m or 12.90m).

Re: You shouldn’t use a spreadsheet for important work

#184

Post by DanBC »

People here don't seem to inderstand wuite why people use spreadsheets.

I knew someone who ised the spreadsheet supplied with MS Works (a truly hateful piece of software) to create fax cover sheets and notice signs and printed letters.

The spreadsheet was also used to creat Bills of material for electronic subcontracting. These would be printed out and then only the paper copy was relevant. (This worked better than you might think - that paperwork system had been refined over years and all the kinks ironed out. Everyone knew if a part had been ordered, arrived, booked in, kitted, issued to the shop floor, paid for by us and paid for as a final product by the customer. Moving from that paperwork system to sage line 100 was painful and produced a lot of confusion and kludges.

So, people like spreadsheets because they can get stuff done. To you it might seem like they're hammering in nails with the handle of a screwdriver, but the answer is to make better documentation and better software.

(Also I see people using screwdrivers and other handtools and I wonder why noone ever told them how to do it properly.)

Re: You shouldn’t use a spreadsheet for important work

#185

Post by DanBC »

nucleardog wrote: post #135
seanstickle wrote: post #7

Felienne Hermans has done some very interesting work examining how to refactor spreadsheets. ( http://www.felienne.com/publications ) Her PhD dissertation on the subject "Analyzing and Visualizing Spreadsheets" ( http://www.felienne.com/archives/2534 ) is particularly detailed. Much though some people might think that spreadsheets are not for serious work, they're being used for that right now, and that is unlikely t…

I was watching a show the other day where they discussed their mapping of Benjamin Franklin's social network (who he communicated with, where they were in the world, etc). They showed some of the process and it appeared to involve some Excel workbooks with hundreds of sheets and terribly normalized data. I can only imagine the amount of work involved in trying to extract any sort of useful information from the raw da…

If I had millions I would pay people like you to teach people like them how to "do it properly". Or even just implement it properly with lots of comments and docs and maybe a talky video.

Re: You shouldn’t use a spreadsheet for important work

#186

Post by netcan »

bane wrote: post #101
seanstickle wrote: post #7

Felienne Hermans has done some very interesting work examining how to refactor spreadsheets. ( http://www.felienne.com/publications ) Her PhD dissertation on the subject "Analyzing and Visualizing Spreadsheets" ( http://www.felienne.com/archives/2534 ) is particularly detailed. Much though some people might think that spreadsheets are not for serious work, they're being used for that right now, and that is unlikely t…

What's interesting to me is that software spreadsheets are more or less just evolved version of what apparently VisiCalc got right on the first try. Sure software spreadsheets are basically just parroting the physical ledgers that came before them, but turning them into software almost immediately opened up a kind of dynamic that didn't exist before. The latest Excel is basically just an organically evolved VisiCalc.…

That's a very good point and a good reason why anyone who wants to "fix" this problem should build on spreadsheets.

Re: You shouldn’t use a spreadsheet for important work

#187

Post by DanBC »

panarky wrote: post #32

JP Morgan's $6.2 billion "London Whale" trading loss: An unnamed overworked staffer in London copied and pasted the wrong figures into an Excel spreadsheet, throwing the firm's risk models out of whack. http://finance.fortune.cnn.com/2013/01/18/jpmorgan-london-wh... Spreadsheet errors are reaching epidemic proportions globally. What we need is a transnational organization to contain the threat with research, best pra…

I love EUSPRIG.

Another website people interested in spreadsheet erros might like is Ray Panko's spreadsheet research

http://panko.shidler.hawaii.edu/SSR/

Re: You shouldn’t use a spreadsheet for important work

#188

Post by dragonwriter »

rahimnathwani wrote: post #182
frandroid wrote: post #117

Earlier quoted context omitted.

Saying Excel doesn't have any hidden state is stretching things a bit--your formulas don't show up until you are directly on them, so mistakes (formula results that got turned into static data, for example) are hidden until you put your cursor directly onto them.

"formula results that got turned into static data" This hints at one of the sources of problems with Excel. One good approach to using Excel is to always be in one of two modes: changing the structure (equivalent to coding) and entering values (equivalent to using software). Of course, at the early stages of designing something you will be entering lots of dummy values (e.g. ARPU) to check that your formulae work. Ho…

> Always write formulae in a way that they can be copied across or down whilst pointing to the right places. This can be achieved through use of one or more $ signs to fix a reference.

Except if you are using modern version of Excel, don't general use $ references for this, go one step further and use names (for fixed individual datapoints where you would use two $s) or named tables with named columns (for the most common use of single $ references.) This is more self-explanatory and less error-prone.

Re: You shouldn’t use a spreadsheet for important work

#190

Post by mathattack »

What's scary here is how much of Finance is based on Excel. Tons and tons of spreadsheets. Pretty much every bank's risk measurements go through a spreadsheet at some point. So do most trades. High frequency may get executed in C++, but somewhere along the way it gets aggregated in Excel.

The only thing worse than being able to see all these convoluted spreadsheets, is not being able to see the code behind non-spreadsheet systems.

Post reply on HN