Formula

This Analytics Edge wizard inserts a column with an Excel formula, giving you full access to Excel’s advanced functions. It must be the last transformation before writing to a worksheet.

The formulas are created with column references only, and Analytics Edge will automatically populate the row numbers as it writes to the worksheet (WriteToWorksheet).

Column Name – enter a name for the new column that will be created.

Insert 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 for New Column – 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).

Pick Column – select a column to add to the formula (optionally By Name). 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 will be dropped if you apply other functions before writing to a worksheet, so add this as your next-to-last step in the macro.

Updated for v8.2.