MAX per row across columns with dynamically spilled array

**Marbleking** · 09-07-2022, 09:17 AM

Hi,

Please see attached workbook. I have a table of date/time data and need to calculate the max value across columns for each row in the table. I want the result column to spill using a dynamic array formula. NOTE: The columns will not necessarily all be adjacent to each other.

I have seen a suggested solution at the following link, but I don't yet have the BYROW and LAMBDA functions available in my version of O365 and therefore need an alternative solution:

https://answers.microsoft.com/en-us/...e-976bcd966ba6

Best regards,
Marbleking

**Fluff13** · 09-07-2022, 09:33 AM

How about

Formula:

Please Login or Register  to view this content.

although this will only work for consecutive columns

**kvsrinivasamurthy** · 09-07-2022, 09:39 AM

In J2

Please Login or Register  to view this content.

Format the column for dd/mm/yyyy hh:mm

**Marbleking** · 09-07-2022, 10:12 AM

Thanks, these work

I tried to accommodate the SUBTOTAL version to work with non-consecutive columns using a CHOOSE function like this, instead of Table1[@]:

CHOOSE({1\2\3\4\5\6},Table1[@[Column A]],Table1[@[Column B]],Table1[@[Column C]],Table1[@[Column D]],Table1[@[Column E]],Table1[@[Column F]])

Using the CHOOSE function provides the same array of columns for the current row, but the OFFSET function doesn't seem to be able to handle it.

Regards,
Marbleking

**Fluff13** · 09-07-2022, 10:28 AM

You cannot use offset with an array, it only works with a range.
How about

Formula:

Please Login or Register  to view this content.

**Marbleking** · 09-07-2022, 10:36 AM

Thanks a lot, Fluff13

This works for me!

Regards,
Marbleking

**Fluff13** · 09-07-2022, 10:45 AM

Glad to help & thanks for the feedback.

MAX per row across columns with dynamically spilled array

LinkBack

Thread Tools

Rate This Thread

Display

MAX per row across columns with dynamically spilled array

Re: MAX per row across columns with dynamically spilled array

Re: MAX per row across columns with dynamically spilled array

Re: MAX per row across columns with dynamically spilled array

Re: MAX per row across columns with dynamically spilled array

Re: MAX per row across columns with dynamically spilled array

Re: MAX per row across columns with dynamically spilled array

Thread Information

Users Browsing this Thread

Similar Threads

Two-way Lookup returning multiple text values (Spilled array formula)

[SOLVED] Spilled Array to count the number of rows that a word is found in

[SOLVED] Appending more than two Columns using one Dynamic array or spilled formula.

Formula - Sum and multiplying - Dynamic array spilled behavior

[SOLVED] YTD with dynamic spilled array

YTD with spilled dynamic array

Switching Rows and Columns in Array Reference Dynamically

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions