what I want to do is simple: calculate the delta of my price series. Of course, the first value is always going to be #N/A, and the rest are calculated by difference (see picture below, column D).

What I want to do is to use dynamic arrays in my formula.

Calculating the delta without the first #N/A value is relatively simple, it works fine (see column F).

Trying to VSTACK zero delta series is also easy (column H in the picture). However, VSTACKing #N/A delta does not work, and results in a single (useless) value (column I).

EXPAND works only if I want to add the #N/A value after my delta series, but it is impossible to put it as the first element in my column (column K).

CHOOSE doesn't come to my rescue, as the result that I get is meaningless.

Please, beware that I am using the Spanish International settings, in which the field separator for function parameters is the ";" instead of the "," better known of the American users; in the CHOOSE function, the separator is "" for horizontal constants.

Does anybody have a clue as to what I can do in order to solve this "simple" problem?

CodePudding user response：

One option:

=LET(ξ,C5#,ζ,SEQUENCE(COUNT(ξ),,0),IF(ζ=0,NA(),ξ-INDEX(ξ,ζ)))

CodePudding user response：

Seems like you need:

Formula in D5:

=IFERROR(C5:C14-C4:C13,NA())