Problem with indirect function in XIRR function

Hello,

I have in E181:

F177:Q177,S177:AD177,AF177:AQ177,AS177:BD177,BF177:BQ177,BS177:CD177,CF177:CQ177,CS177:DD177,DF177:DQ177,DS177:ED177,EF177:EQ177,ES177:FD177,FF177:FQ177,FS177:GD177,GF177:GQ177,GS177:HD177,HF177:HQ177 these are the cash flow cells

in F181 I have:

F169:Q169,S169:AD169,AF169:AQ169,AS169:BD169,BF169:BQ169,BS169:CD169,CF169:CQ169,CS169:DD169,DF169:DQ169,DS169:ED169,EF169:EQ169,ES169:FD169,FF169:FQ169,FS169:GD169,GF169:GQ169,GS169:HD169,HF169:HQ169 these are the months associated with the cash flows

these strings are returned by a macro I wrote....
when I enter this formula

=XIRR(INDIRECT(E181),INDIRECT(F181)), I got #REF error, looks like Excel does recognize the strings. Can someone please help?
thanks

In my limited experience, it seems that the XIRR() is one of those functions that really does not play nice with discontiguous ranges. As one who is not a user nor a fan of big array formulas, I would tend towards a solution that:
1) Uses filters, lookups, or other strategies to extract the necessary data into a nice, contiguous ranges.
2) Apply the XIRR() function to those ranges.
I have seen others provide array functions that seem to get the XIRR() function to operate on "subranges" within a larger range. Perhaps the solutions proposed here may help: https://www.excelforum.com/excel-for...h-vlookup.html

Untested, but try this

=XIRR(INDEX((F177:HR177)*(LEN(F169:HR169)>4),0),INDEX((F169:HR169)*(LEN(F169:HR169)>4),0))

List of numbers of columns by the {}
It is not so easy to create and manage but you can try.

Formula:

Please Login or Register  to view this content.

hm. strange the first attempt was replace to 0 but result of XIRR was err. Now it's ok. #6 is solution.

Glad you got it sorted. In case you do not wish to use the helper cell C167, use this formula (not the prettiest though!)

=XIRR(INDEX((INDEX(F177:HR177,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR177)*(LEN(INDEX(F169:HR169,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR169)>4),0),INDEX((INDEX(F169:HR169,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR169)*(LEN(INDEX(F169:HR169,MATCH(1,INDEX(--(F177:HR177<>0),0),0)):HR169)>4),0))

The problem is that INDIRECT only works with SINGLE-AREA ranges. While

(F177:Q177,S177:AD177,AF177:AQ177,AS177:BD177,BF177:BQ177,BS177:CD177,CF177:CQ177,CS177:DD177,DF177:DQ177,
DS177:ED177,EF177:EQ177,ES177:FD177,FF177:FQ177,FS177:GD177,GF177:GQ177,GS177:HD177,HF177:HQ177)

[note the parentheses] may be a valid 17-area range reference, INDIRECT can't handle it. OTOH, INDIRECT can handle arrays of textrefs with a lot of coaxing. For example, with A1: =ROWS(A$1:A1) and filled down into A2:A12, =INDIRECT({"A2";"A3";"A5";"A7";"A11"}) returned diectly as an array formula returns a 6-row by 1-column array of #VALUE! errors, but =N(INDIRECT({"A2";"A3";"A5";"A7";"A11"})) returns the array {2;3;5;7;11}. This is undocumented behavior.

IOW, INDIRECT passed an array of textrefs returns something which seems to be an array of range references. FWIW, those results could be used as 1st arguments to COUNTIF, which would return an array result due to that argument. Since COuNTIF only accepts range references as 1st arguments, it seems INDIRECT passed an array argument returns an array of them. This is inference, not proof. To repeat: this is undocumented behavior.

All that said, there's a much simpler approach if you use array formulas. CORRECTION

=XIRR(IF(MOD(COLUMN(F177:HQ177),13)<>5,F177:HQ177,0),IF(MOD(COLUMN(F169:HQ169),13)<>5,F169:HQ169,MAX(F169:HQ169)+1)

This replaces the cells you want EXCLUDED in cols R, AE, AR, BE, BR, etc with 0 values and dates equal to one day after the last cashflow date. If that date is in cell HQ169, simply replace the MAX call above with HQ169. Adding cashflows of zero after the end of the actual cashflows should have no effect whatsoever on XIRR.