Including non-contiguous cell in the range (series) in the XIRR formula

Question

I use the XIRR formula as follows:

=XIRR(E$11:E17,B$11:B17) 

but you must add an additional non-contiguous cell to the range of values ​​and dates.

My first attempt:

 =XIRR((E$11:E17,H17),(B$11:B17,B17)) 

led to #VALUE

I tried to do this using the CHOOSE function as follows:

 =XIRR(CHOOSE({1,2},E$11:E17,H17),CHOOSE({1,2},B$11:B17,B17)) 

But this does not work to get the right results.

I can't figure out how to add one cell to the end of the range. The following worked to give the correct results, but will not work for me, since I need to use a range and a single cell, and not all separate cells

 =XIRR(CHOOSE({1,2,3},E11,E12,H13),CHOOSE({1,2,3},B11,B12,B13)) 

Thank you for your help.

Answer 1

You can try something similar to this:

=XIRR(IF(1-FREQUENCY(9^9,B11:B17),E11:E17,H17),IF(1-FREQUENCY(9^9,B11:B17),B11:B17,B17))

Answer 2

I understood how this works and thought that I would share with those who are faced with this.

The trick is that the FREQUENCY function returns an array with one more element than the input array. I will spare the entire explanation of this function here, since the help file does a good job, but since it is implemented in this case, it returns an array, for example {0; 0; 0; 1}. When the operation 1- {} is executed, we are left with the array {1; 1; 1; 0}.

This array is now introduced into the IF function along with an array of values ​​(adjacent cells) for evaluation for elements equal to 1 (in the array above) and one value (ejection cell) for evaluation for elements equal to 0. This creates the desired array, which will be used as input for the XIRR formula.

Notes: The FREQUENCY function must not use one of the value arrays as input. He needs only an array of numerical values ​​one element smaller than the desired output array. You can create a hidden column from the side full 0, and use it again as needed throughout the sheet. If you use 0s, the first value in the FREQUENCY function can be any value greater than 0. For example, 1 makes reading easier. 9 ^ 9 was used as arbitrarily large value.

You can repeat this process to build an array of discrete cells from the entire sheet.