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...

enter image description here

| improve this answer | |

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


Mathematically-speaking, for example:


is equal to:


which is:





| improve this answer | |
  • 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:


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)

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.