Excel 365: How to put a MAX function into an array?

[paulma1960]

Hi, I want to do a simple MAX function for all the values per row (simple enough), but I don't know the syntax for putting this into an array for multiple rows. I'm using Excel 365, so the ability to just enter this in one cell would be great. Any pointers appreciated. TIA, Paul

[RaulSerg]

=SUBTOTAL(4,OFFSET(D14#,,,SEQUENCE(ROWS(D14#))))

This should fill down and give the max value of each row

D14# is whatever array you're trying to reference

edit: shoot...I just noted it doesn't work as the range is expanding and not just taking one row at a time
maybe this is a starting point and someone else got another idea :/

[RaulSerg]

@paulma1960, please try this:

=SUBTOTAL(4,OFFSET(B2#,SEQUENCE(ROWS(B2#),,0,1),,1))

B2# being your referenced array

btw - to my knowledge there's no way via MAX to achieve the same result; neither to replace OFFSET with a non-volatile INDEX-alternative; at least I haven't figured one out yet, but happy to be shown otherwise!

[XOR LX]

RaulSerg is correct. If and when the BYROW function becomes available (currently only for Office Insiders), then we'll have a non-volatile alternative:

=BYROW(B2#,LAMBDA(x,MAX(x)))

Regards

[XOR LX]

Ah, wait, we can use constructions such as:

=MOD(LARGE(10^6*ROW(B2#)+B2#,SEQUENCE(ROWS(B2#),,COUNT(B2#)-COLUMNS(B2#)+1,-COLUMNS(B2#))),10^6)

That's just an example. I'm sure the SEQUENCE part can be abbreviated considerably.

Though of course this set-up is rather artificial and not sound for a range containing large values.

Regards

[RaulSerg]

Nice idea.

[paulma1960]

Thanks, RaulSerg. That's brilliant, and works a treat without any probs.

Just another question, how can I emulate a MAX function on two columns, per row. ie, I want the MAX of two values in the same row, and then spill into an array down the column? TIA, Paul

[RaulSerg]

Hmm, how is that not the same question as before?

If you mean that columns 1 and 2 are not an array that can be referenced with the #-operator, it defeats slightly the spilling idea, but I believe the underlying principle is the same.

Pls see the example attached.
Pls correct me if I have not understood correctly.

Regards

[paulma1960]

The values are in the same row, but the columns aren't contiguous.

[RaulSerg]

Is it a continuous range or are certain columns inbetween to be omitted?

[RaulSerg]

Sorry, I misread.

I don't think there's an easy solution.
Better restructure your data to make them contiguous.
After all, spilling is also happening into contiguous cells, so pulling data from non-contiguous cells is counterintuitive to being with.

Based on the file I attached, you can have empty columns inbetween the two columns from which you wish to pull the max, but if there's other data inbetween, you're most likely out of luck.

[FlameRetired]

Someone named Lori is famous for coming up with formulas that solve outrageously difficult problems.

This is one of them. Though we aren't supposed to post links to competing forums this one is too complicated to explain here and the workbook I used this in is a 'train wreck'. I just know this formula works at my end. Array references in this formula are to a simulation of a calculated array.

The link: By Lori (Post #35 first of 2)

The formula I used array entered Control + Shift + Enter

Please Login or Register  to view this content.

You will need to see hers in the context of that link.

The simulated array mentioned above:
{40,29,27,2;21,39,12,37;50,35,31,26;0,50,37,10;35,9,20,29;49,16,44,-49;48,20,15,2;18,12,0,19;28,40,46,16;5,25,11,38}

I played with this for days about 4 or 5 years ago. I never experimented with blanks. Perhaps this will give you a start.

Hope this is of some use.

Edit: PS Come to think of it I believe she also included a formula somewhat similar to the one posted by XOR LX above in that link, but I didn't go looking for it.

[paulma1960]

Vielen Dank, RaulSerg. I can't place the columns continguously, as it messes up the logic of the data to the end-user. I have, however, placed a helper column contiguously and hidden it, so that works fine.
By the way, where in Germany are you? Until recently, I was living in Kassel.

And FlameRetired, I haven't tried the formula, though I'm sure I could get it to work. For the given situation, it's simply too complex for my needs. I appreciate the suggestion, maybe someone else can make use of it. Thanks.

[paulma1960]

That's a very useful alternative, FlameRetire. I'm running with the other solution suggested by RaulSerg, because I can follow it easier.

[Fluff13]

If your two spill ranges are in P2 & S2 you could use

Please Login or Register  to view this content.

EDIT:
This assumes that both spill ranges are 1 column wide & the same number of rows.

[paulma1960]

Ah yes, @Fluff13, so simple, I should have thought of that. For this situation, that's all I need. Thanks...

[Fluff13]

Glad to help & thanks for the feedback.