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?

 

[Peter_SSs]

Not sure if it possible to make that dynamically spill

One way would be to employ a user-defined function that will spill. An example

Function ProdSpill(r As Range) As Variant
  Dim i As Long
  Dim Prods() As Double
  
  ReDim Prods(1 To r.Count, 1 To 1)
  For i = 1 To r.Count
    Prods(i, 1) = WorksheetFunction.Product(Range(r.Cells(i), r.Cells(r.Cells.Count)))
  Next i
  ProdSpill = Prods
End Function

pasunmaa.xlsm
	B	C	D
3	1		36
4	2		36
5	1		18
6	3		18
7	6		6
8
Sheet1

Cell Formulas
Range	Formula
B3:B7	B3	={1;2;1;3;6}
D3:D7	D3	=ProdSpill(B3#)
Dynamic array formulas.

 

[pasunmaa]

Thx Peter, let me try user defined VBA-function, although I was hoping to be able to stick to a native array formula version. It's interesting that the limitations (in this case dynamic array formulas) of excel capabilities are not really documented anywhere (at least I am aware of), but rather users and communities need to find them out by error and trial.

 

[pasunmaa]

I tried Peter's VBA code and it works nicely even inside simple LET function, but it turned out that if I have slightly more complex LET function it fails still. If I am not directly referring to source column, but use CHOOSE before the custom VBA-function inside LET, I also get VALUE error. Then I tested a combination of CHOOSE and custom function. It turned out (again) that the issue has nothing to do with LET, but rather it seems to be related CHOOSE-function.

Dynamic arrays VBA and CHOOSE not working.xlsm
	A	B	C	D	E	F	G	H	I
1	Source Data			Correct result copy down		VBA	VBA + simple LET	VBA + complex LET	VBA + CHOOSE
2	Split ratio	Split ratio		Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier
3	1	1		36	36	36	36	#VALUE!	#VALUE!
4	2	2		36	36	36	36
5	1	1		18	18	18	18
6	3	3		18	18	18	18
7	6	6		6	6	6	6
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)
F3:F7	F3	=ProdSpill(B3#)
G3:G7	G3	=LET( x, ProdSpill(B3#), x )
H3	H3	=LET( x, CHOOSE({1}, B3#), y, INDEX(x,,1), z, ProdSpill(x), z )
I3	I3	=ProdSpill(CHOOSE({1}, B3#))
Dynamic array formulas.

 

[pasunmaa]

Excel VBA array handling seems to be more complex than expected. I figured out the solution eventually from this blogpost Dynamic arrays and VBA user defined functions (UDFs) - Excel Off The Grid and from its comments. Not according to my original interest just using the native excel dynamic array enabled formulas, but it works.

VBA code becomes somewhat more complex that what Peter suggested:

Function ProductArray(inputArray As Variant) As Variant

Dim i As Long
Dim j As Long
Dim tempArray As Variant
Dim resultArray As Variant

inputArray = ConvertArrayTo2D(inputArray)

'Force the resultArray to be the same size as the input array
resultArray = inputArray

If (UBound(resultArray, 2) > 1) Then ' Return VALUE error, if there are multiple columns
    resultArray = CVErr(xlErrValue)
Else
    'Calculate the result
    For i = LBound(resultArray) To UBound(resultArray)
        On Error Resume Next
        ReDim tempArray(i To UBound(resultArray))
        tempArray = SubArray(resultArray, i, UBound(resultArray))
        resultArray(i, 1) = WorksheetFunction.Product(tempArray)
    Next i
End If

ProductArray = resultArray
End Function

Private Function SubArray(arr As Variant, first As Long, last As Long) As Variant

Dim x As Long
Dim resArray() As Variant
ReDim resArray(first To last)

For x = first To last
    resArray(x) = arr(x, 1)
Next x

SubArray = resArray
End Function

Private Function ConvertArrayTo2D(toBeConverted As Variant) As Variant
' VBA treats single row arrays is being just one dimension, while single column arrays are seen as two dimensions.
' TRANSPOSE function converts single row arrays to 2D array
    ' For example
    ' Array(1,2,3) returns a one-dimensional array
    ' Application.Transpose(Array(1,2,3)) returns a two-dimensional array
' There are four input possibilities, all of which must be converted to 2d Variant Arrays:
' Column Range, Row Range, Constant Column Array {x;y;z}, Constant Row Array {x,y,z}
' This function converts all four types to 2D arrays, that can be used by VBA array functions
' SOURCE: https://exceloffthegrid.com/dynamic-arrays-and-vba-user-defined-functions-udfs/
    ' Read comments too

Dim outputArry As Variant

If TypeName(toBeConverted) = "Range" Then ' If Range
    If toBeConverted.Rows.Count = 1 Then ' 1D Array as Row Range
        outputArry = WorksheetFunction.Transpose(toBeConverted.Value)
    Else
        outputArry = toBeConverted.Value ' 2D Array
    End If
ElseIf TypeName(toBeConverted) = "Variant()" Then ' If Array
    ' Test for columns
    On Error Resume Next
    test = UBound(toBeConverted, 2)
    On Error GoTo 0

    If test = 0 Then ' Row Array
        outputArry = WorksheetFunction.Transpose(Radius)
    Else ' Column Array
        outputArry = toBeConverted
    End If
End If

ConvertArrayTo2D = outputArry

End Function

And the final sample excel looks like this:

ProductArray VBA v3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1					Copy down		VBA
2	Source Data				PRODUCT	*	VBA	VBA + Array constant	Table	VBA + FILTER	VBA + complex LET	VBA + CHOOSE
3	Split ratio	Type	Split ratio		Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier	Split multi-plier
4	4	Div	1		6	6	6	6	8	2	6	6
5	2	Buy	2		6	6	6	6	2	1	6	6
6	1	Buy	1		3	3	3	3	1	1	3	3
7	1	Buy	3		3	3	3	3	1		3	3
8	1	Div	1		1	1	1	1	1		1	1
9	1	Div							1
10	1	Div							1
11
12	Multi column fails intentionally
13	1	2	6
14	2	4	12
15	1	2	6
16	3	6	18
17	1	2	6
18
19	#VALUE!
Sample

Cell Formulas
Range	Formula
C4:C8	C4	={1;2;1;3;1}
E4:E8	E4	=PRODUCT($C4:INDEX(C:C, ROWS($C$4#)+ROW($C$4)-1) )
F4:F8	F4	=C4*IF(F5=0,1,F5)
G4:G8	G4	=ProductArray(C4#)
H4:H8	H4	=LET( x, ProductArray({1;2;1;3;1}), x )
I4:I10	I4	=ProductArray(SourceTable[Split ratio])
J4:J6	J4	=LET( x, ProductArray(FILTER(SourceTable[Split ratio], SourceTable[Type]="Buy")), x )
K4:K8	K4	=LET( x, CHOOSE({1}, C4#), y, INDEX(x,,1), z, ProductArray(x), z )
L4:L8	L4	=ProductArray(CHOOSE({1}, C4#))
A13:C17	A13	={1;2;1;3;1}*TRANSPOSE({1;2;6})
A19	A19	=ProductArray(A13#)
Dynamic array formulas.

Big thx for directing me to resolve this!