excelexcel-formulaxirr

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

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.

Solution

  • You could 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))