How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

0 votes

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 by Kithuzzz
• 38,010 points
243 views

1 answer to this question.

0 votes

Use VSTACK: vstack to make an array:

enter image description here

Use it as value input instead of range. Result:

enter image description here

answered Mar 18, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

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

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

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
716 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
2,766 views
0 votes
1 answer
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,018 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,334 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
653 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,147 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,470 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,420 points
363 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP