Forum Discussion
Build a Range from the values of two cells
Hello, everyone. First post on this forum.
I'm looking to use a range in an XIRR formula, but this range is not static, it might change. I would like to know how to build a range/array by doing the following:
Cell A1 contains B1
Cell A2 contains B10
Combine the values from A1 and A2 into a range/array so that I get the range B1:B10
If there is not a way to do that, which I don't think there is, another way I can solve my problem is to use arrays. I can get a long array with all the values of a column, but I would like to be able to extract a subset of that array.
Say the array is {0;0;0;4;5;6;0;0}, I would like to be able to extract a subset of that array so that the new array would only be {4;5;6}. XIRR doesn't seem to like using the full array, it needs the array without zeros, or at least the dates part of the XIRR does. I would prefer to not have to use a user-defined function, but I am not against it.
note: I got the zeros in this array by using nested if statements
- The INDIRECT function is what you are need here. Note that this function always recalculates meaning of calculation intensive cells depend on cells containing INDIRECT, they will recalc regardless of what was changed in your model.
4 Replies
- The INDIRECT function is what you are need here. Note that this function always recalculates meaning of calculation intensive cells depend on cells containing INDIRECT, they will recalc regardless of what was changed in your model.
I tried that I believe, but I only seem to get the first and last values in the range. See pictures.
Edited:
Actually I should be doing Indirect(B1):Indirect(B2). That gives me the correct array. Thank you.
I am not sure why you would do it that way. I just learnt some from this community. You may want to do it like
=OFFSET($A$1,0,0,COUNT($A$1:$A$1000),1)
