HELP refer function to a specific range

[riiiiiichhhhhhhhh]

Hi All,
Trying to refer an XIRR formula which requires values and dates for each ID series in, and could not find a way to do that but manually
in the following example i have 4 ID number but a lot more in reality

ID Number​	Date​	amount​
1020​	26/12/2017​	-30,000​	-0.244994607​
1020​	06/03/2018​	6,000​
1020​	26/03/2018​	8,210​
1020​	10/04/2018​	13,714​
3320​	25/08/2017​	-35,000​	xirr for 3320​
3320​	03/11/2017​	38,000​
3358​	28/01/2020​	-25,000​	xirr for 3358​
3358​	25/02/2020​	1,940​
3358​	05/03/2020​	960​
3358​	30/03/2020​	5,000​
3358​	20/04/2020​	5,000​
3358​	30/04/2020​	5,000​
3358​	15/05/2020​	5,000​
3358​	06/07/2020​	1,340​
3358​	27/07/2020​	1,841​
3358​	17/08/2020​	7,786​
3358​	04/09/2020​	1,043​
3358​	18/09/2020​	800​
3358​	21/10/2020​	800​
3358​	06/11/2020​	750​
2222​	03/11/2020​	-3,000​	xirr for 2222​
2222​	18/11/2020​	5,245​
2222​	29/03/2021​	575​
2222​	01/07/2021​	25,648​

 

[Alex Blakenburg]

I am sure someone can come up with a better version but here is one option using MS 365

20220223 XIRR with dynamic range.xlsx
	A	B	C	D	E
14	ID Number	Date	amount	Sample	Formula
15	1020	26-Dec-17	-30000	-0.244994607	-0.24499
16	1020	6-Mar-18	6000
17	1020	26-Mar-18	8210
18	1020	10-Apr-18	13714
19	3320	25-Aug-17	-35000	xirr for 3320	0.535434
20	3320	3-Nov-17	38000
21	3358	28-Jan-20	-25000	xirr for 3358	2.325108
22	3358	25-Feb-20	1940
23	3358	5-Mar-20	960
24	3358	30-Mar-20	5000
25	3358	20-Apr-20	5000
26	3358	30-Apr-20	5000
27	3358	15-May-20	5000
28	3358	6-Jul-20	1340
29	3358	27-Jul-20	1841
30	3358	17-Aug-20	7786
31	3358	4-Sep-20	1043
32	3358	18-Sep-20	800
33	3358	21-Oct-20	800
34	3358	6-Nov-20	750
35	2222	3-Nov-20	-3000	xirr for 2222	839503.6
36	2222	18-Nov-20	5245
37	2222	29-Mar-21	575
38	2222	1-Jul-21	25648
Sheet1

Cell Formulas
Range	Formula
E15:E38	E15	=IF(A15<>A14, XIRR(XLOOKUP(A15,$A$15:$A$38,$C$15:$C$38,"",0):XLOOKUP(A15,$A$15:$A$38,$C$15:$C$38,"",0,-1), XLOOKUP(A15,$A$15:$A$38,$B$15:$B$38,"",0):XLOOKUP(A15,$A$15:$A$38,$B$15:$B$38,"",0,-1), 0.1), "")

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

HELP refer formula to specific number of cells

Hi All, I have the following table with 2 IDs for an example but have a lot more in reality, I am trying to apply the XIRR formula that requires values and dates for each series of IDs and see no way of doing that but manually. How can I refer the XIRR function only to the required number of...

www.excelguru.ca

HELP refer formula to specific RANGE

Hi All, am trying to find a way to refer the XIRR which requires dates and values for each series of ID numbers, meaning for a specific number of rows in accordance to the series ID number. cant find a way to do this not manually, please help. here is an example of 4 different ID numbers...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[riiiiiichhhhhhhhh]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

HELP refer formula to specific number of cells

Hi All, I have the following table with 2 IDs for an example but have a lot more in reality, I am trying to apply the XIRR formula that requires values and dates for each series of IDs and see no way of doing that but manually. How can I refer the XIRR function only to the required number of...

www.excelguru.ca

HELP refer formula to specific RANGE

Hi All, am trying to find a way to refer the XIRR which requires dates and values for each series of ID numbers, meaning for a specific number of rows in accordance to the series ID number. cant find a way to do this not manually, please help. here is an example of 4 different ID numbers...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

really sorry for that, apologies and thank you for your response