How do I make an array formula out of a function that normally takes array arguments?

[FragileX]

What I’ve managed to do successfully:

{=SIN(A1:A3)}

This formula will iterate the sine function over rows 1 through 3, returning {SIN(A1);SIN(A2);SIN(A3)}. I can then pass the array to the AVERAGE function:

{=AVERAGE(SIN(A1:A3))}

This returns a single number, the average of the three sines.

However, I am unable to get the same behavior out of an inner function that normally takes an array. For example, if I wanted to find the max number from columns A through C over three rows, I might write something like this:

{=MAX((A1:A3):(C1:C3)))}

However, this statement will search the 3x3 matrix for the single largest number and return it, i.e. {MAX(A1:C3)}. Is there any way to get each row to evaluate individually, to get {MAX(A1:C1);MAX(A2:C2);MAX(A3:C3)}?

And yes, I know that I could just use a column of intermediate cells to hold the MAX values and then AVERAGE them, but I’d rather not clutter up the spreadsheet.

[mariosv]

If I am not wrong, I think a formula like this can do the job:

{=IF((A1:A3>C1:C3);A1:A3;C1:C3)}

I think the issue with MAX() by their own nature always returns the maximum in the scope of the function.

[FragileX]

Good idea. However, that formula leaves out column B. I think a full replacement would look like this:

{=IF(AND(A1:A3>B1:B3,A1:A3>C1:C3),A1:A3,IF(B1:B3>C1:C3,B1:B3,C1:C3))}

I’d still prefer to know how to use the MAX function if able, since it doesn’t balloon as the number of columns increases.

[MarkJeronimus]

I had the same problem with the AND and OR functions. They aggregate the entire range before applying the array just like MAX. Those can be replaced with * and + respectively.