Including a non-adjacent cell in a Range (series) in the XIRR formula

Question

I am using the XIRR formula as follows:

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

but need to add an additional non-adjacent cell to the Range of values and dates.

My first attempt:

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

resulted in #VALUE

I have attempted to do it using the CHOOSE function as follows:

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

But this is not working to produce the correct results.

I cannot figure out how to add one cell onto the end of the range. The following did work to give correct results but isn't going to work for me, as I need to use a range and individual cell, not all individual cells

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

Thanks for your help.

Answer 1

I figured out how this works and thought I would share for anyone who comes across this.

The trick is that the FREQUENCY function returns an array that has one more element than the input array. I'll spare the whole explanation of that function here as the help file does a good job, but as it is implemented in this case it is returning an array like {0;0;0;1}. When the 1-{} operation is performed, we are left with the array {1;1;1;0}.

That array is now input to the IF function along with an array of values (the contiguous cells) to evaluate to for elements equal to 1 (in the array from above) and a single value (the outlier cell) to evaluate to for elements equal to 0. Thus, producing the desired array to be used as input for the XIRR formula.

Notes: The FREQUENCY function does not have to use one of the value arrays as input. It only needs an array of numeric values one element smaller than the desired output array. You could create a hidden column off to the side full of 0s and use it as needed repeatedly throughout the sheet. If using 0s, the first value in the FREQUENCY function can be any value greater than 0. For example, 1 makes it easy to read. 9^9 was used as an arbitrarily large value.

You could repeat this process to build an array of discrete cells from all over a sheet.