Calculate average difference of a series.

So, basically, (1-9)/4, for your example above? All of the numbers between the first and the last cancel out.

How dynamic does this need to be? The simplest is if the numbers are listed from A1:A5, then
=(A1-A5)/4

Try this..
Assuming the source range is A1:A5

Formula:

Please Login or Register  to view this content.

This will do...

if the datas are in A1 to G1 try

Formula:

Please Login or Register  to view this content.

For another approach, a little algebra will show that the average of the differences is the same as the difference of the averages:

((1-3)+(3-5)+(5-7)+(7-9))/4 -> (1+3+5+7)/4-(3+5+7+9)/4 -> =average(set1)-average(set2)

Originally Posted by avinkris

what about for a random series like this 19,23,43,54,20,13,19? i want to calculate the average change between numbers from left to right.

It doesn't matter that your series is random or not. Mathematically-speaking, performing:

=AVERAGE(x₁-x₂,x₂-x₃,x₃-x₄,...,x_n-1-x_n)

is identical to performing:

(x₁-x_n)/(n-1)

as Pauleyb points out.

Or, in Excel terms, the array formula**:

=AVERAGE(A1:A5-A2:A6)

is identical to the non-array:

=(A1-A6)/(COUNT(A1:A6)-1)

Regards

Always enjoy a good math discussion. If @avinkris knows the amount of measurements and the quantity of measurements is always the same, then I think we all agree that
(First Measurement - Last Measurement)/(Number of Measurements - 1)
is the simplest solution, and then 'hard coding' it to something like
=(A5-A1)/4
No need for arrays.

So, I wanted to know whether or not the number of measurements is dynamic. Here is my solution assuming the list starts in A1 and then goes across row 1:
=(A1-INDEX(1:1,,MATCH(1E+99,1:1,1)))/(MATCH(1E+99,1:1,1)-1)

Could use COUNT instead of MATCH, but the MATCH will ignore any blank cells between A1 and the last number (assuming that is a possibility).

Any thoughts to improve? Maybe determining where the start of the numbers are (e.g. A1 is blank and the numbers start in A2?).