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.
33 Answers
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)/6or
=(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.)
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.
1It'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
