Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Compare 1 cell to column of cells returning adjacent cells info? | Excel Worksheet Functions | |||
join data in multiple cells when adjoining cells match criteria ?? | Excel Worksheet Functions | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) |