# 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.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Older than dirtCommented:
Is there any limit to the number of past values that you want to average?
0
Excel & VBA ExpertCommented:
See if approach like this would work for you.
One code is on Sheet1 Module and another on Module1.
Average-of-Values.xlsm
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Older than dirtCommented:
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.
0
Excel & VBA ExpertCommented:
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?
0
Older than dirtCommented:
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.
0
application programmerAuthor Commented:
Wonderful sktneer, how to see the code in excel please?
0
Excel & VBA ExpertCommented:
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.
0
MIS LiasonCommented:
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
0
Older than dirtCommented:
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.
0
application programmerAuthor Commented:
thanks a lot , and very sorry for late
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.