Using MIN () inside ARRAYFORMULA ()
I've seen several use cases SUM()
internally ARRAYFORMULA()
at Google Spreadsheets (and oddly enough they all seem to be workarounds), but I can't figure out how to apply them to use MIN()
instead.
Let's say I have columns A
, B
and C
, and I just want to get the result MIN(A:C)
in a column D
, for only three cells that will match each row. There should be a direct path ARRAYFORMULA(MIN(A1:C))
, but it certainly doesn't work.
How can I programmatically calculate MIN()
some cells in a row for all rows in a Google Sheets?
source to share
MIN()
always returns one value regardless of the size of the range, so it ARRAYFORMULA()
doesn't change the result - it helps transform formulas that don't handle the range.
As a quick answer, you can simply rewrite the logic 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)))
source to share
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 it works?
Mainly
- first transfers numbers to A: C
- calculates the minimum for each column (QUERY function)
- then these minimum values are wrapped back into lines
source to share
