0

I have a row of numbers, say, 1 2 3 (each number is in separate column, of course). I want to calculate the average difference between all values. This means that I would calculate all the differences: 1-2=-1, 1-3=-2 and 2-3=-1, and after that I take the average of the differences: (-1-2-1)/3=-1,333.... Is there a function that could do this? I would also calculate the largest and smallest (closest to 0) of these values as well as the median difference.

I tried to calculate the average as =AVERAGE((C3:J3)-(B3:I3)), when the values are in rows B-J but this does not give the same answer when I calculate the thing manually. Does anyone know how to do this? Doing this manually requires a massive amount of work that is not practical by any means.

EDIT:

There are 9 columns, I would like to calculate the difference between each possible combination of the values (but only in one direction) and then the average of those values.

user1718526
  • 1
  • 1
  • 2
  • 1
    The "average difference between all values" is unclear, could you expand a bit on that? Do you mean only the forward combinations (so a-b, a-c, a-d, b-c, b-d, c-d)? If you take every combination in both directions the average will always be zero. And is the number of items fixed? In that case, the formula is fairly simple [e.g. for four items, it is (3a+b-c-3d)/6] – Paul Aug 04 '22 at 12:42
  • 1
    You are only using two differences, don't you need to average all the differences ? All you have to do is get your formula right. It would help if you actually gave a sample of your real data, at least two rows, and all the columns. – Rohit Gupta Aug 04 '22 at 12:45
  • I mean all possible combinations so that each combination is calculated once. The direction does not matter. Say, I have values a,b,c,d. I would then calculate a-b, a-c, a-d, b-d and c-d, and then the average of these. Or I could equally well calculate b-a, c-a, d-a, d-b and d-c and the average. The number of terms is always 9. – user1718526 Aug 04 '22 at 12:49
  • For some reason I am not able to attach a picture here. – user1718526 Aug 04 '22 at 12:55

2 Answers2

2

For 9 terms, the answer is simply (8 * B3 + 6 * C3 + 4 * D3 + 2 * E3 - 2 * G3 - 4 * H3 - 6 * I3 - 8 * J3) / 36, using only the forward direction (and yes, the direction does make a difference!).

Note that cell F3 isn't used at all, because it is cancelled out, the same as the "2" in your original example: (1-2 + 1-3 + 2-3)/3 = (1 + 1-3 - 3)/3 = -4/3.

Paul
  • 895
  • 2
  • 11
  • Nice algorithm! In Excel 365, you can create a formula (for data in the same row): `=SUMPRODUCT(rng,SEQUENCE(,COUNT(rng),COUNT(rng)-1,-2))` and for the average difference divide by `COMBIN(COUNT(rng),2)` where `rng` refers to: `B3:J3` or some other contiguous range. – Ron Rosenfeld Aug 08 '22 at 11:09
0

Your need is for 9 terms and Paul has that answer.

Just to broaden that a smidge, to a general number of terms, for n values to use in your manner, you will have a polynomial of n terms the first of which (taking them left to right as you do) will have a coefficient equal to n-1 and each succeeding term will see that coefficient lower by 2.

Hence Paul's coefficients beginning with 8 and becoming 6, 4, 2, 0, -2, -4, -6, and -8.

So for 8 values, you'd have coefficients of: 7, 5, 3, 1, -2, -3, -5, and -7. So eight terms (a-h) like so:

7a + 5b + 3c + 1d - 1e - 3f - 5g - 7h

That gets their sum, considered "left to right." Or multiply the result by -1 to have their sum considered "right to left."

To find the average, you divide that by the sum of the sequence from 1 to n-1. In your case, that is 36. For the example just above for 8 values, it would be 28.

(In the special case, presumably not yours as I imagine your example is simplified for us, of the values going from 1 to however many values there are (1 to 9 in your example's case), the result is simply (1/3)*(n-1). NOT extensible to the general case real world numbers would bring!)

Jeorje
  • 1