Spreadsheet Rules

When I first started working at Babcock & Brown, a San Francisco based investment that focused on project and lease financings, one of the analysts shared this document with me.  In this little 2 pager, he captures many of the essential elements of spreadsheeting.  This is important.  Read it four or five times.

Historical note: You’ll see that he described functions with @sum, @choose, @sumproduct, and @if.  That was the goofy syntax that Lotus 123 used, which was the predominant tool at the time, up until about 1996, when the finance world transitioned over to Excel which has been the predominant spreadsheet ever since.

From: Ted N (7/24/95)
To: Jason

Mail*Link¨ SMTP Spreadsheet rules

Here’s a little write-up I produced, collecting rules Mike and I put together over time.

Your additions welcome.
/sf/ted/text/spreadsheet.rules

Spreadsheeting–the practice of analyzing a situation in a computer-based
spreadsheet–is an under-appreciated task. Much as good expositional
writing is more than using proper grammar, good spreadsheeting means
that your result not only “works”, i.e., produces the right answer, but
serves as an easy-to-understand explanation of HOW you produced the
answer.

The objectives are to produce spreadsheets that are not only RIGHT
(i.e., they figure out what they’re supposed to) but are also
(a) EASY to use, both for yourself and for anyone else;
(b) EASY to adapt to changes in circumstances
(C) VERIFIABLE: if something’s wrong, that mistake won’t be hidden
behind a thicket of formulas, but instead will be easy to see.
(D) COMPLETE: The steps leading from “input” to “answer” are laid out
on hard copy so that one doesn’t need to refer to the electronic
spreadsheet to verify the answer because it’s all right there.
(E) ROBUST: You can change a piece without having the whole thing
come crashing down on you.

These rules are supposed to help you
(a) CREATE a spreadsheet that meets the above objectives, and
(b) UNTANGLE a spreadsheet that somebody might have hacked together
without heed for those objectives.

I probably should create a spreadsheet showing right and wrong
ways to do things.

ONE MORE METAPHOR: Anyone can build a house: just hammer ENOUGH
nails into ENOUGH wood, and any design will stand up. But will it
collapse the first time somebody slams a door?

NEVER EVER EVER HIDE A CELL. If something is in a cell, that
means it’s important, and it deserves to be displayed and labelled.
If you want it off a particular page, you can move it somewhere else.

THE RULE OF THUMB:
No formula should be so long that you can’t cover it with your thumb.
Break it down into several sub-formulas.

THE TWENTY-FOUR SECOND CLOCK:
If you can’t understand (or explain) a formula in 24 seconds,
it’s too complicated
and should be simplified. Break it down into pieces.

SELF-EXPLANATORY OUTPUT: Output is supposed to clarify things.
If it doesn’t, it is a failure. Admit it. Don’t count on being
able to explain it over the phone, and don’t write separate
documents to explain it. Reconfigure it.

@if(a,b,c)…one of the worst occasions of sin.

First, ask yourself if you want @if, or if you want
to use @max or @min or @choose.

Never combine @if in a larger formula.
It should always appear alone inside a cell.
COUNTEREXAMPLE: +a1+@if(b,c,d)

Never perform computations inside @if. The TRUE,FALSE, and CRITERION
results should be in cells by themselves.
COUNTEREXAMPLE: @if(a,b+c,d+e)
COROLLARY: Never nest @if’s
COUNTEREXAMPLE: @if(a,@if(b,c,d),e)

Do NOT use the < sign (use >= and reverse the criterion).
If you adopt a convention that all your comparisons will use as their
criterion > or >=, the consistency will make your spreadsheets easier
to read and understand: you won’t accidentally build contradictory
formulas.

REACHING IS BAD. Bring assumptions into the sub-sheet, and build
off that local copy, instead of referring continually to a faraway
copy. Especially with descriptive tab names, formulas can quickly
get unreadably large.

NEVER change a formulation in the middle of a spreadsheet row or
column. Copy each formulation to a complete row of its own, and use
@if or @choose to select based on a constant criterion. It’s a HUGE
amount of trouble to vet a row before copy-righting a formula. You
should ALWAYS be able to copy-right the first item in a spreadsheet
row, confident you’re not going to wipe out some changed item.

DO NOT MIX SIGNS in totals.
COUNTEREXAMPLE: A1+A2-A3-A4+A5-A6
Adopt the convention that all numbers in a table should be presented
with consistent signs, so that you can produce the proper total
by using @sum.

DO NOT HAVE SCATTERED TOTALS.
A42+A96+A72+A34…is very difficult to understand.
Bring each of those items down individually and create a sum column
for them.

SORT SCATTERED TOTALS BY INDEX
If you *MUST* have scattered totals, sort them by row index.
A34+A42+A72+A96 is a lot easier to parse out because you’re not going
up and down and up and down in search of your data.

@sumproduct: a godsend. Learn to use it, learn to love it.
A1*B1+A2*B2+A3*B3+A4*B4+A5*B5…
becomes
@sumproduct(A1..An,B1..Bn)

#AND#, #OR#… NEVER use these. Just don’t do it. Never.
Any formula
containing one of these is nearly CERTAINLY going to wind up being
too complicated. Break it down.

INPUT/OUTPUT sections: each subsheet should have an “input” and an
“output” section, so you can easily control what’s going in and what’s
going out. You can thus work on the other stuff, confident that
something elsewhere in the spreadsheet doesn’t depend on it.

CATEGORICAL CONDENSATION should be done close to the source.
Example: Summarizing monthly data as annual data. The @sum’s should
be on the MONTHLY page, not the ANNUAL page. It’s easier to get
(and keep) the @sums right if they’re on the same page as the data
they’re working on.

ITERATIVE CALCULATIONS: This is a big red flag that something hasn’t
been thought through carefully.

MACROS: Do not use them. Period. (Some make exceptions for
mechanical tasks like printing.) Using macros in sheet computation
is an invitation to disaster. Macros are hard to write, and many
times harder to maintain than a well-written spreadsheet would be.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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: