John F' Raffensperger, PhD

1
New Guidelines for Spreadsheet Style

• John F. Raffensperger, PhD
• Dept. of Management
• University of Canterbury
• Christchurch, NZ
• j.raffensperger_at_mang.canterbury.ac.nz
• July 2001 EuSPRIG Conference

2
The current spreadsheet style

• Mostly specified by accountants in the
  practitioner literature.
• Many references from the past 15 years.
• Modular standard format data, formulas,
  output.
• Plus heading, date, file name, author, approval
  signoff, contents,error summary box,
  instructions, range names, validation
  formulas,no constants in formulas.
• If you use modular spreadsheets, you will avoid
  errors.
• People must be forced to used standard formats.
• Writing a spreadsheet is like writing a computer
  program.
• Cragg King (1993), Davis (1996) people do not
  use or like this style.

3
A new idea for spreadsheet style follow older
arts

• Text, graphics, and math have been in existence
  for centuries.
• Text graphics math spreadsheet.So
  rules of style for text, graphics, and math apply
  to spreadsheets.
• The new style Write a spreadsheet as text, math
  and graphics are written.
• 1. Make your spreadsheets read from left to right
  and top to bottom.
• 2. Be concise.
• 3. Format for description, not decoration.
• 4. Show all the information.

4
Rule 2 Be concise.

• Strunk White, Elements of Style,
  1979,Vigorous writing is concise.
• Higham, Writing for the Mathematical Sciences,
  1993,Do not use mathematical symbols unless
  they serve a purpose.
• Daellenbach, Systems and Decision Making,
  1994,A good model is a model that is as
  parsimonious as possible
• Tufte, Visual Display of Quantitative
  Information, 1983,Graphical excellence is that
  which gives to the viewerthe greatest number of
  ideas in the shortest timewith the least inkin
  the smallest space
• An old rule for text, math, and graphics. New for
  spreadsheets!
• Be concise with sheets, blocks, cells, and
  formulas.

5
Be concise with sheets.
Multiple sheets breed spurious cells, dislocate
related blocks,and make audit tools useless.
Edward Tufte, Envisioning Information, Micro/mac
ro designs enforce both local and global
comparisons and, at the same time, avoid the
disruption of context switching.
6
Multiple sheets are hard to navigateand make
data hard to find, even the lack of data.
Does Sheet have data? We must click to know.
7
Be concise with blocks.

• Structure the spreadsheet according to the
  business logic.
• Strunk White, Use definite, specific, concrete
  language.
• The primary data type should be concrete, not
  abstract.
• People or time,
• rather than data, formula, output.
• Align data so formulas can be copied across.

8
Posterised over multiple sheets.To change, click
3 sheets.To view, click-click.To audit, click,
store, click, recall.
Input Pfact Mon 10 Tue 22 Wed 39 Thu 5 Fri 5
Sat 2 Sun 79
Part A Pfact Jfact Kfact Lfact Mon 10 53 41 178
Tue 22 5 12 196 Wed 39 89 44 204 Thu 5 79 14 34
9 Fri 5 323 15 283 Sat 2 62 92 242 Sun 79 48 65
131
Part B Lfact Mfact Nfact Ofact Mon 178 3,223 1 2
Tue 196 123 0 3 Wed 204 123 0 4 Thu 349 999 1 7 Fr
i 283 0 0 99 Sat 242 4 0 8 Sun 131 222 0 9
9
Transposed, one sheet, duplicates deleted.A
smaller model is easier to read, print, and
audit.Only one sheet needs modifying. Formulas
can be copied across.
Mon Tue Wed Thu Fri Sat Sun Input Pfact 10 22
39 5 5 2 79 Part A Jfact 53 5 89 79
323 62 48 Kfact 41 12 44 14 15 92 65 Lfact 178 1
96 204 349 283 242 131 Part B Mfact 3,223 123 123
999 0 4 222 Nfact 1 0 0 1 0 0 0 Ofact 2 3 4 7 9
9 8 9
Tufte, Envisioning Information, In flatland,
after all, every opportunity to spread additional
data over an already-available dimension must be
cherished.
10
A complex flow is more likely. Shattered context.
Lots of problems matching references, especially
endpoints. Inanimate data.
11
Transposed, on one sheet.Formulas can be copied
across. Inputs affect formulas, left to
right.Data is animated - change d and observe
the effect instantly.

• Input Part 1 2 3 4 5 6 7 8 9
• b B1
• c,f C
• B2
• d D
• a A
• e E

Tufte, Envisioning Information, Constancy of
design puts the emphasis on changes in data, not
changes in data frames.
12
Be concise with cells.

• Between 1 and 4 of cells have errors.The
  chance a spreadsheet is wrong increases with the
  number of cells.Conclusion reduce the number of
  cells.
• Do not break up complex formulas, within
  reason.Would Euler, Pascal, or Dantzig use an
  unneeded variable?
• Erase dangling cells.
• Useless calculations.
• Validation formulas.
• Interpreted output.
• Nest and erase to reduce complexity and the
  number of cells.

13
A cell with no dependants is dangling.No other
cell needs it. Erase!
Cell L7 is dangling.It is an irrelevant
calculation.
14
Erase dangling validations when youre done.
Validations distract the reader and may have
errors.Like this one.
15
Dont interpret output.
Dont mix the model with its documentation. Use
formulas only for the model. Use concise constant
text for labels.
16
If a cell has just 1 dependent, nest and erase!
Minimise the number of characters over all
formulas. Try to make formulas reference
constants.
B18 B11 B16 B19 B2 B18
Better B19 B2 B11B16
17
Make formulas concise

• Original 3 formulas
• I13 E13 - G13'Production Variables'!B330.5
• D14 B14 C14
• E14 if(I13lt0, D14, D14I13)
• Step 1 Simplify first.
• E14 D14 if(I13lt0, 0, I13) D14 max(0,
  I13).
• Step 2 Nest and erase I13 into E14.
• E14 D14 max(0, E13-G13'Production
  Variables'!B33/2).
• Step 3 Move 'Production Variables'!B33 to the
  current sheet.
• E14 D14 max(0, E13-G13B33/2).
• Step 4 Nest and erase D14 into E14.
• E14 B14 C14 max(0, E13-G13B33/2).

18
Use sumproduct().
A vector product the long way is verbose.
Sumproduct() is compact.
19
Be concise with blank space.
Spreadsheet blank ? graphical blank. Make blank
cells look blank. Graphical blank cannot be
covered by nearby information. But a blank cell
may appear to have data that is really in another
cell. Is K32 blank?
20
Make cells with data look like they have data.
Graphical blank hides nothing.Spreadsheet blank
can hide information. I33 is not blank.
21
If cells depend on blanks, the spreadsheet is
perverse.
Graphic blank has only a visual effect. A cell
can be blank, but perversely another cell can
depend on it. It is wrong because it is
blank. This error is most common when precedence
arcs are(1) too long and(2) do not point the
way we read.
22
Show blank space with the grid.
Columns A and Cjust confuse the reader.
23
Dont try to idiot-proof it. Instead, make it
small.
11 screens, 5 sheets. versus 3 screens, 1 sheet.
24
The new style

• Write a spreadsheet as text, math and graphics
  are written.
• 1. Make your spreadsheets read from left to right
  and top to bottom.
• 2. Be concise.
• 3. Format for description, not decoration.
• 4. Show all the information.