Large SumProduct Array -- original title:Loop Function

VBA is the wrong way to go for this problem.

It isn't clear what you want to do with this, but some variation of SUMPRODUCT would be faster and easier to maintain than a VB function.

In the first section are those 100 values, A1*E1+B1*F1+C1*G1 etc. to be considered seperatly or summed together?

In what cells do you want the results?

As there are so many combinations I thought a VBA function could automatically calculate all the combinations seperately.
The first 100 combinations have to be calculated seperately and not summed up. The results of the first 100 results can be set into one column.
In this way there are 5000 combinations in this example.

Though I have to mention that the real data contains 5151 * 120 combinations, so VBA may be useful?

Have you looked at SUMPRODUCT?

SUMPRODUCT(A1:C1,E1*G1) is equal to A1*E1 + B1*F1 + C1*G1.

The table of results can be layed out to take advantage of relative addressing to minimize the typing nessesary. (And the people on this board know some tricks to make layouts behave well.)

For straight-forward calculations, like this, Excel's innate functions are faster and better than VB. The coding for Excel is closer to machine code than VB and therefore faster.

If your data is changing infrequently, setting calculation to Automatic Except for Tables will prevent unnessesary re-calculation.

If you could attach a sample worksheet of the input data and the output data to show where you want these results, we could come up with an easy way for you to get your results.

I added the excel file. As you can see there are very much combinations possible. If I have to do this manually with the SUMPRODUCT method this will cost very much time. An automatic alternative would be great. If the results for every 5151 combination are just put into 1 new row there will be 120 rows with results.

I added columns and a row to label those entries.
The Name rightSide needs to be added for the formula to work.

You're right, it is a large project. In order to make it small enough to upload, I, ommited some columns and rows from the result table. Just fill everything to the right and down until you are done.
Notice that I set calculation to Automatic except Tables.
Also, the rest of the spreadhseet should be on a different sheet from this data monster.

Foolish of me. I should share the solution with those who don't want to download a monster file.
I selected I1 and created a namedRange =OFFSET(!$E$1,COLUMNS(!$E1:E1)-1,0,1,3). Since relative addressing aplies to namedRanges, it is important to select the proper cell when creating a named range.

Then in I1 I entered =SUMPRODUCT($A1:$A2,rightSide)

Filling down and right will result in a table of the results.


(Are these inner products of vectors?)

That file is large. The 5151 rows of the group on the left. Those rows are variations of 1-100,1-100,1-100.

In this file, I eliminated the 5151X3 area and replaced it with a single column of integers A1:100. The three yellow cells are list validated to that column. The column next to the grey area shows the SUMPRODUCT of that row and the entries in the yellow cells.

I've also changed the title of the thread, since a loop is not the method needed to attack the problem.
It's still in this forum in case some one has a VB approach.

Thank you very much for providing me this quick solution.
The only thing that misses was the 0 value in the yellow selection menu.
Could you please tell me how to set the yellow vectors over a wider range including 0.
As 0 is an important indicator it would be if I could include this one.

Insert one cell in A1. Put 0 in that cell.
change the list source for the validation to A1:A101