The Better Spreadsheet Fallacy

There are, and always have been a stack of ‘better than spreadsheet’ applications trying to encourage spreadsheeters away from their big grids. Improv, I’m looking at you.

Many of the live spreadsheets I see are ugly, damned ugly. Most of them would be far better in almost any other tech,( even an abacus for some of them!).

And I think from this mess of live spreadsheets, people think that a better tool will result in better applications to replace theses ugly pugly indirect offset nightmares.

Thats a fallacy though. Heres why:

The spreadsheet is the journey not the destination. When you look at a live spreadsheet you are not seeing what the developer initially intended, you are seeing all the twists and turns, scope creep, blind alleys and good days and bad days of the development lifecycle. And more often than not you see the shortcuts the developer had to make to meet the (perhaps constantly changing) timescales.

The main strengths of spreadsheets is their ability to support the developer laying out their ideas, and developing them over time. This is why I think they are a great (unmatched?) prototyping tool. What’s left after it goes live is often suboptimal, which is why I’m a big fan of migrating stabilised spreadsheets to a more production ready technology. But I have yet to see a tools that supports that freeform brain dump-organise-enhance-use cycle as well as a spreadsheet.

I think the opportunity is in migrating spreadsheets to technologies that better support live use, things like databases to organise data into an easy to maintain structure, and code to allow the full suite of dev tools like source control, static analysis, code metrics etc. I am convinced the spreadsheet step needs to be in there in a great many cases.

What do you think?

cheers

Simon

Advertisements

23 Responses to “The Better Spreadsheet Fallacy”

  1. dougaj4 Says:

    Couldn’t have put it better myself.

    The only point of difference I’d make is that in my context (as primarily an engineer, rather than a software developer) the spreadsheet very often is the destination as well as the journey. The end product may not be optimal, but unless the deficiencies are serious it probably isn’t worth the time transporting it to a different environment.

    The key thing is to be aware of the possibilities of the spreadsheet giving rubbish answers, and have systems in place to make sure those answers are dumped; but the same could be said of the output of any software, no matter how rigorous and refined the development process.

  2. Mel Says:

    Hi dougaj4

    Before you all jump on me Yes I do work for a software technology company and Yes I do have a commercial interest in spreadsheets as a development tool. I work with guys like you dougj4 most days in companies of all sizes because these companies – they all rely on spreadsheets – they’re kidding themselves if they deny it. And the reason for that is well documented. Spreadsheets are the most efficient way of getting an answer! So we – EASA – say stick to spreadsheets for development AND for the journey. Where’s the sense in developing an application using spreadsheets, testing it, proing it works, then redeveloping it? Especially if there is an alternative way of achieving management, control and protection (of IP) of a spreadsheet application without changing the spreadsheet – to make it behave like a core business application – with change control, version control and an audit trail. Thats what EASA does. At the UK AEA, engineers built models using spreadsheets, legacy code, off-the-shelf software and hand-cut code. They reached a similar conclusion to you dougj4 – that so much investment was in the model it would be madness to rewrite it – the model was so successful that it could be used by more users than was originally designed for. But to release the model was to open the Pandora’s box of multiple versions and misuse and loss of IP. And often the model is not easy to use.

    EASA takes a spreadsheet and holds it on a central server. Users access the spreadsheet via a browser through an interface built using EASA software. The spreadsheet never exists on the desktop. All users see the published version. When the owner of the spreadsheet makes a change – and publishes the updated version of the spreadsheet – all users see the new version. The intelligent interface can restrict a users view of a spreadsheet based on userid/authorisation. making it easier to deploy complex models to inexperienced users. And every session by every user is logged to a separate database (of choice) with data, userid, date/time and version. A n audit trail is created as well as a mechanism for rewinding to a point in time and recreating the session.

    Skinning spreadsheets and deploying them as web applications is a reliable way of securing the value of a spreadsheet application at minimal cost. And support/maintenance is a doddle – one version in one place to change and publish!

    Spreadsheets….bring ’em on!!!

    Mel

    SpWIth EASA the users/business build an interface

  3. Mike Woodhouse Says:

    Improv had some good ideas, as did Spreadsheet 2000.

    I think you can learn a lot about the state of a user’s mind by seeing how they’ve constructed a workbook (he says, with a senior mamager’s effort on the other screen).

    The problem with the scope-creepy/blind-alley thing is that most users (hell, most spreadsheet developers and “real” programmers too) don’t have the first notion of how to keep a workbook well-factored. Come to think of it, most probably think refactoring is something to do with dividing by prime numbers.

    And therein lies our long-term ability to feed our children… ;-)

  4. Bob Phillips Says:

    Ah yes, but how many proptotypes/tactical solutions get re-deployed in a suitable, production technology. That is the problem, when it works(or probably, looks as though it works), nobody is going to touch it, especially when the business hides it so that those evil IT types don’t find out.

  5. Mel Says:

    Well in many cases there’s no choice but to place the spreadheet in a controlled environment or redevelop. Take Solvency II (or not as you wish) the capital adequacy regulations imposed in 2009 on the insurance sector. It’s incumbent on every European insurer to adopt a model to calculate reserves of capital required to cover risk( I know – you’re thinking – surely they always did that – well er … no). many will reach for their spreadsheet developer to do the job. However once proven and in production it must also be in a controlled environment – or the regulator may take your license away – so pretty terminal. No hiding place for spreadsheets here.

  6. Dennis Wallentin Says:

    Simon,

    In theory I can agree with You but from a practical experiences I do not agree. That’s why I develop solutions (add-ons) to spreadsheet rather than trying to convince clients to port Excel solutions to standalone applicationns.

    If You expose Yourself as an Excel developer then clients expect to get Excel solutions from You.

    The only exception is when working with enterprises where I use Excel to develop prototypes distributed worldwide. If the tools are OK then they are ported to their main systems. In all cases this approach is cheapier than any other approach. This is excellent and support Your main idea.

    Kind regards,
    Dennis

  7. Bob Phillips Says:

    Well you must have worked in a different financial sector than I Mel. Admittedly I left 5 years ago, but the warning signs were all there then, and although the FSA had the power to take a license away, there were many fearful practices then and it was clear that they were never going to do it.

  8. Mel Says:

    Hi Bob – not sure I get your point – maybe you’referring to the banking sector and maybe the investment aspect of the insurance business. Sure, the insurance sector is a graveyard of initiatives to make life better….it is notoriously slow to change and of course each player in the market is differentiated by the very aspects that the FSA etc wish to control..(did we go off Simon’s topic here? Ah well)..and so regulation of this type is going to be resisted up to a point. Solvency II is different – it’s European – it’s happening – and there will be casualties .. amongst the insurers rather than the insured.

    Back on topic…or at least related….I had a friend visit recently for the weekend from Sweden – he is the MD of a global elevator manufacturer/installer etc. They have invested millions in ERP – SAP actually. Whilst staying he was asked to prepare some figures for a board meeting on the day of his return. He had -wisely he thought – come away without his laptop and so borrowed mine. He downloaded spreadsheets (SAP where are you?) to work with – the very spreadsheets that all of the board were using that weekend to compile numbers. He was pulling his hair out – this was not the version he expected – ‘his’ version worked differently. He tinkered with it…finsished what he had to do and sent it off for some poor sod to aggregate/cut and paste with others. Nightmare. It’s worse still. He left his on my laptop. Intellectual Property and numbers – now out of his control. Madness. This for me sums up the challenge faced by a business using spreadsheets. It must be happening all the time. Despite massive investment in IT systems – when it gets important it’s a spreadsheet. Whether it’s a derivatives trader building his own pricing model or an MD presenting his forecast. Redevelopment of spreadsheets is not the answer – control of spreadsheets is the way forward.

  9. Mike Staunton Says:

    A few guys in London are trying with their software called Resolver One that converts spreadsheet formulas into Python

    http://www.resolversystems.com/products/

    At the moment the two major drawbacks are that it doesn’t convert all Excel functions and, a more common drawbacks to all these conversion projects, the level of user-knowledge required is greater than almost all spreadsheet users have or want to have

  10. Mel Says:

    Hi Mike – interesting. I did take a look at Resolver – v interesting. But if new skills are required it kind of negates the accessibility of Excel – and hence it’s popularity. There may be an easier way.

    Take a look at EASA. . EASA (see comments above). EASA takes an excel spreadsheet and converts it into a web application, skinning the excel with a fully customisable interface. The interface is created by excel users – not by IT bods. Its point/click/drag stuff. EASA supports all Excel functions – all versions – and all macros/add-ins etc. Nothing changes in your excel – it simply becomes a web-app.

    I’m sure I’m doing too much selling here…slap my wrist Simon if you disapprove….but the fact is that everyone who has looked at EASA has said Yes! And that includes huge names like Zurich FS, Procter & Gamble, Honda, AMLIN etc

  11. Simon Says:

    Mel, yeah I was wondering how to phrase it, perhaps shorter less salesy comments would be better.

    Dennis fair point, I guess I didin’t mean to always totally migrate, often moving key parts to other techs give the best blend.

  12. Bob Phillips Says:

    I was referring to the likelihood of the UK regulators revoking any Bank’s license. They were, and still look like, a powder-puff regulator. Look at what has just happened, the toughest boss they have had in years (not saying much), charged with improving its performance, resigns because he sees the writing on the wall.

  13. Biggus Dickus Says:

    I agree that an argument can be made for the spreadsheet as merely a prototyping tool, but I continue to be disappointed that there isn’t more effort put into promoting spreadsheets as an potentially efficient, capable and cost-effective REAL part of REAL end solutions.

    IMHO the spreadsheet is a canvas with incredible power baked into every cell and every worksheet and workbook that is horribly under-utilized. To truly get the value out of this technology requires people with knowledge of all the capabilities, with a well-developed set of Best-Practices that lead to consistency at least within a project or a company and a strong knowledge of database technologies. Add to that comprehensive understanding of each unique business requirement (Process) and the spreadsheet CAN be not only the most cost-effective but reliable, capable and cooool even.

    The way spreadsheets are being defined by those who have no idea how to use them or any idea of their REAL capability is disgusting. We should be speaking up for it more and so should Microsoft – but t sure looks like a losing battle when I read threads like this among people who SHOULD be its most vocal proponentsof Excel.

    Dick

  14. Harlan Grove Says:

    Excel got close to what spreadsheets really need when it came out with labels which could be used in formulas. But they didn’t get close enough. Improv/Quantrix (dare I include Javelin Plus) definitions by broad categories which automatically flowed down to individual items (dimensions/axes/subcategories?) was the right direction. Excel needs these at least for conditional formatting criteria.

    But Improv et al didn’t sell, and part of the problem was the need to handle exceptions. The mechanism for subtracting the costs from the XYZ lawsuit from the western region of the Foobar division’s quarterly costs was just too difficult. Potential buyers/users misinterpreted this as a fatal lack of flexibility when a better alternative would have been providing an implicit, always available table of exceptions (thus another failing of Improv and the like — easy connections to and use of data from databases as tabular rather than hypercubic structures) with ALL ‘formulas’ like A+B*f(C) implicitly treated as A+B*f(C)+ExceptionForThisItem.

    I actually used an exception table approach way back when in Lotus 123 where a separate worksheet was used for exceptions with fields for 3D cell address, exception value, reason for exception, date exception added, person who added it, date of approval, and approver. Run a particular macro with the cellpointer on a cell needing an exception, and a dialog box would appear with fields for each of the fields. And the cell in question would have a formula ending with

    +@DSUM(ExceptionTable,ExcValue,ExcAddress=@CELL(“COORD”))

    Naturally it was viewed as too damn much work, so users unprotected the main worksheets and overrode formulas — old school exception handing.

    Moral: spreadsheets can be very useful development tools, but users think they know spreadsheets while they’re not so sure about dynamic HTML, JavaScript and PHP. Because they think they know spreadsheets, users aren’t willing to put up with sound design principles if they give the appearance of requiring anything beyond minimal user effort at a single point in time. Offer to save users hours of effort over a full year in exchange for 1 minute additional effort on initial entry, and users will opt to save that minute now and be damn to the hours later.

    Spreadsheets are dying technology because there are too many users who believe they’re advanced users while they’re really worse than novices. They know where more things are, so have much broader scope for screwing things up. IT/IS just love this. They have a vested interest in not disabusing these users about their self-perceived skill level. Rather, IT lets them screw up, then takes away their toys and takes over their systems.

    • Biggus Dickus Says:

      “Spreadsheets are dying technology because there are too many users who believe they’re advanced users while they’re really worse than novices. They know where more things are, so have much broader scope for screwing things up. IT/IS just love this. They have a vested interest in not disabusing these users about their self-perceived skill level. Rather, IT lets them screw up, then takes away their toys and takes over their systems.”

      Amen !! Exactly !!

      So what can we do about that ?? That’s what I’m keep trying to do but it’s getting more and more diiscouraging all the time.

      Dick

      • Harlan Grove Says:

        Other than disabusing clients that they know a lot less about spreadsheets than they believe they do?

        The problem is that Excel is usually the only automatable software business users are given. The need to establish self-esteme will drive the cleverer 50% to believe they know more than the other 50%. They do, but they still don’t know enough. As I see it you need to show them what you know and they don’t and hope that humbles them without humiliating them. Then you may have a chance to convince them that you, but not they, can use Excel as a true development platform and do at least as good a job as IT/IS could manage on their intranet.

        Putting a different spin on this, IT/IS don’t provide training, and MSFT, bless its little collective heart, doesn’t provide ANY starter documentation any more. Even if most people wouldn’t read it, the few (and getting fewer all the time) who want to do more with Excel can’t manage the hurdle between figuring out pivot tables and creating true applications because there’s no guidance unless there’s an oldster like us to show them how.

        So maybe that’s what’s necessary: offer training along with development. Show your clients some of what’s possible, some of the best practices. They probably half believe IT/IS that spreadsheets are too unreliable. Show them how spreadsheets can be just as reliable as other development platforms. The risk is you teach them well enough that they can handle their own application development.

  15. Biggus Dickus Says:

    “Offer to save users hours of effort over a full year in exchange for 1 minute additional effort on initial entry, and users will opt to save that minute now and be damn to the hours later.”

    I see that EVERY day….. and I confront that attitude (in a nice way) or I turn down the biz. I find the ones most resistant to that which is different than what they know NOW are those at the lowest and the highest ends of the pecking order in organizations…starnge but kinda makes sense I guess.

    Dick

  16. Charles Williams Says:

    I wrote one of these better spreadsheet thingies back in 1998/1999.
    It was called GenExcel and was a compact high-level language based around tabular data structures and extended Excel syntax that dynamically generated and formatted complete Excel spreadsheets (sort of like Structured Table References on steroids).
    I used it on a couple of client projects and it worked quite well, but in the end it was too difficult for most Excel users and would have taken too much effort to turn into a commercial product, so I (reluctantly) killed it.

    It still seems to me that that there is virtue in this approach … but it has to be an extension to Excel rather than a replacement if its going to succeed in the marketplace.

    • Darren Miller Says:

      Charles, I am interested in what you were trying to achieve with GenExcel a while back. We are attempting something similar (I guess) with our Sumwise modelling application. (www.sumwise.com but not much there right now as we’re still in prototype.) I’d be interested in anything you’d care to share with us regarding your concept, experiences, advice, etc.

  17. Harlan Grove Says:

    More nuanced relative/absolute referencing would be a start. I have to admit I’m human, so I screw up once every few years, and my latest screw-up was mindlessly copying a range with some absolute references (the range was A1201:AB1680, cols H, M, R and W in rows 1211 and down had formulas with references to H$1209, M$1209, etc.) and pasting it into A1801:AB2480, and I forgot to change the part absolute references in these cols. Stupid on my part, but I figure this is a fairly common Excel formula bug.

    What’d be useful is references absolute within a range but relative outside the range. In this particular case, the H1812 cell formula’s reference to H1809 could be given by

    INDEX(H:H,ROWS(H$1:H$600)*INT((ROW()-1)/600)+9)

    which would have referred to H9 in H1:H600, H609 in H601:H1200, etc., and that would have prevented my formula errors. Actual range addressing syntax could be something like

    H$9;H$1:H$600 or R9C;R1C:R600C

    That is, something for cell formulas similar to OM expressions like

    Range(“D5:Z20”).Range(“E3”)

    which refers to cell H7. The idea would be that the portion of the reference to the left of the semicolon would be relative/absolute within the portion to the right, but the portion to the right would adjust in chunks when copied outside of itself. For example,

    H12: =$B12*H$9;H$1:H$600

    copied into M102 and R752 would give

    M102: =$B102*M$9;M$1:M$600

    and

    R752: =$B752*R$609;R$601:R$1200

    I know, I know, all Excel needs is more specialized syntax. But relative and absolute often aren’t sufficient. Yes, there’s always INDEX as above, but it’s awkward and longer than ideal. I also know I could have replaced H$ with H in H11:H600 etc before copying into the other areas. But like most people I’d prefer something simpler and more idiot-proof, especially against the idiot I have to contend with most often – myself.

  18. Mel Says:

    Seems like we have 2 topics in one here….how to build better spreadsheets – and how to make spreadsheets easier to maintain/manage/support when deployed.

    How to build better spreadsheets – I’m not an excel developer – I attended EUSPRIG’s annual conference last year and was impressed at just how much emphasis and effort goes into researching building better spreadsheets – it went over my head for the most part but I don’t doubt the high quality of the research and advice. But EUSPRIG did not attract the business users in big numbers – the people who I suppose are in many cases building sub-optimal spreadsheets. I can recommend attendance at EUSPRIG for developers AND business users.

    How to make spreadsheets easier to maintain/manage/support when deployed… I do talk to the ‘business’ about spreadsheets every day – to audit/IT/and the business.

    The business wants Excel – including execs by the way.
    IT want rid…unsupportable as it is currently deployed.
    Audit want controls in place so that Excel behaves like a supportable business application development platform…version control, etc.

    So – quality….and control. Need both. Even the most optimally designed superbly developed spreadsheet will go off the rails once it’s out there unless its within a framework of deployment that secures it’s integrity.

    With the right deployment platform, new spreadsheets can be checked, tested, ratified – and published when they meet the required standards.

    The solution for control – whatever it is – is also the mechanism for managing quality.

  19. Spreadsheets as prototypes for more robust information tools « Wheatland Computing Says:

    […] Posted in Uncategorized by Scott on July 1, 2010 Smurf on Software discusses this in The Better Spreadsheet Fallacy : “The main strengths of spreadsheets is their ability to support the developer laying out […]

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: