eXtreme Spreadsheet Engineering

What spreadsheet modelling can learn from XP
Member Submitted

Ray Panko [2] in his study of spreadsheet error rates also finds that "[pairs] reduced errors by about a third".

This practice is linked to "moving people around", where changing one pf a pair ensures continuity of thought. People are moved around to spread knowledge across the team, keep thinking fresh, and avoid bottle necks. When a new person joins a task, the questions that they ask to get up to speed show up what needs to be clarified or simplified in the task, which ultimately makes the system easier to maintain.

In some financial industry environments, where there are performance bonuses or competitive pressures, pair working may be unacceptable to the users. In that case, the managers need to decide to what extent the spreadsheets are personal assistance tools or corporate assets. Look at whether they always disappear with each project or person, or whether they are handed on and re-used.

This drive towards simplicity starts with the imperative "do the simplest thing that could possibly work". It's always faster and cheaper to replace complex logic now, before you waste a lot more time on it. There is a formal process called "refactoring" where you remove redundancy, eliminate unused functionality, and rejuvenate obsolete designs. The process is written up in scenarios where experienced people describe how they make complex logic and structures more simple so that the spreadsheet is easier to understand, modify, and extend. For example, "make sure everything is expressed once and only once", "put constants in their own cells". Such techniques get passed around quickly in the “moving pairs” team structures, lifting everybody's skill level.

Exploratory solutions
XP's approach to tough technical or design problems is to create "spike solutions" reduce the risk of a technical problem or increase the reliability of an estimate. Again a difference from common spreadsheet practice is that people normally hate to throw away work; but XP recognises that spikes are usually not good enough to keep, so it is expected that these experiments are thrown away and a clean solution written in.

Test-first development
XP creates the test before the code. In spreadsheets, there is no code/compile/integrate cycle; there is immediate feedback of any change, so it should be actually easier in that environment. For example, you could add cross-check calculations to verify that the answers are as expected. The users are required to have a set of test data and an expected answer first. Even if the answer is not known (which is the reason for building the spreadsheet), there must be some existing data and some expectation of what the result might be. If the expectation is wrong, that in itself is a useful outcome.

A large system developed from multiple spreadsheets does have a need for integration tests. In this environment, you are nearer a conventional systems development project. Standards.

There is an equivalent to XP’s coding standards: spreadsheet design standards. These are common conventions and practices that make it easier to pick up a worksheet and know where everything is, to navigate easily, and to follow its logic clearly.

Finally, the XP practice is 40 hour weeks–no overtime, no fuzzy eyes, fogged thinking, and wasted time from going down the wrong road from tiredness. Think about it!

[1] What is Extreme Programming?

About the author

AgileConnection is a TechWell community.

Through conferences, training, consulting, and online resources, TechWell helps you develop and deliver great software every day.