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

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.

edit retag reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-04 02:24:36.484175

Sort by » oldest newest most voted

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.

more

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.

( 2013-07-20 01:13:02 +0200 )edit

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.

( 2017-11-17 17:20:01 +0200 )edit