Dynamically construct range to use in XIRR formula

问题:

I have a sheet in this form:

Fund | Date      | Amount
A    | 10-Jan-05 | -5000
A    | 10-Feb-05 | -5000
A    | 08-Oct-13 | 12500
B    | 10-Sep-05 | -5000
B    | 10-Oct-05 | -5000
B    | 10-Nov-05 | -5000
B    | 08-Oct-13 | 22500

I'm looking for an output column that provides the XIRR for each fund. The XIRR function takes values and dates as ranges. I need a way to dynamically construct the range using the fund name as a search criteria.

Fund | XIRR
A    | ...
B    | ...

I could manually specify the range for each fund after sorting it by name, but its not a scalable solution.

回答1:

You can use OFFSET to get the correct ranges, e.g. this formula

=XIRR(OFFSET(C$2,MATCH(A11,A$2:A$8,0)-1,0,COUNTIF(A$2:A$8,A11)),OFFSET(B$2,MATCH(A11,A$2:A$8,0)-1,0,COUNTIF(A$2:A$8,A11)))

see screenshot