How merge/mix range of cell as formula parameter

问题:

In Excel I want to calculate XIRR of a projects.
My payments are in two separate ranges.
But XIRR function (and other) accept single range as parameter.
How Can I merge ranges in functions parameters? Like something as this

=XIRR(MixRanges(a1:a4,d1:d2), MixRanges(b1:b4,e1:e2))  

Thanks for any suggestions

回答1:

I was trying not to use a UDF, but I couldn't figure out a clever built-in function to use.

Add this code to your workbook:

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

And use it like so:

=XIRR(MergeRange(A2:A6,D2:D6),MergeRange(B2:B6,E2:E6),0.1)

This will accept multiple ranges, grab their values, and return the values as a contiguous array. Since XIRR needs values and dates, using the Single data type should be ok.

回答2:

For your specific example you can use CHOOSE, i.e.

=XIRR(CHOOSE({1,2,3,4,5,6},A1,A2,A3,A4,D1,D2),CHOOSE({1,2,3,4,5,6},B1,B2,B3,B4,E1,E2))

but that might not be easy to extend for larger ranges......

回答3:

I don't know about the XIRR, but I was trying to do the same with RANK.EQ and I found that all that is needed are brackets i.e. =RANK.EQ(J6,(J$6:$J$999,S$6:$S$999),0).