# How to find average of first difference in one set of column excel/google docs?

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.

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

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

• For the average difference, you need n-1 rather than n (there's n-1 differences). – fixer1234 Aug 31 '15 at 16:45
• yes, n-1 works not just n – Spongebob Squarepants Aug 31 '15 at 16:48

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.

• (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

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.