## Introduction

This article describes how to add an average...

...of specified rows to a table:

## Requirements

A categorical table. In this example, we are averaging the Coke brand row percentages and creating a new row called *Coke Average %*.

## Method

1. Select your table.

2. Go to **Automate > Custom Rule**.

3. Paste the below into the dialog:

includeWeb("JavaScript Array Functions");

form.setHeading('Creates an Average % of Specified Rows');

form.setSummary('Creates an Average % of Specified Rows');

// Get the list of statistics on the table.

var statistics = table.statistics;

// What is the last row in this span?

var last_row = table.numberRows - 1;

// Add a new Average row.

var row_name = "Coke Average %";

table.insertRowAfter(last_row, row_name);

// Remember the index of the new Average row.

var average_row = last_row + 1;

// Specify labels and statistic to use

var specified_labels = ["Coca-Cola", "Diet Coke", "Coke Zero"];

var statistic = "%";

// Check for duplicates in row labels / entered labels

var row_labels = table.rowLabels;

var dupe_check1 = arrayHasDuplicateElements(specified_labels);

var dupe_check2 = arrayHasDuplicateElements(row_labels);

if (dupe_check1)

form.ruleNotApplicable('the specified row labels have duplicates');

if (dupe_check2)

form.ruleNotApplicable('the table has duplicate row labels');

// For each row, sum its statistics.

// For each column...

for (var column = 0; column < table.numberColumns; column++) {

// For each statistic in the table...

for (var stat = 0; stat < statistics.length; stat++) {

var values = table.get(statistic);

var sum = 0;

var count = 0;

for (var row = 0; row < last_row+1; row++) {

if (specified_labels.indexOf(row_labels[row]) > -1) {

sum += values[row][column];

count++;

}

}

// Store the average in the new Average row.

values[average_row][column] = sum / count;

// Store the values of the Average row into the table.

table.set(statistics[stat], values);

}

}

- We first define the labels of the rows we wish to sum and the statistic to use.
- A validation check for rows with duplicate labels is performed.
- We use the
`insertRowAfter`

function to add the new row to the very end of the table. - We then loop through each column and sum the values of any rows with the specified labels.
- Finally, we use
`table.set`

to set the values for the new row.

4. OPTIONAL: Change *specified_labels* to include the table row labels you wish to use in the calculation.

5. OPTIONAL: Change *statistic* to the table statistic you wish to use in the calculation.

6. OPTIONAL: Change *row_name* to the name of the row that will store the average calculation.

7. Press the **Play** button **> Close** **>** **OK**.

## Comments

0 comments

Article is closed for comments.