Getting the above value of the currently processed array (result array during formula execution)

[aaboodi]

Hi,

I know how to get a value from an above cell of a cell with a formula being executed but I am struggling to do the same with an array. You can simply refer to the above cell and then let the drag function of excel do the magic for the other consequence cells.

However, doing that with a dynamic array that has a single formula is new to me. I want to be able to get the above (calculated) value of a result array (currently being executed by a formula).

Take a look at the attached picture, you can see at C3 I failed to refer to the above row of the result array. The expected column in E2 is what the result should be.

Jos Woolley in the StackOverflow forum suggested a solution (see here) with the "LOOKUP" function, but it was too slow for my set of data as a full column scan is performed for each row. While I can simply use the cell drag function and apply the individual formula for each cell in column C to have faster performance, it is not practical if I want to automate that in the case of a large dynamic set of data. Besides, I am really wondering about the possibility of doing that with the dynamic array.

Note: dynamic array to Power Query is not yet supported in my version of excel.

Please help

[#2 · Accepted solution]

@aaboodi 

You comment on StackOverflow " I only get "From Table/Range" . But this one works with dynamic arrays as well. At least you may try if Power Query in attached file works in your environment.

[#3]

@aaboodi If you can't connect to the DA, why not create a named range called "myRange", for instance, referring to B3# and connect to that named range in PQ and use the Fill Down there?

[#4]

@Sergei Baklan 

Thanks a lot,

Both solutions work and are clear and fast enough for the data set I have. I liked the SCAN-LAMBDA solution.

For the sake of benefit could you please elaborate more on the use of the SCAN function; this is the first time I encounter it. I don't understand how 'first' gets updated during the steps. From the way I look at it, it should always put G1.

Regarding my comment, it seems I did not know how to do it. Thanks to your file and your clear PQ steps I understand how to do it.

[#5]

@Riny_van_Eekelen 

yes, I realize I can do it after your replies.

thank you

[#6]

@aaboodi 

SCAN has two parameters, initial value for the accumulator and an array which is scanned. Into the lambda function SCAN passes accumulator and value of the current array element. What is combined from them on each step is resulting in accumulator on that step and returned as an element of generated array.

Samples and definitions are here SCAN function (microsoft.com)

[#7]

got it, thank you