Using MIN() inside ARRAYFORMULA()

Question

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?

Answer 1

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)))

Answer 2

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

Answer 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 ...

• AVERAGEIF, AVERAGEIFS
• COUNTIF, COUNTIFS
• MAXIFS (singular form doesn't seem to exist)
• MINIFS (singular form doesn't seem to exist)
• SUMIF, SUMIFS

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

Answer 4

=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")