How to Perform Mathematical Calculations on Tables

This article describes how to perform basic mathematical functions on tables in Displayr using the Calculation menu. These functions include Divide, Multiply, Subtract, Average, Sum, Maximum, Minimum, Any of, None of, Count, Standard Deviation, and Variance.

For example, we can go from a table like below...

...to one that has been divided by 2...

...or a table that calculates the average of each column:

Requirements

• A Displayr document with a data set.
• One or two tables or tabular outputs.

Method - Divide, Multiply, Subtract

1. Select the one or two tables you wish to perform the calculation on. Note, the order you select these tables determines where they are placed in the calculation.

When working with multiple tables, the calculation will be performed across the tables for each cell provided they have common row and column labels and the same dimensions. In this example, we will select a single table only. 

2. Go to Calculation > Divide/Multiply or Subtract in the toolbar.

3. OPTIONAL: In the object inspector, you can adjust which Rows/Columns to exclude by name. By default all rows and columns apart from NET and SUM will be selected.

4. OPTIONAL: You can change the selected outputs by using the relevant drop-down.

5. OPTIONAL: If you only select a single table, it will default to a Single numeric value of 1 for the second reference. You can change this value as required to update your calculation. Here, we have changed this to 2.

6. OPTIONAL: You can additionally switch either reference between an Output and a Single numeric value if required.

7. OPTIONAL: Update Properties > APPEARANCE > Decimals and Number format as required.

Method - Average, Sum, Maximum, Minimum, Any of, None of, Count, Standard Deviation, Variance

1. Select the one or more tables you wish to perform the calculation on. Note, the order you select these tables determines where they are placed in the calculation.

2. Go to Calculation > Average/Sum/Maximum/Minimum/Any of/None of/Count/Standard Deviation/Variance in the toolbar. You can then choose from any of the below options:

• Average
• Average Each Row/Column
• Sum
• Sum Each Row/Column
• Maximum
• Maximum Each Row/Column
• Minimum
• Minimum Each Row/Column
• Any of
• Any of Each Row/Column
• None of
• None of Each Row/Column
• Count
• Count Each Row/Column
• Standard Deviation
• Standard Deviation Each Row/Column
• Variance
• Variance Each Row/Column

Note, on a single table, the options without Each Row or Each Column will return a single value from all cells. When working with multiple tables, the calculation will instead provide averaged or summed values across the tables for each cell provided they have common row and column labels and the same dimensions and statistics. The row and column-specific functions, on the other hand, can only be performed on a single table. In this example, we will perform a column average on one table.

3. OPTIONAL: In the object inspector, you can adjust which Rows/Columns to exclude by name. By default, all rows and columns apart from NET and SUM will be selected.

4. OPTIONAL: You can change the input table in the drop-down as required. If using Average, Sum, Maximum, Minimum, Any of, None of, Count, Standard Deviation, or Variance, you can also add or remove inputs.

5. OPTIONAL: If you select more than one input table, you will have the additional option to Automatically match elements.

You can choose between the following options:

• Yes - hide unmatched - keeps only the matching rows/columns.
• Yes - show unmatched - shows both the matching rows/columns and the unmatched rows/columns by appending the latter like a merged table.
• No - does not match on labels.
• Custom - lets you apply different settings for columns and rows.

6. OPTIONAL: Untick Calculate for inputs with incomplete data if you wish to return missing for any record that has missing data in any of the selected variables.

7. OPTIONAL: For all versions of the Any of, None of, and Count options where you are working with numeric data, you can additionally adjust Values to count. This takes the format of individual specified values or a range of values, e.g. 0-10 or 1,2,3 or >1. When working with categorical data, you instead specify the Categorical labels to include.

8. OPTIONAL: Update Properties > APPEARANCE > Decimals and Number format as required.

Next

How to Perform Mathematical Calculations on Variables

How to Perform Mathematical Calculations Using R

Bespoke Analyses and Calculations