Automatic Formula Update

[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?

 

[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

 

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