2

This is my sheet :

enter image description here

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.

4 Answers 4

1

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.

1

Exclude Zero

=BYROW(A:C, LAMBDA(r, 
   IFERROR(1/(1/MAX(r)))))

Include Zero

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

Formulas will both skip empty rows.

If some values may be negative numbers or zeros then use the second formula.

0

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

1
  • dmax() expects a header row, but a virtual header can be inserted as shown in the new formula. Aug 22, 2023 at 8:12
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.