5

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, 2015 at 16:18
  • Average of the differences Aug 31, 2015 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, 2015 at 16:26

3 Answers 3

6

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, 2015 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, 2015 at 16:46
  • yes, n-1 works not just n Aug 31, 2015 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. Aug 31, 2015 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .