Calculate the average value for one cell values.

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.
Mohammad Alsolaimanapplication programmerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Martin LissOlder than dirtCommented:
Is there any limit to the number of past values that you want to average?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
See if approach like this would work for you.
One code is on Sheet1 Module and another on Module1.

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Martin LissOlder 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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Subodh Tiwari (Neeraj)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?
Martin LissOlder 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.
Mohammad Alsolaimanapplication programmerAuthor Commented:
Wonderful sktneer, how to see the code in excel please?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:

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, are you treating Blank or Zero values? Nulls or as Zeros
Note that


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...

Martin LissOlder 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.
Mohammad Alsolaimanapplication programmerAuthor Commented:
thanks a lot , and very sorry for late
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.