Automatic Formula Update

A

Andy_jm

I have an Excel 2003 spreadsheet with a column of figures to which I add 2
figures daily. I have a second column showing the daily average. The
difference between the last average and the first appears in a seperate cell.
However I have to update this cell manually. I'm wondering if anyone knows
how to automate this calculation so that it updates whenever I add a new
figure to the original column?
 
B

Bernie Deitrick

Andy,

You could use the workbook open event to automatically copy the value to a storage cell. If on
Sheet1 your cell B2 has the formula

=AVERAGE(A:A)

and you copy and paste its value to cell C2 just before every time you update the values in column
A, then you could use

Private Sub Workbook_Open()
Application.EnableEvents = False
Worksheets("Sheet1").Range("C2").Value = Worksheets("Sheet1").Range("B2").Value
Application.EnableEvents = True
End Sub

Copy the code and place it into the Thisworkbook's codemodule.

See here for more instructions on using event code:

http://www.cpearson.com/excel/Events.aspx


HTH,
Bernie
MS Excel MVP
 
R

ryguy7272

Note: find contents of last used cell (row)
=LOOKUP(2,1/(LEN(1:1)>0),1:1)

Note: find contents of last used cell (column)
=LOOKUP(1E+100,H:H)

Average of last three numbers in a row:
=AVERAGE(OFFSET(A1,0,COUNT(1:1)-3,1,3))

Average of last three numbers in a column:
=AVERAGE(OFFSET(INDIRECT("A" & COUNTA(A:A)),-2,0,3))

HTH,
Ryan--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top