"How do you know your spreadsheet is right?"

[Dr T Non-Fan]

When I copy and multi-paste a formula, I expect it to paste the same relative formula all the way down. I don't see how it could get pasted incorrectly. Of course, I'm usually perfect, in every way.

One formula that does deserve some scrutiny is "OFFSET". Even when hitting F2, this formula doesn't show (in different colors) which cells are actually being computed. Checking the formula's result with the specific range that is intended to be calculated (be it averaged or summarized) is paramount.

[whisper]

Originally Posted by Dr T Non-Fan

When I copy and multi-paste a formula, I expect it to paste the same relative formula all the way down. I don't see how it could get pasted incorrectly. Of course, I'm usually perfect, in every way.

Its not a question of did the formula get entered correctly. Its more of a question of alterations in a spread sheet after the formula entry has made the values different.

[dumples]

Originally Posted by whisper

Its not a question of did the formula get entered correctly. Its more of a question of alterations in a spread sheet after the formula entry has made the values different.

Or when you have the pleasure of working on someone else's files.

[Expunge]

wrt R1C1 vs A1, I'm going with his "Break the rules" statement on Page 11. The unfamiliarity with r1c1, and the visual style of it (ie. the [ ] + -) that goes with it, makes it more difficult to determine what a formula is doing how many ranges make up the formula etc.

anyways... On Page 9 in talks about common errors, and in the paragraph it says "...as long as you construct the range names automatically." How does one construct range names automatically?

[BassFreq]

Insert>Name>Create

[Expunge]

Originally Posted by BassFreq

Insert>Name>Create

That doesn't seem so "automatic" to me.

[Expunge]

Originally Posted by Expunge

That doesn't seem so "automatic" to me.

Oh, you can name multiple rows/columns at once, I guess thats quicker than what i was doing "editing in that dropdown box just above the top left cell"
Still I usually have a blank row between the label and first data point, although as long as that stays empty, it should still work most of the time.

[Dr T Non-Fan]

Originally Posted by dumples

Or when you have the pleasure of working on someone else's files.

This is probably more like it. There are such ranges of knowledge available in the distribution curves that if I get the "pleasure" of peer-reviewing someone's work (or even checking a source that I must use), I've found that the farther away they are from my knowledge base (either more or less -- I consider myself slightly more than average, based on a spreadsheet that I keep such information on), the more difficult it is to proofread their files.

I haven't peer-reviewed the OP's link yet, so I'll see if there's anything interesting in it a bit later.

[whisper]

Originally Posted by Expunge

wrt R1C1 vs A1, I'm going with his "Break the rules" statement on Page 11. The unfamiliarity with r1c1, and the visual style of it (ie. the [ ] + -) that goes with it, makes it more difficult to determine what a formula is doing how many ranges make up the formula etc.

You can easily mix and match views. If you need to make sure a formula is correct - you can use the A1 format to understand what the formula is. If you need to check that the formulas are consistent, you can use the R1C1 format to easily check for consistency.

[campbell]

More links on spreadsheet errors, error rates, and the preventing thereof:
Block that Spreadsheet Error

Preventing Spreadsheet Problems

Methodology for the Audit of Spreadsheet Models

For fun, spreadsheet error horror stories