This Analytics Edge wizard lets you perform typical math calculations without formulas, including a number of series calculations across rows or down columns.
Add a New Column
You can choose to add a new column to your table, and the new column will contain the results of the calculation.
New Column Name – Enter a name for the column to be added.
Column A – Select the first column to use in your math operation, optionally By Name or by position (column letter).
Math Functions – Select the math operation you want to perform. Depending on your selection, some of the other options will change. Operations usually involve 2 columns (A and B) such as Adding A + B.
Note that some functions involve a range of columns, such as Average (A to B). In this case, the function will average the selected columns A and B, as well as all columns between them.
Column B – Depending on the function you choose, you can enter a Value (click the button to use an Excel cell reference to the value). The second option is to use an aggregate, such as Column Sum. The third option is to pick a specific column By Name or by position (column letter).
Replace Existing Values
Alternatively, you can choose to replace the values in the table with the results of the calculation, such as a rolling average or percent of column total.
Math – select an operation, and enter a value or use the button to use an Excel cell reference to a value. This performs the operation on the selected columns.
Cumulative – select a function and a direction. Performs a cumulative operation down each column selected, or across the rows of the selected columns. For example, a cumulative sum down each column (a running total) where each cell would contain the sum of all of the cells above it.
Repeating – select a function and a direction. Performs the operation down each column selected, or across the rows of the selected columns. For example, repeating difference down the column would replace each cell value with the difference from the cell above it.
Rolling (moving) – enter a value and select a function and direction. Performs a rolling function down each column selected, or across the rows of the selected columns.
Relative – select the type of comparison, the axis to compare to, and the aggregate to compare with. Each of the selected columns is changed to that new comparison value.
Select Columns – The columns in the table are listed with their column letter (A, B, C, etc) for reference. Select one or more columns (they will be highlighted in yellow), then select one of the match operations to apply.
To select multiple individual columns, you can hold the Ctrl-key down and click. To select a range, pick one then hold the Shift-key down and select the other end of the range, or hold the mouse key down and drag down the list.
By Name – check this box to select the columns using the name of the column instead of its position. This is useful if the column order might change over time. Do not use this option if the column names might change; like dates, for example.
ZZZ Last Column – automatically selects columns to the right of any selected columns. This is typically used with reports that have an unknown or inconsistent number of columns, such as days of the month. Select the first column to use, then pick the ZZZ Last Column entry.
Updated for v10.