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
insertRowAfterfunction 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.setto 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.