Google Sheets – Using ARRAYFORMULA and QUERY for Multiple Columns

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values of two different variables for those groups.

Using the table below as an example, I would like to determine the AVERAGE and STDEV of two different variables for each unique ID and type (1a, 1b, 2a, 2b, 3a, 3b).

I received assistance on this problem earlier which combined QUERY and ARRAYFORMULA and it worked wonderfully, but that was only for a single variable. Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets.

Using the same formula twice adds additional unique value columns. This not only adds additional clutter, but also seems it could introduce additional errors if the unique ID columns don't match or aren't aligned. If possible, I would like to ensure that the calculations are being pulled from the same set of unique IDs.

The answer to my previous question broke down the process into two steps, the first step grouping unique IDs and providing an AVERAGE for them, and the second step applying STDEV to those groups. Using the table below, the intended output would produce a column for the unique IDs, and then columns for "AverageLength" "AverageWidth" "LengthSD" "WidthSD". If the new formula could provide the first three output columns, the STDEV formula provided from the previous question could be applied to both the length and width data to create the final two output columns.

ID Type L   W
1   a   5   9
1   a   7   8
1   a   9   3
1   b   2   4
1   b   4   1
1   a   6   3
1   a   9   8
1   b   8   5
1   a   1   2
2   a   5   2
2   a   2   1
2   a   4   7
2   b   6   6
2   b   7   5
2   a   8   9
3   b   1   2
3   b   7   4
3   a   5   3
3   a   6   6
3   a   8   9
3   a   9   3
3   b   4   5
3   b   6   4
3   b   3   1

Best Answer

`F2:`

=ARRAYFORMULA(QUERY({A2:A&B2:B, C2:D}, 
 "select Col1,avg(Col2),avg(Col3) 
  where Col1 is not null 
  group by Col1 
  label avg(Col2)'',avg(Col3)''", 0))

`I2 and drag down:`

={STDEV(FILTER(C:C, A:A&B:B=F2)), STDEV(FILTER(D:D, A:A&B:B=F2))}

Related Solutions

Google Sheets – Using IMPORTXML Function to Download Multiple Columns

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service.

Example:

Adaptation of the code taken from the answer by Vidar S. Ramdal

function parseFeedXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
   var feed = document.getRootElement();
   var entry = feed.getChildren('entry',atom);
   for (var i = 0; i < entry.length; i++) { 
     var content = entry[i].getChildren('content',atom);
     var contentElement = '';
     var resultRow = [];
     for (var j = 0; j < content.length; j++) { 
       contentElement = content[j];
       evaluateText(contentElement.getChild('filing-date',atom), resultRow);
       evaluateText(contentElement.getChild('filing-type',atom), resultRow);
       evaluateText(contentElement.getChild('filing-href',atom), resultRow);
       evaluateText(contentElement.getChild('form-name',atom), resultRow);
       evaluateText(contentElement.getChild('size',atom), resultRow);
       evaluateText(contentElement.getChild('amend',atom), resultRow);
       result.push(resultRow);                
     }
   }
   return result;
}

function evaluateText(element, resultRow) {
  if (element) {
    resultRow.push(element.getText());
  } else {
    resultRow.push('');
  }
}

Google Sheets – Fixing ‘An Array Literal Was Missing Values for One or More Rows’ Error

When stacking arrays vertically, they must have the same number of columns. In case of an error, query outputs a single cell with #REF or #N/A or another message. You put iferror wrapper around it, but that only makes it so the output is one empty cell. Problem is, it is one cell and you need 8 columns to match the other array.

Solution: add a blank row with 8 cells as the second argument of iferror, to be used in case of errors.

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL"),  
 {"","","","","","","",""}); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"),
 {"","","","","","","",""})
 }),7,FALSE)

Specifically, the error occurs because there is no sheet named '5 Star Gear Clubs (Hidden)'. Suggestion: when a formula throws an error, enter its parts in separate cells (without iferror wrappers) to see what they do.