# How to use max() with arrayformula()

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.

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, `TRANSPOSE`s them, forms a `QUERY` that retrieves the `MAX` from as many columns as the original data set has rows, and then `TRANSPOSE`s 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.

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