Value in the last cell of a moving column

[rtbrd]

I am tracking certain items, a column for each, and the data is entered in
the current last data cell in that column. Each time I enter data I insert a
row (there are statistics being run on the data below the last data entry).
I would like to calculate the difference from the currently entered data from
the first entry for that column. I would like to reference the newly entered
data without having to enter the data twice.

Example:
A1 Day 1 Day 2
A2 100 100
A3 200 200
A4 500 500
A5 300
A6 Average 267
A7 Delta 400 Average 275
A8 Delta 200

On day 1 I want the difference between cell A2 and A4 and the average
formula is =average(a2:a5). On day 2 I have inserted a row to move the
statistics down and I now want the difference between cells A2 and A5 and the
average formula is =average(a2:a6). Is there a way to make that reference?

 

[Bob Phillips]

Doesn't

=average(A$2:A5)

do it?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[rtbrd]

Yes, for the average but I want to update the difference formula
automatically on a daily basis without making manual changes.

Delta Day 1 = a4 - a2 and day 2 sould be = a5 - a2 but when I insert a row
the delta calculation remains at the previous cell, for day 2 after insertion
the formula remains = a4 - a2. I want a4 to change to a5 when I insert a row.

 

[Bob Phillips]

How about using

=OFFSET(A5,-1,0)-$A$2

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[rtbrd]

Thanks Bob that works.

How about using

=OFFSET(A5,-1,0)-$A$2

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)