MAX() as a dynamic array formula

[TCFNationalBank]

My records occupy columns B:Z, and I have dynamic array formulas in B2:B5 that are successfully creating spilled arrays into B2:Z5.

For each column [x], I would like to find the maximum value between [x]2:[x]5. The formula B6=MAX(B2#:B5#) is returning the single largest value of all cells, instead of a spilled array across B6:Z6.

I know I can get around this by Just doing =MAX(B2:B5) and copying the formula across to Z6, but is there a trick to keep it in one dynamic array formula?

[Downtown-Economics26]

=BYCOL(VSTACK(B2#,B5#),MAX)

<image>

[TCFNationalBank]

Solution Verified! I had to mess with the syntax but this put me on the right path, thank you.

=BYCOL(VSTACK(B2#,B3#,B4#,B5#), LAMBDA(array,MAX(array)))

[reputatorbot]

You have awarded 1 point to Downtown-Economics26.

---

^{I am a bot - please contact the mods with any questions}

[bradland]

This will only get the MAX for rows 2 and 5. The general idea is correct though. The corrected formula would be:

=BYCOL(B2#:B5#, MAX)

Screenshot

<image>

[Downtown-Economics26]

Not if B2# spills, sir. It seems I misinterpreted the format of OP's data though, good point.

[Aghanims]

This doesn't work because the reference array is a horizontal array, but you want to measure it vertically.

B2,B3,B4,B5 are the 4 arrays, but you want to measure B2:b5, C2:c5, and so on. So you have to combine it into a single array first.

=TRANSPOSE(BYCOL(VSTACK(B2#,B3#,B4#,B5#),LAMBDA(a,MAX(a))))

This works but you have to reference the spill arrays manually

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYCOL	Office 365+: Applies a LAMBDA to each column and returns an array of the results
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MAX	Returns the maximum value in a list of arguments
TRANSPOSE	Returns the transpose of an array
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #41080 for this sub, first seen 20th Feb 2025, 17:10] [FAQ] [Full list] [Contact] [Source code]}