3

I have one column with numbers eg. 130, 131, 135, 140, 120.., I would like to find the differences eg. 131-130, 135-131, 140-135.. and find their average without using another column to find the differences and then the average. Is there a formula to do that all together.

3
  • Are you looking for multiple outputs (differences & average)? Is the average you're looking for the average of the list values or the average of the differences? – fixer1234 Aug 31 '15 at 16:18
  • Average of the differences – Spongebob Squarepants Aug 31 '15 at 16:20
  • 1
    All but the first and last values cancel each other out when you add up the differences to find the average. The average of the differences is (last value - first value)/(count of values - 1). – fixer1234 Aug 31 '15 at 16:26
5

You're overthinking this:

  (131-130) + (135-131) + (140-135) + ...
= 131 - 130 + 135 - 131 + 140 - 135 + ...
= -130 + 131 - 131 + 135 - 135 + 140 ...
= -130 + (131-131) + (135-135) + ...
= -130 + 0 + 0 + ... + 120
= 120-130

i.e., the sum of the differences between pairs of consecutive numbers is simply the difference between the last number and the first one.  To get the average, just divide that by the number of pairs, which is the number of numbers minus 1.

So, for example, if the numbers are in A1 ... A7, you can say

=(A7-A1)/6

or

=(A7-A1)/(ROWS(A1:A7)-1)

or

=(A7-A1)/(ROW(A7)-ROW(A1))

(Note that you don't need to say -1 in the last form.)

3
  • 2
    For the average difference, you need n-1 rather than n (there's n-1 differences). – fixer1234 Aug 31 '15 at 16:45
  • @MinahilIkram - Welcome to Superuser. If you find an answer to question helpful (whether it was an answer to your question or someone else's), indicate by "Upvoting" - clicking the little grey triangle pointing up to the left of the answer. Similarly if you ask a question, wait a little while, then if there is an answer that completely answers your question, click on the check mark to the left of the answer to "accept" it as correct. – Adam Aug 31 '15 at 16:46
  • yes, n-1 works not just n – Spongebob Squarepants Aug 31 '15 at 16:48
2

This method is overkill in this case, but you could also do this with an array formula:

=AVERAGE((A2:A7)-(A1:A6))

To enter an array formula, type it in, then, with your cursor still in the formula editing field, press the combinationCtrl+Shift+Enter. The formula should now should show up as

={AVERAGE((A2:A7)-(A1:A6))}

...but you can't type those curly braces in yourself - you have to use the key-combo.

How it works: As an array formula, (A2:A7)-(A1:A6) returns an array (list) of each of the differences: A2-A1, A3-A2, etc. This is wrapped in an AVERAGE() and voilà.


The observation about most of the values cancelling out that @fixer1234 pointed out in a comment and @Scott explained in his answer is the best approach for your particular problem. As Scott noted in a comment below, you could do something similar with =AVERAGE(A2:A7)-AVERAGE(A1:A6). This would also take advantage of various terms calculating each other out.

The array formula approach in this answer does not rely on cancellation at all - it actually calculates each difference and finds the average of them. This is unnecessary for your exact scenario, but could serve as a building block if your requirements become more complex.

1
  • 1
    (1a) I believe that you have a sign error.  The OP appears to be asking for the average of A2-A1, A3-A2, ..., so your answer should be =AVERAGE((A2:A7)-(A1:A6)).  (1b) You don't actually need the extra parentheses; you can say =AVERAGE(A2:A7-A1:A6).  Arguably, the extra parentheses may make it clearer.  (2) You don't need an array formula; just do =AVERAGE(A2:A7)-AVERAGE(A1:A6).  (3) While I certainly figured this out by myself, and didn't copy from anybody, we should recognize the fact that fixer1234 was the first to post the observation about most of the values cancelling out. – Scott Aug 31 '15 at 17:17
0

It's been a long time since my last Statistics course, but it sounds like you're looking for the average deviation, correct?

Try =AVEDEV()

Another option would be to go ahead and use another column and hide it.

1

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.