The XIRR(values,timestamps) function is required. It requires two columns of input in order to operate.
Let's say this example works just fine:
|
Column A |
Column B |
| 1 |
-12000 |
1900/01/01 |
| 2 |
-12000 |
1901/01/01 |
| 3 |
25000 |
1902/01/01 |
| 4 |
=XIRR(A1:A3,B1:B3) |
|
It will return a proper result in the field A4.
Now the problem: I want it to work if the last value of the list is for example not in A3 but in C3 instead, e.g.:
|
Column A |
Column B |
Column C |
| 1 |
-12000 |
1900/01/01 |
|
| 2 |
-12000 |
1901/01/01 |
|
| 3 |
|
1902/01/01 |
25000 |
| 4 |
=XIRR((A1:A2 C3),B1:B3) |
|
|
Hence, a combination of the values from columns A and C would be the first input parameter for the XIRR function. The function, however, obviously rejects this. I cannot just add values to a virtual column as an input argument.
It does not work for me to simply duplicate the value of C3 to A3 and then issue the command =XIRR(A1:A3,B1:B3). In actuality, there is an array for "C3" that has values that are roughly 1000x1000. But, "C3" should always be the last element for the value of the input parameter for the XIRR function.
In essence, I'm looking for a way to dynamically and artificially combine a list of values from various locations in a work sheet into a temporary or imaginary column that can be used as a valid input argument within a function call (of a function that expects a column as input) without having to actually create the column within the excel sheet.
