Wednesday, February 09, 2011


A few weeks ago, I built an Excel spreadsheet for the department of Institutional Research. It was a basic statistical analysis that generated a report for course evaluations. The department liked it so much that i was asked to adapt it for the online course evaluations. Initially, the adaptation was fairly easy, there are a few more questions on the online evaluation so insert a few lines, copy a few formulas and bingo.

Here's where the hitch came in. The data that gets imported for analysis comes from a different source than the original sheet. On the first version, anytime a "N/A" was selected, and asterisk was marked for data; in the Excel sheet, it was simple to count the asterisks and determinie how many "N/A's" were selected. The problem is that the data source for the oline courses designates a NULL character, the same NULL character that lives in every other cell on the sheet, even within the data range...hmmmm.

Alas, my quest to win "most creative use of a tilde character in a spreadsheet". I replaced all of the cells within the data range to start with a ~, a character unlikely to be typed accidentally; it worked like a charm. Feeling cocky afterward, I created a macro to repoplate the data range with the tlde character with a simple ctrl-j keystroke.

Okay, I am done, you can wake up now.

No comments: