Formulas to decide on vertex name

Mar 11, 2010 at 8:22 AM


Is it possible to change the vertex 1 and vertex 2 in edges and vertex in vertices using a formula?  I want to create different views of the information based on a selection criteria. My idea was to put a formula in saying

= if (selectionCriteria = "categoryView"; add all values from categoryView column; remain with default) - but these columns don't appear to accept formulas.


Any ideas?



Mar 11, 2010 at 5:27 PM


Quick answer:  Format the Vertex 1 and Vertex 2 columns as General, and your formulas will start working.

Longer explanation:  The Vertex 1 and Vertex 2 columns are formatted as Text, and formulas don't work in Text cells.  They are formatted as Text instead of General because with General cells, Excel sometimes modifies the text you enter in unwanted ways.  If I enter a vertex named "1/1" in a General cell, for example, Excel "helpfully" changes it to "1-Jan".  It doesn't do this with Text cells.

Several other columns are also formatted as Text, including Label and Tooltip.  If you forget this, hover the mouse over the column header.  With Text columns, you'll get a pop-up message that says "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

Mar 11, 2010 at 5:34 PM

Another point: the Vertex 1 and Vertex 2 columns in the Edges worksheet and the Vertex column in the Vertices worksheet are used to identify but not necessarily LABEL a vertex.  Once you change the formatting of the Label column, you can use formulas there to create a text string that identifies the node without changing its underlying identifier in the edge or vertices worksheets.  Revealing the label columns may require that you "unhide" them using NodeXL Menu:Workbook Columns:Show All.



Mar 12, 2010 at 6:03 PM

Hi Tony and Marc,

Thanks for the suggestions - both work.

My idea was to have an entry cell that allows me to state which relationships within an excel sheet I want to view without having to cut & paste into the vertex. For that Tony's suggestion was the solution.


Thanks again!