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?
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)))
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
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.
=QUERY(TRANSPOSE(QUERY(TRANSPOSE(A1:C),
"select "®EXREPLACE(JOIN( , ARRAYFORMULA(IF(LEN(A1:A&B1:B&C1:C),
"min(Col"&ROW(A1:A)-ROW(A1)+1&"),", ""))), ".\z", "")&"")),
"select Col2")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With