Calculate the average value for one cell values.
Hi:
I have two cells in excel sheet
The first cell (let's say cell number C7) , I'll paste a numeric value on it, every several seconds. That's mean the new value will overwride the old value of the cell.
The second cell (let's say cell number C8), It should automatically show the average value of all values that pasted in cell number C7.
How could I calculate this average value? Please.
I had some knowledge in VBA.
(maybe I need to create an array to hold all C7 values, so I can display the average every time!)
please describe the events I should use, and how to go to code page in excel.
thanks in advance.
I have two cells in excel sheet
The first cell (let's say cell number C7) , I'll paste a numeric value on it, every several seconds. That's mean the new value will overwride the old value of the cell.
The second cell (let's say cell number C8), It should automatically show the average value of all values that pasted in cell number C7.
How could I calculate this average value? Please.
I had some knowledge in VBA.
(maybe I need to create an array to hold all C7 values, so I can display the average every time!)
please describe the events I should use, and how to go to code page in excel.
thanks in advance.
Martin Liss
Is there any limit to the number of past values that you want to average?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sktneer's solution works well and it is similar to what I was considering as an answer, but the reason I asked my question was that that solution works by adding the previous values to column A in sheet2 and there's the potential to run out of rows.
I think that won't be a case as if you update the cell value every second for whole day i.e. 24 hours, only 86400 cells will be populated on col. A of sheet2 and if the OP is using excel vesion 2007 and above, there will enough rows for 12 days or so. What do you say?
Certainly in most cases that would be enough rows (the maximum in Excel 2010 is 1048576), but it's conceivable that the user might need more so let's wait and see what he says.
ASKER
Wonderful sktneer, how to see the code in excel please?
Thanks!
To view the code, right click on Sheet1 Tab --> View Code
This way you can view the code on Sheet1 Module.
To view the code on Module1 (Standard Module), double click the Module1 from the Project Explorer on the left side of the VBA Editor.
To view the code, right click on Sheet1 Tab --> View Code
This way you can view the code on Sheet1 Module.
To view the code on Module1 (Standard Module), double click the Module1 from the Project Explorer on the left side of the VBA Editor.
M_SOLAIMAN,
Why the design of "Overwriting" the previous values...? Can you explain?
Why not keep/store the values, in the same sheet, ...then averaging is ridiculously simple...
For example, you could:
Hide the rows/columns with your values, or, ...
(or Put them in another (possibly hidden) sheet, like what was suggested)
The other concern with your current design is "History"
What if you are asked for yesterday's average?
Finally, ...how are you treating Blank or Zero values?
...as Nulls or as Zeros
Note that
Average(10,5,NULL,7)
=7.333
While:
Average(10,5,0,7)
=5.5
The interface you propose seems convenient, ...but can lead to a lot of unforeseen issues...
And, to me, ...this just seems like a lot of work to go through, ...just to get a simple average...
JeffCoachman
Why the design of "Overwriting" the previous values...? Can you explain?
Why not keep/store the values, in the same sheet, ...then averaging is ridiculously simple...
For example, you could:
Hide the rows/columns with your values, or, ...
(or Put them in another (possibly hidden) sheet, like what was suggested)
The other concern with your current design is "History"
What if you are asked for yesterday's average?
Finally, ...how are you treating Blank or Zero values?
...as Nulls or as Zeros
Note that
Average(10,5,NULL,7)
=7.333
While:
Average(10,5,0,7)
=5.5
The interface you propose seems convenient, ...but can lead to a lot of unforeseen issues...
And, to me, ...this just seems like a lot of work to go through, ...just to get a simple average...
JeffCoachman
I've requested that this question be closed as follows:
Accepted answer: 500 points for sktneer's comment #a41028664
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 500 points for sktneer's comment #a41028664
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
thanks a lot , and very sorry for late