Table calculations are used to perform calculations about a table in an event, profile or database form. Table calculations are only used to perform calculations about data within the table and should not be part of the table. This article covers:

- Types of table calculations
- Aggregating functions for table calculations
- Table calculation syntax
- Steps to build a table calculation

The fields called **Total Session Load**,** Maximum Intensity** and **Total Sets** below the table in the example screenshot above are table calculations. The first one is adding up entries in the **Load** column and the last one is adding up the **Sets** column. Note that these table calculations are not part of the table.

## Types of table calculations

**Table calculations**can be used to calculate numeric values, using aggregate functions, from data entered by a user into columns in a table in the form. For example, a table calculation field could use a formula that returns the average of all values entered into a table column.**Table option calculations**can be used to calculate values, using aggregate functions, from data entered by a user into a table in the form and return a result in option format. For example, a table option calculation field could use a formula that returns an option result of "Pass" when the sum of numeric entries into a column is higher than 60 or "Fail" when it does not.**Table text calculations**can be used to calculate values, using aggregate functions, from data entered by a user into a table in the form and return the results in text format. For example, a table text calculation field could use a formula that returns a result of “All criteria met” if the data entered into an option field column meets certain criteria.**Table date calculations**can be used to calculate values, using aggregate functions, from date data entered by a user into a table in the form and return the results in date format. For example, a table date calculation field could use a formula that returns the newest date from a column of date entries.**Table duration calculations**can be used to calculate values, using aggregate functions, from duration data entered by a user into a table in the form and return the results in duration format. For example, a table duration calculation field could use a formula that returns the sum of all duration entries in a column.

## Aggregating functions for table calculations

Table calculations differ from simple calculations because they use an aggregating function to decide how the data in each table row should be treated. The aggregating function is used in conjunction with the formula entered in the calculation field.

In this example, the table field called **Best 40m** will be calculated using the **minifpositive **aggregate function to return the fastest 40m sprint time from a table that records multiple sprints. Note that the aggregation function options available are different depending on the type of table calculation you are using.

These are some of the most commonly used aggregate functions and their variants:

**Count**will return the number of tables rows with data in them. If**count**is used in reference to columns containing text instead of numbers (exception: fields with scored options), wrap the field with the**safe**function:**safe**(**Field name**). Be mindful that when using the**safe**function empty cells will count as well. The**countifpositive**variation will ignore negative values.**Count**and**countifpositive**should only be used in the table calculation field type.- To return the sum of data from a column, you can use the
**sum**aggregate function. It also has a**sumifpositive**variation. These functions should only be used in the table calculation field type. **Max**,**min**,**mean**,**median**,**mode**,**stddev**,**var**(variance),**sem**(standard error of the mean) all operate in the same way as**sum**. These functions should only be used in the table calculation field type.**Firstvalue**will return the first value entered in a table column. It has variations like**firstpositive**,**firstnonzero**and others that do exactly what their name implies. These functions should only be used in the table calculation field type.**Lastvalue**will return the last value entered on the column. It has same variations as**firstvalue**. This function does not ignore empty cells. These functions should only be used in the table calculation field type.**Lastenteredvalue**is similar to**lastvalue**but will ignore blank cells. These functions should only be used in the table calculation field type.**Textsummarise**will summarize the text from each row of a table column and place a comma between each text. This function should only be used in the table text calculation field type.**Whitespacetextsummarise**is the same as**textsummarise**but will leave a space between the text instead of a comma. This function should only be used in the table text calculation field type.

Note that **nthmax**, **nthmin**, **index** and all of their variations do not operate as aggregate functions for table calculations.

## Table calculation syntax

The example above is a simple table calculation that combines an aggregate function and a field name reference. However, table calculations can be more complicated if necessary. It is possible to create more complex table calculations like adding all **Reps** that were done at over 70% of **Body Mass** by selecting **sumifpositive** as the aggregate type and writing the calculation’s formula as follows:

If(Weight > (0.7 * Body Mass), Reps, -1)

In this example **Body Mass** is a field outside the table and the formula will return either the **Reps** for that row or -1 depending on the condition being met or not. Then, because we are only adding up the positive values, the rows where the condition was not met will be ignored.

A useful trick is the use of the **row** function. This function simply returns the number of the row but by targeting specific rows it opens up a great range of possibilities. For example, using the table calculation field with the **sumifpositive** aggregate and writing the following formula would allow the builder to discard the **Load** values in the first two rows and sum all other positive values for **Load**.

If(Row() > 2, Load, -1)

The most common table text calculation function is **textsummarise**, which allows the builder to extract all the text in a column. With this function all exercises performed in the first example could be extracted as a text string. This would avoid having multiple rows for the same record appear in reports.

Table option calculations are usually used to extract the latest status of something. For example, a physio treatment record would usually have a table to record each individual treatment for a particular injury. After every visit the physio has to update the status of the injury, which is normally an option field. Let’s assume that the options are simply **Open** or **Closed**. A table option calculation could be created by setting the aggregate type as **lastenteredvalue** and entering the field name into the calculation text box:

Injury Status

It is necessary, as with option calculations, to set the options of the table option calculation exactly the same as the options of the fields being referenced (**Injury Status**). When properly built this calculation would result in the current injury status.

## Steps to build a table calculation

- Log into the builder interface.
- Select the
**Event forms**tool. - Create an event form and add the fields you wish to use, choosing the
**Table**entry mode option for each field. - Select the type of table calculation needed on the
**Add question**tab and add this field, ensuring the calculation is not part of the table. - Select an appropriate aggregate function for the calculation from the drop-down list of aggregate functions.
- Write the calculation formula you wish to use in the
**Calculation**tab - Click the
**Preview**button and test whether the calculation is working as expected - Exit preview mode and either continue working on the form or save the form.