Consider last 6 cells only
 

How can a formula consider and act on the last 6 items in a column where a
new item is added to the next blank cell each day. I want to keep all the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

Consider last 6 cells only
 

In A1:A12 - Enter 1,2,3,4,5,6,7,8,9,10,11,12
In Cell B6 enter =SUM(OFFSET(A6,-5,0,6,1)) and copy down to B12
Sample using Offset()
HTH

"Barney" wrote in message
...
How can a formula consider and act on the last 6 items in a column where a
new item is added to the next blank cell each day. I want to keep all the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

Consider last 6 cells only
 

Here is a formula that adds the last 6 items in a column A

=SUM(OFFSET(A1,COUNTA(A:A)-6,,6))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barney" wrote in message
...
How can a formula consider and act on the last 6 items in a column where a
new item is added to the next blank cell each day. I want to keep all the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

Consider last 6 cells only
 

Thanks Jim, that works, but my hope was that the result, 'SUM' in this case,
would always appear in the same cell.

"Jim May" wrote in message
news:YKzyd.9194$jn.8211@lakeread06...
In A1:A12 - Enter 1,2,3,4,5,6,7,8,9,10,11,12
In Cell B6 enter =SUM(OFFSET(A6,-5,0,6,1)) and copy down to B12
Sample using Offset()
HTH

"Barney" wrote in message
...
How can a formula consider and act on the last 6 items in a column where
a
new item is added to the next blank cell each day. I want to keep all
the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

Consider last 6 cells only
 

See my post

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barney" wrote in message
...
Thanks Jim, that works, but my hope was that the result, 'SUM' in this
case,
would always appear in the same cell.

"Jim May" wrote in message
news:YKzyd.9194$jn.8211@lakeread06...
In A1:A12 - Enter 1,2,3,4,5,6,7,8,9,10,11,12
In Cell B6 enter =SUM(OFFSET(A6,-5,0,6,1)) and copy down to B12
Sample using Offset()
HTH

"Barney" wrote in message
...
How can a formula consider and act on the last 6 items in a column
where
a
new item is added to the next blank cell each day. I want to keep all
the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

Consider last 6 cells only
 

Sorry but this isn't working for me. I always get 'zero'.

I have 1, 2, 3, 4, 5, 6, 7, and 8 in A1 thru A8. A20 should give me the sum
33 for A3:A8. When I add 9 in A9, the sum in A20 should be 39.

Thanks, Barney


"Bob Phillips" wrote in message
...
Here is a formula that adds the last 6 items in a column A

=SUM(OFFSET(A1,COUNTA(A:A)-6,,6))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barney" wrote in message
...
How can a formula consider and act on the last 6 items in a column where
a
new item is added to the next blank cell each day. I want to keep all
the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

Consider last 6 cells only
 

=SUM(OFFSET(A1,COUNTA(A1:A19)-6,,6))

worked for me.

--
Regards,
Tom Ogilvy

"Barney" wrote in message
...
Sorry but this isn't working for me. I always get 'zero'.

I have 1, 2, 3, 4, 5, 6, 7, and 8 in A1 thru A8. A20 should give me the
sum
33 for A3:A8. When I add 9 in A9, the sum in A20 should be 39.

Thanks, Barney


"Bob Phillips" wrote in message
...
Here is a formula that adds the last 6 items in a column A

=SUM(OFFSET(A1,COUNTA(A:A)-6,,6))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barney" wrote in message
...
How can a formula consider and act on the last 6 items in a column
where
a
new item is added to the next blank cell each day. I want to keep all
the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney

Consider last 6 cells only
 

Thanks Tom and Jim. You were both right and very helpful. I figured out
what I was doing wrong.

Barney
"Tom Ogilvy" wrote in message
...
=SUM(OFFSET(A1,COUNTA(A1:A19)-6,,6))

worked for me.

--
Regards,
Tom Ogilvy

"Barney" wrote in message
...
Sorry but this isn't working for me. I always get 'zero'.

I have 1, 2, 3, 4, 5, 6, 7, and 8 in A1 thru A8. A20 should give me the
sum
33 for A3:A8. When I add 9 in A9, the sum in A20 should be 39.

Thanks, Barney


"Bob Phillips" wrote in message
...
Here is a formula that adds the last 6 items in a column A

=SUM(OFFSET(A1,COUNTA(A:A)-6,,6))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barney" wrote in message
...
How can a formula consider and act on the last 6 items in a column
where
a
new item is added to the next blank cell each day. I want to keep all
the
old records.

Using Win XP and Excel 2002.

Thanks in advance.

Barney