The spreadsheet advantage

What are the one or 2 most compelling reasons for you choosing to develop (part of) your system in a spreadsheet?

And what do you see as the biggest down side?

For me: probably the biggest (cultural) issue is that business users understand (or think they do) spreadsheets. They are certainly more comfortable with them than SQL or any of the previous supposed End User developer tools. I’d be interested in any research as to whether this is an intrinsic thing, or just because people have more training/experience in spreadsheets. I am undecided as to whether this is a valid reason, or one we should be challenging with training/coaching/education etc. What do you think?

These are my top 2 most important features of spreadsheet development:

  • Ultra fast development time
  • Simple deployment

What are yours?

I’ll have to have a think about what the biggest downsides are – feel free to start the ball rolling. Mine would be something around easy to mis-manage probably.

Cheers Simon

Advertisements

10 Responses to “The spreadsheet advantage”

  1. Harlan Grove Says:

    Ease of deployment doesn’t distinguish spreadsheets. Any software package that doesn’t require entries in the registry could be distributed using batch files or similar scripts. Spreadsheets are just one of many examples. Well-written Tcl/Tk scripts could be distributed as easily (and would be easier to maintain).

    Ultrafast development, more on the layout side than the formula side, is the sole benefit spreadsheets provide over other application development tools. Spreadsheet formulas are neither as quick to write correctly nor as reliable in terms of maintaining referential integrity as APL, J, R or other intrepretted functional or hybrid functional-procedural programming languages. Well, maybe the simpler formulas are easier, but the more complex stuff isn’t, especially anything involving array manipulation or matrix operations.

    Then there’s necessity. The typical business user has only the following tools for doing anything that resembles original programming.

    – Excel
    – VBA
    – CMD.EXE for batch files
    – Windows Script Host

    Most of them likely also have a Java runtime at which they could throw Java source code files, but no Java development environment. Many may also have Kixtart. But that’s it. Given this choice, what’d any rational person use to produce and distribute numerically intensive applications to their colleagues?

  2. Dermot Balson Says:

    For a consulting firm like ours, it is many things
    * versatility
    * usability and checkability
    * accessibility (as users don’t need programming training to build and use them)

    For me, the test is “what would you use instead?”. In nearly all the cases I see, any formal programming alternative would be laughable. Note, I’m not talking about mega-systems, but typical business situations that happen every day.

    imho, spreadsheets are the Swiss army knife of business. Yes, they can be abused, but let’s not ignore the huge benefits they offer. I see them as very similar to cars, which are similarly very versatile, & easy to learn to drive – but difficult to control in complex situations. Over confidence is a factor in many car crashes, too….

  3. Ross Says:

    Have to disagree with you there Harlan,
    I’m not 100% sure, but I would say that an excel solution, that can be sent as a single file, opened and run without having to install any exe’s, all through a GUI that people are ultra use to, must make deployment of workbook based solution very easy?

    I think for a while now open source dev environments have been around, so I don’t quite agree with the necessity point either.

    For me it’s fast, but also it’s what I know. It’s what I know because the macro recorder gave me a route in.

    Biggest downside – lack of power, [Excess!]

  4. Harlan Grove Says:

    Dermot states the spreadsheet trap unintentionally: “users don’t need programming training to build […] them”.

    They don’t need programming training to build SIMPLE ones, just like the somewhat self-motivated didn’t needed programming training to tweak their AUTOEXEC.BAT files in DOS days. But small and simple spreadsheets aren’t much of a concern for developers or their clients. It’s the big, complex ones that cause trouble.

    While anyone with raw strength may be able to build a shelter from cinder blocks and plywood, a little architectural and carpentry experience generally leads to much better results. Same applies to spreadsheets: a little programming discipline and attention to design makes for much more usable, robust and accurate models.

    The car metaphor is apt. It’s useful to be able to carry light loads quickly in various directions, but who’d use passenger cars to haul coal by the hundreds of tons? That’s the metaphorical use to which multi-megabyte spreadsheets are put.

    IMO, there are NO good spreadsheets that exceed EITHER 2MB file size (if we were talking about Lotus 123, that’d be 1MB, but I have to make allowance for MSFT’s profligacy with storage) or 5,000 cell formulas. There may be some useful spreadsheets that exceed these thresholds, but I’d class them as arguably necessary evils rather than good tools.

  5. Harlan Grove Says:

    Ross,

    There are no EXEs to install if one assumes Excel is already installed. Excel isn’t yet part of Windows, but it’s commonly found on most business PCs. As for EXEs, many don’t require ‘installation’ in terms of adding classes, typedefs and other cruft to the registry. Most business PCs also have Java runtime, so they could run Java applications distributed as source code files and maybe a few Java archives. Just copying files and opening them. It’s also possible to distribute applications as script-laden HTML files which also require no ‘installation’.

    As for development tools, there’s what’s available and what’s allowed. In most larger businesses, nothing is allowed that the IT department didn’t install.

  6. Ross Says:

    “As for development tools, there’s what’s available and what’s allowed. In most larger businesses, nothing is allowed that the IT department didn’t install.”

    That is a good point.

    I guess I kinda think that if your building tools with “serious” programming behind them (i.e., as a developer) then the IT department will let you install applications.

    good stuff

  7. MikeC Says:

    “imho, spreadsheets are the Swiss army knife of business.” (Dermot, Comment 2)

    Possibly the best description of Excel that I’ve ever heard. It does pretty much everything you could need it to do passably, though specialised tools to do each of the same jobs would be more efficient. But would you prefer to carry:
    a. a swiss army knife
    b. a rucksack full of specialised tools?

    Especially when you consider that everyone else who is going to so much LOOK at the work you produce would also have to have the same set of specialised tools in order to make use of them? Seeing as these people are quite often within different companies that you might send data etc to once a year, that kinda rules out many bespoke apps right away.

    Versatility, and almost universal availability, is definitely the biggest, hugest, most impossibly enormous advantage to Excel usage.

    The second would be that most users are passingly familiar with it. If you pass them a completely new application, many are scared by this. If you “hide” an app within an Excel workbook, they’re immediately more comfortable using it, even if it works entirely in (say) VBA, and uses very, very little of Excel’s core capabilities – purely because they’re opening a .xls in the first place and they see the grid. This basic familiarity is also handy because it means that most users of the program can perform basic functions (like low-level formulae etc) quite happily on their own, and don’t need dev time for the slightest item – whereas in dedicated apps they frequently do (this crosses over with the versatility point above).

    The main drawback, and I’m sure that I won’t get too much argument on this seeing as others have already said it, is the potential for abuse instead of use. This is exacerbated by my second “plus” point above. Many users THINK they know lots about Excel, and that they can do “x, y and z” – and don’t register what this will do to carefully constructed code in the background / dependant formulae. Spreadsheets are also very hard to control – if anyone knows of a way in which we can prevent people taking a copy of a spreadsheet….?

    Case point: within my company, I have constructed a “pro forma” which auditors use when conducting checks on work. There is a single saved copy on the network which should be used by all auditors. Every so often, we get someone who decides to save a copy of this to their personal drive / desktop etc. This means that when I update the central copy (e.g. adding staff members etc), they’re suddenly working on an out-of-date copy, because they don’t realise that by saving the .xls to their desktop, it doesn’t get updated alongside the network copy.
    (as it is, I’ve got round this as best as possible by putting a “timer” on – any copy of the form will cease functioning if it hasn’t been updated for 10 days – normally updated every 5. When they cease functioning, the user contacts me and tells me it’s broken, and I call them names and tell them to use the right one..)

  8. Dennis Wallentin Says:

    Hi,

    Cost and time savings:
    Rapid development of ‘prototypes’ (for number crunching and presentation) which are roled out in organization for discussions and evaluations. Later on some of them are implemented into the larger business systems.

    Availability:
    Where MS Office is used Excel is always available, i e no further investments of hard- and software, are necessary.

    The weak parts are:
    Difficulties to protect intellectual property
    ‘Everyone knows how to work in Excel’ —> Issue for external developers
    Compared with VB/C++, VB.NET/C#/VSTO etc Excel’s development platform is not enough ‘powerful’ and is out of date (VBE is from 1993).

    Kind regards,
    Dennis

  9. Marcus Says:

    Maybe it’s my limited upbringing but I’ve not encountered business users developing solutions CMD, WSH, Kixtart or Java. The most common is a combination of Excel, Access and VBA. Also many corporate PC’s are ‘locked down’ so users can’t install or execute anything IT doesn’t want them to. I had to go through IT support to register an XLA at one place. Hence for many the attraction simply is, as Edmund Hillary put it; “it was there”.

    — Compelling Reasons —

    – Familiarity
    Nearly everyone has Excel on the desktop and most are comfortable using it.

    – Prototyping
    Excel it a flexible and fast environment for developing mock-ups, prototypes and proof-of-concepts.

    – Tactical Solutions
    As per prototyping, Excel is an apt platform for tactical solutions. Something the business needs quickly (i.e. now), for a short period or as an interim measure.

    – Cost
    Most of the work I undertake exists because there is a gap (chasm?) between the business and IT. As one client put it after liaising with their IT dept; “they offered to build a Mack truck but we only wanted a Vespa”. Many spreadsheet based solutions can be delivered for a fraction of the cost other platforms would incur.

    — Biggest Downsides —

    – Versionitis
    Unless you implement controls to mitigate, users invariable allow an spreadsheet to breed and mutate.

    – Mutli Developer Support
    Excel is a pain to develop with in a multi-developer environment.

    – Unmonitored Organic Growth
    Related to versionitis, organic growth occurs when the spreadsheet solution is expanded over time (and often many developers) as the business realises they forgot something or it really must have feature ‘X’. Not only does it become a very different beast than when started, but had the business given requirement analysis it’s due diligence the resulting solution would have engineered quite differently.

    – Tactical Solutions
    While the benefits of tactical solutions are many, their largest drawback is that many tactical solutions become strategic. By their nature, these solutions aren’t developed for the impacts that longevity and business dependence imply.

    Cheers – Marcus

  10. Simon Says:

    Excellent discussion
    the bit about Excel definitely being on the client is interesting, I have down played.net because no version of the framework has reached similar penetration.
    Tactical becoming strategic – how true!

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: