19

I've seen some examples of using SUM() inside an ARRAYFORMULA() in Google Spreadsheets (and oddly enough, they all seem like workarounds) but I can't figure out how to apply them to using MIN() instead.

Let's say I have columns A, B and C and I just want to get the result of MIN(A:C) on the D column, just for the three cells that would match each row. The straightforward way should be ARRAYFORMULA(MIN(A1:C)) but surely enough that doesn't work.

How can I programmatically calculate the MIN() of some cells within a row, for all the rows in a Google Spreadsheet?

player0
  • 124,011
  • 12
  • 67
  • 124
lima
  • 799
  • 4
  • 8
  • 26

5 Answers5

16

MIN() always returns a single value, regardless of the range size, so ARRAYFORMULA() does not change the output - it only helps convert formulas that do not handle a range.

As a quick answer, you could simply rewrite the logic of MIN() using something like IF(): =ARRAYFORMULA(if(A:A < B:B, if (A:A < C:C, A:A, C:C), if(B:B < C:C, B:B, C:C)))

Adam B
  • 3,775
  • 3
  • 32
  • 42
  • 4
    This works fine for few columns but becomes *really* nasty as the column number increases. I read the original question as looking for a general solution with "Let's say I have columns `A`, `B` and `C`" as just there to just explain the problem. The solution for four columns in case anyone needs to use it is `=ARRAYFORMULA(if(A:A < B:B,if(A:A < C:C,if(A:A < D:D, A:A, D:D),if(C:C < D:D, C:C, D:D)),if(B:B < C:C,if(B:B < D:D, B:B, D:D),if(C:C < D:D, C:C, D:D))))`. The overall size about doubles for each extra column. 5 columns is just about manageable but takes 312 characters. – AnnanFay Jan 09 '20 at 14:21
  • What is the solution for 5 colums? – Mozart75 Jul 28 '22 at 20:57
4

in D1 try this workaround/formula:

=index(ArrayFormula(transpose(query(transpose(A:C),"select "&join("),","min(Col"&row(indirect("A1:A"&count(A:A))))&")"))),,2)

and see if that works ?

Basically this

  • first transposes numbers in A:C
  • calculates the minimum for each column (QUERY function)
  • then those minimum values are transposed back into rows
JPV
  • 26,499
  • 4
  • 33
  • 48
3

Some "statistical" and "math" type functions - AVERAGE, COUNT, MAX, MIN, SUM - each have a corresponding *IF and / or *IFS variant that, when combined with the INDEX function, seem to satisfy this sort of need.

For example, I have a sheet named monthly with dates in column A and dollar amounts in column B. I wanted to get the minimum dollar amounts for rows where the date was in the year 2021.

Combining INDEX and MINIFS worked for me ...

=INDEX(MINIFS(monthly!$B$2:$B, YEAR(monthly!$A$2:$A), 2021))

Functions of this type include ...

Singular forms (*IF) match rows based on a single criterion. Plural forms (*IFS) match rows based on multiple criteria.

RH Becker
  • 1,692
  • 1
  • 14
  • 11
1
=QUERY(TRANSPOSE(QUERY(TRANSPOSE(A1:C), 
 "select "&REGEXREPLACE(JOIN( , ARRAYFORMULA(IF(LEN(A1:A&B1:B&C1:C), 
 "min(Col"&ROW(A1:A)-ROW(A1)+1&"),", ""))), ".\z", "")&"")),
 "select Col2")

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Is the `&""` necessary after the `REGEXREPLACE` call? – AnnanFay Jan 09 '20 at 14:29
  • @AnnanFay yes. for safety reasons only tho. it depends on your dataset. regex formulas cant work with numeric values so by adding this chunk to it it will convert numeric values into text-string values. – player0 Jan 09 '20 at 15:58
1

Updated solution from JPV to: avoid dropping the last row, allow for smaller ranges, and to be shorter.

Data in A:C and Formula in D1:

=TRANSPOSE(index(query(TRANSPOSE(A:C),"select "&textjoin(", ",1,arrayformula("min(Col"&ROW(A:C)&")"))),2))

Data in A2:C14 and Formula in D2:

=TRANSPOSE(index(query(TRANSPOSE(A2:C14),"select "&textjoin(", ",1,arrayformula("min(Col"&ROW(A2:C14)-Row()+1&")"))),2))

Explanation

  • Convert rows into columns with transpose
  • Query data with select min(Col1) for each column
    • Use textjoin to generate min queries (e.g. min(Col1), min(Col2), ...)
  • Use index to remove query headings
  • Use Transpose again to convert results from columns to rows
Benjamin Ziepert
  • 1,345
  • 1
  • 15
  • 19