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:
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:
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 ...
... but I do not get the desired result.
Can anybody help?
CodePudding user response:
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.
CodePudding user response:
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))))