Now, I am wondering if this is possible...

1. My formula gives me two values - either 0 or 1.

2. I input data daily in column A

3. I need a sum of the "trailing" 25 periods

4. My cells look like this:

a2001 1
a2002 1
a2003 0
a2004 0
a2005 0
a2006 0
a2007 1
....
a4999 1
a5000 1
etc.

5. Of course, I could create a formula adjacent to the cell, b5000=
sum(a4076:a5000), but I would like to have that same result listed on
cell A1 for the previous 25 periods (for quick reference/summary page).
Is that possible?

-E

Maybe something like this:

With
Text entries beginning in A10
Numeric entries beginning in B10
(both extending down as far as needed)

This function locates the last text entry in Col_A and returns the sum of
last 25 cells from Col_B, ending with the Col_B cell on the same row as the
last Col_A entry
A1: =SUM(OFFSET(INDEX(B:B,MATCH(REPT("Z",255),A:A)),-24,0,25))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"gotta know" wrote:

Now, I am wondering if this is possible...

1. My formula gives me two values - either 0 or 1.

2. I input data daily in column A

3. I need a sum of the "trailing" 25 periods

4. My cells look like this:

a2001 1
a2002 1
a2003 0
a2004 0
a2005 0
a2006 0
a2007 1
....
a4999 1
a5000 1
etc.

5. Of course, I could create a formula adjacent to the cell, b5000=
sum(a4076:a5000), but I would like to have that same result listed on
cell A1 for the previous 25 periods (for quick reference/summary page).
Is that possible?

-E

In case that happens to work for you.....
This version avoids volatile functins:

A1:
=SUM(INDEX(B:B,MATCH(REPT("Z",255),A:A)-24):INDEX(B:B,MATCH(REPT("Z",255),A:A)))


***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Maybe something like this:

With
Text entries beginning in A10
Numeric entries beginning in B10
(both extending down as far as needed)

This function locates the last text entry in Col_A and returns the sum of
last 25 cells from Col_B, ending with the Col_B cell on the same row as the
last Col_A entry
A1: =SUM(OFFSET(INDEX(B:B,MATCH(REPT("Z",255),A:A)),-24,0,25))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"gotta know" wrote:

Now, I am wondering if this is possible...

1. My formula gives me two values - either 0 or 1.

2. I input data daily in column A

3. I need a sum of the "trailing" 25 periods

4. My cells look like this:

a2001 1
a2002 1
a2003 0
a2004 0
a2005 0
a2006 0
a2007 1
....
a4999 1
a5000 1
etc.

5. Of course, I could create a formula adjacent to the cell, b5000=
sum(a4076:a5000), but I would like to have that same result listed on
cell A1 for the previous 25 periods (for quick reference/summary page).
Is that possible?

-E

If you do your formula idea in column B, then in cell A1, just put
=LOOKUP(2,1/B1:B10000,B1:B10000) and set 10000 as high as you need.
Also, if you set your data up as a list, then you just make new entries at
the bottom of the list and the formula automatically drags down for you.
That way you don't have to drage your formula down to B10000.

"gotta know" wrote in message
ups.com...
Now, I am wondering if this is possible...

1. My formula gives me two values - either 0 or 1.

2. I input data daily in column A

3. I need a sum of the "trailing" 25 periods

4. My cells look like this:

a2001 1
a2002 1
a2003 0
a2004 0
a2005 0
a2006 0
a2007 1
...
a4999 1
a5000 1
etc.

5. Of course, I could create a formula adjacent to the cell, b5000=
sum(a4076:a5000), but I would like to have that same result listed on
cell A1 for the previous 25 periods (for quick reference/summary page).
Is that possible?

-E

Hi

With some header text (like 'Data' or 'Values' in A1, and 1s or 0s in column
as starting from A2 without any gaps in datarange, enter into any cell the
formula
=SUM(OFFSET($A$1,MAX(COUNTA($A:$A)-25,1),,MIN(25,COUNTA($A:$A)),))


Arvi Laanemets



"gotta know" wrote in message
ups.com...
Now, I am wondering if this is possible...

1. My formula gives me two values - either 0 or 1.

2. I input data daily in column A

3. I need a sum of the "trailing" 25 periods

4. My cells look like this:

a2001 1
a2002 1
a2003 0
a2004 0
a2005 0
a2006 0
a2007 1
...
a4999 1
a5000 1
etc.

5. Of course, I could create a formula adjacent to the cell, b5000=
sum(a4076:a5000), but I would like to have that same result listed on
cell A1 for the previous 25 periods (for quick reference/summary page).
Is that possible?

-E

Try this:

=SUM(OFFSET(A2001,COUNT(A2001:A65536)-1,,-25))

Or, use a cell to hold the number criteria:

B1 = 25

=SUM(OFFSET(A2001,COUNT(A2001:A65536)-1,,-B1))

Biff

"gotta know" wrote in message
ups.com...
Now, I am wondering if this is possible...

1. My formula gives me two values - either 0 or 1.

2. I input data daily in column A

3. I need a sum of the "trailing" 25 periods

4. My cells look like this:

a2001 1
a2002 1
a2003 0
a2004 0
a2005 0
a2006 0
a2007 1
...
a4999 1
a5000 1
etc.

5. Of course, I could create a formula adjacent to the cell, b5000=
sum(a4076:a5000), but I would like to have that same result listed on
cell A1 for the previous 25 periods (for quick reference/summary page).
Is that possible?

-E

A correction:


...., enter into any cell except column A the

Thank you all for your help. Arvi hit it on the $... works well,
simple forumula, didn't have to arrange anything. Wow, 2 days worth of
headbanging for what seems a simple forumula.

I wonder why excel doesn't have a simple formula for what many must
use?:

=sum("last data in column",-25) or something...

Thank you again.

-EG

Arvi Laanemets wrote:
Hi

With some header text (like 'Data' or 'Values' in A1, and 1s or 0s in column
as starting from A2 without any gaps in datarange, enter into any cell the
formula
=SUM(OFFSET($A$1,MAX(COUNTA($A:$A)-25,1),,MIN(25,COUNTA($A:$A)),))


Arvi Laanemets



"gotta know" wrote in message
ups.com...
Now, I am wondering if this is possible...

1. My formula gives me two values - either 0 or 1.

2. I input data daily in column A

3. I need a sum of the "trailing" 25 periods

4. My cells look like this:

a2001 1
a2002 1
a2003 0
a2004 0
a2005 0
a2006 0
a2007 1
...
a4999 1
a5000 1
etc.

5. Of course, I could create a formula adjacent to the cell, b5000=
sum(a4076:a5000), but I would like to have that same result listed on
cell A1 for the previous 25 periods (for quick reference/summary page).
Is that possible?

-E