# INDEX range and INDIRECT not working inside LET functions

#### pasunmaa

##### New Member
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?

#### Attachments

• INDEX not working within LET.jpg
90.9 KB · Views: 15

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### jasonb75

##### Well-known Member
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

##### New Member
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
ABCDEFGHI
1Source DataCorrect resultFailed within LET function
2Split ratioSplit ratioSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plier
3113636#VALUE!36#VALUE!
4223636#VALUE!36#VALUE!
5111818#VALUE!36#VALUE!
6331818#VALUE!36#VALUE!
76666#VALUE!36#VALUE!
Sample
Cell Formulas
RangeFormula
B3:B7B3={1;2;1;3;6}
D3:D7D3=PRODUCT(\$B3:INDEX(B:B, ROWS(\$B\$3#)+ROW(\$B\$3)-1) )
E3:E7E3=B3*IF(E4=0,1,E4)
G3:I7G3=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

##### Well-known Member
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)
Excel Formula:
``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

##### New Member

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
ABCDEFGHIJKLMN
212/31/20Current1,8008.24Div TaxNot included
63/23/09Div4500.651.002,7000.11AccountPantti220000000118661000
73/16/10Div4501.301.002,7000.222200090946249
83/4/11Split4500.002.002,7000.00
93/15/11Div4500.901.001,3500.30CurrencyEUR
103/20/12Div9001.401.002,7000.47Unrealised Gain/Loss €9,163 \$
1110/29/12Sell30030.951.0090010.32Unrealised Gain/Loss (%)161.5 %
123/19/13Div6001.501.001,8000.50Realised Gain/Loss €6,430 \$
133/18/14Div6001.351.001,8000.45Dividends Collected €8,999 \$
143/16/15Div6001.151.001,8000.38IRR €
153/14/16Div6001.201.001,8000.40IRR
163/13/17Div6000.651.001,8000.22
179/21/17Div6000.651.001,8000.22
183/12/18Split1,2000.003.003,6000.00
193/19/18Div6000.691.006000.69
209/27/18Div1,8000.231.001,8000.23
213/18/19Div1,8000.241.001,8000.24
229/27/19Div1,8000.241.001,8000.24
233/16/20Div1,8000.241.001,8000.24
249/17/20Div1,8000.241.001,8000.24
2500.00
2600.00
2700.00
Asset Analyses MrExcel
Cell Formulas
RangeFormula
C2C2=XLOOKUP(1,--(tAssetPerAccount[Asset] = \$L\$3) * (tAssetPerAccount[Account] = \$L\$4),tAssetPerAccount[Units])
D2D2=XLOOKUP(1,--(tAssetPerAccount[Asset] = \$L\$3) * (tAssetPerAccount[Account] = \$L\$4),tAssetPerAccount[Price])
A2A2=TODAY()
A3:E24A3=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:H27H3=PRODUCT(\$E3:INDEX(\$E:\$E, ROWS(\$A\$3#)+ROW(\$A\$3)-1) )*C3
I3:I27I3=D3/PRODUCT(\$E3:INDEX(\$E:\$E, ROWS(\$A\$3#)+ROW(\$A\$3)-1) )
N4:N7N4=LET( uniqueAcnAsset, UNIQUE(tTransactions[Account]:tTransactions[Asset]), filtered, FILTER(uniqueAcnAsset, INDEX(uniqueAcnAsset,,2) = \$L\$3), INDEX(filtered,,1) )
L5L5=XLOOKUP(L4, tAccountName[Account],tAccountName[Broker])
L6L6=XLOOKUP(L4, tAccountName[Account],tAccountName[Account Type])
L9L9=XLOOKUP(1, (\$L\$3 = tAssetPerAccount[Asset]) * (\$L\$4 = tAssetPerAccount[Account]), tAssetPerAccount[Currency])
L10L10=XLOOKUP(1, (\$L\$3 = tAssetPerAccount[Asset]) * (\$L\$4 = tAssetPerAccount[Account]), tAssetPerAccount[Unrealised Gain/Loss €])
L11L11=XLOOKUP(1, (\$L\$3 = tAssetPerAccount[Asset]) * (\$L\$4 = tAssetPerAccount[Account]), tAssetPerAccount[Unrealised Gain/Loss (%)])
L12L12=XLOOKUP(1, (\$L\$3 = tAssetPerAccount[Asset]) * (\$L\$4 = tAssetPerAccount[Account]), tAssetPerAccount[Realised Gain/Loss €])
L13L13=XLOOKUP(1, (\$L\$3 = tAssetPerAccount[Asset]) * (\$L\$4 = tAssetPerAccount[Account]), tAssetPerAccount[Dividends Collected €])
Dynamic array formulas.

#### jasonb75

##### Well-known Member
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

##### New Member

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
LM
29AssetIRR
30ACN9,80 %
31WRT1V16,50 %
32IBM3,70 %
33CVS9,10 %
Asset Analyses MrExcel

Petri Asset Portfolio Account v7.xlsx
OPQR
29YearTotal portfolio valueTotal Unrealised Gain/LossTotal Realised Gain/Loss
3020100 €0 €0 €
3120110 €0 €0 €
3220120 €0 €0 €
3320130 €0 €0 €
3420140 €0 €0 €
3520150 €0 €0 €
3620160 €0 €0 €
3720170 €0 €0 €
3820180 €0 €0 €
3920190 €0 €0 €
4020200 €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

##### Well-known Member
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.
Excel Formula:
``=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

##### New Member
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)
Excel Formula:
``=PRODUCT(\$B3:INDEX(B:B; ROWS(\$B\$3#)+ROW(\$B\$3)-1) )``
and compare it to its incorrect dynamic counterpart (in column G)
Excel Formula:
``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

##### MrExcel MVP, Moderator
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)
Excel Formula:
``=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
Excel Formula:
``=PRODUCT(B3:INDEX(B\$3#, ROWS(B\$3#)))``

Replies
3
Views
80
Replies
1
Views
110
Replies
3
Views
152
Replies
1
Views
130
Replies
1
Views
315

1,129,559
Messages
5,637,056
Members
416,955
Latest member
Gohar hussain

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back