XIRR with non contigous cells
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 |
XIRR with non contigous cells
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 |
XIRR with non contigous cells
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 |
All times are GMT +1. The time now is 05:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com