0

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.

2 Answers 2

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.

0

Move your data down one row, and insert a column before column A, so that the data table is in the range B2:D. Then put this formula in cell E2 to show the max value in each row in B2:D starting in row 2:

=arrayformula( ( 1 / ( 1 / dmax( transpose(A2:D), sequence(rows(A2:D)), transpose(iferror(column(A2:D) / 0)) ) ) ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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