Nested ArrayFormula in Google sheets - AverageIfs on Uniques

Question

I am trying to get a way to apply some function to values in a table where the descriptor is in an array of unique descriptors.

For example, averaging all of the values for a unique descriptor. However, I have the unique array function, so I would like to just apply it to all of the values given by an array given by vlookup.

I would like this formula working as an array formula (input in one cell that creates the length of array that is required, rather than having to copy an unknown amount of rows)

=ARRAYFORMULA(AVERAGEIFS(values_to_avg,descriptors,UNIQUE(descriptors)))

or, for a more general solution, we would like something which can take different functions than average - my poor attempt was this:

=ARRAYFORMULA(AVERAGE(ARRAYFORMULA(VLOOKUP(UNIQUE(descriptors),CHOOSE({1,2},descriptors, values_to_avg), 2,0))))

So, my hope was to VLOOKUP all the values for a unique descriptor, where the range has been rearranged by CHOOSE. Now we want all the values where this unique desirptor is, so we have to use ARRAYFORMULA around that. Then we want the average of all of these values, but for each unique desriptor, so we need another arrayformula around that.

However, this does not work.

So I am unsure of how to nest array formulas with VLOOKUPs or FILTERs.

Does anyone have suggestions to get this with an arrayformula?

EXAMPLE:

I have two columns of expermental data or user input:

sheet

and I would like to get an array of the averages for all of the unique values. Something like this:

=ARRAYFORMULA(AVERAGEIFS($A$2:$A,$B$2:$B,UNIQUE($B$2:$B)))

or this:

=ARRAYFORMULA(AVERAGE(arrayformula(VLOOKUP(UNIQUE($B$2:$B),CHOOSE({1,2},$B$2:$B, $A$2:$A), 2,0))))

Can this nested array formula function work?

Thank you so much! This is a great function. Now I just have to figure out a way to get standard deviance in the query as well. — chase

Ed Nelson Ed Nelson · Accepted Answer · 2015-12-06T23:00:51

Query works great for avg, however STDEV is not available with query. Arrayformula seems to have a problem with column lengths not matching after filtering for unique descriptor. The best I have come up with is in D2 of you sample data put

=IFERROR(average(FILTER($A$2:$A$7, $B$2:$B$7=$C2)),"")

In E2 put

=iferror(stdev(FILTER($A$2:$A$7, $B$2:$B$7=$C2)),"")

To solve the problem of the formulas as new data is added. Put the following in the script editor and run it. It will fill the formulas down.

function FillFormulasDown() {
var ss= SpreadsheetApp.getActiveSpreadsheet()
sheet= ss.getActiveSheet()
var originRange = sheet.getRange("D2:E2");
var target = sheet.getRange("D3:E10");
originRange.copyTo(target);
}

I set the target (how far down to copy the formula to 10 just for testing) to D3:E10 however D3:E would fill the entire sheet. The iferror will show these as blank until data is available. See if this works for you.

Nested ArrayFormula in Google sheets - AverageIfs on Uniques

1 Answers