voluntaryXchange

A Joke for Coders

Test your code before it matters. This goes for your spreadsheets too … if you didn’t’ think they count as “code”.

Technorati Tags: excel,test,code,production

Excel Tip #20: Thermometer Graphs

I think this sort of graph begs for downstream user misinterpretation, but I have had students ask me how to do this:

So, here’s a thermometer graph tutorial from Susan Harkins at TechRepublic.

Technorati Tags: thermometer,graph

Excel Tip # 19: Protecting a Style

This is kind of neat trick. Obvious in retrospect, but I’d never thought of it.

Any sort of cell formatting you can do can be saved as a style and applied elsewhere.

But … protection is a form of formatting … so that can be part of your style as well.

Why is this useful? Because often you want to protect most of your sheet from downstream users, while leaving open a few select spots for them to edit. If such editable cells share a style, you can turn them on and off as a group, or add new cells to that list by simply assigning a style to them.

Technorati Tags: excel,tip,29,protect,style

Beware of Spreadsheets’ Modelers

An oldie but a goodie:

Technorati Tags: dilbert,spreadsheet,financial,modeling,strategy

Confidence Interval

Technorati Tags: confidence,interval

Excel Tip #18: Custom Lists

Remember the first time you typed a 1, and then a 2, and then dragged those cells to create a sequence of numbered cells?

You can do the same with other lists: Excel includes obvious ones like days of the week and months. What I really like about these is that you can start in the middle and they still work.

Access Analytics points out another good one: a list of letters you can drag.

In Excel 2010

• Type the list you want to be able to reuse. Sorry, no formulas.
• Click “File”
• Click “Options”
• Click “Advanced”
• Scroll down and click “Edit Custom Lists”
• Click the button to select a range, and select the list you typed.
• Click “Import”. Your list should appear in the box.
• Click “Add”

What sort of lists might you want to create this way?

• Upper case letters
• Lower case letters
• Roman numerals
• Ordinals (First, Second, Third, etc.)
• People’s last names
• People’s first names

Those last two are pretty useful for a teacher: I can quickly generate lists of students to mix and match for group projects, and such.

Here’s an odd one I’ve done. I give multiple choice exams on scantrons. The rows of “bubbles” for questions are in groups of 10. Sometimes, if I have more than one thing on a test, I have to give students a score out of questions 1-10, and say, 11-20. So I have a custom list that starts with those and goes up to 91-100.

Technorati Tags: excel,tip,18,custom,list

Excel Tip # 17: A Note that Isn’t a Comment

Excel comments are great. But, they do have drawbacks. They can cover your data (at least, temporarily). And they’re public —  they’re great for the downstream user, but maybe not for the developer.

The Barrow offered this clever tip.

There’s a function, =n(), that converts some types of specialized text-like entries into numbers: a common use is to convert a column of True/False into ones and zeros.

But, you can also use it include the equivalent of a comment in the formula in the cell itself — because it converts most strings to a zero.

For example, the formula:

=sum(a1.a9)+n(“This is my sum.”)

will return the correct numerical sum, but the note will only be visible if you go looking for it. And, your downstream user won’t see it even if they do something like open all the comments.

Technorati Tags: excel,tip,17th,note,comments

Excel Tip # 16: A Pitfall In Showing Comments When You Print

If you want to show your comments when you print a spreadsheet, you must turn that option on before you do Print Preview.

Once you are in Print Preview, the toggle to print your comments is deactivated.

Technorati Tags: excel,tip,16,print,comments

Excel Tip # 15: Restrict Users Free Space

Got a worksheet whose blank space you don’t want users to use for scratch work?

Open your Developer menu† (you may need to add this through Excel Options). Then paste this code into a module for the file (you may need to create one‡).

Private Sub Worksheet_Activate()

  'Set/restrict sheet's scroll area.

  Me.ScrollArea = "A1:G15"

End Sub

One drawback of this is that you will now have to save as an XLSM file, and your users will have to enable macros when they open it.

† To display the Developer Tab, click on the Office 2007 button in upper left corner, then on options, and select Developer.

‡ Click the Developer tab, then “Visual Basic”. A window will open. On the left, right-click VBAProject and choose “Insert” then “Module”. Paste the code above into the new window that opens.

Technorati Tags: excel,tip,15,restrict,free,space

Hayek Quote About Economics

Via Don Boudreaux at Café Hayek:

… It is probably no exaggeration to say that economics developed mainly as the outcome of the investigation and refutation of successive Utopian proposals – if by “Utopian” we mean proposals for the improvement of undesirable effects of the existing system, based upon a complete disregard of those forces which actually enabled it to work.

The cite is also quoted from Boudreaux’s post:

From page 19 of Hayek’s 1933 essay “The Trend of Economic Thinking,” reprinted in F.A. Hayek, The Trend of Economic Thinking: Essays on Political Economists and Economic History (Chicago: University of Chicago Press, 1991), pp. 17-34.

Note that this quote is now nearly 80 years old. That’s how long ago people recognized that the sort of economic nonsense that politicians use to appeal to voters was a problem.

Technorati Tags: hayek,quote,utopia