Well, I said doubtfully, I could just put a cell that points to the discount rate right next to the output of present values.
Then he could see the discount rate when he sees the output.Recently, I was working on a big spreadsheet for an important client. I showed my spreadsheet to Grace Gridwright. Here's what she said.
"Hmmm, boss, this is really hard to understand."
Why? What seems to be the trouble with it?
"Well, have a look at this formula."
I did just what you said in Tip 1. That formula refers only to cells that are above and to the left.
"Yes, good job there. But those cells are really far away!"
So what's the problem?
"Well, I can't see them when I am looking at the formula."
So move the cursor!
"Now, don't get testy. It is hard to understand when the context is broken up. If you put related cells together, on the same screen, then you build context. Blocks with related meaning are together rather than spread apart. Your spreadsheet will be more naturally ordered according to the business logic."
You said that in Tip 1.
"Well, yes, I did. These tips go together."
All right. Any other justification for Tip 2?
"Yes, there are more reasons.
"If you put related cells together, you can see how a change in one affects the rest. For example, put the discount rate right next to the table of present values. That way, your client can change the discount rate, and immediately see how it affects the results. That's the whole idea of a spreadsheet, seeing immediately how changes affect the results.
"If you put the discount rate on an Input sheet, your client has to first view the Output sheet and remember the numbers. Then he has to go to the Input sheet to change the discount rate. Then he has to come back to the Output sheet, and compare his memory with what he sees. Plus he has to remember what he did to the discount rate."
Well, I said doubtfully, I could just put a cell that points to the discount rate right next to the output of present values.
Then he could see the discount rate when he sees the output.
"Don't make me sick. I'll talk about that in another tip sometime. Your client still would have to change sheets to change the discount rate, and would still have to remember the change in the output. By adding a spurious cell, you just add fake complexity to the spreadsheet."
"Here is a simple example of a spurious cell. It references another cell on the same sheet. Your reader will scream out, like the old game, 'Will the REAL value please stand up?'"
All right. So you're saying I should rearrange my spreadsheet so that related cells are close together.
"Yeah. Imagine a utility that rearranged your spreadsheet to minimize the total distance between all related cells. It would be like rearranging the equipment in a factory, so material movement is as short as possible. It would be hard to do, but you know there is some kind of minimum. Write your spreadsheet so the precedence arcs are as short as possible."
But wouldn't it be a good idea to have a blank row here and there to separate blocks?
"Yeah, but don't over-do it. Think of it as the blank space between paragraphs. The main thing is to get related cells reasonably close together."
"And one more thing. A formula is a lot easier to check when its references are nearby."
Hmmm, I didn't think of that. All right. Now, what were you saying about spurious cells?
"That's another tip: Put it all on one sheet. More on that another time."
You're joking! That's absurd! Everybody says multiple sheets are good!
"I don't thiiiiink so! I'll tell you more about that in Tip 10."