Forum Discussion
Referring to PREVIOUS rows only within BYROW() (or any other function)
The first part might be
= LET( currentMax, SCAN(0, currentValue, MAX), priorMax, DROP(VSTACK(0, currentMax), -1), action?, currentMax > priorMax, HSTACK(currentMax, IF(action?, "New Max!", "Same old")) )giving
Good morning all and sorry for the delay. I was trying out the solutions - both to really understand them and to see how they work with real world data. I accepted PeterBartholomew1 first reply, since it was the first and used the same idea for the second situation (as did OliverScheurich in his).
Some observations - the basic approach seems to be (i) clone the existing array, (ii) push it down (I'm using old-style stack push/pop terminology here...) by adding a 0 on top, and (iii) lop off the bottom of the new stack. You up with the with two arrays/stacks - with the 2nd shifted down one position from the first.
Good solution and it works. But like other situations with modern day Excel, it feels more like a workaround. In real life, I have to use it twice - once to determine the max() of the previous rows of one column and once again to determine the running total of the previous rows of another. Then make adjustments to eliminate potential undesired results (negative numbers, for example). I won't bore everyone with the details but at the end, after a lot of tinkering, it works but looks ugly.
(As an aside, I tried to see how this would be resolved with python - that solutions seems a little cleaner (and in the process learned about "float('-inf')" which was entirely new to me). If I have the time and brain power, I'll try to use it with a python-in-Excel approach and, if successful, post it here for everyone's edification!).
Final note - although not substantively similar, this experience had, for me, the same feel as the one generated by the trials and tribulations with the "array-of-arrays" quagmire. The powers-that-be behind Excel really need to do something about the look-and-feel of these solutions. Unlike, say, python, where you're rarely (if ever) expected to share the source code with others, Excel has a strong social aspect to it - you usually need to share the "source code" internally and sometimes externally; it's hard to believe I can explain this to anyone else (even if fairly Excel-savvy); they'll either have to take my word for it, or demand that it be redone "the old fashioned way".
OK, I'm off the soapbox. Thanks everyone for your suggestions.
Back on topic.
At first sight one might expect both ACCUMULATE and DIFFERENCE to be implemented by similar stack operations as one another. After all they are just a pair of inverse operations. However, for ACCUMULATE, you need the running total and the current value, which is precisely what SCAN provides. DIFFERENCE however requires the current value and the prior value at each step. That would need both the prior value and the prior difference to be available on the stack. When the calculation is complete the prior values can be discarded but, meanwhile, the two variables form an array
.The solution I adopted was to move away from the stack and offset the entire array, in order to return the differences as a single array calculation. The offset step is somewhat ugly but it is effective. Things get worse when you want to evaluate convolutions, even with a small kernel. There, for depreciation or moving average calculation, one requires a small array of prior values to be retained, along with the newly calculated value. Again, there are a number of workarounds, but one should not have to be thinking of workarounds for such frequently-arising process steps.
Hi. Please don't even consider getting off the soapbox!!! Hold on while a get you a megaphone. The more well-informed users like you can get together with the message to Microsoft that they fouled up big-time in specifying the functionality, the better the chance of getting them to fund the correction. I would want it even if it meant yet another rewrite of the calc engine and abandoning backward compatibility; it it that important. OK, so that is not realistic to serve the needs of a small minority of Excel users, but I really hope something is possible.
It seems that as soon as Excel knows it is handling an array (TYPE 64) it fails to recognise the content as being Lambda functions (TYPE 128). As the scope of my Lambda functions grows, with one function per worksheet being a possible end point, the more times I hit array-of-array problems within any given formula. The workarounds are, frankly, ugly and make it a hard sell that, what is otherwise superb functionality, should be used consistently for all Excel solutions.