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.

Feb 27, 2023 in Others 243 views

## 1 answer to this question.

Use VSTACK: vstack to make an array:

Use it as value input instead of range. Result:

• 63,420 points

## How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

## How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

## Excel: How to set a dropdown list cell to fetch list of strings from an API JSON response

The values you wish to display in ...READ MORE

## How to build a custom column formula in Power query that will retrieve Image and load it to Excel to display that image from the specified column

Make sure ignore privacy is selected in ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE