Using Choose Function as an array

[al_noggin]

I would like to use the choose function to combine two ranges to form a single array. I'm not sure what I am doing wrong, but when I use the following function, only the first value of the arrays get combined:


=CHOOSE({1,2},K4:S4,H15:I15)


Ultimately I would like to use this array in another function like XIRR:


=XIRR(CHOOSE({1,2},K4:S4,H15:I15),CHOOSE({1,2},K3:S3,H14:I14))


Is this possible? What am I doing wrong??


Thanks!

 

[jeffreyweir]

It sounds like you are expecting the CHOOSE function to take two separate arrays and put them into one long 1D array. It doesn't do this...instead you still have two separate arrays.


Furthermore, because your range arguments for CHOOSE go across the page, you need to have a semi-colon instead of a comma in the {1,2} bit. Without a semi-colon, this is what you get (assuming you have the letter A in K4:S4 and the letter B in H15:I15):

={"A","B",#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A}


..and with a semicolon instead, this is what you get:

={"A","A","A","A","A","A","A","A","A" ; "B","B",#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A}


Basically a semicolon means "Row" and a comma means "Column". Note in that last example there is a semicolon between the As and the Bs, denoting where one array ends and the next begins. (I've put this in bold and put some extra space there to draw your attention to it).


All thone #N/As are because you are trying to put two different sized arrays together. In cases like that, Excel makes each array the size of the largest one - in this case the 9 element range K4:S4. But because the K4:S4 array only has two elements in it, Excel makes up the length with #N/As.

 

[NARAYANK991]

Hi Al ,


There has been another question on the usage of non-contiguous data ranges in XIRR ; do you think it can help ?


http://chandoo.org/forums/topic/xirr-for-non-contiguous-data


Narayan

 

[al_noggin]

Jeffrey, thanks for the detailed explanation about the CHOOSE function. I wasn't exactly sure how it worked. Is there any elegant solution for what I am trying to achieve?


Narayan, I will take a look at the other question. Looks a bit daunting at first! Thanks for pointing it to me. I'll play with it and wil hopefully get somewhere.


Thanks guys for your help!

 

[jeffreyweir]

Hi Al. I'm not sure if what you want can be done. And even though I've read through the link Narayank posted, I'm not quite sure what the outcome there was either.

 

[Hui]

Al_Noggin


You can use a variable eg cell A1 to allow you to choose either set of data

like: =XIRR(CHOOSE(A1,K4:S4,H15:I15),CHOOSE(A1,K3:S3,H14:I14))

 

[jeffreyweir]

Al, does this work:

=XIRR(OFFSET(H4,{0,0,0,0,0,0,0,0,0,10,10},{3,4,5,6,7,8,9,10,11,0,1}),OFFSET(H4,{0,0,0,0,0,0,0,0,0,10,10}-1,{3,4,5,6,7,8,9,10,11,0,1}))

 

[jeffreyweir]

Sorry, that should have been =XIRR(N(OFFSET(H4,{0,0,0,0,0,0,0,0,0,10,10},{3,4,5,6,7,8,9,10,11,0,1})),N(OFFSET(H4,{0,0,0,0,0,0,0,0,0,10,10}-1,{3,4,5,6,7,8,9,10,11,0,1})))


Note that this is hard-wired to the range you've posted above. Those arrays of numbers are equivalent to playing the kids game battleships.

 

[NARAYANK991]

Hi Jeff ,


The main point to take away is that the arrays must be equally sized ; the following formula works :


=XIRR(CHOOSE({1;2},K4:S4,H1515),CHOOSE({1;2},B5:J5,C10:K10),15%)


Change the size of the second array from H1515 to anything else , or from C10:K10 to anything else , and the result is #N/A.


Narayan

 

[jeffreyweir]

Cool. Maybe I'll have a crack at putting together a UDF that takes different sized ranges as arguments and puts them into a nice 1d array.

 

[al_noggin]

Thanks, really appreciate the help. Wasn't able to work on it yesterday. Will try some things out today. I think a UDF may be the way to go, but I was trying to avoid that!


I guess another way to hack it is to make the second array the same size by making the date the same as the last date in the array and having zeros as the values. Sounds like that would work, but not sure how easy it would to make the second array dynamically sized and the filler date dynamically changed.


It may just be easier with a brute force method where I create 100 new arrays that the XIRR function can handle!