Ask Your Question
0

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

asked 2013-07-18 05:35:26 +0100

FragileX gravatar image

updated 2013-07-19 17:38:36 +0100

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted
0

answered 2013-07-20 00:35:45 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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.

FragileX gravatar imageFragileX ( 2013-07-20 01:13:02 +0100 )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.

Mark Jeronimus gravatar imageMark Jeronimus ( 2017-11-17 17:20:01 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2013-07-18 05:35:26 +0100

Seen: 2,800 times

Last updated: Jul 20 '13