Using Min and Max functions inside ArrayFormula

Question

I have multiple columns of dates in a Google Sheets spreadsheet (created using arrayformula) Some values have dates, some are blank.

For each row I want to find the ealiest date, but Min() function does not work inside ArrayFormula(). How to do this?enter image description here

share a copy of your sheet with example of desired output

player0
– player0

2021-10-13 08:25:04 +00:00
Commented Oct 13, 2021 at 8:25 — player0
– player0, Commented Oct 13, 2021 at 8:25

Mike Steelson · Accepted Answer · 2021-10-17 06:09:31Z

1

Try

=query(transpose(query(
{transpose(D2:J)},
"select "&"min(Col"&arrayformula(textjoin("),min(Col",true,sequence(1,rows(D2:J),1,1)))&") ",
0)),"select Col2",0)

the core of the formula is to transpose values so that they are in columns, and then calculate the min(Col1), min(Col2), ... until min(ColX)

Explanation

1- transpose your array

{transpose(D2:J)}

2- apply query

3- select min(ColX) the suite min(ColX) is obtained by

min(Col"&arrayformula(textjoin("),min(Col",true,sequence(1,rows(D2:J),1,1)))

4- finally, transpose the result and select only Col2 to erase the labels

edited Oct 17, 2021 at 6:09

answered Oct 13, 2021 at 3:51

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Craig Avis Over a year ago

Thank You! I am having trouble understanding it, but it worked when I plugged it in, so thank you!

Mike Steelson Over a year ago

I updated my answer with some explanation, especially the third ... try it alone to see the result

Collectives™ on Stack Overflow

Using Min and Max functions inside ArrayFormula

1 Answer 1

Explanation

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related