1

I have an existing formula that is sort of a cumulative MAX of another column: it has a range covering only the rows above it, inclusive. So B2 is =MAX(A$1:A2), and I can run this down or auto-fill and the range ends on the current row for each one. But I'd much rather use ARRAYFORMULA to cover an indefinitely long column without further edits.

By analogy with AdamL's answer in Cumulative Sum without Script which I use successfully elsewhere, I am trying: =ARRAYFORMULA(MAXIFS(A2:A,ROW(A2:A),"<="&ROW(A2:A))) but my result is an array all equal to the value in A2. Other values further down in A are larger, but they are not reported as the MAX() in their respective row.

I don't understand how the SUMIFS one worked and this MAXIFS one doesn't, assuming the semantics are similar.

Improve this question

1 Answer 1

Reset to default
0

You can use BYROW to expand it as an array formula:

=BYROW(A2:A,LAMBDA(each,IF(each="","",MAX(A2:each))))
Improve this answer
7
  • That works, thanks!
    – jxh
    Commented Apr 11, 2023 at 14:22
  • Then if you're working with two "parallel" ranges you can use MAP. Something like this: =MAP(B2:B,D2:D,LAMBDA(bcell,dcell,IF(dcell<>"",dcell,MAX(E1,bcell)))) -- obviously, you can adapt the names inside LAMBDA to be more representative of what they actually name
    – Martín
    Commented Apr 11, 2023 at 14:25
  • Ah, you're too fast! I answered my own supplementary question using INDIRECT("D"&ROW(each)). Thanks again!
    – jxh
    Commented Apr 11, 2023 at 14:27
  • Oh, and MAP can do more than two ranges. Nice! For posterity, here's my final result, that works, and no INDIRECT(): =MAP(BG2:BG,D2:D,E2:E,LAMBDA(showing,request,nextrally,IF(request<>"",request,IF(nextrally="",,IF(showing="notThisTime",,MAX(E1,nextrally))))))
    – jxh
    Commented Apr 11, 2023 at 14:40
  • You're right! You can add more ranges while they're of the same size. I'm glad it was useful! Consider marking the question as answered ;)
    – Martín
    Commented Apr 11, 2023 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.