Adding a non-adjescent cell to a Range (series) in the XIRR formula

[TripleLindy]

I am using the XIRR formula as follows:

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

but need to add an additional nonadjescent cell to the Range of values and dates. 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 value.

I cannot figure out how to add one cell onto the end of the range. The following did work 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.

 

[EdwardK]

There is a thread that covers this started by ichetan called "XIRR and Non Contiguous Values" posted Jan 2, 2013 that provides a solution. A key element is using CNTRL - Shift - Enter when entering the formula instead of just enter. If you just use enter you get a #NUM error.

Good Luck

I am using the XIRR formula as follows:

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

but need to add an additional nonadjescent cell to the Range of values and dates. 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 value.

I cannot figure out how to add one cell onto the end of the range. The following did work 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.