Looking for formula help

[amw1]

I've got a column of values, primarily sorted but not guarenteed. Column length not static.

165
164
163
163.5
164

I need a formula to find the average difference. I prefer to not do this through vba but will if it is my only option

Thanks in advance

 

[xlbo]

just add an extra volumn that does 1 minus the other
copy down
average that column

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[bluedragon2]

=SUMIF(A1:A4000,">0")/COUNTIF(A1:A4000,">0")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Never mind, didn't see the difference part

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SilentAiche]

amw1:

I am confused by part(s) of your question.

primarily sorted but not guaranteed

Does this mean it does not matter in what order the values appear?

The average difference in your values, in the order presented, is -.25; however, if you sort them low-to-high, the average difference becomes +.5. Big difference.

Another thought. If the value goes from 165 to 164, do you want the absolute value of the difference ("1") included in the computation, or "-1"? This, too, makes a huge difference.

xlbo's answer, of course, is correct for the question as asked, but I was wondering if you gave enough details in your question to get the answer you really need. May I ask the nature of the data (for example, are you tracking day-to-day changes in stock price, temperature, etc.)??

THanks,
Tim

 

[KenWright]

Subject to the caveats that the others have brought up, assuming your data starts in A1 with no headers, and assuming that you are looking for

A
1 165
2 164
3 163
4 163.5
5 164

Average(A2-A1,A3-A2,A4-A3,A5-A4......)

then try

=SUMPRODUCT((OFFSET($A$1,1,,COUNTA(A:A)-1)-(OFFSET($A$1,,,COUNTA(A:A)-1))))/(COUNTA(A:A)-1)

Result will change automatically as values are added to the end of the list.

Assumes NO OTHER VALUES in Col A besides your data to be averaged, and assumes NO BLANKS in your data.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------