CODEWITHSUNDEEP
google-sheets
Crystark
Crystark

Reputation: 4216

AVERAGEIF based on multiple columns in google spreadsheet

I have the following Stylesheet:

       A   |   B
1 | Name 1 | Value 1
2 | Name 2 | Value 2
3 | Name 2 | Value 3
4 | Name 3 | Value 4

I'd like to do an average value of the lowest value for each name. So here the average would be on values of row 1 2 and 4 if Value 2 < Value 4.

That seems a bit complex so I tried an other approach: do the average value of the first entry for each name. I'd just have to order those fields to get the result I want. I tried the following: AVERAGEIF(A:A,"<>INDIRECT(""A""&(ROW()-1))", B:B) but that doesn't seem to work and it takes all rows.

Upvotes: 0

Views: 1281

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

Try this formula:

=AVERAGE(QUERY(QUERY({A1:B4},"select Col1, min(Col2) group by Col1", 0),"select Col2"))

and evarage doesn't look for strings, so this should work too:

=AVERAGE(QUERY({A1:B4},"select Col1, min(Col2) group by Col1", 0))

Upvotes: 1

Related Questions