This Analytics Edge wizard inserts a column with an Excel formula, giving you full access to Excel’s advanced functions. It is evaluated in Excel AFTER you write it to a worksheet, so it should be followed by a Write to Worksheet function.
The formulas are created with column references only, and Analytics Edge will automatically populate the row numbers as it writes to the worksheet (WriteToWorksheet).
New Column Name – enter a name for the new column that will be created.
Insert New Column – select where you want to insert the new column, Before or After the column you pick from the list. Note you can reference the column by position (column letter) or By Name.
Enter formula – enter an Excel formula to be used in each row of the new column. Excel formulas start with an equal symbol ( = ). Reference other columns by picking them from the list below. Column references should be in the format of [Column Name] or [@F]. Do not include row numbers. Analytics Edge will expand the formula, filling in cell references with the appropriate column and row coordinates, when the data is written out to a worksheet. Specific cell references must be entered with as absolute references (e.g. $G$32).
Insert Column Reference… – select a column to add to the formula (optionally By Name) and click Insert. This will insert a column reference into the formula for you.
Important: the formula MUST start with an ‘=’ sign, just as you would enter into Excel. If you do not, you may get an error that Excel cannot accept the data being written.
Important: the formula columns may be lost if you apply other functions before writing, so immediately follow it by a Write to Worksheet function..
Updated for v10.