How to use max() with arrayformula()

Question

This is my sheet :

in column E, i want to find the max among col A,B,C using this simple formula in E1 :

=max(A1:C1)

But rather than pasting the formula to E2 .. E10, i want to see if possible to use max() in arrayformula() ? so i can use single formula for the whole column E.

Answer 1

Formula

=BYROW(A:C, LAMBDA(r, 
   IFERROR(MAX(r)/(COUNTA(r)>0))))

• Uses COUNTA(r)>0 to return 1 if there are any values in the row, or 0 if the row only contains blank cells.
• Dividing by zero always returns an error which IFERROR handles by returning an empty value, which effectively skips blank rows.

Answer 2

If your data to compare is really only in three columns (A, B, C), you can simplify things with this formula in E1:

=ArrayFormula(IF(A:A="",,IF((A:A>B:B)*(A:A>C:C),A:A,IF(B:B>C:C,B:B,C:C))))

This reads "If any row of A is blank, return null; otherwise, if A is greater than B and A is greater than C, return A; otherwise, if B is greater than C, return B; otherwise, return C."

If you will have more columns than three and you know you will have fewer than 5500 rows of data and your numbers to compare will be similar in length to those shown in your post (i.e., numbers with 3 or fewer digits), then you can use this in E1:

=TRANSPOSE(INDEX(QUERY(TRANSPOSE(FILTER(A:C,A:A<>"")),"SELECT "&JOIN(",","MAX(Col"&SEQUENCE(COUNTA(A:A),1)&")")),2))

This essentially takes all non-null rows, TRANSPOSEs them, forms a QUERY that retrieves the MAX from as many columns as the original data set has rows, and then TRANSPOSEs everything back to the original orientation again.

The limitations are based on the fact that JOIN can only concatenated up to 50,000 characters. So if your real numbers may be up to 6 characters long, you can get about 4500 rows out of the above formula, etc.

Answer 3

The cleanest way to do this is with either byrow or bycol formulas. If you're looking for the max value of multiple rows, then if would look something like this:

=bycol(A1:C5,lambda(_c,max(_c)))

You can swap the logic to be byrow, and/or insert empty value logic into this as you need.

Answer 4

The lambda way

The max() function is an aggregating function so it will not give row-by-row results in an array formula.

To iterate the data row by row, use byrow() with a lambda function that gets the max value on each row separately, like this:

=let( 
  data, A1:C, 
  rowMax_, lambda(r, iferror(1 / max(r) ^ -1)), 
  byrow(data, rowMax_) 
)

See let(), lambda(), byrow() and iferror().

The old way

Before lambda functions became available in September 2022, row-by-row aggregation of data was often implemented with array formulas using the likes of mmult(), sumif, countif(), countifs() and query(), or "database" functions such as daverage(), dcount(), dcounta(), dget(), dmax(), dmin(), dproduct(), dstdev(), dstdevp(), dsum(), dvar() and dvarp().

One pattern to get row-by-row max values is to apply transpose() and use dmax() with blank criteria created with iferror(), like this:

=arrayformula(iferror( 
  1 / dmax( 
    transpose({ row(A1:C), A1:C }), 
    row(A1:C), 
    iferror(A1:C / 0) 
  ) ^ -1 
))

See dmax(), transpose(), row() and iferror().