Why Excel formulas sometimes don't work

Sep 5, 2009 at 6:21 PM

Several people have run into a problem where a formula inserted into a NodeXL column doesn't work.  Let's say you want to fill the Primary Label column on the Vertices worksheet with the vertex name prefixed with "Name: ", like "Name: John".  If you try to accomplish this by inserting the following formula into the Primary Label column:

="Name: " & Vertices[[#This Row],[Vertex]]

the formula will not get evaluated, and what you'll see as the primary label in the graph pane is the formula itself.  Not very useful.

The reason for this is that the Primary Label column is formatted as Text, and Excel formulas don't work in Text columns.  The fix is to reformat the column as General or one of the numeric formats before inserting your formula.

Why is Primary Label formatted as Text?  It's to prevent Excel from performing unwanted conversions on the cell values.  For example, when I enter "1-1" into a General cell, Excel "helpfully" converts it to "1-Jan" because it thinks I meant to enter a date.  I didn't, and it's annoying.  The Text formatting prevents such conversions.

Columns formatted as Text include vertex names, Primary Label, Secondary Label, and Tooltip.  If you hover the mouse over the column header for these columns, you'll see the following reminder:

"This column is formatted as Text, which causes formulas to be ignored.  If you want to use an Excel formula in this column, you must change the column format to General."

-- Tony