|
|
|
|
© Duncan Williamson |
|
Duncan@duncanwil.co.uk |
|
July 2001 |
|
|
|
|
|
We all know that a spreadsheet is basically
a grid comprising cells in which we can enter |
|
Text |
|
Values |
|
Formulae |
|
Spreadsheets can be single sheet files,
multi sheet files (3D) … |
|
|
|
|
The Power of the Modern Spreadsheet |
|
|
|
An average modern computer can carry
out in one second more calculations than the great 17th
Century mathematician Pierre de Fermat did in his entire lifetime! |
|
|
|
|
|
|
In my spreadsheet there are |
|
|
|
332 built in functions |
|
|
|
53 are financial functions |
|
|
|
|
Function categories and examples |
|
|
|
|
Because a spreadsheet is so powerful,
it should come with a warning: |
|
|
|
This spreadsheet can seriously damage
your business |
|
|
|
|
|
|
|
Should we use a spreadsheet at all? |
|
|
|
Consider when to use one or more of the
following instead of a spreadsheet: |
|
|
|
Database |
|
Accounting package |
|
Word processor |
|
|
|
|
|
Spreadsheet design: before not after |
|
|
|
|
|
Start by taking a piece of old fashioned
paper and write down what you need, then design it and then check
it |
|
|
|
|
go through all of your data first so
that you know exactly what it looks like as you are designing
it |
|
|
|
|
|
have a piece of paper that is divided
into squares … don’t have squared paper? … make your own … paper
isn’t big enough? … join two or three pieces together! |
|
|
|
|
|
Identify Your Work |
|
|
|
Title |
|
Name of author |
|
Date of creation/latest revision |
|
Purpose |
|
Filename (and directory if appropriate) |
|
Software and version |
|
Indicate whether this is the final or
a draft version |
|
|
|
|
Title: Preparation of Income Statement |
|
Name of author: Duncan Williamson |
|
Date of creation/latest revision: 1 March |
|
Purpose: to demonstrate the construction
of … |
|
Filename (and directory): designing.xls |
|
Software and Version: MS Excel 2000 |
|
Indicate final or a draft version: final |
|
|
|
|
|
If the file is complex and/or large eg |
|
|
|
|
|
Inputs: A1:B42 |
|
Production reports: A1:J100 |
|
Packing and distribution reports: A1:C19 |
|
… |
|
|
|
|
|
Document Your Spreadsheet: |
|
Use
Comments and Explanations … |
|
to ensure we are using the correct spreadsheet |
|
to help understand the inputs, relationships
and outputs |
|
to help make changes and updates and
additions |
|
|
|
|
|
We can document our spreadsheet in a
number of ways eg |
|
|
|
Comments in cells |
|
Explanations in text boxes |
|
Comment columns or ranges |
|
|
|
|
|
|
|
|
|
|
Or just type directly |
|
in a cell |
|
|
|
|
|
|
What are the inputs in this case? |
|
|
|
|
|
Outputs |
|
Read from left to right and top to bottom |
|
Clear and uncluttered |
|
Colours |
|
Align labels to what follows |
|
One sheet or more |
|
Don’t hard code |
|
Totals should be near to their data |
|
No empty cells: use “.” |
|
|
|
|
|
Read from left to right and top to bottom |
|
|
|
Our eyes are accustomed to working in
these directions and they will be confused if a spreadsheet tries
to make us work in a different way |
|
|
|
|
|
|
|
|
|
|
Align labels to what follows |
|
|
|
|
|
|
Don’t Hard Code |
|
|
|
=B4+C15/(125+14+53+369) |
|
|
|
|
|
=B4+C15/(SUM(D1:D4)) |
|
|
|
|
|
|
|
Totals should be near to their data |
|
|
|
if we are preparing a table that has
column and/or row totals, make sure that the totals are physically
close to those columns and rows |
|
|
|
|
|
No empty cells: use “.” |
|
|
|
How can we tell that an empty cell has
been evaluated? We can’t, so forcing the result in each cell to
be expressed as some character or other is a valuable thing to
do: it doesn’t matter that the character is ‘0’ or ‘-‘ or ‘.’
Or ‘*’ as long as it exists |
|
|
|
Notes