Debate: spreadsheet style and design
John F. Raffensperger
I am a relative newcomer to the newsgroups, though an old hack with Excel. I
have seen many questions here that suggest to me that the question writers
are building their spreadsheets in a way that will likely hinder them down
the road, and make their spreadsheets less readable, and more error-prone.
People have written heaps about "How to use Excel," but there is very little
written about "How you *should* use Excel." Making an analogy to text, it is
the difference between the MS Word manual and Strunk & White's
_Elements_of_Style_.
I am interested in hearing some debate about these issues. I have written at
some length about spreadsheet style at my commercial site,
http://spreadsheetstyle.com, and also at my academic site,
http://www.mang.canterbury.ac.nz/people/jfraffen/. Particularly the latter
article is quite long, but I would invite feedback on them, as I imagine
that some of what I have to say is not exactly "common wisdom," and may even
be considered by some to be heresy. For example, I suggest that most
spreadsheets should be written in a single sheet; multiple sheets are to be
avoided. Also, I suggest that formulas in different cells should be
combined, contradicting the adage "split complicated formulas into multiple
cells." And I don't even use the word "user" very often, preferring instead
"spreadsheet writers" and "spreadsheet readers."
My logic for these ideas is not my own taste, but were developed from an
extensive study of style in graphics, text, mathematics, and math modeling.
That is, "How is style problem X handled in graphics?" Then I tried to make
the analogy to the spreadsheet. Believe me, now I cringe when I see my old
spreadsheets.
So I would be interested in knowing whether most people think I am
completely off my rocker, or whether the ideas may have merit.
If you join the debate, I would plead that you try to support your ideas
rather than propound something based on taste. Most of my ideas came from
Strunk & White, Edward Tufte's gorgeous book "The Visual Display of
Quantitative Information," and study of dozens of articles about
spreadsheets in the academic, accounting, and professional literature. I do
not expect you to be able to quote chapter and verse of, say McConnell's
"Code Complete", but some careful logic would help.
Anyone want to discuss this?
Warm regards to all,
Fritz
-------
John F. Raffensperger, PhD
http://SpreadsheetStyle.Com
Harald Staff
welcome.
Your article has many very strong points, I'll take it home and study it
carefully before I eventually comment it further. Except this: I strongly
disagree on the matters of protection and fool-proofing. I have tried about
every possible degree of freedom for my users, and my conclusion has always
been: If you allow users to overwrite formulas or delete important rows then
they will. One can not foolproof enough.
Protection "reduces the reader's confidence in the spreadsheet" you say.
Yes, I feel that way since I know my way around Excel, and the first thing I
do with a protected file is try to unprotect it. But I have quite a few
users that don't have confidence in a worksheet if they "may do anything
wrong", they have confidence in it when I say "you can not destroy anything
here" -and this is not a thing I dream up in my superiority, this is what
they ask me to make for them.
This said, protection can be as friendly, smooth and logical as everything
else within a spreadsheet, it does not have to be angry modal messages or
heavy colors. I've used a few macros that re-inserts overwritten formulas
and found that to be a sensible way of locking. There is a also a distinct
difference between an entry disabled cell and a hidden formula.
Looking forward to the in-depth reading. (I will probably say "There are
times when it is sensible to split a job onto multiple worksheets" when I'm
done :-)
Best wishes Harald
John F. Raffensperger <j.raffe...@mang.canterbury.ac.nz> skrev i
news:e6TagFalAHA.2172@tkmsftngp05...
Harald Staff
ideas, but I somewhat feel that "spreadsheet" in your writing is defined too
much as the quite common "multiply rightwards and sum at the bottom" kind of
sheet -secretary stuff. There are definitely different tasks -and "There are
times when it is sensible to split a job onto multiple worksheets". ;-) I
won't argue this in public unless a livey discussion appear- so far it's
surprisingly quiet...
Keep up the good work.
Best wishes Harald
Harald Staff <harald...@nrk.no> wrote in message
news:uTUqWMclAHA.1220@tkmsftngp05...
(snip)
John F. Raffensperger
Thanks for your kind words.
I will re-think the issue of protection - there are no obvious analogies to
that in text, math, or graphics, except "don't insult your reader," and I
generally presume a literate audience. Like you, I always remove protection
immediately. However, when we are creating a spreadsheet for purely clerical
input, perhaps you are right.
I feel much more strongly about left-to-right and top-to-bottom. I would be
interested in seeing examples where violating that rule is obviously correct
and more easily interpreted by the reader, compared to following the rule. I
am aware of a very few plausible exceptions, but even those are only
plausible, not demonstrably and obviously better.
Yes, I am guilty of thinking that a spreadsheet is ideally defined (or
better *designed*) much like the "quite common 'multiply rightwards and sum
at the bottom' kind of sheet -secretary stuff." When the spreadsheet is
designed otherwise, it has been made unnecessarily hard. I would go so far
as to say that *most* spreadsheets can be written this way, to look very
ordinary. The writer is tempted to add colors, borders, input boxes, charts,
etc., to make it look more important, when really all that is needed is
"multiply rightwards and sum at the bottom."
Feel free to write to me directly, if you wish. Thanks again for taking the
time to look at my material, this is very generous of you.
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
Harald Staff <harald...@eunet.no> wrote in message
news:OhTU4AglAHA.2052@tkmsftngp03...
>
lee kowalkowski
the commercial site more enjoyable than the academic site, but the latter
gave good examples of the guidelines.
We can always find exceptions to guidelines, as they depend on context.
Such as the "never hide cells" rule (does this also apply to rows and
columns?) - I have a sheet where the user enters football teams. The names
of all teams are in hidden rows above where they are entered so that the
autocomplete will activate after the first few letters are entered. Showing
these cells would lead to confusion because their function is not apparent
to our everyday users.
Reading your work would benefit most. It instils a heightened awareness of
readability issues.
-LK-
"John F. Raffensperger" <j.raffe...@mang.canterbury.ac.nz> wrote in
message news:e6TagFalAHA.2172@tkmsftngp05...
Charles Williams
easily understand the spreadsheet is absolutely correct (IMHO).
> I somewhat feel that "spreadsheet" in your writing is defined too
> much as the quite common "multiply rightwards and sum at the bottom" kind
of
> sheet -secretary stuff.
I agree with Harald: does seem somewhat oriented toward small and simple
spreadsheets.
some additional thoughts:
- design for the audience: both the users and the circumstances of use
- two short sentences (formulae) are better than one long one
- ease and clarity of navigation are important: dont ignore the role of
sheets in this
- designing for change is often a critical factor
- nothing wrong with having two sheets open at the same time if the user
knows how to do this
- short arcs of precedence, whilst a good idea, get somewhat meaningless on
anything other than small spreadsheets
- protection against accidental change is often important: use protect
without a password
- using fewer sheets also tends to calculate faster.
Charles
_____________________________________
Decision Models Ltd,17 Binswood Avenue,
Leamington Spa,Warks CV32 5SE, UK
Tel: (44)01926-334289 Fax: (44)01926-881487
John F. Raffensperger
Your example of hiding information to use Autocomplete is a new one! That is
definitely interesting. You have hidden irrelevant information to provide
more function. Cool. I'm actually kind of dumbfounded.
Okay, I may be convinced on this special case. Please note, folks: he has
hidden *irrelevant* information so he could provide *added functionality.*
Still, if you give the spreadsheet to a fellow developer without documenting
your Autocomplete trick, I doubt he will guess why you put in all those
names.
I stand by the general rule, but I think I need to modify it to be more
precise, something like, "Don't hide real information." And maybe, "Document
hidden cells."
Lee, thanks for your comments. You're making me think!
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
lee kowalkowski <lee.kow...@idea.com> wrote in message
news:#qjZALnlAHA.1348@tkmsftngp02...
>
John F. Raffensperger
Your observation that these ideas seem oriented to "small and simple
spreadsheets" contains a bit of circular logic. I am saying that most
spreadsheets are too big and can easily be made smaller.
>- design for the audience: both the users and the circumstances of use.
On the issue of protection, I concede for that one. However, what is easy to
understand for Bill Gates will probably also be easy to understand for
Grandma Smith. You see, I have this very strong idea that in some sense,
there is an optimal design for each spreadsheet. I realize this is a bold
statement, but that is where I'm going. For the most part, I am inclined to
reject the idea of designing differently for different audiences. The
problem is just to make it as clear as possible, no matter who is reading
it. Note, I do not say for a different *purpose*, and that is an important
distinction. Design a spreadsheet differently for a different purposes, not
for different audiences.
One thinking correspondent, emailing directly to me, compared writing
spreadsheets to writing text. This is a good analogy (though incomplete - we
need to add rules of style from graphics and math modelling). I think that
writing text for a specific audience is good, but requires a mastery that
may be beyond many of us. Furthermore, I would be that the greatest masters
simply write, and do not think much about their audience, and they are great
because their language is understandable by all.
Such masters of the English language are far and few between, and mastery
may be an unreasonable goal. I supervise grad students. They can't write! It
is a chore just getting them to push F7 in Word. More often than not, I tell
someone, "Do X." They say in broken English, "But X not sound good!" I have
to reply, with a straight face, "Get it right and get it clear. Then get it
concise. Then quit. Elegance is beyond me and you." You might be amused by
my "Instructions to graduate students," on my academic site
http://www.mang.canterbury.ac.nz/people/jfraffen/.
Similarly, the problem with spreadsheets is not making them elegant. The
problem is getting even them half-way reasonable. There are no rules
whatever about how to write spreadsheets, and the "common wisdom" out there
(such as the use of multiple sheets and breaking up formulas into more than
one cell) directly violates rules of style from established arts. There is
no "Museum of Cognitive Art," as Edward Tufte put it. Besides, a spreadsheet
is not poetry, but technical writing.
Spreadsheets are hard to get right - not just hard, but very hard. The
studies show frightening results - the percent of wrong spreadsheets is just
scary (see http://panko.cba.hawaii.edu/ssr). So I think we spreadsheet
writers (as a field or profession) are still at the grammar school level of
"How to write a spreadsheet," even with developers who can do VBA in their
sleep. Ten years from now, I hope we will be saying, "Try to make all
formulas reference constant data," as much as we say, "Don't put constants
in formulas."
>- two short sentences (formulae) are better than one long one.
No! Unlike English, breaking up a formula into two cells increases the total
number of characters, and adds an abstracting layer of indirectness. After
combining two or more formulas, the writer with a bit of algebra can often
simply formulas considerably. Try to make formulas reference constants.
>- ease and clarity of navigation are important: don't ignore the role of
sheets in this.
No! Multiple sheets wreak havoc on ease and clarity of navigation. Did you
see http://spreadsheetstyle.com/style/tip10.htm?
>- designing for change is often a critical factor.
Yes, I agree.
>- nothing wrong with having two sheets open at the same time if the user
knows how to do this.
No, there are many things wrong with it. See
http://spreadsheetstyle.com/style/tip10.htm.
- short arcs of precedence, whilst a good idea, get somewhat meaningless on
anything other than small spreadsheets.
No. Short arcs of precedence are most important on large spreadsheets, just
harder to do. Besides, if you have a big spreadsheet, your on-going goal
should be to make it smaller.
>- protection against accidental change is often important: use protect
without a password.
Reluctantly, I must agree.
>- using fewer sheets also tends to calculate faster.
You're just giving me more ammunition! Thanks, I didn't know that.
Hmm, looks like we disagree on a number of points. Have you any examples
where multiple sheets provide an overwhelming and definitive advantage?
Lee got me to see hidden rows in a new light!
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
Bruce Sinclair
:>- two short sentences (formulae) are better than one long one.
:No! Unlike English, breaking up a formula into two cells increases the total
:number of characters, and adds an abstracting layer of indirectness. After
:combining two or more formulas, the writer with a bit of algebra can often
:simply formulas considerably. Try to make formulas reference constants.
Most Of the problems I have seen with spreadsheets come from trying to cram
2 lines of formula into a cell. I tell people that it doesn't matter how
big the spreadsheet is as long as you can understand it and I try to get
them to break it up into sensible parts (eg inputs; outputs then
inputs-outputs) because it's more easily understood than the whole thing
together. It also stops sillinesses like outputs-inputs ...
which can be surprisingly harder to spot when all together.
The other essential is good names for rows/columns/ranges etc.
I am reminded that it is often easier to reprogram from scratch rather than
attempt to decipher someone elses program ... and I feel (with no data -
sorry :)) that spreadsheet design/execution is rather like programming.
Trying to read someone elses work usually elicits "why on earth did they do
that !" noises :)
:>- ease and clarity of navigation are important: don't ignore the role of
:sheets in this.
:No! Multiple sheets wreak havoc on ease and clarity of navigation. Did you
:see http://spreadsheetstyle.com/style/tip10.htm?
Up to a point they can make it easier ... after that i'll agree entirely :)
What that point is ... I'm not sure but I don't think it's many sheets :)
:>- designing for change is often a critical factor.
:Yes, I agree.
I'd settle for simply thinking about it a bit before launching into it.
Many people have made it really hard to do easy things by the way they set
up their data initially. It's often easier to fix the data - then it all
becomes clear.
I like to see their logic in the sheet ... whether I think it's right or
not, at least you can understand it then - and it definitely helps when
fixing things :)
Bruce
--------------------------------------------------------------------
Oook !
NOTE remove the not_ from the address to reply. NO SPAM !
John F. Raffensperger
Another man from down under!
> :>- two short sentences (formulae) are better than one long one.
> :No! Unlike English, breaking up a formula into two cells increases the
total
> :number of characters, and adds an abstracting layer of indirectness.
After
> :combining two or more formulas, the writer with a bit of algebra can
often
> :simply formulas considerably. Try to make formulas reference constants.
>
> Most Of the problems I have seen with spreadsheets come from trying to
cram
> 2 lines of formula into a cell. I tell people that it doesn't matter how
> big the spreadsheet is as long as you can understand it and I try to get
> them to break it up into sensible parts (eg inputs; outputs then
> inputs-outputs) because it's more easily understood than the whole thing
> together. It also stops sillinesses like outputs-inputs ...
> which can be surprisingly harder to spot when all together.
> The other essential is good names for rows/columns/ranges etc.
Oooo, I have a long list of reasons why range names are criminal (another
heresy of mine), but let's talk about that another time. I don't understand
what silliness you are referring to. What do you mean by "sillinesses like
outputs-inputs ..."?
One of my main points is that smaller is better. Conciseness is good in ALL
other arts (except postmodern philosophical analysis). Conciseness is valued
in text, graphics, mathematics, and math modelling, all of which apply to
spreadsheets. How is it suddenly better to be verbose in a spreadsheet?
I agree that long formulas can be hard to understand sometimes. But a
well-written formula can still be both long and understandable. Furthermore,
when the writer combines formulas, there are usually opportunities to
simplify algebraically.
> I am reminded that it is often easier to reprogram from scratch rather
than
> attempt to decipher someone elses program ... and I feel (with no data -
> sorry :)) that spreadsheet design/execution is rather like programming.
> Trying to read someone elses work usually elicits "why on earth did they
do
> that !" noises :)
Why is it easier to reprogram from scratch? It is because it was horrible
and hard to understand in the first place. You are making a strong case for
readability - none of us like deciphering someone else's spreadsheet. This
is because most of us do not know how to write a spreadsheet well!
> :>- ease and clarity of navigation are important: don't ignore the role of
> :sheets in this.
> :No! Multiple sheets wreak havoc on ease and clarity of navigation. Did
you
> :see http://spreadsheetstyle.com/style/tip10.htm?
>
> Up to a point they can make it easier ... after that i'll agree entirely
:)
> What that point is ... I'm not sure but I don't think it's many sheets :)
I agree. I just think that the breakpoint is 1.0 sheets. Can you suggest an
example where multiple sheets is obviously the right thing to do?
> :>- designing for change is often a critical factor.
> :Yes, I agree.
>
> I'd settle for simply thinking about it a bit before launching into it.
> Many people have made it really hard to do easy things by the way they set
> up their data initially. It's often easier to fix the data - then it all
> becomes clear.
> I like to see their logic in the sheet ... whether I think it's right or
> not, at least you can understand it then - and it definitely helps when
> fixing things :)
I strongly agree. What is important, and what I am trying to get across, are
the visual structures that *show* the logic (right or wrong). Make your
spreadsheet read from left to right and top to bottom. Have short arcs of
precedence. Use one sheet, if at all possible. Format constants differently
from formulas. Those few rules would solve a lot of problems.
Bruce, thanks for taking the time to think about this, and for your
thoughtful ideas. Could you come back with the bit about "sillinesses like
outputs-inputs ..."?
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
Harald Staff
I'm glad this thread is growing, interesting and well thought. First let me
say that I strongly support all your ideas regarding clarity, readability,
"stanhdardization", visual design and formula readability. And if the task
is "multiply rightwards and sum on bottom" then I support all your rules.
The task is very often that.
But quite a few times the task is datamining. More times than it is solved
as that.
Example: My department have 100 projects a year, each generating one to 40
Tv shows and of course a budget or two each. Big boss is interested in
bottom line only (and this alone tells me: don't put it at the bottom :-).
If OK then finished and of to another meeting. If very good or very bad then
she needs bottom line for each project. And bottom line for each budget
group in the deviating project. And so on. This calls for summaries that
look like summaries, "don't bother me with detail I don't want".
I have done this task in multiple ways during the years. I think collapsing
rows (grouping) is the most intuitive solution for a reader, a separate
summary sheet may be a way to go -I find this awkward and vulnerable but it
can be "pretty". My definite favorite is a Pivot table.
A pivot table needs a very strict datalist spreadsheet; one information on
one row only. I have converted quite a few very well designed sheets into a
datalist format. The result data are almost unreadable compared to the
origin, but the table can answer anything instantly and that was the task.
Is a spreadsheet the best tool for datamining ? I'm not sure. I guess
sometimes it is...
Best wishes Harald
John F. Raffensperger <J.Raffe...@mang.canterbury.ac.nz> skrev i
news:Opbf93hlAHA.2048@tkmsftngp02...
John F. Raffensperger
Does your boss view the spreadsheet on screen or on paper? The difference
may not be significant, I guess.
Let me think about the problem of summaries. If the big boss wants to see it
on-screen, big boss will want to see more if needed. Show summaries first,
but show detail on demand. I have to think about it some more.
A good exercise! Thanks, Harald. Give me a few days.
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
"Harald Staff" <harald...@nrk.no> wrote in message
news:uEgAk9xlAHA.2408@tkmsftngp02...
Charles Williams
gives the correct answer to its users.
Therefore I am in strong agreement with you on the importance of ease of
understanding.
I also agree that fewer sheets are often better than more sheets.
Single-sheet spreadsheets are great for small relatively static problems.
But IMHO your design proposals would cause navigation, useability, testing,
auditability and maintenance problems on large/ data-driven spreadsheets.
( Although its possible to get round some of these problems using a dynamic
spreadsheet generator/navigator etc)
> Your observation that these ideas seem oriented to "small and simple
> spreadsheets" contains a bit of circular logic. I am saying that most
> spreadsheets are too big and can easily be made smaller.
>
Smaller is better as long as it doesnt impact ease of understanding and
testing.
I have looked at your suggestions for making spreadsheets smaller, and they
are excellent for small multi-sheet spreadsheets, but they just dont help
with the sort of spreadsheet I am involved with.
> Spreadsheets are hard to get right - not just hard, but very hard. The
> studies show frightening results - the percent of wrong spreadsheets is
just
> scary (see http://panko.cba.hawaii.edu/ssr).
I agree. Most studies show that very few spreadsheets are rigourously and
systematically tested and audited, let alone designed to be tested.
> >- two short sentences (formulae) are better than one long one.
> No! Unlike English, breaking up a formula into two cells increases the
total
> number of characters, and adds an abstracting layer of indirectness. After
> combining two or more formulas, the writer with a bit of algebra can often
> simply formulas considerably. Try to make formulas reference constants.
>
Simpler is better, but again, this suggestion is mostly only feasible on
small spreadsheets, and ignores the problems of testing and debugging
megaformulas.
Breaking large, incomprehensible, difficult to test things into small,
simple, easily-understood, step-by-step testable things works well for me!
> >- ease and clarity of navigation are important: don't ignore the role of
> sheets in this.
> No! Multiple sheets wreak havoc on ease and clarity of navigation. Did you
> see http://spreadsheetstyle.com/style/tip10.htm?
>
yes I have looked at that:
good advice if you can see the structure of your spreadsheet at 25%. I just
don't work with any spreadsheets that small.
Also seems to ignore that you can navigate the auditing arrows to other
sheets by double-clicking.
Also ignores the problem of how the user is supposed to find their way
around (navigate) a large spreadsheet.
Also makes it more difficult to assemble a correct and coherent set of
inputs.
regds
William Hopkins
what taking a service call mid-session will do to you.
I personally wouldn't want to see a debate started in the microsoft.public
newsgroups, no matter how valid or not the points made are to be. A better
forum might be comp.apps.spreadsheets or similiar. Most come to these Excel
newsgroups to get specific application related help when a procedure, formula,
or function doesn't work as they expected it to, and wouldn't likely benefit
from such a debate and the messages they'd have to wade through. OTOH, great
'spreadsheet' tips might be missed by those using a competing spreadsheet
product in that they'd never visit the debate in this newsgroup.
As for shuddering, while I never may like the looks of something I wrote for
pay back with Visicalc, I never shudder when I think of the bills I paid with
the compensation.
William Hopkins
out of microsoft.public.excel.<any> soon, as the points being made aren't
really specific to Excel.
1. These points aren't really answering users' questions regarding Excel's
functions, formulas, or procedures which might not be working as those users
would expect.
2. Users of competing spreadsheet products would miss out since it's probably
unlikely they'll be picking up microsoft.public.excel.<whatever>.
3. Most of the MVP's here haven't taken time to respond, as they're probably
just wading through/skipping the thread looking for those questions they can
be helpful at.
4. comp.apps.spreadsheets would probably have a better audience for such a
debate.
On Thu, 15 Feb 2001 08:17:40 +0100, "Harald Staff" <harald...@nrk.no>
wrote:
Harald Staff
> I personally wouldn't want to see a debate started in the microsoft.public
> newsgroups, no matter how valid or not the points made are to be. A
better
> forum might be comp.apps.spreadsheets or similiar.
Maybe so, but the traffic there is second to none due to all the application
spesific groups that has appeared. And most of those few are Excel users
anyway.
> Most come to these Excel
> newsgroups to get specific application related help when a procedure,
formula,
> or function doesn't work as they expected it to, and wouldn't likely
benefit
> from such a debate and the messages they'd have to wade through.
Not quite true. There is a big mass of "quiet" readers that browse around
for useful tricks. Many that post a problem and get it solver -hopefully-
also read more than their own thread for the same reason.
I won't deny that this thread is a litle unusual and it does not provide the
copy-paste-use kind of solutions that is more common here. But I feel that
this topic may be a little more important also to Excel users than the 74th
repost of "go tools > options and unckech fixed decimals" is. All it
occupies is a little -collapsable- space in a newsgroup.
But I sincerely apologize for posting to this thread if everyone else feels
different.
Best wishes Harald
lee kowalkowski
tips are Excel orientated. John mentions the pages are about "How you
*should* use Excel".
Also, news.microsoft.com is my only news provider at present (apart from
inferior web-based providers which are painful to use). So if it were in
comp.apps.spreadsheets, I would have never seen it. :-)
-LK-
"William Hopkins" <whop...@bellsouth.net> wrote in message
news:3a8bf62...@news.mia.bellsouth.net...
> I personally wouldn't want to see a debate started in the microsoft.public
> newsgroups, no matter how valid or not the points made are to be. A
better
> forum might be comp.apps.spreadsheets or similiar.
> great 'spreadsheet' tips might be missed by those using a competing
spreadsheet
> product in that they'd never visit the debate in this newsgroup.
> On Tue, 13 Feb 2001 22:41:14 +1300, "John F. Raffensperger"
> <j.raffe...@mang.canterbury.ac.nz> wrote:
>
> >Dear all,
> >
> >I am a relative newcomer to the newsgroups, though an old hack with
Excel.
> >
Alan B. Pearce
>for useful tricks. Many that post a problem and get it solver -hopefully-
>also read more than their own thread for the same reason.
Of which I am one. It has been good to see a different thread than the oft
repeated ones from people that come when they have a problem to solve, and then
disappear when solved.
>I won't deny that this thread is a litle unusual and it does not provide the
>copy-paste-use kind of solutions that is more common here. But I feel that
>this topic may be a little more important also to Excel users than the 74th
>repost of "go tools > options and unckech fixed decimals" is. All it
>occupies is a little -collapsable- space in a newsgroup.
It also gives those of us who do not do a lot of sheet design some insight into
ways of doing it faster/better/cheaper etc. The instant problem fix is not
always the most interesting thread. Another example was the probability thread
of combination locks recently, although that seemed to be too theoretical, and
not based on a real world methodology.
Chip Pearson
newsgroup. There isn't enough attention paid to broad topic of
spreadsheet/workbook design. Most books, websites, etc, concentrate on how
to make each little piece work properly -- syntax for formulas, VBA
procedures, command bars, etc -- without addressing the larger issue of how
to put all these components together to create a usable system.
I've seen lots of workbooks in which each piece was fine. Each formula was
correct, and VBA procedure was correct, etc, but when taken together, the
result was a rather awkward and hard to use application.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Harald Staff" <harald...@nrk.no> wrote in message
news:e$Oceh$lAHA.1568@tkmsftngp03...
Hans Knudsen
Jon
Please accept that I write to you directly as I saw someone very strongly argued against such debate in the newsgroup.
My message to John may have been a bit too harsh. I see that now and I regret it. But I felt somewhat instigated and thought (maybe unjustified) that his tip number 10 (Put it all in one sheet) was rather theoretical and unrealistic in a company where you need to have things done.
I absolutely agree to most of what is mentioned in his articles, and I also think I acknowledged that in my message. Almost the only point in dispute as far as I am concerned is his tip number 10. I agree that fewer sheets are better than more, but I absolutely don't agree that one sheet in all circumstances is the optimal number. Far from.
I will maintain that I couldn't get my job done if I had to follow that advice.
Our employee urge us to get the most out of the many, many copies of Excel which he bought for a lot of money. I do not feel John's tip 10 has much to offer in this respect.
Regards
Hans Knudsen
----- Original Message -----
From: Peltier
Newsgroups: microsoft.public.excel.misc
Sent: Friday, February 16, 2001 3:39 AM
Subject: Re: Spreadsheet style and design
Hans -
I spent a while last week on John's site, and while I cannot use all his
suggestions all the time, they are good guidelines for construction of
sheets that someone else has to use and understand. My own personal
workbooks are often convoluted, with multiple sheets containing data,
parameters, etc. But reading John's suggestions is a good reminder that
other people I work with, most of whom are not facile in Excel, need to
understand what the heck I'm doing.
- Jon
Hans Knudsen wrote:
>
> I sent a message like the below about twelve hours ago but still it does not seem to show up so I will send it again. My apologies should it appear twice.
>
> (English is my second language - just in case of any uncommon usage)
>
> First of all; I agree to most of what Harald Staff has written already. I welcome your article "The New Guidelines for Writing Spreadsheets", your "10 Tips to improve Your Spreadsheet Style" and your invitation to feedback on them.
> I too have finished reading of the above and I too support most of the ideas, but definitely not what you yourself call the most controversial, your tip no. 10, "Put it all in one sheet". I shall elaborate my point of view shortly.
>
> It might be of relevance to write a couple of lines on my spreadsheet experience. Since the days of LOTUS Symphony I have been what I think you would call a "spreadsheet writer". I have used (written) spreadsheets more hours daily for many different tasks, mainly within accounting and tax in a large multinational company with about 50 subsidiaries around the world. Let me say that I have absolutely no knowledge of neither the two books you mention nor operations research so what follows is based on practical knowledge.
>
> I can not know if the "quite common 'multiply rightwards and sum at the bottom' kind of sheet -secretary stuff." is the way that most people in this forum use Excel, but I can say for sure that it is not the way we use Excel in the company where I am working. I simply could not resist joining this debate as I saw your "Put it all in one sheet" tip as a kind of provocation. It simply got me to wonder how I should be able to do my work if I had to follow your advice.
>
> I may not be a typical Excel user (writer), but I will give an example which hopefully will support that I am justified in saying that (for me at least) multiple sheets are really a good thing.
>
> Example
> I use Excel to prepare monthly royalty statements to a foreign licensee for about 50 companies. I built this model by finishing one sheet (for one company) including test of formulas, formatting (much in accordance with the advice in your articles) etc. Then I copied this sheet to other 49 sheets. Finally I made a summary sheet (named Summary) showing total royalty to be paid for the month per company. The most meatful formula in this sheet is what I think you would call a monster:
>
> =ROUND(INDIRECT($A9&"!$F$27"),0)
>
> where A9 (in sheet Summary) holds the sheetname for the company in question and F27 (in the individual company sheets) holds total royalty for the month.
> Furthermore I made a print macro so that by the click of a button you get a print of all 50+ pages.
>
> Now I have a model which I can use for monthly reporing. Of course it was not that easy to get it all work the first time, but I can assure you that now it is a real time saver. I am fully aware that Excel is not a word processor but none the less I often use Excel for reporting (this makes it difficult to follow your advice to use the same column width), that is for both text and numbers. I prefer to initially use some more time on formatting so that it looks as nice as if it was set up in Word. Then I have "in all future" the great advantage og much better calculation facilities.
> I can not see why in such a situation I should avoid multiple sheets. How should I avoid short arcs of precedence even if I place it all in one worksheet. Or do you really mean that I should split it up in 50 xls files?
> I have lots of workbokks with multiple sheets and I share many of them with colleagues in companies around the world without great problems, but to advantage for all of us.
>
> That was but one example. I could give lots of others but this must be enough for now.
>
> Regards
> Hans
John F. Raffensperger
You are a gentleman, indeed. Thanks for being part of our debate. Yes, Tip
10 (http://spreadsheetstyle.com/style/tip10.htm) is provocative. I stuck my
neck out with that one! Your =ROUND(...) formula is not a monster, but an
ingenious formula to solve that particular problem. I wouldn't dare call
your spreadsheet a monster without seeing it.
To everyone:
Tip 10 is definitely the most controversial, as I obviously expected it
would be. I wish we could all sit together in a big room, with a half-dozen
spreadsheets available, up on a big screen (and some dark beer), where we
could argue over these a bit more real time, all looking at the same
spreadsheets.
Would anyone be willing to post a spreadsheet where multiple sheets make
sense? I would welcome the challenge of reviewing it, and then posting
either my obviously much better sheet or my humble retraction. If one or two
others did the same, it would add tremendously to our debate.
William, I took your message to heart (perhaps too quickly as a new
newsgroup reader), and I stayed away for a day or two, thinking the fun was
over. But it looks like there is a general desire to continue the thread.
Certainly you would be welcome to voice your suggestions! Thanks to all who
have participated in this debate thus far.
Who wants to start arguing about range names? Here I go! Range names are of
the devil! (-;<
What is wrong with range names?
1) They require a separate chore to debug - printing a table of range names,
and doing a match, one cell at a time.
2) They can point to the wrong cell, and this problem is less identifiable
than an address.
3) The range names can get long and complicated, and can be hard to
remember - "owners_discount_rate" - or was it "Owners_discount_rate".
4) There is the range name that I just disagree with, such as "Print_range".
I *never* want to print out what they want me to print out.
5) Range names sometimes collect garbage. Select Insert, Name, Define, and
there is gibberish in the range names. (But I admit that a bug in Excel is
not a good argument against a certain style.)
6) Range names are extremely hard to modify. They have to be deleted, then
re-defined.
7) They only copy absolutely, when I often wish they would copy relatively.
There are more reasons, but I'm sure this is enough to generate some debate,
if not some flames!
A perverse way to use a defined name (not a range name per se) is to define
a constant with a name. Select Insert, Name, Define. Where it says "Refers
to:" type in 1. Give it the name "You_are". Now, wherever you type
"=You_are", you get the value 1. Talk about hidden constants! This is worse
than putting a constant in a formula.
Instead of range names, formulas can be made clear with short arcs of
precedence and good labeling.
Last comment: The very best book to read, if you are interested in
spreadsheet style, is Edward Tufte's book, "The Visual Display of
Quantitative Information." It is probably in your local library, and you
will find it reads like a comic book. You'll finish it in three hours, and
most likely come away completely changed.
Warm regards to all,
Fritz
-------
John F. Raffensperger, PhD
http://SpreadsheetStyle.Com
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
http://SpreadsheetStyle.Com
"Hans Knudsen" <hans.k...@mail.tele.dk> wrote in message
news:#ZEDH8EmAHA.1892@tkmsftngp05...
After having seen Chip Pearson's message in this thread I decided to post a
12 line message I earlier today sent to Jon Peltier in a private message.
This message is copied below.
Jon
Please accept that I write to you directly as I saw someone very strongly
argued against such debate in the newsgroup.
My message to John may have been a bit too harsh. I see that now and I
regret it. But I felt somewhat instigated and thought (maybe unjustified)
that his tip number 10
...
respect.
Regards
Hans Knudsen
----- Original Message -----
From: Peltier
Newsgroups: microsoft.public.excel.misc
Sent: Friday, February 16, 2001 3:39 AM
Subject: Re: Spreadsheet style and design
Hans -
I spent a while last week on John's site, and while I cannot use all his
...
Niek Otten
Nice article!
I agree with the meaning behind most of the guidelines you give: make the
spreadsheet understandable and readable.
I do not always agree with the resulting guidelines/rules; maybe it depends
more on the nature of the data, calculation rules and the intended use of
the spreadsheet than you think.
I write spreadsheets that calculate Life insurance. They are used to test
and verify high-speed calculation programs being developed in C.
The spreadsheets can be checked by our actuaries: most of them wouldn't be
able to check the C code.
This use leads to several conflicts with your rules.
>Therefore, to write clearly, each formula should depend only on cells above
and to the left.
My spreadsheet has a row for each month. A row starts with a premium being
paid. That is the start value. In subsequent columns costs are calculated
and subtracted, some 150 columns. At the end of the month interest is added:
the final value for that month.
The start value in the next row (600 rows) is the final value of the row
before; the arc of precedence is both long and pointing to the left. But
this is a very natural tabular format that is easily understood by all
involved.
>So be concise in the number of sheets.
A spreadsheet is not just used: it has to be built first and most likely it
will need some maintenance every now and then. I think spreadsheets should
not just be easy to read, they should also be easy to build and maintain and
the risk for errors should be minimised.
Suppose I have the table I described above and another matrix in which I
calculate disability probabilities on the same sheet. Following your advice
it is located beneath the first one.
I check the first table and I find that two formulas (so two columns) can
easily be integrated to one. I delete the now superfluous column. Now I have
inadvertently also deleted a valuable column in the disability calculation.
My advice: New structure? New sheet. Sheets are like chapters in a book. New
subject? New chapter.
>If a cell has just one dependent, nest and erase!
I strongly disagree; depends entirely on the use of the data. If the sales
commission is wrong, is it because I calculated the duration wrongly, or
because I missed one step in the percentage table or because I forgot to
subtract a threshold value? The result of this spreadsheet is not just one
figure: all the intermediate results are needed as well for the purpose it
was built for.
>Try to put division at the end of the formula.
I find it more important that the formula follows the specifications. I am
not in a position to tell our actuaries how they should write their specs.
But if I follow those specs as closely as possible, correctness of the
spreadsheet is easier to prove.
>Do not use abbreviations, not even for ones that "everybody knows."
I think one should use defined names instead of cell references wherever
possible. I also think labels and names should be the same: this makes a
spreadsheet straightforward and easy to check. In formulas long descriptive
names are certainly not better than generally accepted abbreviations. Those
abbreviations are most likely already in the specs, and conformity with
specs is another good habit.
Regards,
Niek Otten
John F. Raffensperger <j.raffe...@mang.canterbury.ac.nz> schreef in
berichtnieuws e6TagFalAHA.2172@tkmsftngp05...
Hans Knudsen
As being about the only one which have so far questioned your tip 10 I must say that I feel inclined to accept the challenge and post a spreadsheet where I feel multiple sheets make sense. I will do that to the newsgroup on condition that:
1. At least one more person (other than John) shows interest.
2. You accept that I will not send it until mid next week as I can't access the file until Monday morning and I will have to change quite some names and numbers to make it all non recognizable.
3. You tell me how I should post the file, xls, zip or ... (as xls it is 1,95 mb). For discussion purposes I could of course delete half or more of all the sheets and thus make it smaller if that makes sense.
By the way - the workbook I am thinking of has a lot of those "devil range names" -:)
In case no one in the newsgroup is interested I would be willing to send the file by private mail to you John for you review and eagerly awaiting your much better sheet.
Best regards
Hans
John F. Raffensperger <j.raffe...@mang.canterbury.ac.nz> skrev i en nyhedsmeddelelse:u2UBwqMmAHA.1832@tkmsftngp03...
Harald Staff
I believe we should keep attatched files away from this newsgroup -as
everyone is encouraged to. Would it be a sloution either to have it/both
either described with screenshots from Fritz's website or downloadable fro
there ?
> I wish we could all sit together in a big room, with a half-dozen
> spreadsheets available, up on a big screen (and some dark beer),
Brilliant idea. As soon as a sponsor show interest and advance payment we'll
all meet halfway (Mexico ? Hawaii ? :-)
Best wishes Harald
David Byrne
On occasions I have been known to cause offence without that intention.
No intention is present now, either, **but, with all due respect** what sort
of little spreadsheets does the originator of this thread use/create??
Having spent the last much of the last 10 years developing spreadsheet for
the two largest Industrial and Financial services organizations in this
country, I believe I have the basis for an informed opinion.
The Financial/Manufacturing Models for a complex industial process would
have been an absolute nightmare on a single sheet, as would most of the
other projects for Motor Vehicle Manufacturers, Finance Brokers,
International Aid Organizations, Major Infrastructure Projects, Life and
General Insurance Companies, Catering organizations, Local and International
Trading Banks, Actuaries, Stock Brokers, Futures/Options Traders......
As for the comments on Range Names..........
And I guess Pivot Tables are a no-no too.
Mr. Raffensperger, get off the grass.
David
in article ecFcs5NmAHA.1856@tkmsftngp02, Hans Knudsen at
hans.k...@mail.tele.dk wrote on 17/2/01 11:54 PM:
Niek Otten
I'm interested!
In case you withdraw, I have a 5.5 Mb, 10 sheet workbook that is only
understandable because I used defined names rather than cell references and
divided it into multiple sheets instead of stuffing it all in one
sheet...............<g>
Regards,
Niek Otten
Hans Knudsen <hans.k...@mail.tele.dk> schreef in berichtnieuws
ecFcs5NmAHA.1856@tkmsftngp02...
John F. Raffensperger
I have gotten lots of flak about Tip 10. You're not the only one!
As for your Challenge Model, I am not in a hurry, and you can post it to me
or the group, in whatever form is appropriate and convenient for you,
following reasonable netiquette. If I were doing it, I would zip it and put
it on a web site.
For discussion purposes, a half-size spreadsheet may do the job. If you feel
that your point is made better by a full-size sheet, that's fine, too.
Hans, this is very gracious of you to offer this.
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
Hans Knudsen <hans.k...@mail.tele.dk> wrote in message
news:ecFcs5NmAHA.1856@tkmsftngp02...
John F. Raffensperger
Sure, bring it on! More examples is better.
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
Niek Otten <nico...@xs4all.nl> wrote in message
news:96mf7h$7jk$1...@news1.xs4all.nl...
John F. Raffensperger
Respect is always welcome. You clearly have lots of experience in writing
spreadsheets, and your informed opinion would be valuable.
Have you read Panko's web site (http://panko.cba.hawaii.edu/ssr/) about
spreadsheet error? If you have not, I'm sure you're generally aware of the
studies that have shown the very high rate of error in spreadsheets. Panko,
citing many studies, puts the percentage of wrong cells at 1% to 4%, even
for advanced developers. The key problem, in his view, is overconfidence.
Since you are an experienced developer, we might assume only a 1% error
rate. Try counting the number of cells in one of your smaller spreadsheets;
call this number n. Then calculate 0.99^n. This may be viewed as the
approximate probability that the spreadsheet is completely correct. If you
wish to be more liberal, use n = the number of formulas.
Some writers in our debate have observed that my style rules seem most
appropriate for smaller spreadsheets. This is reasonable, but just slightly
misses the point. The goal is to make spreadsheets smaller, to increase the
probability that the spreadsheet is correct.
Like you, I have written many large spreadsheets. Most of these have been
operations research models that required a linear integer programming solver
add-in, such as Lindo System's What'sBest, or plain old Solver. In finding
the optimal solution to a decision model, the solver will fully exploit
whatever mistake I make. The solver acts as a grammar checker. When the
answer comes up goofy, it is almost always because I did not design the
spreadsheet correctly. After the third or fourth iteration, when I'm *sure*
I've got it right THIS time, such mistakes are quite humbling. I probably
would not have discovered them without the solver.
I found a 4.5MB file of my own (actually given to me by a client, but let's
carry on). COUNTA() returns 401,000 cells. Let's rate me an absurdly good
99.9% error rate, one bad cell in a thousand. Following Panko, the
probability that this spreadsheet is completely correct is 4.46*10^-175,
effectively zero. I imagine that formulas are more likely to be wrong than
constants (or at least more likely to be a mistake of the spreadsheet
writer), and my spreadsheet was mostly data. Also, as I teach in my graduate
management science classes, we have to pay attention to what in particular
is likely to be wrong, and the cost of different types of error, but
hopefully you see my point. Big spreadsheets tend to be wrong. My hair
stands on end when I think about how an error in one of my spreadsheets
could damage a client.
Like Panko, I have studied spreadsheets. Instead of error rates, I want to
know why it is that we make these errors, at a finely-grained level. I
figured that if we could understand the reasons for the errors, then we
could understand better how to avoid them. I began my study in the
university library, reading dozens of articles about "How you should write a
spreadsheet." Almost all of these articles agreed that "Writing a
spreadsheet is like writing a computer program." It is a bad analogy, which
they did not even follow properly. Those articles specify that a spreadsheet
should have modules, e.g. inputs, formulas, and outputs. Programmers don't
write this way anymore (and only did so for punch cards).
A spreadsheet has less in common with programming than it does with writing
text, graphics, and mathematics. So I turned to study those older and more
refined arts: text (Strunk & White), graphics (Edward Tufte), writing of
mathematics (Nicholas Higham), and math modelling (Linus Schrage and Hans
Daellenbach), even the Chicago Manual of Style for layout. What rules of
style do they prescribe? What say great people in those well-developed arts?
When I applied those rules to spreadsheets, I found a rich set of guidelines
that improved my spreadsheets. Furthermore, I could articulate why the
spreadsheets were improved, and it was easy to create examples.
I did not write those rules of style to be arrogant, but because I was
swayed by great masters in closely related arts. Spell-check in Excel for
the same reason you spell-check in Word. Then Panko whispers in my ear and
again reminds me to avoid over-confidence. So I am anxious to get feedback
and more input, and have a proper debate.
Disagreement is not a case. I gave a list of concrete reasons why range
names can cause problems. I would be interested in hearing concrete
counterarguments or examples. Let's not go near pivot tables for a while.
Regards,
Fritz
-------
John F. Raffensperger, PhD
David Byrne <tah...@webtime.com.au> wrote in message
news:B6B4C911.BBD3%tah...@webtime.com.au...
Tushar Mehta
John (Fritz) [Raffensperger]'s understanding of the capabilities of
defined ranges requires some 'correction.' <g>
In <u2UBwqMmAHA.1832@tkmsftngp03>, John F. Raffensperger
<j.raffe...@mang.canterbury.ac.nz> wrote
> Dear Hans,
>
[snip]
>
> Who wants to start arguing about range names? Here I go! Range names are of
> the devil! (-;<
>
> What is wrong with range names?
>
> 1) They require a separate chore to debug - printing a table of range names,
> and doing a match, one cell at a time.
So what? Writing a good program requires declaration of variables.
The benefit is that after doing a 'cell by cell' match, one can safely
use the name anywhere and know that it references the correct cell!
>
> 2) They can point to the wrong cell, and this problem is less identifiable
> than an address.
The problem with addresses is less identifiable (and that is arguable)
only if the spreadsheet is 'small' or the arc of precedence, to use
Fritz's terminology, is short.
>
> 3) The range names can get long and complicated, and can be hard to
> remember - "owners_discount_rate" - or was it "Owners_discount_rate".
So, what? When working in a complex spreadsheet, how does one remember
where one put a piece of basic information? Is it in cell A1 or A2 or
A3?
What's basic information? In a financial model, it might be something
like the risk free discount rate.
>
> 4) There is the range name that I just disagree with, such as "Print_range".
> I *never* want to print out what they want me to print out.
>
> 5) Range names sometimes collect garbage. Select Insert, Name, Define, and
> there is gibberish in the range names. (But I admit that a bug in Excel is
> not a good argument against a certain style.)
Don't really understand the role of either 4 or 5 in a discussion of
good design.
>
> 6) Range names are extremely hard to modify. They have to be deleted, then
> re-defined.
Not true. Select the name, type in (or point with the mouse) the new
formula / cell reference and click OK (or Add).
>
> 7) They only copy absolutely, when I often wish they would copy relatively.
If I understand this correctly...
Not true. With cell B1 selected, define a name, say, aName as $A1.
[Note the combination of absolute '$A' and relative '1' addressing.]
Next, in B1, enter the formula =aName^2. Drag it down a few cells. Put
some numbers in the corr. cells in column A.
Modesty aside, I exploited this capability brilliantly when modeling a
manufacturing plant as a queueing network. Each of a number of work
stations had a particular arrival rate, a service rate, and count of
servers. Using a combination of absolute and relative references, I
defined only one name for each of the three parameters!
>
> There are more reasons, but I'm sure this is enough to generate some debate,
> if not some flames!
>
> A perverse way to use a defined name (not a range name per se) is to define
> a constant with a name. Select Insert, Name, Define. Where it says "Refers
> to:" type in 1. Give it the name "You_are". Now, wherever you type
> "=You_are", you get the value 1. Talk about hidden constants! This is worse
> than putting a constant in a formula.
Yeah, I would agree that defining constants is not a good idea, but as
bad as putting a constant in a formula? No. Best is to have the
information is a cell with a defined name, next best is a defined
constant. Next is setting up the constant in a cell and referencing it
by address. The worst... using the constant directly in a formula.
In a financial model, I would rather enter 0.065 in a cell, name the
cell risk_free_rate and refer to risk_free_rate in all my formulas.
>
> Instead of range names, formulas can be made clear with short arcs of
> precedence and good labeling.
Two comments.
First, how do you handle basic information in a 'big' spreadsheet? I
don't see how one would use short arcs without duplicating basic
information, which in itself would be bad practice.
Second, I draw an analogy between labels in a spreadsheet with comments
in a program. My belief is that the only good code is self-documenting
code. Let me go further and make a very strong statement that I have
lived by -- and been successful with -- through all the years as a
consultant. Documentation, by definition, is obsolete the moment it is
complete.
FWIW, of all the compliments I received about my programs, the ones
that I'm the most proud of were along the lines of, "Tushar, I looked at
this program you wrote, found no documentation, and yet found it was
incredibly easy to modify."
>
> Last comment: The very best book to read, if you are interested in
> spreadsheet style, is Edward Tufte's book, "The Visual Display of
> Quantitative Information." It is probably in your local library, and you
> will find it reads like a comic book. You'll finish it in three hours, and
> most likely come away completely changed.
I will have to look up that book. Thanks for the tip.
>
> Warm regards to all,
>
> Fritz
> -------
> John F. Raffensperger, PhD
> http://SpreadsheetStyle.Com
>
>
[snip]
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
Bruce Sinclair
:Dear Bruce,
:
:Another man from down under!
yep :)
I checked out your web site and agree with much (probably 95%) of what is
there. I think we might be approaching this from different angles ... I
note you say "business" (only once, but I suspect that difference is
significant :) ). I approach it from the "science" angle.
:> :>- two short sentences (formulae) are better than one long one.
:> :No! Unlike English, breaking up a formula into two cells increases the
:total
:> :number of characters, and adds an abstracting layer of indirectness.
:After
:> :combining two or more formulas, the writer with a bit of algebra can
:often
:> :simply formulas considerably. Try to make formulas reference constants.
:>
:> Most Of the problems I have seen with spreadsheets come from trying to
:cram
:> 2 lines of formula into a cell. I tell people that it doesn't matter how
:> big the spreadsheet is as long as you can understand it and I try to get
:> them to break it up into sensible parts (eg inputs; outputs then
:> inputs-outputs) because it's more easily understood than the whole thing
:> together. It also stops sillinesses like outputs-inputs ...
:> which can be surprisingly harder to spot when all together.
:> The other essential is good names for rows/columns/ranges etc.
:
:Oooo, I have a long list of reasons why range names are criminal (another
:heresy of mine), but let's talk about that another time. I don't understand
:what silliness you are referring to. What do you mean by "sillinesses like
:outputs-inputs ..."?
Didn't spot it eh ? ... in the original example a few lines up it was
inputs - ouputs :)
Simple reversal of stuff like that has been the hardest to find in my
experience :)
:One of my main points is that smaller is better. Conciseness is good in ALL
:other arts (except postmodern philosophical analysis). Conciseness is valued
:in text, graphics, mathematics, and math modelling, all of which apply to
:spreadsheets. How is it suddenly better to be verbose in a spreadsheet?
I agree to leave out all irrelevant stuff (I agree with a lot of your style
guides) but I suspect most people don't think before they XL ... if you get
my meaning. They just rip into it, then it's done. Then they use it ... and
sadly, if it works you tend to leave it alone :)
:I agree that long formulas can be hard to understand sometimes. But a
:well-written formula can still be both long and understandable. Furthermore,
:when the writer combines formulas, there are usually opportunities to
:simplify algebraically.
True ... but there are always exceptions. I sometimes use 1000/10 (say for
example) in a formula rather than just 100. Reason ? ... it reminds me that
this is a real correction of real data (perhaps in this case 10 mls of 1000
mls) so that when i come back to it I can SEE that rather than working it
out again.
You could argue that this is redundant ... and you'd be right :), but I'd
argue that it's more understandable ... at least it is for me :)
:> I am reminded that it is often easier to reprogram from scratch rather
:than
:> attempt to decipher someone elses program ... and I feel (with no data -
:> sorry :)) that spreadsheet design/execution is rather like programming.
:> Trying to read someone elses work usually elicits "why on earth did they
:do
:> that !" noises :)
:Why is it easier to reprogram from scratch? It is because it was horrible
:and hard to understand in the first place.
Agreed ... but real programmers don't document :) :)
You are making a strong case for
:readability - none of us like deciphering someone else's spreadsheet. This
:is because most of us do not know how to write a spreadsheet well!
Totally agree - see point about starting before thinking :)
:> :>- ease and clarity of navigation are important: don't ignore the role of
:> :sheets in this.
:> :No! Multiple sheets wreak havoc on ease and clarity of navigation. Did
:you
:> :see http://spreadsheetstyle.com/style/tip10.htm?
:>
:> Up to a point they can make it easier ... after that i'll agree entirely
::)
:> What that point is ... I'm not sure but I don't think it's many sheets :)
:
:I agree. I just think that the breakpoint is 1.0 sheets. Can you suggest an
:example where multiple sheets is obviously the right thing to do?
For me it was doing a trial and collecting (about) 6 different samples.
Many of the calculations were the same ... but some were not ... so it
seemed logical to me to put each sample type on a separate sheet and do all
the common calculations (mostly dilution factors) first.
It worked fairly well ... but wasn't perfect - I'd certainly do it a
different way now ... but not on one sheet :)
The thought of putting all that data on one sheet really horrifies me :)
:> :>- designing for change is often a critical factor.
:> :Yes, I agree.
:> I'd settle for simply thinking about it a bit before launching into it.
:> Many people have made it really hard to do easy things by the way they set
:> up their data initially. It's often easier to fix the data - then it all
:> becomes clear.
:> I like to see their logic in the sheet ... whether I think it's right or
:> not, at least you can understand it then - and it definitely helps when
:> fixing things :)
:I strongly agree. What is important, and what I am trying to get across, are
:the visual structures that *show* the logic (right or wrong). Make your
:spreadsheet read from left to right and top to bottom. Have short arcs of
:precedence. Use one sheet, if at all possible. Format constants differently
:from formulas. Those few rules would solve a lot of problems.
Yep ... sure would. I've become a fan of naming constants recently ... then
you can a) see what it's supposed to be; b) find it easily wherever it is
:)
The more I think about this the more I'm sure it's simply lack of thought
when starting a sheet ... and inertia not to fix it when it gets ugly.
Question ... how many courses are there in spreadsheet design ?
Seems to me rather like the microsoft push to use .html in email. To me
colours and fancy fonts get in the way of the words and increase the noise
to signal ratio ... perhaps it's the same with spreadsheets as you've
suggested ? Maybe we ned a "text only spreadsheet" ... like notepad instead
of word ?
Then you'd have to think a bit first :)
:Bruce, thanks for taking the time to think about this, and for your
:thoughtful ideas. Could you come back with the bit about "sillinesses like
:outputs-inputs ..."?
Hope I explained it better this time :)
John F. Raffensperger
Ahhh! What a wonderful note!
> > What is wrong with range names?
> >
> > 1) They require a separate chore to debug - printing a table of range
names,
> > and doing a match, one cell at a time.
>
> So what? Writing a good program requires declaration of variables.
>
> The benefit is that after doing a 'cell by cell' match, one can safely
> use the name anywhere and know that it references the correct cell!
I concede partially. In a spreadsheet, no declaration of variables is
needed, so making such a declaration seems superfluous. But it is true that,
once one has checked the range names, that you can use the name anywhere.
> > 2) They can point to the wrong cell, and this problem is less
identifiable
> > than an address.
>
> The problem with addresses is less identifiable (and that is arguable)
> only if the spreadsheet is 'small' or the arc of precedence, to use
> Fritz's terminology, is short.
Yes, that is true. Still, having short arcs of precedence allows the reader
to understand the spreadsheet visually, and should be a priority in design.
More on this further down.
> > 3) The range names can get long and complicated, and can be hard to
> > remember - "owners_discount_rate" - or was it "Owners_discount_rate".
>
> So, what? When working in a complex spreadsheet, how does one remember
> where one put a piece of basic information? Is it in cell A1 or A2 or
> A3?
>
> What's basic information? In a financial model, it might be something
> like the risk free discount rate.
If you have short arcs of precedence, one need not remember where data is
located, because it will be on the screen, right in front of you. I concede
that one can remember a range name better than a cell address. The point of
short arcs of precedence is to avoid needing range names in the first place.
> > 4) There is the range name that I just disagree with, such as
"Print_range".
> > I *never* want to print out what they want me to print out.
> >
> > 5) Range names sometimes collect garbage. Select Insert, Name, Define,
and
> > there is gibberish in the range names. (But I admit that a bug in Excel
is
> > not a good argument against a certain style.)
>
> Don't really understand the role of either 4 or 5 in a discussion of
> good design.
As for 4, why should I be forced into something the writer wants? I concede
for 5.
> > 6) Range names are extremely hard to modify. They have to be deleted,
then
> > re-defined.
>
> Not true. Select the name, type in (or point with the mouse) the new
> formula / cell reference and click OK (or Add).
No, I don't concede this one. Unless you have a better way, this is inside
the Insert, Name, Define box, right? (That's a chore to me - a dialog box!)
Changing the reference is easy, but changing the name is hard. You can add
an alias by typing over the address box to the left of the formula, but now
we have two range names for the same cell, very confusing. If you delete a
name in Excel (Insert, Name, Define, Delete), all the references become
"#NAME?", and the writer must search for the deleted range name and replaced
them with the correct address. (In the old versions of Lotus 1-2-3, these
were automatically updated with the correct cell addresses. I don't know how
this feature works in the newer versions. Arguably, then, this is a bug in
Excel.) If you know a better way to handle changing or deleting a name, I
would be pleased to hear it!
> > 7) They only copy absolutely, when I often wish they would copy
relatively.
>
> If I understand this correctly...
>
> Not true. With cell B1 selected, define a name, say, aName as $A1.
> [Note the combination of absolute '$A' and relative '1' addressing.]
> Next, in B1, enter the formula =aName^2. Drag it down a few cells. Put
> some numbers in the corr. cells in column A.
A new one for me! I concede completely on this point. I was not aware of
this feature (probably because I avoid range names). Thanks!
> > A perverse way to use a defined name (not a range name per se) is to
define
> > a constant with a name. Select Insert, Name, Define. Where it says
"Refers
> > to:" type in 1. Give it the name "You_are". Now, wherever you type
> > "=You_are", you get the value 1. Talk about hidden constants! This is
worse
> > than putting a constant in a formula.
>
> Yeah, I would agree that defining constants is not a good idea, but as
> bad as putting a constant in a formula? No. Best is to have the
> information is a cell with a defined name, next best is a defined
> constant. Next is setting up the constant in a cell and referencing it
> by address. The worst... using the constant directly in a formula.
>
> In a financial model, I would rather enter 0.065 in a cell, name the
> cell risk_free_rate and refer to risk_free_rate in all my formulas.
Yes and no. To me, a defined constant is at least as bad as a constant in a
formula, because I have to open up a dialog box to see it, but I am likely
to see a constant in a formula on screen, just browsing. The defined
constant is just as hidden. For that matter, you could write a VB function
to return a constant. If I read you right, I think we agree that visible
constants are preferred to hidden constants.
To me, the best way to handle constants is in a well-labeled cell, where
related formulas access that constant with as few intermediate cells as
possible. Make formulas reference constant data as much as possible, and put
the formulas close to the constant data that they reference.
> > Instead of range names, formulas can be made clear with short arcs of
> > precedence and good labeling.
>
> Two comments.
>
> First, how do you handle basic information in a 'big' spreadsheet? I
> don't see how one would use short arcs without duplicating basic
> information, which in itself would be bad practice.
By "basic information," I think you mean constants. I strongly agree that
duplicating constants (with spurious cells, where Formulas!J51 = Data!A3,
for example) is bad practice! Instead, I use very careful design and layout.
Consider the idea of a "canonical spreadsheet." The constants and formulas
in spreadsheet (without a circular reference) can be represented as a
directed graph, and often as a tree, with cells as nodes, and the arcs of
precedence as arcs of the graph. Imagine lifting out all the cells that were
used, and floating them in the dark night air, gleaming rectangles of data
and formulas, sort of like a yellow organizational chart glowing against a
huge black screen. Next, we pull out a facility location algorithm, which
rearranges all cells/nodes, with the arcs stretching like cosmic lines of
force, trying to shorten. The facility location algorithm rearranges all the
cells without breaking or rearranging the arcs, and then places all the
cells back in the Excel spreadsheet, such that total length of all arcs of
precedence is minimized, subject to the constraint that all formulas flow
down and to the right. Now, whenever you look at a formula, its references
are most likely very nearby, probably right on screen.
(One other feature of a canonical spreadsheet: All formulas have been
rearranged algebraically so the spreadsheet contains the minimum number of
formula characters possible, perhaps subject to a liberal constraint on the
length of each formula.)
Now the reader can easily see see how the discount rate affects the cash
flow, because the cash flow depends directly on discount rate, and cells
will be close together. You see a cell address for the discount rate in the
cash flow formula, and can immediately see the related cell nearby. If you
want to try a different discount rate, voila, you can see the immediate
result in the cells that are affected. This is the whole point of Excel.
This is its strength, the reason the spreadsheet is such a cool tool. You
make a change and you can see the result immediately. To substitute range
names or multiple sheets for careful layout defeats the purpose of the
medium.
If you have a big VB app, full of input boxes, hidden sheets, automatic
printing, etc., then who cares what spreadsheet style you have? (The writer
will be more productive and make fewer errors with good style, but the
reader doesn't care.) For the writer, the spreadsheet is simply a place to
hang numbers and formulas. The big VB app is not exploiting the powerful
native visual elements of the spreadsheet, and the writer may even intend
the reader to ignore the spreadsheet. So why use a spreadsheet or even call
it a spreadsheet application? It's a VB application with some Excel for
convenience (which people do because they don't want to buy Visual Basic
separately). For style about programming, read McConnell's outstanding
magnum opus "Code Complete."
> Second, I draw an analogy between labels in a spreadsheet with comments
> in a program. My belief is that the only good code is self-documenting
> code. Let me go further and make a very strong statement that I have
> lived by -- and been successful with -- through all the years as a
> consultant. Documentation, by definition, is obsolete the moment it is
> complete.
>
> FWIW, of all the compliments I received about my programs, the ones
> that I'm the most proud of were along the lines of, "Tushar, I looked at
> this program you wrote, found no documentation, and yet found it was
> incredibly easy to modify."
So you might use only range names, with no labels? I must not understand you
on this one. I agree that in any standard procedural programming language,
the virtuous programmer carefully names functions and variables, and uses
good object oriented design, per McConnell.
Perhaps the key disagreement is which analogy is most helpful and most
useful for spreadsheet style. I view a spreadsheet not as programming, but
as a combination of text, graphics, and mathematics. "Documentation" is
simply writing and explaining what I mean. I frequently edit my
spreadsheets, just as I edit any writing, to make it clearer and more
understandable. It is not complete until it is published, until the client
has paid for it, or until I am satisfied. I don't think of correcting a
model as "debugging." Instead, I try to be "truthful, correct, clear, and
concise," in that order. All - *ALL* - other related arts - text, graphics,
written mathematics, and mathematical modelling - value conciseness
(concision?) as a method to achieve clarity. But now I'm getting off the
topic of range names.
I have more thoughts on the comparative analogies of programming versus
writing, which I shall try to post tomorrow.
Tushar, great work! I'm amazed how much I have conceded here. You have also
taught me at least one new Excel trick. Thanks! Now I think I have to go
back and touch up the comments on range names in my essays. "Try to have
good layout instead of range names."
Warm regards,
John F. Raffensperger
> I checked out your web site and agree with much (probably 95%) of what is
> there. I think we might be approaching this from different angles ... I
> note you say "business" (only once, but I suspect that difference is
> significant :) ). I approach it from the "science" angle.
Once upon a time I was an engineer, but I wanted to do operations research.
During the Ph.D., I decided I was really a scientist with a focus on
business. Management science. My approach is a science angle, too, maybe too
much sometimes, just not a life science. Anyway, all these big operations
research models are science stuff, believe me.
. . .
> Didn't spot it eh ? ... in the original example a few lines up it was
> inputs - ouputs :)
> Simple reversal of stuff like that has been the hardest to find in my
> experience :)
Maybe I'm brain dead, I still don't know what silliness you mean. I'm so
swamped in it these days; a particular silliness has to be pointed out like
a particular mushroom.
. . .
> :I agree that long formulas can be hard to understand sometimes. But a
> :well-written formula can still be both long and understandable.
Furthermore,
> :when the writer combines formulas, there are usually opportunities to
> :simplify algebraically.
>
> True ... but there are always exceptions. I sometimes use 1000/10 (say for
> example) in a formula rather than just 100. Reason ? ... it reminds me
that
> this is a real correction of real data (perhaps in this case 10 mls of
1000
> mls) so that when i come back to it I can SEE that rather than working it
> out again.
> You could argue that this is redundant ... and you'd be right :), but I'd
> argue that it's more understandable ... at least it is for me :)
I won't argue too much. A good label should solve that problem.
. . .
> :> :No! Multiple sheets wreak havoc on ease and clarity of navigation. Did
. . .
> For me it was doing a trial and collecting (about) 6 different samples.
> Many of the calculations were the same ... but some were not ... so it
> seemed logical to me to put each sample type on a separate sheet and do
all
> the common calculations (mostly dilution factors) first.
> It worked fairly well ... but wasn't perfect - I'd certainly do it a
> different way now ... but not on one sheet :)
> The thought of putting all that data on one sheet really horrifies me :)
Sounds like it might as easily have been 6 separate files?
. . .
> The more I think about this the more I'm sure it's simply lack of thought
> when starting a sheet ... and inertia not to fix it when it gets ugly.
>
> Question ... how many courses are there in spreadsheet design ?
>
> Seems to me rather like the microsoft push to use .html in email. To me
> colours and fancy fonts get in the way of the words and increase the noise
> to signal ratio ... perhaps it's the same with spreadsheets as you've
> suggested ? Maybe we ned a "text only spreadsheet" ... like notepad
instead
> of word ?
> Then you'd have to think a bit first :)
Yes! When it's ugly, we don't fix it, because editing is hard work. Just
like a big report. We're tired of it and want it to go away.
Lotus 1-2-3v2.01, the greatest of text-based programs. That was a long time
ago. "Noise to signal ratio" is a phrase I use in spreadsheet design! It is
why we should make our spreadsheets concise. Beautiful!
If you're in Christchurch, look me up at U of Canterbury.
Hans Knudsen
I will take note of Harald's advice that is not posting any file to the newsgroup. My file (full-size) with multiple sheets and a lot of range names will be sent to your private mail address during this week in zipped format, and then I will, as already mentioned, eagerly await your much better sheet. I have no web site and I have no intention of getting such a thing. If you might find it appropriate to put my file on your own web site, well that's fine. If not, that's also fine.
Regards
Hans
John F. Raffensperger <J.Raffe...@mang.canterbury.ac.nz> skrev i en nyhedsmeddelelse:OhuxJifmAHA.1652@tkmsftngp03...
John F. Raffensperger
As I said in my just-posted note, I would try to add more about the
analogies of computer programming and writing. (I previously sent some of
this privately to one of this thread's correspondents.)
There was an intriguing thread on comp.apps.spreadsheets
(http://groups.google.com/groups?hl=en&lr=&safe=off&ic=1&th=e82811838e2e7681
&seekd=930289666#930289666)
about the space-time computational complexity of spreadsheets. What kind of
programming language is a spreadsheet, in the zoo of formal programming
languages? The answer would make a great academic paper, and might shed
light on spreadsheet style, but computational complexity is beyond me! I had
a couple courses in it in grad school, and never did too well. We know the
spreadsheet is a much higher-level language than C, and not as powerful. How
much higher does it have to go before we're not programming anymore?
Practically, then, the question, "Is writing a spreadsheet the same as
writing a computer program?" becomes less interesting and less useful. If we
have a voice interface, and the computer understands it has the wrong
formula when we wince, is that programming? To me, a spreadsheet is a
combination of text, graphics, and mathematics, more like Mathematica,
MathCAD, or even Word (with all its power) than Fortran or C. Excel is a
compound interactive document, intended to be run and viewed in the
foreground. That is its strength.
Empirically, the studies (on Panko's site) found that spreadsheet errors can
be debugged best by programming-like testing procedures. At least in deep
debugging, programming techniques apply. But everything else about
programming, such as declarations, object orientation, subroutines, loops,
etc., stretches the analogy too far for me. Without VB, those typical
programming chores don't really occur in a spreadsheet, at least not in the
same formal way. The studies also found that people don't use formal
debugging techniques. Cragg & King and others found that spreadsheet writers
and readers don't want to use formal techniques and resent having
programming-like standards enforced on them.
Part of my logic for appealing to styles from text, graphics, and math, is
because these styles are easy to describe and provide a powerful and useful
analogy. A marketing analyst with a BA in history, who is writing a big
spreadsheet, might appreciate that analogy better then the programming
analogy. We are appealing to their literate side with a more intuitive
approach, rather than confronting them with programming which they are
trying to avoid.
So the programming analogy is often unhelpful, often misused, always unused
where it might be useful, and frequently hard to sell. The writing analogy
is very helpful, easy to use (though still controversial!), and may be
easier to sell to literate people.
All night, I dreamed of vectors with very long labels on them. Edward Tufte
was weeping, alone in the wilderness, "Don't use legends. Don't make them
memorize codes. Don't do things verbally. Do them visually!"
The ideal analogy for spreadsheet style is the spreadsheet itself. If you
were to explain to a 5-year-old why the spreadsheet was so good, first you
might introduce him to a calculator, and then to Fortran. Type input. Run.
See output. Ooops, wrong input. Type input. Run. See output. Like Dick,
Jane, and Sally, it's laughable now. Those dinosaurs show clearly why the
spreadsheet was revolutionary.
If we have the constants very close to the formulas that use them, then we
use the power of the spreadsheet in the way it was originally meant to be:
visual.
If we have multiple sheets, we are back to Type input, Run, See output. Put
the inputs on the Input screen. Then click the special MyThingie Toolbar to
print the Print_Area. What happened to the spreadsheet? It is just a
calculator.
My point about spreadsheet style is to get back to using the spreadsheet as
a spreadsheet, with its original visual concept, where you make a change and
can instantly see the result, without clicky clicky that takes me to a
different screen first.
More reasons to avoid range names!
The reader's ability to understand the meaning depends mightily on the
writer's ability to communicate, on the writer's care in selecting good
names, and even the writer's ability to spell. If the reader finds the names
"monthly_houses_built" and "monthly_huses_built" in the same sheet, there
will be a felt need to compare them.
Range names tend to have more characters than a cell address, making
formulas longer, as in the monster,
Owner_s_Required_Rate_of_Return_on_Equity (in a real spreadsheet produced by
a grad student in operations research). These long range names cannot be
viewed in the address box at top left, even with the drop-down box.
If the same range name appears on different sheets, which one is the "real"
one? We can pretty quickly guess that the writer has a spurious reference.
There is a felt need to go and compare, "Is this number the same as that?
Yes, I guess it is." A waste of time.
Range names can be confused with the natural language interface. Without
defining a name, type in cells A1:A3
a
b
d
then type in cells B1:B3
2
4
5
In cell B5, type the formula
=a+b+d
You get the answer 11. These are not defined range names. The feature uses
Excel's natural language interface.
Now, there are some mighty interesting things here. First, you can't use
"c", I don't know why; you can't even name a range "c". That's okay, let it
go.
Second, you can put the *same* information on the *same* worksheet in
columns D and E, with the same labels, *different* numbers, and the same
formula, and you get the correct result from columns D and E. The same
formula appears, E5=a+b+d. Excel works the way it should and the way we (as
superstar Excel developers) expect, but this is really going to throw off a
reader who is not brilliant with Excel!
Third, define a range name, "d", say, cell G1. Type 100 in that cell. The
natural language feature automatically updates the natural language
formulas, surrounding "d" with single quotes, so the two "=a+b+d" become
"=a+b+'d'". Excel does not confuse the defined range name "d" with the
natural language "d". But now we have absolutely lost our reader.
You might respond that the parameters a, b, and d should appear only once in
a spreadsheet. Maybe. If you're going to copy worksheets to use the same
formulas for different data, that problem still occurs, though it's not so
bad. The confusion with the range name still occurs. Only the plain address
is authoritative.
Fourth, go to cell D8, type "=d". Wham, Excel is lost, you get an error. For
C8=d, you get zero, no error.
Fifth, if you change the label of A3 from "d" to "e", the natural language
interface automatically adjusts the formula, so cell B5=a+b+e. That's great,
but it's harder to remember a name that can be changed so easily.
The need for range names goes away if the writer uses good layout and short
arcs of precedence. Plus, the reader can visually see the result, on screen,
in real time, when he makes changes. If we use a spreadsheet as simply a
place to allocate memory for a whole lot of constants and formulas, with no
intention whatever of having the reader actually look at it or interact with
it, then we can do what we like. If we use a spreadsheet like a spreadsheet,
we don't need range names.
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
Tushar Mehta <ng_p...@bigfoot.com> wrote in message
news:MPG.14fa5be0b...@msnews.microsoft.com...
> This post is restricted to a discussion of defined names. I believe
> John (Fritz) [Raffensperger]'s understanding of the capabilities of
> defined ranges requires some 'correction.' <g>
>
> In <u2UBwqMmAHA.1832@tkmsftngp03>, John F. Raffensperger
> <j.raffe...@mang.canterbury.ac.nz> wrote
> > Dear Hans,
> >
> [snip]
> >
> > Who wants to start arguing about range names? Here I go! Range names are
of
> > the devil! (-;<
> >
> > What is wrong with range names?
> >
> > 1) They require a separate chore to debug - printing a table of range
names,
> > and doing a match, one cell at a time.
>
> So what? Writing a good program requires declaration of variables.
. . .
Bruce Sinclair
:This post is restricted to a discussion of defined names. I believe
:defined ranges requires some 'correction.' <g>
:
:In <u2UBwqMmAHA.1832@tkmsftngp03>, John F. Raffensperger
:<j.raffe...@mang.canterbury.ac.nz> wrote
:> Dear Hans,
:>
:[snip]
:>
:> Who wants to start arguing about range names? Here I go! Range names are of
:> the devil! (-;<
:>
:> What is wrong with range names?
:>
:> 1) They require a separate chore to debug - printing a table of range names,
:> and doing a match, one cell at a time.
:So what? Writing a good program requires declaration of variables.
Actually they're really easy ... just pick the name from the name box
(usually shows "a4" or whichever cell you're looking at) and you'll be
magically transported there to see it :)
:The benefit is that after doing a 'cell by cell' match, one can safely
:use the name anywhere and know that it references the correct cell!
Agreed ... once it's checked it can be used anywhere.
:> 3) The range names can get long and complicated, and can be hard to
:> remember - "owners_discount_rate" - or was it "Owners_discount_rate".
:So, what? When working in a complex spreadsheet, how does one remember
:where one put a piece of basic information? Is it in cell A1 or A2 or
:A3?
Agreed ... and don't use long range names :) (we wouldn't write them in a
document ... I hope :) )
I have also found that range names are the only way to reliably get word
mal merge and XL to talk to each other.
:> 5) Range names sometimes collect garbage. Select Insert, Name, Define, and
:> there is gibberish in the range names. (But I admit that a bug in Excel is
:> not a good argument against a certain style.)
:Don't really understand the role of either 4 or 5 in a discussion of
:good design.
:> 6) Range names are extremely hard to modify. They have to be deleted, then
:> re-defined.
:Not true. Select the name, type in (or point with the mouse) the new
:formula / cell reference and click OK (or Add).
I think the point is that once you've created a name they're difficult to
manage. I agree - and also agree that it's an XL problem and not range name
problem :) Deleteing names is far too cumbersome. I'll try your suggestion
.. but I'm fairly sure it hasn't worked for me in the past.
Bruce Sinclair
(snip)
:Consider the idea of a "canonical spreadsheet." The constants and formulas
:in spreadsheet (without a circular reference) can be represented as a
:directed graph, and often as a tree, with cells as nodes, and the arcs of
:precedence as arcs of the graph. Imagine lifting out all the cells that were
:used, and floating them in the dark night air, gleaming rectangles of data
:and formulas, sort of like a yellow organizational chart glowing against a
:huge black screen. Next, we pull out a facility location algorithm, which
:rearranges all cells/nodes, with the arcs stretching like cosmic lines of
:force, trying to shorten. The facility location algorithm rearranges all the
:cells without breaking or rearranging the arcs, and then places all the
:cells back in the Excel spreadsheet, such that total length of all arcs of
:precedence is minimized, subject to the constraint that all formulas flow
:down and to the right. Now, whenever you look at a formula, its references
:are most likely very nearby, probably right on screen.
This is very interesting. I'm aware of a few programs that do shortest path
or sanest connection in the electronics area ... could something like that
be employed as a spreadsheet designer ? I guess the obvious problem is
feeding it sufficient data about nodes and connections :)
:(One other feature of a canonical spreadsheet: All formulas have been
:rearranged algebraically so the spreadsheet contains the minimum number of
:formula characters possible, perhaps subject to a liberal constraint on the
:length of each formula.)
:Now the reader can easily see see how the discount rate affects the cash
:flow, because the cash flow depends directly on discount rate, and cells
:will be close together. You see a cell address for the discount rate in the
:cash flow formula, and can immediately see the related cell nearby. If you
:want to try a different discount rate, voila, you can see the immediate
:result in the cells that are affected. This is the whole point of Excel.
:This is its strength, the reason the spreadsheet is such a cool tool. You
:make a change and you can see the result immediately. To substitute range
:names or multiple sheets for careful layout defeats the purpose of the
:medium.
I suspect you don't have much in the way of raw data. I guess we could
argue that it should be "processed" before being enetered, but I suspect
that I along with many others here, process the raw data with xl :)
This seems to imply that I'll have either a large ugly sheet, a few sheets
(I often use 2 - raw data and calculations. - keeps the calculated numbers
clear of clutter while the raw data is still available) or more than one
workbook (this I will certainly avoid if I can in future ... XL can't
handle lots of books open at one time ... let alone me handling it :) ).
Bruce Sinclair
:Dear Bruce,
:
:> I checked out your web site and agree with much (probably 95%) of what is
:> there. I think we might be approaching this from different angles ... I
:> note you say "business" (only once, but I suspect that difference is
:> significant :) ). I approach it from the "science" angle.
:Once upon a time I was an engineer, but I wanted to do operations research.
:During the Ph.D., I decided I was really a scientist with a focus on
:business. Management science. My approach is a science angle, too, maybe too
:much sometimes, just not a life science. Anyway, all these big operations
:research models are science stuff, believe me.
I know somone that used to do this stuff ... agreed :)
:> Didn't spot it eh ? ... in the original example a few lines up it was
:> inputs - ouputs :)
:> Simple reversal of stuff like that has been the hardest to find in my
:> experience :)
:Maybe I'm brain dead, I still don't know what silliness you mean. I'm so
:swamped in it these days; a particular silliness has to be pointed out like
:a particular mushroom.
:) ... nope ...it's hard to spot because it's simple. My first example said
outputs-inputs (eg to work out how much something is retained) ... but the
next time I wrote it it was inputs-outputs.
I have found that many people get this sort of thing wrong if they are
trying to put the whole calculation in one cell.
Worse, it's REALLY hard to find. If they calculate a column of inputs, then
outputs then subtract a) it's usually right ; b) it's much easier to fix if
it's wrong :) :)
:> :I agree that long formulas can be hard to understand sometimes. But a
:> :well-written formula can still be both long and understandable.
Sorry I can't agree. I find that after about 10 various cell references,
the same operations, brackets etc most formulae become unreadable.
Maybe that's just me ... but I don't think so. Maybe it's a style
thing; or maybe it's what you're used to :)
:> :> :No! Multiple sheets wreak havoc on ease and clarity of navigation. Did
:.. . .
:> For me it was doing a trial and collecting (about) 6 different samples.
:> Many of the calculations were the same ... but some were not ... so it
:> seemed logical to me to put each sample type on a separate sheet and do
:all
:> the common calculations (mostly dilution factors) first.
:> It worked fairly well ... but wasn't perfect - I'd certainly do it a
:> different way now ... but not on one sheet :)
:> The thought of putting all that data on one sheet really horrifies me :)
:Sounds like it might as easily have been 6 separate files?
.. it already was 3 separate files ... and I won't do that again. In many
ways files are harder to understand than sheets ... at least the sheets are
there ... files need to be opened and XL 97 didn't handle multiple files
all that well either. May be better with xl 2000 ... but I'm not willing to
chance it :)
(snip)
:Lotus 1-2-3v2.01, the greatest of text-based programs. That was a long time
:ago. "Noise to signal ratio" is a phrase I use in spreadsheet design! It is
:why we should make our spreadsheets concise. Beautiful!
I started with appleworks :) :)
:If you're in Christchurch, look me up at U of Canterbury.
Haven't been there for a while - will certainly try if I get down that way.
Alan B. Pearce
>:Sounds like it might as easily have been 6 separate files?
Why would you separate data that lives together into separate files? I would
certainly keep it in several sheets in one file to keep it all filed in one
place. It also makes cross referencing between sheets for running totals and the
like a lot easier.
Alan B. Pearce
>cell risk_free_rate and refer to risk_free_rate in all my formulas.
I would agree here. having had to modify programs in the past where things were
done by entering constants in formula, when it should have been defined once as
a named constant was a pain. It took several iterations of modification to catch
all the constants, simply because it was not obvious which referred to the item
I needed to modify.
John F. Raffensperger
Alan B. Pearce <A.B.P...@rl.ac.uk> wrote in message
news:96tdvh$10...@newton.cc.rl.ac.uk...
But that had to be because those constants were far, visually, from the
formulas that used them. -jfr
Bruce Sinclair
:
:Alan B. Pearce <A.B.P...@rl.ac.uk> wrote in message
Which in a large sheet is quite likely if a constant is used a lot :)
Is it more logical to have it in one place or to redefine it close ?
I know which I choose :)
Jim & Beth Hess
May I Thank You for your article and your web site address! I, one of the "big
mass of "quiet" readers that browse around for useful tricks" find this type of
posting very useful. And "First of all; there are too few articles on this and
your work is very welcome" (quotes taken from Harald's postings). Developing a
Workbook and the Worksheets that provides ease of use and readability is/can be
a challenge. I know that I will visit your site at times for tips.
Again, Thank You.
jph
Tushar Mehta
This is still a rather restricted post, in the sense that it addresses
mechanics of name management. I just haven't had the opportunity to
write up my thoughts on spreadsheet design.
Thanks for the compliment on my previous post. Much appreciated.
Here are a few ways to address the concerns about the complexity and
inconvenience of managing names.
To name a range of cells: On the formula bar, on the extreme left is the
'Name Box.' Select a range of cells, click in the Name Box and type in
a name.
To go to the location of a particular name: click the drop-down arrow
adjacent to the Name Box and select that name.
To paste a name into a cell (as part of a formula, maybe): XL has a
'Paste Names' button (and is the equivalent of Insert | Name >
Paste...). It is not part of any toolbar by default, but one can
customize a toolbar by adding the button to it. The beauty about this
is that it works even when one is using the Formula Wizard to enter a
formula!
To replace one name by another (which, in itself, should be a somewhat
rare event): I don't see it as any more difficult than replacing one
cell by another or changing the name of a variable in a program. Add
the new name, do a replace of the old name by the new, and delete the
old.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <#YYKB3kmAHA.2088@tkmsftngp05>, John F. Raffensperger
<j.raffe...@mang.canterbury.ac.nz> wrote
John F. Raffensperger
Tushar Mehta <ng_p...@bigfoot.com> wrote in message
news:MPG.14fd9366e...@msnews.microsoft.com...
. . .
> To paste a name into a cell (as part of a formula, maybe): XL has a
> 'Paste Names' button (and is the equivalent of Insert | Name >
> Paste...). It is not part of any toolbar by default, but one can
> customize a toolbar by adding the button to it. The beauty about this
> is that it works even when one is using the Formula Wizard to enter a
> formula!
This is a new one for me! Thanks. But I'm *still* not convinced that range
names are such a great idea.
> To replace one name by another (which, in itself, should be a somewhat
> rare event): I don't see it as any more difficult than replacing one
> cell by another or changing the name of a variable in a program. Add
> the new name, do a replace of the old name by the new, and delete the
> old.
Replacing one name with another will be a common event, if we use a many
range names. Hmmm, looks like somebody should write a Range Name Manager
toolbar for name deletion and renaming, that does all that search & replace.
Warm regards,
Fritz
-------
John F. Raffensperger, PhD
Niek Otten
anywhere.
And, as an alternative for naming cells via the name box or via menus, just
press CTRL+F3. If the range has a header, it will suggest that as a name
automatically.
I can't remember where in this long thread (I enjoy it!) I saw this, but I
think it was Fritz who noticed that natural language formulas don't
recognise "c" as a name.
That is because C is a reserved name; it means "this column" in the R1C1
addressing system, which I like very much, but I think we have enough
discussions already!
Just an example from my Life insurance spreadsheet: "=UltValue R[-1]" means:
the intersection of the column named "UltValue" with the row above. Looks
very self-explanatory to me....
Regards,
Niek Otten
Tushar Mehta <ng_p...@bigfoot.com> schreef in berichtnieuws
MPG.14fd9366e...@msnews.microsoft.com...
Tushar Mehta
but this one might we worth remembering <g>
On the other hand, CTRL-F3 brings up the Name dialog box, which is what
Fritz does not like.
I used the R1C1 system a long time ago, but quickly learnt that nerds
like me were in the minorly minor minority! Just like the world has
adopted the inefficient QWERTY keyboard (compared to, say, the DVORAK
keyboard), spreadsheet addressing with the A1 convention is something
that the world chose over the R1C1 system. :(
I hate XL's natural language formulas since I find it inconsistent and,
hence, unreliable. I usually have the 'Allow labels in formulas' (or
some such) option turned off. Maybe it's time for me to revisit it ;-)
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <971a82$7jo$1...@news1.xs4all.nl>, Niek Otten <nico...@xs4all.nl> wrote
> Or, instead of clicking the Paste name button, just press F3. Works
> anywhere.
>
> And, as an alternative for naming cells via the name box or via menus, just
> press CTRL+F3. If the range has a header, it will suggest that as a name
> automatically.
>
> I can't remember where in this long thread (I enjoy it!) I saw this, but I
> think it was Fritz who noticed that natural language formulas don't
> recognise "c" as a name.
> That is because C is a reserved name; it means "this column" in the R1C1
> addressing system, which I like very much, but I think we have enough
> discussions already!
> Just an example from my Life insurance spreadsheet: "=UltValue R[-1]" means:
> the intersection of the column named "UltValue" with the row above. Looks
> very self-explanatory to me....
>
> Regards,
>
> Niek Otten
>
[snip of *everything* in a long discussion thread]
Alan B. Pearce
>formulas that used them. -jfr
No, because using a name showed which value in the formula was being used for
which action. It is a fair time ago I did this, but to have a formula where some
values were discounts and others were constants, and then try and change the
discount amount, was a hair raising procedure. It required a fair amount of
reverse engineering of the formula to figure what was happening, only to find
something was incorrectly replaced, because numbers instead of symbolic
quantities were used in the formula.
Perhaps we are talking at cross purposes here, or I did not explain myself very
well. I dealt with a situation where instead of defining a constant that was
used in multiple cases, each case had a numeric value plugged into it that had
to be found and altered. I dearly hope this is not what you are suggesting
should be done instead of named constants that can be put in a cell and altered
as needed (such as interest rate, discount).
John F. Raffensperger
discounts and other constant values in their own well-labeled cells, and
adjacent to the formulas that use them.
Alan B. Pearce <A.B.P...@rl.ac.uk> wrote in message
news:9730rd$k...@newton.cc.rl.ac.uk...