Calculate

calculate1This Analytics Edge wizard lets you perform typical math calculations without formulas, including a number of series calculations across rows or down columns.

Common uses include calculating period-to-period growth rates, levelling out spikes with rolling averages, and scaling numbers to report in thousands or millions.

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.

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.

calculate2New Column Name – enter a name for the column to be added.

1-Column Math Functions – these functions allow you to use the value from the selected column and a number, such as add 1 or multiply by 100, and put the result in the new column. Note that the order is important for functions like subtract and divide, so functions with both arrangements are available.

1-Column Relative Functions – these functions allow you to use the value from the selected column and and express it relative to an aggregate of that column, such as percent of total (Divide by Sum). Note that the order is important for functions like subtract and divide, so functions with both arrangements are available.

2-Column Math Functions – these functions allow you to use the values from two selected columns in the calculation, and put the result in the new column. Note that some

Multi-Column Math Functions – these functions allow you to use values from a range of columns in the calculation, such as the sum of column C through F.

calculate3Pick 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. Note that if the column names might change (dates, for example), do not use this option.

Math – select an operation and enter a value. Adds, subtracts, multiplies or divides the selected columns by the value entered. For example, divide by 1000 could be used to shows numbers in thousands.

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 is also referred to as 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.

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

Updated in version 1.3.1