INDEX range and INDIRECT not working inside LET functions

pasunmaa

New Member
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
    INDEX not working within LET.jpg
    90.9 KB · Views: 15

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
1DateTypeUnitsUnit priceSplit ratioSplit adjusted unitsSplit adjusted priceSplit adjusted unitsSplit adjusted price
212/31/20Current1,8008.24Div TaxNot included
310/17/08Buy15020.011.009003.34AssetWRT1VHoldings in accounts
410/24/08Buy15017.011.009002.84Account #2200013509799022000135097990
52/2/09Buy15019.521.009003.25BrokerNordea6035711
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
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

  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
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
May 28, 2005
Messages
48,447
Office Version
  1. 365
Platform
  1. Windows
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#)))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,215
Members
416,847
Latest member
inaramos

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top