Formula

add a calculated column to ExcelThis Analytics Edge wizard, used at the end of a macro, allows you to populate a column with an Excel formula, giving you full access to Excel’s advanced functions. It must be the last transformation in the sequence.

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

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

Formula – enter an Excel formula to be used in each row of the column. Reference other columns by picking them from the list at right. Do not enter cell references unless they are absolute cell references (e.g. $A$1).

Pick Column – select a column to add to the formula (optionally By Name). This will insert a column reference into the formula.

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.

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.