Adding/multiplying all combinations of numbers from 2 areas

Originally Posted by MarcoPonte

I have 2 ranges of numbers, let's say they are stored in area 1 : cells A1 to C6 (18 numbers) and in area 2 : cells D9 to E11 (6 numbers). Is there an easy way to add every combination of an element from area 1 with an element from area 2 and store the result in a third area. So A1 + D9, A1 + D10, A1 + D11, etc So in total 18*6 numbers. The size of the resulting area can be 16x18 or 9x12.

Hi and welcome to the forum!

Can you clarify what you mean by the "size of the resulting area" being either 16x18 or 9x12? Your example results in 18x6 i.e. 108 permutations. How does this value relate to 16x18?

Also, does the order in which results are returned matter? If it does, what should that order be?

Regards

Does this spreadsheet help at all (I only finished it last week)?

in B2:B19 enter your first LIST of numbers
in C2:C7 enter the second list of numbers
in D1:G1 enter 1 for all the cells

You should now find in columns K and L are the combinations from columns B and C
Just enter a formula in column Q
=K1 + L1
and copy down to produce the various results.

You'll have to rearrange parts of column Q to get the output format you want but the main point of the spreadsheet is to generate combinations.

This will place the 108 permutations of sums from the entries in A1:C6 and D9:E11 into an 18-row-by-6-column matrix:

=SUM(INDEX($A$1:$C$6,1+INT((ROWS($1:1)-1)/COLUMNS($A$1:$C$6)),1+MOD((ROWS($1:1)-1),COLUMNS($A$1:$C$6))),INDEX($D$9:$E$11,1+INT(COLUMNS($A:A)-1)/COLUMNS($D$9:$E$11),1+MOD(COLUMNS($A:A)-1,COLUMNS($D$9:$E$11))))

in your first cell of choice and then copied to the right and down appropriately.

Regards