ok, let me try again...i seem to be "challenged"

[gotta know]

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

 

[KC Rippstein]

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.

 

[Arvi Laanemets]

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

 

[T. Valko]

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

 

[Arvi Laanemets]

A correction:


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

 

[gotta know]

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