MacLochlainns Weblog

How you add an image to a comment isn’t exactly what I’d call easy but it is straightforward. One of my students noted that a tip in Microsoft Office Excel 2007 Bible didn’t provide the magic to insert an image.

I’d have to say that it did but it didn’t. Basically, the general idea is there but a step of magic requires something special. You need to place your cursor on the highlighted edge of an editable comment before right clicking to launch the context menu. If your cursor is on the text portion of the comment, the context menu operates differently. The Format Comment … menu choice launches a different set of options dependent on your location in an editable comment.

Hoping to help my students and others learn the magic, here are some steps.

I remember when the Mac version of the office suite was like the Windows version. It’s so disappointing to find more shortfalls between the two. Here is a dialog that appears in Excel 2008 when you try to open an Excel 2007 workbook that includes the powerful new table components:

A year after the release of Excel 2007, and Microsoft didn’t include structured references, Excel tables, or table styles … What were they thinking about up in Redmond? Were they thinking that they should release something less on a Mac to sell Windows Vista? It couldn’t be that, could it?

I was doing some cross product testing between Excel 2007 (PC) and Excel 2008 (Mac) and got a big surprise! The number representing a date differs by 1,462. Excel 2007 starts using integers at 1 equal to 1-Jan-1900, but Excel 2008 starts with 0 equal to 1-Jan-1904. It clearly means that dates should be uploaded as formatted strings rather than numbers when loading them into a database. That also appears to mean any shared computations linked to dates across a collection of PC and Mac computers fails unless you account for the difference. I’ve updated that earlier post on what’s true with the details.

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.

I thought it would be nice to walk through the Excel 2008 configuration steps to query Oracle. I was quite surprised when navigating the path, this error dialog was thrown:

When you navigate to the Microsoft web site, you’ll find that you have a choice of an ODBC driver from Open Link or Actual Technologies. You might think that Oracle would have their own ODBC driver that you can use without paying for a 3rd party solution. Unfortunately, there isn’t one. The most recent kits are missing the libsqora.so shared library. The only ones that I could find are for the Mac OS X Tiger edition.

I may have missed something but you’ll find the Oracle documentation here. Feel free to comment with a solution. My solution is to use Code Weaver’s CrossOver Mac, and Microsoft Office 2007. How I regret the money wasted on Microsoft Office 2008.

A quick note, addendum, it looks like Actual Technologies is the best. Unfortunately, they charge for one copy for Oracle and another for MySQL and Postgre. What a discouraging note, but I may bite the bullet on the $60 bucks for both. I’ll defer the MySQL and Postgre until they release their 2.9 version. Don’t forget to also download Microsoft’s Query tool.

I caught a post on the OTN forum asking how to do this, and it happened to be something I’m working on for a new course that I’ll be teaching on data analytics. Ultimately, Microsoft Excel is the de facto tool of many accounts and financial analysts, protests notwithstanding.

This shows you how to query an Oracle 11g database from Excel 2007. Actually, it should work on any current version of the Oracle database. The key to making this work is having the Oracle 10g Client software or an Oracle 11g database on the same machine. The Oracle client software allows you to resolve an Oracle Network Alias (found in the %ORACLE_HOME%\network\admin\tnsnames.ora file).

The steps are provided in this blog page …