Helix wrote:I maybe wrong but, as I understand it, your solution would be difficult to expand to a dynamic 50 or more rows of data.
I used
array constants in the formula, since your example shows only six cells. Perhaps there's a way to calculate, in groups of cells, what the parameters for INDEX would be. Then you could use
=XIRR(INDEX(A1:A50~D1:D2;X1:X52;Y1:Y52;Z1:Z52);INDEX(B1:B50~E1:E2;X1:X52;Y1:Y52;Z1:Z52)). But it's very possible that building the X, Y, and Z cells is going to be harder than copying the discontinuous ranges into continuous ranges with formulas.
Helix wrote:It's a pity that XIRR has such a rigid parameter requirement.
Documentation for the
~ operator is minimal. In my experience, the only functions which accept it are INDEX and ones that perform data consolidation, like SUM. To use it with most others, it is necessary to hide the operator inside a call to INDEX. Also, it's documented that the
~ operator is not allowed in an array formula.
Helix wrote:… the Excel macro could be made to work if …
The application programming interfaces (API) for Excel and Calc are totally different. In general terms, Excel macros need to be rewritten for Calc, and Calc macros need to be rewritten for Excel. It's unusual if only "cosmetic" changes are needed. It seems that at least half the time, when people want to use a macro, a solution is available using standard Calc features, though perhaps not in the manner that was anticipated.
Helix wrote:I was being over simplistic in the explanation of my problem as I was anticipating a solution to 'the evil macro'. … I'm sorry I may have misled you by not describing my application better.
This is a common occurrence in the forum. People oversimplify their situation and receive advice which won't scale for them. Getting this right can be tricky. The other extreme is people who don't simplify at all and are ignored because the analysis of their complex spreadsheet would be too laborious. I can appreciate that getting the right level of detail is difficult, especially for people not used to working with forums for technical advice. Perhaps others have a way to do this with a macro. I can't advise on writing macros.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).