SumProduct over cell sets (not touching)

Question

I have a complete data set that is designed for 4 different groups. One of the values ​​is average time, the other is quantity. For Total, I have to multiply them and then divide by the total number. I am currently using:

=SUM(D32*D2,D94*D64,D156*D126,D218*D188)/SUM(D32,D94,D156,D218) 

I would rather use SumProduct, if possible, to make it more readable. I've tried:

 =SUMPRODUCT((D2,D64,D126,D188),(D32,D94,D156,D218))/SUM(D32,94,D156,D218) 

But, as you can tell from my post here, this did not work. Is there a way to make SumProduct the way I want?

Answer 1

This may be possible with the excel-fu workshop, but even if it can be done, it is unlikely to be more readable than your original solution. The problem is that even after 20 years, Excel is still undergoing discontinuous ranges. Naming them will not work, array formulas will not work, and, as you can see, with SUMPRODUCT, they usually do not work in the root functions of the array. It’s best to come up with a custom function here.

UPDATE

You doubt that I thought about how to handle discontinuous ranges. This is not what I had to deal with in the past. I did not have time to give a better answer when you asked a question, but now that I have a few minutes, I have hacked a user-defined function that will do what you want:

 Function gvSUMPRODUCT(ParamArray rng() As Variant) Dim sumProd As Integer Dim valuesIndex As Integer Dim values() As Double For Each r In rng() For Each c In r.Cells On Error GoTo VBAIsSuchAPainInTheAssSometimes valuesIndex = UBound(values) + 1 On Error GoTo 0 ReDim Preserve values(valuesIndex) values(valuesIndex) = c.Value Next c Next r If valuesIndex Mod 2 = 1 Then For i = 0 To (valuesIndex - 1) / 2 sumProd = sumProd + values(i) * values(i + (valuesIndex + 1) / 2) Next i gvSUMPRODUCT = sumProd Exit Function Else gvSUMPRODUCT = CVErr(xlErrValue) Exit Function End If VBAIsSuchAPainInTheAssSometimes: valuesIndex = 0 Resume Next End Function 

Some notes:

• Excel lists ranges by column, then row, so if you have a continuous range where the data is organized by column, you need to select separate ranges: gvSUMPRODUCT (A1: A10, B1: B10), not gvSUMPRODUCT (A1: B10).
• The function works by mutually multiplying the first half of the cells with the second and then summing these products: gvSUMPRODUCT (A1, C3, L2, B2, G5, F4) = A1 * B2 + C3 * G5 + L2 * F4. That is, order matters.
• You can extend the function to enable n-wise multiplication by doing something like gvNSUMPRODUCT (n, ranges).
• If there is an odd number of cells (not ranges), it returns a #VALUE error.

Answer 2

I agree with the comment "Perhaps this is possible with the excel-fu workshop, but even if it can be done, it is unlikely to be more readable than your original solution."

A possible solution is to embed the CHOOSE() function in your SUMPRODUCT (this trick is actually quite convenient for vlookups, finding conditional maxima, etc.).

Example: Say your data has eight cases and is in two columns (columns B and C), but you do not want to include some cases (excluding cases in rows 4 and 5). Then the SUMPRODUCT code looks like this ...

 =SUMPRODUCT(CHOOSE({1,2},A1:A3,A6:A8),CHOOSE({1,2},B1:B3,B6:B8)) 

I actually thought about it on the fly, so I don’t know the limitations and, as you can see, this is not so.

Hope this helps! :)

Answer 3

It might be useful to create an intermediate table that summarizes the data that you use to calculate the total product. It would also facilitate the calculation.

Answer 4

Note that sumproduct(a, b) = sumproduct(a1, b1) + sumproduct(a2, b2) , where the range a is divided into ranges a1 and a2 (and similar for b)