# Average Sum of Pairs of Numbers in a Column

Suppose I have a column of 10 numbers and I want to know what the average of the sums of each number with each other number (except itself) is. So it would be the average of the sum of, for example, A1+A2, A1+A3....,A8+A10,A9+A10.

What is the fastest way to do this? My current method of fixing one cell constant and iterating through every other cell seems incredibly tedious. The other, better method I found is to create a table cycling through each one with relative references. Is there a way to do this in one cell though?

• @CharlieRB Updated with a better solution that I had that still doesn't get it down to one cell. – Max Power Jan 9 '15 at 22:38
• LOL I was just typing in an answer with the table answer. I'll post it anyway to see if there is anything there than can help. In the meantime, can you post an example or screen shot of how you want the results? – CharlieRB Jan 9 '15 at 22:44
• @CharlieRB To be honest I'm really just curious about finding one of those one-formula solutions that people seem to be able to come up with so I can dissect how it works. – Max Power Jan 9 '15 at 22:46
• So this is hypothetical and not a real problem? – CharlieRB Jan 9 '15 at 22:48
• If you want a one-cell solution, what is your desired output? A delimited list of sums? A max sum? Min? Please clarify. – Excellll Jan 9 '15 at 22:50

Insert a row at the top and transpose the numbers across it. Then using an `IF` statement you can create a matrix of all the sums.

Place this formula in cell B2 and copy it to the bottom corner of the matrix.

``````=IF(\$A2=B\$1, "", \$A2+B\$1)
``````

The results... I believe this is simply (again, assuming the values are in A2:A9):

``````=2*SUM(A2:A9)/COUNT(A2:A9)
``````

Mathematically-speaking, for example:

``````((a+b)+(a+c)+(a+d)+(b+c)+(b+d)+(c+d))/6
``````

is equal to:

``````(3a+3b+3c+3d)/6
``````

which is:

``````3(a+b+c+d)/6
``````

i.e.:

``````(a+b+c+d)/2
``````

Regards

• Now the phrase that pops in to my head is this one - `"Can't see the wood for the trees"` - this is my answer of the week. In my defence my approach can perhaps be usefully used for other functions (median, standard deviation?) but for average this is much better – barry houdini Jan 12 '15 at 13:11
• :-) Of course - in general such an abbreviated solution may not be derivable given certain conditions/requirements. Something about such questions often makes me suspect there may be such a shortcut, though. And let's not forget that your solution demonstrates some excellent technique, which, quite rightly as you say, has application beyond just this particular case. – XOR LX Jan 12 '15 at 13:40

To get the average of the sums of each pair (except each number with itself) you can do that with a single formula if you re-create CharlieRB's transposed table within the formula like this:

`=AVERAGE(IF(ROW(A2:A9)>TRANSPOSE(ROW(A2:A9)),A2:A9+TRANSPOSE(A2:A9)))`

confirmed with CTRL+SHIFT+ENTER

So `TRANSPOSE(A2:A9)` gives you the transposed column and then `A2:A9+TRANSPOSE(A2:A9)` gives a matrix of the sum of every possible pair. The `IF` function excludes "reversed pairs" (e.g. excludes `A5+A4` because `A4+A5` is already included) and the pairs which are each number summed with itself, and then `AVERAGE` function gives you the required result

If the range is a row of data rather than a column then you need to use 2 x `COLUMN` functions in place of the `ROW` functions

Note: the excluded pairs are only specifically each cell with itself, that doesn't exclude the possibility of two equal numbers being summed, so if A2 = A7 then you still have the pair `A2+A7` included (but not `A2+A2` or `A7+A7` or the reversed `A7+A2`)

• You don't have to exclude the reversed pairs. If each pair is included twice, the average will still be the same. – freekvd Jan 10 '15 at 21:39
• @freekvd, yes that's true, originally my suggested formula only excluded the ones that are summed with themselves (using <> in place of >). I changed it because I think this version more closely answers the question. – barry houdini Jan 10 '15 at 21:48