Spreadsheet disadvantage part 2

Ross asked a good question recently:

Is there something specific about spreadsheets that makes it a poor technology for certain things or is it just the way they are used?

IMO its both, and here is the main technical problem with spreadsheets:

The human view and the machine view of the model are completely disconnected. Spreadsheet formulas tend to work on a select by address basis, but all our logic is based on text descriptions. Apart from the odd look-up or index formula the spreadsheet formulas completely ignore the text descriptions that mean so much to human users.

I think this disconnect makes spreadsheets tremendously easy to change and adapt, but it also makes them fragile.

I think some of the more fervent supporters of using named ranges think that they fix this, but it does not address it all.

Here is that google s/s I did the other day, I’ll use this as an example.

Here a screen shot:

emp cost spreadsheet

Human view of D14 Sub total : Sum of Salaries, bonuses, other and overtime for January.

Spreadsheet view: sum the 4 cells above.

If I asked Capitain Blunder to remove salaries from that, chances are he would delete C10, shifting upwards. That won’t break the spreadsheet at all (no cell errors), but it will become completely wrong.

If I want to use the Jan Salaries figure elsewhere in this spreadsheet I’ll go ‘=D10’ [Select by address]

If this were in a database and I wanted Jan Salaries I would issue a query like ‘SELECT Value from PLtable WHERE LineType = ‘Salaries’ AND Month = ‘Jan’;’ or something. [Select by value]

I’d say the spreadsheet way is faster to get going, the database way more robust.

I could name each row based on the description in column C and then use the intersection operator to get english looking formulas like ‘Salaries Jan’, but that is still disconnected from the text descriptions.

Some authors have suggested that the cell address based approach of spreadsheets is similar to programming in assembler and misses all the progress of modern programming languages.

I think Microsoft tried to address this with the use labels setting, but I think that was even more unpredictable.

This human/spreadsheet logic disconnect is a specific feature of spreadsheets, not present in most other technologies, and is a big driver for my preference to avoid spreadsheets in production. I also think its a key factor that make them so unbeatable for many types of prototyping.

Of course you could argue that with careful controls etc etc then this fragility risk can be mitigated, and I wouldn’t disagree, but personally I would rather see those mission critical spreadsheets migrated to a more (logically) robust technology like a database, or even code.

What do you think?

cheers

Simon

Advertisements

6 Responses to “Spreadsheet disadvantage part 2”

  1. Harlan Grove Says:

    Cell/range addresses aren’t necessarily more cryptic than the 1 or 2 char variable names used in the programming texts from which I learned programming way back when.

    Microsoft has taken the first few steps to fix this by providing ‘structured referencing’ for the new XL2007 tables. It’s missing multiple row selection syntax. And if structured referencing accepted R1C1-style relative addressing (row/column offsets inside square brackets, or even LAST and NEXT qualifiers to reference adjacent rows) it might provide a reasonable alternative to literal cell/range addressing. Then the question would become what the recalc speed differential was between structured referencing and direct cell/range addressing.

    Anyone here ever use Javelin Plus? I only saw it in use once, and it looked like it provided arbitrarily many user named objects (tables, lists, that sort of thing) that could be stitched together into other objects using code that looked part way between spreadsheet formulas and structured high level language code. And other ‘financial modeling’ software like Lotus Improv has also failed because CFOs couldn’t figure out how to tweak the results.

    For sophisticated developers, an OO/procedural programming language with a grid control would be a better set of tools with which to create robust applications. Unless anyone else here could provide an example of any calculations that are more efficiently performed with Excel’s natural recalculation order for worksheet formulas than via procedural coding, I can’t see that Excel is NECESSARILY better than language & grid control for any calculations.

    But back to speadsheets and my CFO example. Anything will fail in the penumbra of the spreadsheet market unless it is focused exclusively on professional developers and intended to build black box products OR it’s easy enough for people who BELIEVE they know how to implement financial models on computers to be able to modify other people’s models without having to read ANY documentation. That is, the market will support only idiot-proof and idiot-modifiable development systems. IMO, Excel falls squarely in the latter category.

  2. Stephane Rodriguez Says:

    Simon said “This human/spreadsheet logic disconnect is a specific feature of spreadsheets, not present in most other technologies, and is a big driver for my preference to avoid spreadsheets in production. I also think its a key factor that make them so unbeatable for many types of prototyping.”

    To me, an even bigger disconnect is when a “Salaries” label applies to filtered values, and that any calculation on “Salaries” will apply to these OR a different set of values depending on whatever context (possibly not in the hands of users).

    Hence the insulation thanks to a business objects layer : in a nutshell, the data source query becomes an object source query, and the objects’ contexts are retained in the report. And it can still be SQL based to avoid learning a new language.

  3. Simon Says:

    Harlan, I agree with your view of the market, and I think this has been shown time and time again with seemingly ‘superior’ products disappearing.
    Anyone else use Oracle Financial Analyzer? That was due to completely replace Excel at one global co I worked with. Till the project got canned for being pure fantasy.

  4. dermot Says:

    I’ll keep saying it – 99.9% of spreadsheets are not written by people like us. That is the strength of Excel, that it puts such power in untrained user hands -and the business benefits are incalculable.

    Like a car, Excel is great if you’re not sure quite where you want to go, because it is so flexible. However,if you absolutely must get to the same place reliably every day, public transport (with trained drivers and backup systems) is a better solution.

    So Excel is not suitable for systems that need to be tightly controlled.

  5. Ross Says:

    I agree with your comment simon,

    quantics, or whater it’s call does the name thing a bit better, not sure if it’s 100% fool proof though! – what is?

    I think one of the short comings of the Spread Sheet by design is that it does not inherently have enough structure and separation. This makes it fast and easy to use, but at a cost of poor design. Because it’s very easy to make Excel perform calculations, SS structure does not have to be thought about so much. In the olden days, with paper and pen, it was worth while making sure you set your data and logic and results out really clearly, because you couldn’t just slap some colour over the few cells etc. Excel has depreciated these skills amongst the masses – we all design perfect layouts of course.

    Picking up on Harlan’s point, may be a grid with a Sketch type user language would work, ie, get rid of incell formulas. Have the user define a block/blocks of data and then perform logic and math on them – the results are reports in another table, which can then be used again to report on, etc etc?

  6. Dennis Wallentin Says:

    “I’ll keep saying it – 99.9% of spreadsheets are not written by people like us. That is the strength of Excel”: At the same time that’s also the weakness with Excel because it’s too easy to create solutions with it.

    Excel can be viewed as an outliner (sandbox projects) where the final ‘product’ can easily be created with a programming tool (using OWC or any third party spreadsheet object).

    In my own experience I would say that it’s a must to use tools to document Excel-solutions.

    Many BI-vendors have pointed out the weakness with Excel. MSFT will soon release their Performancepoint server. I bring it up because it will be of general interest how MSFT will view Excel when it comes to the BI-area.

    Kind regards,
    Dennis

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: