C
candy.chiu.ad
I have the following data
Range1 (1x4)
id 1 3 5
Range2 (3x1)
2
4
6
The goal is very simple. I want to extract the numbers in Range1, then sum product them with Range2.
To extract numbers - Index(Range1, 1, {2,3,4})
Then multiply - MMULT(Index(Range1, 1, {2,3,4}), Range2)
The problem is since the result is single-cell, even I instructed cell to use an array formula, it defaulted back to regular formula. As a result, Index(Range1, 1, {2,3,4}) returned a 1x1 array instead of a 1x3 array, causing MMULT to fail.
Because of this, TRANSPOSE + SUMPRODUCT didn't work neither.
I then thought about creating an IndexWrapper function in VBA to force it to return an array.
' As a Test
Public Function IndexWrapper (arr As range) As Variant()
Dim cols(3) As Double
cols(1) = 2
cols(2) = 3
cols(3) = 4
IndexWrapper = WorksheetFunction.Index(arr, 1, cols)
End Function
WorksheetFunction.Index doesn't seem to take the cols into consideration. The entire row is returned.
Anyone has a good solution for this problem? I am trying not to create an one off work around. Thanks.
Range1 (1x4)
id 1 3 5
Range2 (3x1)
2
4
6
The goal is very simple. I want to extract the numbers in Range1, then sum product them with Range2.
To extract numbers - Index(Range1, 1, {2,3,4})
Then multiply - MMULT(Index(Range1, 1, {2,3,4}), Range2)
The problem is since the result is single-cell, even I instructed cell to use an array formula, it defaulted back to regular formula. As a result, Index(Range1, 1, {2,3,4}) returned a 1x1 array instead of a 1x3 array, causing MMULT to fail.
Because of this, TRANSPOSE + SUMPRODUCT didn't work neither.
I then thought about creating an IndexWrapper function in VBA to force it to return an array.
' As a Test
Public Function IndexWrapper (arr As range) As Variant()
Dim cols(3) As Double
cols(1) = 2
cols(2) = 3
cols(3) = 4
IndexWrapper = WorksheetFunction.Index(arr, 1, cols)
End Function
WorksheetFunction.Index doesn't seem to take the cols into consideration. The entire row is returned.
Anyone has a good solution for this problem? I am trying not to create an one off work around. Thanks.
1 has 1,3,5