XIRR with non contigous cells

[topola]

I am looking for a pattern that allows to use a non contigous cells
reference.
I have a XIRR formula with syntax (Values, Dates, Guess).

This is my investment simple example:

A1:A3 Values, B1:B3 Dates
A100:A103 Values, B100:B103 Dates

I need to calculate XIRR for this investment but without reorganizing
the sheet which is much more complex then in this example.

Thanks in advance,
Tomek Polak

 

[vezerid]

Tomek,
try to use an extra sheet. In there use =Sheet1!A1 and copy for three
rows and two columns. Immediately down use =Sheet1!A100 and repeat.
Then use your XIRR(). Back in Sheet1 use ='Helper Sheet'!cell number
with XIRR().

Change the names of the sheets as necessary, enclosing sheet name in
single quotes if it contains space.

HTH
Kostis Vezerides

 

[topola]

Kostis, thank you for your reply. I understand the idea.
The problem is that I have a portfolio with dozens of investments with
some of them partially sold. The investment history includes both
realized and unrealized IRRs in three different currencies. That's a
lot to convert...
I was rather thinking of something like converting the input of non
contigous cells (using Union function or alike) to one range which is
"digestive" to the standard XIRR function. In other word two ranges
(of dates or values) should be seen as one contigous range.
That would do the job...

Tomek Polak