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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,391
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
BCD
3136
4236
5118
6318
766
8
Sheet1
Cell Formulas
RangeFormula
B3:B7B3={1;2;1;3;6}
D3:D7D3=ProdSpill(B3#)
Dynamic array formulas.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

pasunmaa

New Member
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHI
1Source DataCorrect result copy downVBAVBA + simple LETVBA + complex LETVBA + CHOOSE
2Split ratioSplit ratioSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plier
31136363636#VALUE!#VALUE!
42236363636
51118181818
63318181818
7666666
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)
F3:F7F3=ProdSpill(B3#)
G3:G7G3=LET( x, ProdSpill(B3#), x )
H3H3=LET( x, CHOOSE({1}, B3#), y, INDEX(x,,1), z, ProdSpill(x), z )
I3I3=ProdSpill(CHOOSE({1}, B3#))
Dynamic array formulas.
 

pasunmaa

New Member
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

VBA Code:
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
ABCDEFGHIJKL
1Copy downVBA
2Source DataPRODUCT*VBAVBA + Array constantTableVBA + FILTERVBA + complex LETVBA + CHOOSE
3Split ratioTypeSplit ratioSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plierSplit multi-plier
44Div166668266
52Buy266662166
61Buy133331133
71Buy33333133
81Div11111111
91Div1
101Div1
11
12Multi column fails intentionally
13126
142412
15126
163618
17126
18
19#VALUE!
Sample
Cell Formulas
RangeFormula
C4:C8C4={1;2;1;3;1}
E4:E8E4=PRODUCT($C4:INDEX(C:C, ROWS($C$4#)+ROW($C$4)-1) )
F4:F8F4=C4*IF(F5=0,1,F5)
G4:G8G4=ProductArray(C4#)
H4:H8H4=LET( x, ProductArray({1;2;1;3;1}), x )
I4:I10I4=ProductArray(SourceTable[Split ratio])
J4:J6J4=LET( x, ProductArray(FILTER(SourceTable[Split ratio], SourceTable[Type]="Buy")), x )
K4:K8K4=LET( x, CHOOSE({1}, C4#), y, INDEX(x,,1), z, ProductArray(x), z )
L4:L8L4=ProductArray(CHOOSE({1}, C4#))
A13:C17A13={1;2;1;3;1}*TRANSPOSE({1;2;6})
A19A19=ProductArray(A13#)
Dynamic array formulas.


Big thx for directing me to resolve this!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,217
Messages
5,623,453
Members
415,970
Latest member
ZorroOP

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