Formula

This Analytics Edge wizard inserts a column with an Excel formula, giving you full access to the spreadsheet application’s advanced functions. It is evaluated in the spreadsheet 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.

Enter formula – enter a spreadsheet formula to be used in each row of the new column. Formulas start with an equal symbol ( = ). Reference other columns by picking them from the Column Reference list. Column references in the formula will 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).

Column Reference – select a column and Add to the formula, optionally By Name. This will insert a column reference into the formula for you.

Insert at – select where you want to insert the new column, Before or After the selected column you pick from the list. Note you can reference the column by position (column letter) or By Name.

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..