I am trying to calculate sum(a.*b) = a[1] * b[1] + a[2] * b[2]+.....
The array a with length n is present in the sheet. Values of b though are not present directly. To get b, there is a column of data and n indexes. b[i] = data[indexs[i]]
One way to find sum(a.*b) is to first buffer the value of b using INDEX(data, indexes) and then SUM(a * b). I want it to be done in one cell with no buffering. So I tried replacing b with the formula of b like SUM(a * INDEX(data, indexes)). But excel considers INDEX(data, indexes) to be iterable and gives me [SUM(a * b[1]), SUM(a * b[2]), ...]
How do I tell excel to take INDEX(data, indexes) as an array?
In earlier versions of Excel (pre-O365), you have to use the following manoeuvre to deliver an array using Index as described here:
=SUMPRODUCT(D3:D4*INDEX(A2:A10,N(IF({1},B2:B3))))
The curly brackets around the 1 {1} are not strictly necessary, but I tend to leave them in to signal that I am using this trick.
Tom, question.. is this only tied to versions prior to O365? I just used
=SUM(D3:D4*INDEX(A2:A10,B2:B3))and it worked. I guess it's really just the O365 array working kicking in. I don't have older versions to try. Btw, here is an interesting read indead. Neat trick =)To the best of my belief you have to do this in all versions prior to O365 - I am using Excel 2019. Interesting reading the references - the original article by EXCELXOR was way back in 2014 but I have no idea how they discovered the trick in the first place.
@TomSharpe The earliest description of that technique I've seen was by the late Laurent Longre (who wrote the old Morefunc Add-in for Excel) back in the mid
00's.