Calculating average rate of change in real time

[Geoff Halsall]

I have a real time numerical value in cell A1.
5 seconds later this value updates into cell A2,
then in another 5 seconds it updates into cell A3.
This continues in this way up to say cell 200

How can I calculate the average rate of change
with respect to all the numerical values in column A
at any given time

Would really appreciate any help

Thanks

 

[bbott]

So it's just the sum / count, right? Try:

=SUM(A:A)/COUNT(A:A)

 

[Geoff Halsall]

My next question is this

How would you calculate the average rate of change taking account of say only the last 10 cell values in the column

Example

Cells 1 to 10, then 2 to 11, then 3 to 13, then 4 to 14........and so on


Thanks for your help

 

[bbott]

A bit more involved, but I believe this will work for you:

=IF(COUNT(A:A) > 10,SUM(INDIRECT("A"&ROW(INDEX(A:A,COUNT(A:A)-9,1))):INDIRECT("A"&ROW(INDEX(A:A,COUNT(A:A),1))))/10,SUM(A:A)/COUNT(A:A))

 

[Snakehips]

Maybe ????......


Drag B10 formula down as required.

Excel 2007
	A	B
1	10
2	8
3	6
4	16
5	17
6	18
7	19
8	20
9	21
10	22	15.7
11	15.7	16.27
12	17	17.17
13	18	18.37
14	19	18.67
15	20	18.97
16	21	19.27
17	22	19.57
Sheet11

Cell Formulas
Range	Formula
B10	=IF(A1="","",SUM(A1:A10)/COUNTA(A1:A10))
B11	=IF(A2="","",SUM(A2:A11)/COUNTA(A2:A11))

 

[Geoff Halsall]

WOW bbott. That is a mouthful

I will give it a go and see what happens, then let you know

Thanks for your time and knowledge in helping me sort my problem.

Your a good guy friend

 

[joeu2004]

[deleted]

 

[Geoff Halsall]

Works a treat bbott. Nice one

Great work. Superstar

Would pay a kings ransom for your knowledge on this subject

 

[Rukt]

Edit: Oops, plenty of replies since I refreshed the page.

Hi Geoff,

I wouldn't call that a rate of change; it's just a running average. Assuming your data starts in column A, put this formula in say, cell B1, then copy down.

=AVERAGE($A$1:A1)

Note the mixed static and relative references.

Rukt

 

[bbott]

I wouldn't call that a rate of change; it's just a running average.

You're correct, Rukt.

Geoff, if you just want to see the % change between the current value and previous value in column A, try:

=(INDIRECT("A"&COUNT(A:A))-OFFSET(INDIRECT("A"&COUNT(A:A)),-1,0))/OFFSET(INDIRECT("A"&COUNT(A:A)),-1,0)

 

[Rukt]

bbott, for percentage change, why not the simpler =(A2-A1)/A1, which is equivalent to =(A2/A1)-1. Or if you're concerned about cells moving around, =(A2/OFFSET(A2,-1,0))-1

Geoff, if you're still interested in finding the average of the last 10 cells (or last X cells), the below is an option. A1 and $A$1 are first cell in your range (start with the first one and copy down; note the dynamic and static references); and X is the number of rows you want to take the average across.

=AVERAGE(OFFSET(A1,1-MIN( X ,ROW()-ROW($A$1)+1),0,MIN(X,ROW()-ROW($A$1)+1),1))

Cheers,
Rukt