Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using MIN() inside ARRAYFORMULA()

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?

like image 887
lima Avatar asked Apr 27 '15 04:04

lima


4 Answers

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

like image 178
Adam B Avatar answered Sep 20 '22 13:09

Adam B


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
like image 25
JPV Avatar answered Sep 22 '22 13:09

JPV


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.

like image 29
RH Becker Avatar answered Sep 20 '22 13:09

RH Becker


=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

like image 34
player0 Avatar answered Sep 23 '22 13:09

player0