INDEX range and INDIRECT not working inside LET functions

[pasunmaa]

Hello,

I'm calculating Split Multiplier column from Split Ratio column by multiplying the rows from the current row towards the end of the column. It works nicely within normal excel table with PRODUCT($B3:INDEX(B:B; ROWS($B$3#)+ROW($B$3)-1) ) where the formula is copied down, but if I try to apply this within LET-function I just get #VALUE error.

The other solution I have tried gives the same error:If replace B3*IF(E4=0;1;E4) with multiplierC; splitRatio*IF(INDIRECT("R[-1]C[1]";FALSE) = 0; 1; INDIRECT("R[-1]C[1]";FALSE));
In this latter case, it looks like that there are no way to refer to the column that I'm currently calculating. Is that a case?

See my excel sheet and formulas in the attached picture.

I'm wondering if I am trying to achieve something that LET function cannot handle?

 

[jasonb75]

Could you post your example with XL2BB so that we don't have to retype everything in order to test it please.

Curious as to why you're using INDIRECT for this, unless you plan to insert / delete rows a basic relative reference would suffice.

 

[pasunmaa]

Hello,

I'm calculating Split Multiplier column from Split Ratio column by multiplying the rows from the current row towards the end of the column. It works nicely within normal excel table with PRODUCT($B3:INDEX(B:B; ROWS($B$3#)+ROW($B$3)-1) ) where the formula is copied down, but if I try to apply this within LET-function I just get #VALUE error.

The other solution I have tried gives the same error:If replace B3*IF(E4=0;1;E4) with multiplierC; splitRatio*IF(INDIRECT("R[-1]C[1]";FALSE) = 0; 1; INDIRECT("R[-1]C[1]";FALSE));
In this latter case, it looks like that there are no way to refer to the column that I'm currently calculating. Is that a case?

See my excel sheet and formulas in the attached picture.

I'm wondering if I am trying to achieve something that LET function cannot handle?

Could you post your example with XL2BB so that we don't have to retype everything in order to test it please.

Curious as to why you're using INDIRECT for this, unless you plan to insert / delete rows a basic relative reference would suffice.

Hi,
I wonder if the following is what you expected with XL2BB output? Sorry, I am using the first XL2BB.

INDEX not working within LET.xlsx
	A	B	C	D	E	F	G	H	I
1	Source Data			Correct result			Failed within LET function
2	Split ratio	Split ratio		Split multi-plier	Split multi-plier		Split multi-plier	Split multi-plier	Split multi-plier
3	1	1		36	36		#VALUE!	36	#VALUE!
4	2	2		36	36		#VALUE!	36	#VALUE!
5	1	1		18	18		#VALUE!	36	#VALUE!
6	3	3		18	18		#VALUE!	36	#VALUE!
7	6	6		6	6		#VALUE!	36	#VALUE!
Sample

Cell Formulas
Range	Formula
B3:B7	B3	={1;2;1;3;6}
D3:D7	D3	=PRODUCT($B3:INDEX(B:B, ROWS($B$3#)+ROW($B$3)-1) )
E3:E7	E3	=B3*IF(E4=0,1,E4)
G3:I7	G3	=LET( splitRatio,B3#, splitRatioB, {1;2;1;3;6}, r, SEQUENCE(COUNTA(splitRatio)), multiplierA, PRODUCT(INDEX(splitRatio, r, 1):INDEX(splitRatio, COUNTA(splitRatio), 1)), multiplierB, PRODUCT(B3#:INDEX(splitRatio, COUNTA(splitRatio), 1)), multiplierC, splitRatio*IF(INDIRECT("R[-1]C[1]",FALSE) = 0, 1, INDIRECT("R[-1]C[1]",FALSE)), CHOOSE({1,2,3}, multiplierA, multiplierB, multiplierC) )
Dynamic array formulas.

I was trying INDIRECT, as I was clueless with INDEX. If I get INDEX working, I am much happier.

 

[jasonb75]

That's exactly what is needed, it gives us actual data and formulas to copy and paste to excel so that we have something to work with.

Looking at the LET formula, the criteria appear to be more complex than your working versions so we're most likely going to need a bit of an explanation along with the expected results as well.

The first problem that stands out is this part of your formula (there could be other similar or additional problems)

INDEX(splitRatio, r, 1):INDEX(splitRatio, COUNTA(splitRatio), 1)

The way that the formula is being processed, splitRatio is being evaluated as an array rather than a range which means that the INDEX(...):INDEX(...) method is not valid.
I'm not able to work out what you're actually trying to achieve with the LET function in order to suggest an alternative method.

 

[pasunmaa]

With LET function I want to create a dynamic report of asset holdings, which is based on all the historical transactions. Split is one of the transaction type, that needs to be applied to all the transactions preceding it. My excel is quite complicate and generates different kind of dynamic reports. I'm really excited about LET and how it can be used for generating dynamic reports.

I try to attach another wides XL2PP below, but let's see if it works as it's based on original transaction table.

Petri Asset Portfolio Account v7.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Type	Units	Unit price	Split ratio	Split adjusted units	Split adjusted price	Split adjusted units	Split adjusted price
2	12/31/20	Current	1,800	8.24							Div Tax	Not included
3	10/17/08	Buy	150	20.01	1.00			900	3.34		Asset	WRT1V		Holdings in accounts
4	10/24/08	Buy	150	17.01	1.00			900	2.84		Account #	22000135097990		22000135097990
5	2/2/09	Buy	150	19.52	1.00			900	3.25		Broker	Nordea		6035711
6	3/23/09	Div	450	0.65	1.00			2,700	0.11		Account	Pantti		220000000118661000
7	3/16/10	Div	450	1.30	1.00			2,700	0.22					2200090946249
8	3/4/11	Split	450	0.00	2.00			2,700	0.00
9	3/15/11	Div	450	0.90	1.00			1,350	0.30		Currency	EUR
10	3/20/12	Div	900	1.40	1.00			2,700	0.47		Unrealised Gain/Loss €	9,163 $
11	10/29/12	Sell	300	30.95	1.00			900	10.32		Unrealised Gain/Loss (%)	161.5 %
12	3/19/13	Div	600	1.50	1.00			1,800	0.50		Realised Gain/Loss €	6,430 $
13	3/18/14	Div	600	1.35	1.00			1,800	0.45		Dividends Collected €	8,999 $
14	3/16/15	Div	600	1.15	1.00			1,800	0.38		IRR €
15	3/14/16	Div	600	1.20	1.00			1,800	0.40		IRR
16	3/13/17	Div	600	0.65	1.00			1,800	0.22
17	9/21/17	Div	600	0.65	1.00			1,800	0.22
18	3/12/18	Split	1,200	0.00	3.00			3,600	0.00
19	3/19/18	Div	600	0.69	1.00			600	0.69
20	9/27/18	Div	1,800	0.23	1.00			1,800	0.23
21	3/18/19	Div	1,800	0.24	1.00			1,800	0.24
22	9/27/19	Div	1,800	0.24	1.00			1,800	0.24
23	3/16/20	Div	1,800	0.24	1.00			1,800	0.24
24	9/17/20	Div	1,800	0.24	1.00			1,800	0.24
25								0	0.00
26								0	0.00
27								0	0.00
Asset Analyses MrExcel

Cell Formulas
Range	Formula
C2	C2	=XLOOKUP(1,--(tAssetPerAccount[Asset] = $L$3) * (tAssetPerAccount[Account] = $L$4),tAssetPerAccount[Units])
D2	D2	=XLOOKUP(1,--(tAssetPerAccount[Asset] = $L$3) * (tAssetPerAccount[Account] = $L$4),tAssetPerAccount[Price])
A2	A2	=TODAY()
A3:E24	A3	=LET( rows, SEQUENCE(ROWS(tTransactions)), date, INDEX(tTransactions[Date], rows, 1), type, INDEX(tTransactions[Type], rows, 1), unitPrice, INDEX(tTransactions[Unit Price], rows, 1), units, INDEX(tTransactions[Units], rows, 1), asset, INDEX(tTransactions[Asset], rows, 1), account, INDEX(tTransactions[Account], rows, 1), splitRatio, INDEX(tTransactions[Stock Split Ratio], rows, 1), sourceTable, CHOOSE({1,2,3,4,5,6,7}, asset, account, date, type, units, unitPrice, splitRatio), filteredTable, FILTER(sourceTable, (INDEX(sourceTable,,1) = $L$3) * (INDEX(sourceTable,,2) = $L$4)), filteredDivTax, IF($L$2="Included", filteredTable, FILTER(filteredTable, INDEX(filteredTable,,4)<>"Tax")), sortedTable, SORT(filteredDivTax, {3}, 1), CHOOSE({1,2,3,4,5}, INDEX(sortedTable,,3), INDEX(sortedTable,,4), INDEX(sortedTable,,5), INDEX(sortedTable,,6), INDEX(sortedTable,,7)) )
H3:H27	H3	=PRODUCT($E3:INDEX($E:$E, ROWS($A$3#)+ROW($A$3)-1) )*C3
I3:I27	I3	=D3/PRODUCT($E3:INDEX($E:$E, ROWS($A$3#)+ROW($A$3)-1) )
N4:N7	N4	=LET( uniqueAcnAsset, UNIQUE(tTransactions[Account]:tTransactions[Asset]), filtered, FILTER(uniqueAcnAsset, INDEX(uniqueAcnAsset,,2) = $L$3), INDEX(filtered,,1) )
L5	L5	=XLOOKUP(L4, tAccountName[Account],tAccountName[Broker])
L6	L6	=XLOOKUP(L4, tAccountName[Account],tAccountName[Account Type])
L9	L9	=XLOOKUP(1, ($L$3 = tAssetPerAccount[Asset]) * ($L$4 = tAssetPerAccount[Account]), tAssetPerAccount[Currency])
L10	L10	=XLOOKUP(1, ($L$3 = tAssetPerAccount[Asset]) * ($L$4 = tAssetPerAccount[Account]), tAssetPerAccount[Unrealised Gain/Loss €])
L11	L11	=XLOOKUP(1, ($L$3 = tAssetPerAccount[Asset]) * ($L$4 = tAssetPerAccount[Account]), tAssetPerAccount[Unrealised Gain/Loss (%)])
L12	L12	=XLOOKUP(1, ($L$3 = tAssetPerAccount[Asset]) * ($L$4 = tAssetPerAccount[Account]), tAssetPerAccount[Realised Gain/Loss €])
L13	L13	=XLOOKUP(1, ($L$3 = tAssetPerAccount[Asset]) * ($L$4 = tAssetPerAccount[Account]), tAssetPerAccount[Dividends Collected €])
Dynamic array formulas.

 

[jasonb75]

Sorry, I'm still none the wiser, are you simply trying to replace the formula in columns H and I with a LET equivalent? I don't see that there will be any advantage to doing it, the main purpose of the function is to allow naming of complex arrays in order to reduce the necessity of repeat calculation, from what I can see of your sheet you are actually increasing the workload by trying to use LET instead of decreasing it.

 

[pasunmaa]

1. I would like to calculate XIRR based on open positions and their dividends (and optionally including dividend taxes too). After adjusting transactions to splits, I need to "apply" sell, div and divtax-transactions by decreasing split adjusted units. Eventually I need to also take into account currency and transaction cost, but that's sidetrack at this stage.
2. Once I manage to calculate IRR for a single asset, I would like to generate a report for all assets in the portfolio with their IRRs. In order to do that I would need all the above calculation to take place within a single cell. That's I want to use LET function. The only output number for the whole array operation and calculation would be a single percentage value.
3. Eventually I would also like to generate annual performance report, which requires similar within a cell calculation capability.

Petri Asset Portfolio Account v7.xlsx
	L	M
29	Asset	IRR
30	ACN	9,80 %
31	WRT1V	16,50 %
32	IBM	3,70 %
33	CVS	9,10 %
Asset Analyses MrExcel

Petri Asset Portfolio Account v7.xlsx
	O	P	Q	R
29	Year	Total portfolio value	Total Unrealised Gain/Loss	Total Realised Gain/Loss
30	2010	0 €	0 €	0 €
31	2011	0 €	0 €	0 €
32	2012	0 €	0 €	0 €
33	2013	0 €	0 €	0 €
34	2014	0 €	0 €	0 €
35	2015	0 €	0 €	0 €
36	2016	0 €	0 €	0 €
37	2017	0 €	0 €	0 €
38	2018	0 €	0 €	0 €
39	2019	0 €	0 €	0 €
40	2020	0 €	0 €	0 €
Asset Analyses MrExcel

I'm still wondering, if I am expecting too much from today's excel, or should I wait for LAMBDA-function to be published.

Thx for spending time to understand my case.

 

[jasonb75]

XIRR and IRR are not functions that I'm familiar with, I believe that the number of active forum members with a good understanding of such functions would be quite low.

Breaking down your initial attempt with LET and converting it back to a LET-free formula, this is what you end up with in full.

=CHOOSE({1,2,3},PRODUCT(INDEX(B3#, SEQUENCE(COUNTA(B3#)), 1):INDEX(B3#, COUNTA(B3#), 1)), PRODUCT(B3#:INDEX(B3#, COUNTA(B3#), 1)), B3#*IF(INDIRECT("R[-1]C[1]",FALSE) = 0, 1, INDIRECT("R[-1]C[1]",FALSE)))

This is nothing like the working formulas that you had in your original sheet.

My interpretation of the task at this point is that you're trying to spill the entire table, rows and columns, from a single formula. This is simply asking for trouble, you're effectively taking the simple task of folding a child's paper dart and turning it into a major engineering project. It would be advisable to stick to 1 formula for each column, I think that it is misuse of dynamic arrays that is causing the issue rather than the LET function not being capable.

 

[pasunmaa]

Thx again. Seeing your LET-free formula, I realize that LET function is not a problem, but probably rather how I implement array formula. For sake of simplicity, if we focus only to non-dynamic correct function I need to copy down (in column D)

=PRODUCT($B3:INDEX(B:B; ROWS($B$3#)+ROW($B$3)-1) )

and compare it to its incorrect dynamic counterpart (in column G)

PRODUCT(INDEX(B3#, SEQUENCE(COUNTA(B3#)), 1):INDEX(B3#, COUNTA(B3#), 1))

How should I change the latter one to make it work like the first one but still generating dynamic array, or is it possible?

 

[Peter_SSs]

Thx again. Seeing your LET-free formula, I realize that LET function is not a problem, but probably rather how I implement array formula. For sake of simplicity, if we focus only to non-dynamic correct function I need to copy down (in column D)

=PRODUCT($B3:INDEX(B:B; ROWS($B$3#)+ROW($B$3)-1) )

Not sure if it possible to make that dynamically spill, but as a copy-down formula it could be written more simply as

=PRODUCT(B3:INDEX(B$3#, ROWS(B$3#)))