MAX YTD value with an array formula

vsoler 0
Viewed 57

I have a rectangular array of numbers, and I want to calculate the MAX value YearToDate. I want to use a dynamic array formula.

Let me explain:

enter image description here

I could theoretically use the MAX function in order to calculate a running MAX. However, the result table would not expand as the original "Tabla" expands.

I am able to calculate a running MAX using the SCAN function:

enter image description here

However, this formula deals only with one column of data, not with many.

My goal now is to "wrap" this SCAN function with a BYCOL function, so that all columns are taken into account.

However, in all my attemps so far, I have been unsuccessful. I'd like a formula with BYCOL/SCAN or another formula that yields a similar result.

I have tried, for example ...

enter image description here

... but I do not get the desired result.

Can anybody help?

excel-formula excel
2 Answers

If you are willing to accept a volatile set-up:

=SUBTOTAL(4,OFFSET(INDEX(Tabla,1,1),,SEQUENCE(,COLUMNS(Tabla),0),SEQUENCE(ROWS(Tabla))))

I have a strong suspicion that a succinct, non-volatile set-up does not exist, though I may be proved wrong.

Jos Woolley0

We can use MAKEARRAY to do this:

=MAKEARRAY(ROWS(Table1[#Data]),COLUMNS(Table1[#Data]),LAMBDA(a,b,MAX(INDEX(Table1[#Data],1,b):INDEX(Table1[#Data],a,b))))

enter image description here

Scott Craner0
Related