8

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.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Harrison
  • 3,843
  • 7
  • 22
  • 49
  • 1
    +1 as you clearly have had a good look already. For multi-column choices the `OFFSET` workaround won't be applicable (it works well for dis continuous ranges in a single range or column). For the same reason the `CHOOSE` workaround is cumbersome. If `H11:16` are blank, then this will work `=XIRR(CHOOSE({1,2},E$11:E17,H11:H17),CHOOSE({1,2},B$11:B17,B$11:B17))` as the ranges for `CHOOSE` are now similarly sized – brettdj Jan 05 '14 at 00:23
  • @brettdj. Unfortunately, those values aren't blank. – Harrison Jan 05 '14 at 02:36
  • I think you are stymied then .... I could write a UDF, but think it probably makes more sense for you to reorganise your data? I will ask the formula wizard, Barry Houdini to take a look. – brettdj Jan 05 '14 at 04:26
  • For now I did the math myself and used a VBA to run solver a billion times, but it's not the optimal solution. Thanks for your help. – Harrison Jan 05 '14 at 05:05

2 Answers2

10

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))

lori_m
  • 5,487
  • 1
  • 18
  • 29
  • I tried this out and it produces the correct result +1. I need to apply this to an entire column of different shape arrays [which looks like it works as is]. Can you please explain why it works and specifically how you transformed the array with the Frequency formula. Thanks – Harrison Jan 05 '14 at 17:49
  • Note: I had to put ARRAYFORMULA() around each of the IF statements to get this work. – Dominic Jan 29 '16 at 17:48
10

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.

TheRydad
  • 99
  • 1
  • 2
  • Sounds like this could be a useful answer - but can you include the actual formula? – aucuparia Sep 29 '15 at 20:26
  • 1
    This was a response to lori_m's response above mine to explain the formula: "=XIRR(IF(1-FREQUENCY(9^9,B11:B17),E11:E17,H17),IF(1-FREQUENCY(9^9,B11:B17),B11:B17,B17))". – TheRydad Dec 01 '15 at 02:40