Calculating average rate of change in real time

Geoff Halsall

New Member
Joined
May 21, 2014
Messages
31
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi Geoff,

Can you expand on "rate of change", in this context? Do you want the gradient of a line of best fit over the whole range? Or perhaps an instantaneous rate of change, according to some known function (quadratic, exponential, etc.)?

Also, how is the data getting into the sheet? A macro on a timer, or something else writing to the file?

Cheers,
Rukt
 
Upvote 0
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

For values in A1:A200, the average 5-sec rate of change is:

=(A200/$A$1)^(1/199) - 1

More generally, for any set of data in A1:An, where n is a row number, the average 5-sec rate of change is:

=(An/$A$1)^(1/(ROW(An)-1)) - 1

You might be able to use the RATE function in lieu of the math above. For example:

=RATE(199,0,-$A$1,A200)

Note the opposite signs of A1 and A200.

However, the internal implementation of Excel RATE sometimes returns a #NUM error, requiring that we provide a "guess". (Sigh, it should not in this simple case.)
 
Upvote 0
Hi there! Thanks for your reply

I tried the formula you gave and it returns the message #VALUE! in the cell

Is there a reason for this
 
Upvote 0
Hi there Rukt! Thanks for your reply

It is the average rate of change for all present values held within the column. Every time a new value is written into the next cell (in this case every 5 seconds) then the new average is calculated everytime (i.e. every 5 seconds)

The real time data is compiled in another column by using a dedicated module (i.e macro)

Hope this adds more clarity for you

Once again thanks
 
Upvote 0
I tried the formula you gave and it returns the message #VALUE! in the cell[.] Is there a reason for this

One of two:

1. There is a #VALUE in one of the referenced cells. (Unlikely.)

2. What looks like a number is actually text, probably with leading or trailing nonbreaking spaces (HTML nbsp).

For a quick fix, try entering =SUBSTITUTE(A1,CHAR(160),"") into B1 and copy down through B200 or however many rows you have in column A. Then use column B instead of column A in the formula.

A better fix might be in the macro that you use to create the numbers in column A. See the VBA Replace function. Use VBA Chr(160).

If that does not resolve your problem, I suggest that you upload an example Excel file to a file-sharing website and post the "shared" URL of the file in a response here. The following is a list of some free file-sharing websites; or use of your own choosing.

Dropbox: http://dropbox.com
Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Windows OneDrive: http://onedrive.live.com

PS.... I forgot to mention: format the cell(s) with the formula as Percentage.
 
Upvote 0
I think you are misunderstanding my problem. Let me add clarity

I need to know what the average rate of change is in real time as it happens, not after I have compiled a full column of data. I need to see it as it happens....REAL TIME!!!
 
Upvote 0
Geoff,

Perhaps I'm just being dense, but would you mind sharing a sample of your data (screenshot, in a table, or file-share site)? joeu2004 seems to be making the assumption that your values are growing exponentially; is this the case?

Rukt
 
Upvote 0
This is an addition to my last reply

Here is an example of what I am trying to achieve

Value comes into cell A1, say 10
Value comes into cell A2, say 8
Therefore present average rate of change = 10+8/2 = 9

Then value comes into cell A3, say 6
Therefore new average rate of change =10+8+6/3 = 8

Then value comes into cell A4, say 16
Therefore new average rate of change =10+8+6+16/4 = 10

This process continues up to say cell A200 in real time
 
Upvote 0
Here is an example of what I am trying to achieve

Value comes into cell A1, say 10
Value comes into cell A2, say 8
Therefore present average rate of change = 10+8/2 = 9

Then value comes into cell A3, say 6
Therefore new average rate of change =10+8+6/3 = 8

Then value comes into cell A4, say 16
Therefore new average rate of change =10+8+6+16/4 = 10

This process continues up to say cell A200 in real time
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top