MacLochlainns Weblog

As I occasionally teach an overload freshman class on Microsoft Excel, it has become very interesting to observe how students perceive the truth of a statement. They generally get the idea that an expression evaluates the equality or inequality of expressions. The idea that value x equals value y (x=y), value x is less than value y (x>y) makes sense but when cell coordinates are substituted it can be a bumpy ride to complete understanding.

The idea of relative cell coordinates (or references) versus absolute cell coordinates takes a bit for some. Raising the ante with partially relative, partially absolute, or as most Microsoft Excel books label them mixed references becomes a challenge that is best overcome by experimentation. I label that type of learning as the Mario Brother’s paradigm, success at level 5 is generally preceded by success at level 4.

A nasty twist though is that Microsoft Excel doesn’t limit truth to the results of expressions. A number or date (a date is only a number with a format mask) is always true provided it isn’t equal to zero. Also, the strings TRUE and FALSE are respectively true or false, as if they were written as the =TRUE() or =FALSE() functions. Any other string value returns a #VALUE! expression. Microsoft Excel does this because it applies a weakly typed programming language rule (similar to Perl or PHP), which assumes any positive or negative number is true, while a zero value is false. This really opens up the use of the =IF(conditional_expression, true_outcome, false_outcome) function to advanced users but can be a stunner to new ones. By the way, Microsoft Excel labels the conditional expression as a logical test but the problem is that the rules governing logical tests generally don’t make sense to non-programmers.

Dates formatting is a bit quirky in Microsoft Excel. It would be wonderful if they’d published rules, noted exceptions, and such. My favorite quirk is that you can’t apply a format mask to any number in the range of (((2^16)*2)-70) to (((2^16)*2)+51) in Microsoft Excel 2007. You can format that range in Microsoft Excel 2008 on the Mac. Beyond that oddity, date numbering starts with 0 being equal to 1-Jan-04 in Excel 2008, while 0 formats to 0-Jan-1900 in Excel 2007. Negative numbers also format in Excel 2008 with a negative sign pre-pending the date.

The mystery question is: Where’s the next gap in Excel 2007? As you can see in the image, negative numbers can’t be date formatted in that version. However, you can evaluate whether they’re true or false notwithstanding the formatting error – those #’s. If you use the =DATE(2258,9,1) you create a date in the missing range – amazing.

Obviously, the Microsoft Engineers have a hack of some kind in place in Excel 2007 because a typical unsigned short is ((2^16)*2), while the =DATE(2258,9,1) function works in the offending range. Perhaps the DATE() function works because it uses an integer. I’d bet there’s another gap too. Gaps are shown in the next image.

As noted by Laurent in his comment, 0-Jan-1900 is false on Excel 2007, which makes sense because it’s an invalid date. Excel 2008 also treats a zero as false, but zero is a valid 1-Jan-1904 date. This means the numeric value of a date differs by 1,462 between the two versions of Excel. That makes dates non-portable between Windows and Mac version of the same product – Wowie!

It is possible to fix Excel 2007 to work with Excel 2008 files. All you need to do is click on the Options button, and then choose the Advanced selection. Inside the Advanced selectoin, you’ll find a When calculating this workbook section. Simply click the Use 1904 date system checkbox to enable your Excel 2007 to work like Excel 2008. Unfortunately, I can’t find an equivalent to set Excel 2008 to work like Excel 2007.

You enter formulas by typing them in the cell or clicking the insert formula button. The button launches a wizard that lets you walk through the basics of entering a formula. An =IF() function looks like this in the formula bar:

Cases for entering a logical expression vary. There are some simple rules. They are:

• Numbers can be compared directly.
• Text must be enclosed by double quotes inside logical expressions.
• Formulas are entered without prefacing equal symbols (=).

The following show you how to create some basic logical expressions inside the function wizard for an =IF() function.

1. The equality of numbers, which returns the true statement:

2. The non-equality of numbers, which returns the false statement:

3. The relation between text. Text is compared based on the ASCII values of the characters in the strings. Capital A maps to 65, capital B maps to 66, et cetera, and lowercase a maps to 91, lowercase b maps to 92, et cetera. Notice that the text “one” is not greater than “only” because the ASCII value of an “e” is lower than the ASCII value of an “l”. The comparison stops with the first letter that differs.

The next teaching point is the idea or when two things must be true, or when at least one thing of two things must be true for an expression to be true. It introduces the students to basic truth statements from Philosophy 101 (which they may not have taken). These are implemented as the =AND() function and =OR() functions, which are illustrated in the truth tables below.

When they understand what’s true and when it is true, they can master any combination of truth or non-truth. Until the concepts are clear it seems impossible, or at least very difficult, for end-users to understand or use the pre-built functions in Excel 2007 or Excel 2008.

I’d love to see cool learning vehicles, Flash widgets and such that I could leverage in the classroom. If you know of any, I’d appreciate a comment directing me and other readers there.