Calculating values based on "Locked?" and "Marked?" column values

Aug 7, 2009 at 5:18 PM

I'd like to change visibility on the vertices sheet as a function of whether a vertex is Locked or Marked in the appropriate column, but I can't figure out the appropriate argument for the =if() function.  I want to use something like

=IF(Vertices[[#This Row],[Locked?]]=0,2,4)

for visibility, but the expression

Vertices[[#This Row],[Locked?]]=0

always evaluates to False, as does

Vertices[[#This Row],[Locked?]]="No"


Vertices[[#This Row],[Locked?]]="No (0)", etc.

Evidently I don't understand the data type that you're using in the Locked and Marked columns...

Thanks for your help, and a marvellous tool

--- Pierre

Aug 7, 2009 at 6:36 PM


This formula will set the Visibility column to 1 (which is the same as "Show if in an Edge") if the vertex is locked:

=IF(Vertices[[#This Row],[Locked?]]="Yes (1)", 1, 0)

(That reads: "if the Locked column is the exact text "Yes (1)", set the Visibility column to 1.  Otherwise, set it to 0.")

Similarly, this formula will set the Visibility column to 1 if the vertex is marked:

=IF(Vertices[[#This Row],[Marked?]]="Yes (1)", 1, 0)

And this formula will set the Visibility column to 1 if the vertex is locked OR marked:

=IF(OR(Vertices[[#This Row],[Locked?]]="Yes (1)", Vertices[[#This Row],[Marked?]]="Yes (1)"), 1, 0)

-- Tony


Aug 8, 2009 at 10:20 PM

Thanks, Tony.

I encountered the following behavior; I don't know if it's a bug or a feature: Filter the Visibility column on the "1" value, and click "Refresh Graph" on the canvas window. All the nodes, including the rendered ones are now shown. The locked nodes which are still visible, but they're crowded into the middle of the canvas. (I'm using FR for layout.)

When I go back and clear the filter and Refresh Graph, only the locked ones are shown.

If I filter on "0", then curiously only the locked nodes, which have visibility "1", are rendered when I click Refresh Graph - though they behave as if they're unlocked, and FR scrambles up all their carefully laid out positions..

(Build 88)

--- Pierre

Aug 8, 2009 at 11:16 PM

And here's another peculiar behavior, which may or may not be related: In another tack on trying to hide nodes I don't need, and lock in place and make visible the others, I removed the formula and showed all nodes.  Some were locked, and some were not, from earlier work.

I started selecting nodes in the canvas and editing the visibility property in "Edit Selected Node Properties" to Visibility "Skip (0)".  Everything went fine for a while, and then suddenly the Lock attribute stopped working.  Doing a Refresh Graph or Lay Out Again led to all the nodes moving around, including supposedly locked ones.  I tried closing Excel and starting again, but the behavior continued.

I this state the Select nodes, Edit Selected Node Properties to Visibility "Skip (0)" trick also stopped working: nodes with that changed property didn't disappear triggering a canvas redraw.

--- Pierre

Aug 9, 2009 at 8:20 PM


I think I can explain what you're seeing.  It's by design, although the design has a few surprising side effects, as you've discovered.

By default, a vertex listed in the Vertices worksheet is shown if it is in an edge.  As you know, you can skip it and make NodeXL act as if the vertex row isn't even there by changing the Visibility from "Show if in an Edge (1)" to "Skip (0)."

However, if you use Excel's table filtering (the down-arrows in the column headers) to filter out rows for which Visibility is "Skip (0)," those rows get hidden from NodeXL and the vertices revert to their default visibility of "Show if in an Edge (1)."  So you set the visibility of vertex A to "Skip (0)," but by hiding the visibility (and the entire row), the visibility setting has no effect and vertex A gets shown.

I'm not sure I understand the sequence you followed with locking, but I bet it can be explained the same way.

To avoid this kind of behavior, it might be best to use either Excel table filtering or the Visibility column, but not both.

-- Tony


Aug 11, 2009 at 10:50 PM

Very helpful as always, thanks Tony

A couple of follow-ups:

1. I find it useful to use filtering to clean up columns. For example, I might mark vertices on the canvas and use a formula to show their names, but then afterwards I want to clear the marks. It seems easiest to go to the Marked? column, filter on just the Yes entries, and delete all of them - the nodes could be scattered through the list.  What should I do to prevent the chart from updating in the meantime?  I could just close the canvas, but sometimes it doesn't seem to come back, and I have to close and reopen the worksheet.  Is it OK to "filter, manipulate, clear filter" as long as I don't click Lay Out Again or Refresh Graph in the middle of the manipulation?  (An interim/alternative solution is to sort the Marked? column.)

2. A feature request: it would rather nice if "Show if in an Edge" was completely literal; that is, when the canvas is in a state where a node temporarily doesn't have any edges connected to it, the node would be invisible.  For example: I have edges that come and go at different time steps.  I create a column for those time steps, and then use Dynamic Filtering to range through the steps.  At some time steps a node will have edges, and at other times it won't. It would be convenient to have the option to show a node only in those time steps when it also has edges.  In particular, since I have a lot of nodes, many of them with long names, I need a way to hide most of the node labels most of the time. (That's why I do the filtering in #1 above; a kludge, but the best solution I've found so far.)  With this feature, I could have all nodes have name (secondary) labels, but since nodes (and their labels) would only be visible when they have edges, and all don't have edges in every time step, the number of labels that would have to be accommodated on the canvas would be a lot smaller. With the current design, a node seems to be shown in every time period as long as it has an edge in any time period.

--- Pierre

Aug 13, 2009 at 6:37 AM

Sorry for the delay on this -- I haven't been getting the usual CodePlex notifications of new postings.  I'll respond shortly.

-- Tony

Aug 13, 2009 at 5:24 PM


On your follow-ups:

1. You should be able to do anything to the workbook without affecting the graph pane, provided you don't click Refresh Graph, Lay Out Again, or use Dynamic Filters.  Those are the only times that the graph is updated with the workbook contents.

If you close the graph pane, clicking Refresh Graph makes it reappear.  You can also make it reappear without refreshing the graph by using View, Show/Hide, Document Actions in the Excel Ribbon.  ("Document Actions" is the generic name Excel gives to anything that appears in the right pane, which is the graph pane in NodeXL.)

2. Good idea.  I've updated the feature request list to add an option for the behavior you've described.