[Solved] Merging cell ranges in array formula

[Helix]

I am trying to merge data ranges from two seperate sheets for use as parameters for the XIRR function ie =XIRR(MixRanges(a1:a4,d1:d2), MixRanges(b1:b4,e1:e2))
I have found a suitable solution at: http://stackoverflow.com/questions/1924 ... -parameter which provides the following function for Excel but have been unable to make it run with OpenOffice Basic.

Code: Select all

Option Explicit

Public Function MergeRange(ParamArray rng()) As Single()
    Dim i As Long
    Dim count As Long
    Dim r As Excel.Range
    Dim s() As Single

    For i = LBound(rng) To UBound(rng)
        If (TypeOf rng(i) Is Excel.Range) Then
            For Each r In rng(i)
                ReDim Preserve s(count)
                s(count) = r.Value
                count = count + 1
            Next r
        End If
    Next i
    MergeRange = s
End Function

Googling suggests that ParamArray is not available in OpenOffice and adding "Option Compatible" is a workaround, also I have tried removing the "If (TypeOf rng(i) Is Excel.Range) loop but neither seems to make the Function run.
I am new to OpenOffice basic and would really appreciate any help that could make this function, or a suitable alternative run in Calc.

(OpenOffice 4.0.1 - Debian Linux x86_64)

[MrProgrammer]

Hi, and welcome to the forum.

Dim r As Excel.Range

Is it any surprise that this does not work in Calc?

I am trying to merge data ranges … for use as parameters for the XIRR function.
I … would really appreciate any help that could make … a suitable alternative run in Calc.

In Calc, there is no need for an evil macro. You can use the standard INDEX function. Be sure to make it an array formula (⌘⇑Enter on a Mac, Ctrl+Shift+Enter on other platforms). It works for me. The ~ operator is described in Help → Index → operators;formula functions.
=XIRR(INDEX((A1:A4~D1:D2);{1;2;3;4;1;2};{1;1;1;1;1;1};{1;1;1;1;2;2});
           INDEX((B1:B4~E1:E2);{1;2;3;4;1;2};{1;1;1;1;1;1};{1;1;1;1;2;2}))

If you have lots of these formulas to enter you can use Insert → Names → Define to create R_42, C_42, and E_42, then use:
=XIRR(INDEX((A1:A4~D1:D2);R_42;C_42;E_42);INDEX((B1:B4~E1:E2);R_42;C_42;E_42))

Of course the simplest solution is to copy the discontinuous ranges into continuous ranges with formuas (=B1 =B2 =B3 =B4 =E1 =E2 in six cells) and then use XIRR directly on them. This is less typing than the ideas above. You can get Calc to build the formulas to copy the cells using Edit → Paste Special → Link.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Helix]

Hi, Thank you for your welcome and your reply

Helix wrote:Dim r As Excel.Range

Is it any surprise that this does not work in Calc?

Yes I realise that this could be a problem but did not know if there is an equivalent in OO Basic. I am trying to learn this from scratch so I have a steep learning curve

In Calc, there is no need for an evil macro. You can use the standard INDEX function. Be sure to make it an array formula (⌘⇑Enter on a Mac, Ctrl+Shift+Enter on other platforms). It works for me. The ~ operator is described in Help → Index → operators;formula functions.
=XIRR(INDEX((A1:A4~D1:D2);{1;2;3;4;1;2};{1;1;1;1;1;1};{1;1;1;1;2;2});
INDEX((B1:B4~E1:E2);{1;2;3;4;1;2};{1;1;1;1;1;1};{1;1;1;1;2;2}))

This is an elegant solution and the concatenation operator ~ is new to me, however I was being over simplistic in the explanation of my problem as I was anticipating a solution to 'the evil macro' . In fact the data I am trying to pass to XIRR comes from two sheets on my spreadsheet. The first contains Cash Flow data and consists of an irregular list of about 50 entries and is growing monthly. This would provide the Values and Dates for rows A1 to ~A50 and B1 to ~B50 and the other sheet provides historical valuations which would provide the D1 E1 values. The aim is to plot the Return Rate along with other statistical data I have deduced against historical dates. I maybe wrong but, as I understand it, your solution would be difficult to expand to a dynamic 50 or more rows of data.

Of course the simplest solution is to copy the discontinuous ranges into continuous ranges with formuas (=B1 =B2 =B3 =B4 =E1 =E2 in six cells) and then use XIRR directly on them. This is less typing than the ideas above. You can get Calc to build the formulas to copy the cells using Edit → Paste Special → Link.

I have considered adding a new sheet for doing this and maybe it is what I end up doing but using the merge macro seemed like a suitable solution. It's a pity that XIRR has such a rigid parameter requirement.

From my limited knowledge, the Excel macro could be made to work if there was an openoffice alternative to ParamArray keyword but the details of doing that are beyound my understanding

Thank you Mr. Programmer for your suggestion and I'm sorry I may have misled you by not describing my application better.

[MrProgrammer]

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.

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.

… 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.

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.

[Helix]

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.

I eventually came up with an alternative solution using the Height argument of the OFFSET function.
To recap, I have an array of about 50 date/cash flow pairs (Cash_Flow) and and an array of about 1500 date/valuations and I want to calculate the daily IRR and plot this data on a graph.

For each valuation date I calculated the Height argument of the OFFSET function with MATCH(Valuation_date;Cash_Flow_date Array;1) Then I used this value as the Height argument +1 to get a sub set of the Cash_Flow array data and changed the additional value to be valuation value / date with by comparing the date with an IF function and used this as the arguments of XIRR.

Height=MATCH(DateValue;Cash_Flow_date Array;1)

Then use the Height value in this array formula for XIRR:

{=XIRR(IF(OFFSET(Cash_Flow;0;0;Height+1;1)<=DateValue;OFFSET(Cash_Flow;0;1;DateValue;1);VLOOKUP(DateValue;Valuation_Array;2));IF(OFFSET(Cash_Flow;0;0;Height+1;1)<=DateValue;OFFSET(Cash_Flow;0;0;Height+1;1);DateValue))}

Its probably not optimised but it does work and I used it with Fill Down (with the cntrl key for an Array Formula) to generate the plot data I needed for my graph and maybe someone else will find this useful in the future.