excel - VSTACK doesn't pile up dynamic ranges - Stack Overflow

admin管理员组

文章数量:1335542

My question is simple: when I replace a range (array) with the corresponding VSTACK function, my formula returns weird results.

I am working on a financial model that I have simplified for this post. I have the returns of an asset, month by month, that I want to compound (that is, I give the initial and ending month of my calculation). In the example given, I want to know the total return from month 1 to 2, from month 2 to 4 and from month 1 to 5.

The "from to" months appear in a range. When I use the range directly in my formula, the results are correct, as it is proven below in the check range.

However, if I use the VSTACK function, the function does not work, it gives incorrect returns. It seems to take into account only the "from" month, without paying any attention to the "to" month at all.

It is the first time that I encounter a problem like this one with VSTACK.

The problem that I have is that in my model, the range of "from...to" months expands to the right, so that I cannot use the formula based on the simple range.

I am not obliged to use the VSTACK function, any function that works on dynamic arrays is fine for me, provided that it works, of course.

My questions are: 1- why do you think the VSTACK function is not working in my model? 2- what alternative formulas could I use instead that works on dynamic array (that expand).

Formula that doesn't work:

=BYCOL(VSTACK(J3#;J4#);LAMBDA(c;PRODUCT(1+INDEX(C4:G4;;SEQUENCE(1;INDEX(c;2)-INDEX(c;1)+1;INDEX(c;1))))-1))

Formula that works, but it is not exactly what I need:

=BYCOL(J3:L4;LAMBDA(c;PRODUCT(1+INDEX(C4:G4;;SEQUENCE(1;INDEX(c;2)-INDEX(c;1)+1;INDEX(c;1))))-1))

I am using the International setting for Spain. Thank you

I have proved that VSTACK does not work by using a simple fixed range instead, as it is shown on the picture above.

The result expected is also shown in the picture.

I have simplified my model to the maximum before posting my question.

My question resembles a little the one whose title is "VSTACK (and TEXTSPLIT) on dynamic ranges" but I do not think that the problem is exactly the same.

My question is simple: when I replace a range (array) with the corresponding VSTACK function, my formula returns weird results.

I am working on a financial model that I have simplified for this post. I have the returns of an asset, month by month, that I want to compound (that is, I give the initial and ending month of my calculation). In the example given, I want to know the total return from month 1 to 2, from month 2 to 4 and from month 1 to 5.

The "from to" months appear in a range. When I use the range directly in my formula, the results are correct, as it is proven below in the check range.

However, if I use the VSTACK function, the function does not work, it gives incorrect returns. It seems to take into account only the "from" month, without paying any attention to the "to" month at all.

It is the first time that I encounter a problem like this one with VSTACK.

The problem that I have is that in my model, the range of "from...to" months expands to the right, so that I cannot use the formula based on the simple range.

I am not obliged to use the VSTACK function, any function that works on dynamic arrays is fine for me, provided that it works, of course.

My questions are: 1- why do you think the VSTACK function is not working in my model? 2- what alternative formulas could I use instead that works on dynamic array (that expand).

Formula that doesn't work:

=BYCOL(VSTACK(J3#;J4#);LAMBDA(c;PRODUCT(1+INDEX(C4:G4;;SEQUENCE(1;INDEX(c;2)-INDEX(c;1)+1;INDEX(c;1))))-1))

Formula that works, but it is not exactly what I need:

=BYCOL(J3:L4;LAMBDA(c;PRODUCT(1+INDEX(C4:G4;;SEQUENCE(1;INDEX(c;2)-INDEX(c;1)+1;INDEX(c;1))))-1))

I am using the International setting for Spain. Thank you

I have proved that VSTACK does not work by using a simple fixed range instead, as it is shown on the picture above.

The result expected is also shown in the picture.

I have simplified my model to the maximum before posting my question.

My question resembles a little the one whose title is "VSTACK (and TEXTSPLIT) on dynamic ranges" but I do not think that the problem is exactly the same.

• excel
• excel-formula

Share Improve this question Follow edited Nov 20, 2024 at 15:40 Mayukh Bhattacharya 27.6k88 gold badges2929 silver badges4242 bronze badges asked Nov 19, 2024 at 21:49 vsolervsoler 333 bronze badges 0

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

This is more an issue of function selection. MAP is better suited for this task, because it does not require the help of INDEX or @ to reference and convert array objects (TYPE 64) to numeric values (TYPE 1).

With the start months (s) in range J3# and the end months (e) in range J4#, the formula structure of MAP would simply be:

=MAP(J3#;J4#;LAMBDA(s;e;PRODUCT(1+INDEX(C4:G4;;SEQUENCE(1;e-s+1;s)))-1))

Your original BYCOL formula did not work as expected with VSTACK because of the differences in data types between range references and array objects; however, a complete explanation is two-fold...

SEQUENCE:

When an array object (TYPE 64) is passed to any one of the SEQUENCE function's arguments, it will be interpreted as an attempt to generate an array of arrays (not supported). As a result, only the first value will be returned. For example, =SEQUENCE(1;{3};2) will only return a single value of 2, because the [columns] argument received an array object {3} and the [start] argument was set to 2.

INDEX:

When using INDEX on a single column or row of data (vector), the [column_num] argument can be omitted; however, the data type of the value returned will differ when referencing a physical range vs an array object. For example, working with your sample screenshot, =INDEX(K3:K4;2) will return 4 (TYPE 1), whereas =INDEX(VSTACK(K3;K4);2) will return {4} (TYPE 64), because VSTACK(K3;K4) will evaluate to {2;4}, which is an array object. To ensure the value returned is TYPE 1, simply specify both the row_num and [column_num] arguments of INDEX. You can verify this with the TYPE function:

• =TYPE(INDEX(K3:K4;2)) = 1 (number)
• =TYPE(INDEX(VSTACK(K3;K4);2)) = 64 (array)
• =TYPE(INDEX(VSTACK(K3;K4);2;1)) = 1 (number)
• =BYCOL(J3#:J4;LAMBDA(c;TYPE(INDEX(c;2)))) = 1 | 1 | 1 (numbers)
• =BYCOL(VSTACK(J3#;J4#);LAMBDA(c;TYPE(INDEX(c;2)))) = 64 | 64 | 64 (arrays)
• =BYCOL(VSTACK(J3#;J4#);LAMBDA(c;TYPE(INDEX(c;2;1)))) = 1 | 1 | 1 (numbers)
• =MAP(J3#;J4#;LAMBDA(s;e;TYPE(s))) = 1 | 1 | 1 (numbers)
• =MAP(J3#;J4#;LAMBDA(s;e;TYPE(e))) = 1 | 1 | 1 (numbers)

This explanation was provided as a direct response to the question. However, as stated above, the entire issue can be avoided by using the MAP function instead of BYCOL.

Put @ in front of the three INDEXES inside the SEQUENCE:

=BYCOL(VSTACK(J3#,J4#),LAMBDA(c,PRODUCT(1+INDEX(C4:G4,,SEQUENCE(1,@INDEX(c,2)-@INDEX(c,1)+1,@INDEX(c,1))))-1))

REMEMBER that you need to replace the , with ; for your locale.

本文标签： excelVSTACK doesn39t pile up dynamic rangesStack Overflow