summing last values in column
 

1. I would like to sum the last 25 days values in a column
automatically once the data are input


example:


a. I want to add the sum of the last 25 values in column A
b. the cell that provides me the figure will be cell A1
c. as soon as the data are entered at the bottom of the worksheet, A1
updates automatically
d. some cells in column A are blank

- How do I keep a running total of the last 25, or any values?

Biff, I rewrote for better explanation.

Thanks in advance,
E

summing last values in column
 

=SUM(LARGE(A2:A20,ROW(INDIRECT("1:"&MIN(COUNT(A:A) ,25)))))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"gotta know" wrote in message
ps.com...
1. I would like to sum the last 25 days values in a column
automatically once the data are input


example:


a. I want to add the sum of the last 25 values in column A
b. the cell that provides me the figure will be cell A1
c. as soon as the data are entered at the bottom of the worksheet, A1
updates automatically
d. some cells in column A are blank

- How do I keep a running total of the last 25, or any values?

Biff, I rewrote for better explanation.

Thanks in advance,
E

summing last values in column
 

Try this *array* formula in A1:

=SUM(A1000:INDEX(A2:A1000,LARGE(ROW(A2:A1000)*(A2: A1000<""),26)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"gotta know" wrote in message
ps.com...
1. I would like to sum the last 25 days values in a column
automatically once the data are input


example:


a. I want to add the sum of the last 25 values in column A
b. the cell that provides me the figure will be cell A1
c. as soon as the data are entered at the bottom of the worksheet, A1
updates automatically
d. some cells in column A are blank

- How do I keep a running total of the last 25, or any values?

Biff, I rewrote for better explanation.

Thanks in advance,
E

summing last values in column
 

Hmm. I tried the above, but returns 0.

What I am looking to do...

A1 = (sum of last 25 values in column A)

cell value

a5 2
a6 4
....
a38 0
a39 2
a40 3
a41 0

(I would like to have A1 sum up the last 25 values as soon as the data
are input into column A)

summing last values in column
 

If you tried to put Bob's formula in column A, it should have warned you of
a circular reference (which you could cure by restricting the A:A range).
Try it in column B (and remember it needs C.S.E.).
--
David Biddulph

"gotta know" wrote in message
ups.com...
Hmm. I tried the above, but returns 0.

What I am looking to do...

A1 = (sum of last 25 values in column A)

cell value

a5 2
a6 4
...
a38 0
a39 2
a40 3
a41 0

(I would like to have A1 sum up the last 25 values as soon as the data
are input into column A)

summing last values in column
 

It does give me a circular reference...

However, when I move it to colum B, it gives me the whole sum of column
A, not the "trailing" 25 periods...

-E

summing last values in column
 

*EDIT...

Yes it does give me a circular reference. I moved to column B.

This forumula gives me the sum of values in A2:A20, not the last 25
periods in column A.

=SUM(LARGE(A2:A20,ROW(INDIRECT("1:"&MIN(COUNT(A:A) ,25)))))

summing last values in column
 

Anything wrong with my suggestion?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"gotta know" wrote in message
ups.com...
*EDIT...

Yes it does give me a circular reference. I moved to column B.

This forumula gives me the sum of values in A2:A20, not the last 25
periods in column A.

=SUM(LARGE(A2:A20,ROW(INDIRECT("1:"&MIN(COUNT(A:A) ,25)))))

summing last values in column
 

RD:

Your formula below gave me: "#NUM!"

=SUM(LARGE(A2:A20,ROW(INDIRECT("1:"&MIN(COUNT(A:A) ,25)))))

The example below details what I need. Basically, if I have cells
A5:A5000 filled with data, and keep inputting data in A5001, A5002 on a
daily basis, I need the SUM of the trailing 25 periods.

A1 = sum of last 25 values

cell value
a501 1
a502 8
a503 0
....
a1001 0
a1002 0
a1003 6 ( I would need cell A1 to give me the sum of the last 25
periods sum(a978:a1003)



RagDyeR wrote:
Anything wrong with my suggestion?

summing last values in column
 

RD,
this was ingenious.

Kostis

RagDyeR wrote:
Try this *array* formula in A1:

=SUM(A1000:INDEX(A2:A1000,LARGE(ROW(A2:A1000)*(A2: A1000<""),26)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"gotta know" wrote in message
ps.com...
1. I would like to sum the last 25 days values in a column
automatically once the data are input


example:


a. I want to add the sum of the last 25 values in column A
b. the cell that provides me the figure will be cell A1
c. as soon as the data are entered at the bottom of the worksheet, A1
updates automatically
d. some cells in column A are blank

- How do I keep a running total of the last 25, or any values?

Biff, I rewrote for better explanation.

Thanks in advance,
E

summing last values in column
 

If you include the offset then you don't have "munge" the n: (which could be
really confusing if the range started in, say, A7)

=SUM(A100:INDEX(A2:A100,LARGE((A2:A100<"")*(ROW(A 2:A100)-ROW(A2)+1),25)))

Another way that excludes both empty cells and 0 values:

=SUM(A100:INDEX(A2:A100,LARGE(IF(A2:A100,ROW(A2:A1 00)-ROW(A2)+1),25)))

Biff

"RagDyeR" wrote in message
...
Try this *array* formula in A1:

=SUM(A1000:INDEX(A2:A1000,LARGE(ROW(A2:A1000)*(A2: A1000<""),26)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"gotta know" wrote in message
ps.com...
1. I would like to sum the last 25 days values in a column
automatically once the data are input


example:


a. I want to add the sum of the last 25 values in column A
b. the cell that provides me the figure will be cell A1
c. as soon as the data are entered at the bottom of the worksheet, A1
updates automatically
d. some cells in column A are blank

- How do I keep a running total of the last 25, or any values?

Biff, I rewrote for better explanation.

Thanks in advance,
E

summing last values in column
 

You're right Biff, the number of cells to calculate would be more obvious.

And I assume that you're using the convoluted offset of
"(ROW(A2:A100)-ROW(A2)+1)",
instead of just "(ROW(A2:A100)-1)" because of the possibility of row inserts
and deletes.

I guess it's a matter of preference, since to cover *all* possibilities can
be very difficult.

Delete Row2 and see what happens to your formula.

Insert at Row2 and see what happens to mine.

Both have shortcomings!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------



"T. Valko" wrote in message
...
If you include the offset then you don't have "munge" the n: (which could be
really confusing if the range started in, say, A7)

=SUM(A100:INDEX(A2:A100,LARGE((A2:A100<"")*(ROW(A 2:A100)-ROW(A2)+1),25)))

Another way that excludes both empty cells and 0 values:

=SUM(A100:INDEX(A2:A100,LARGE(IF(A2:A100,ROW(A2:A1 00)-ROW(A2)+1),25)))

Biff

"RagDyeR" wrote in message
...
Try this *array* formula in A1:

=SUM(A1000:INDEX(A2:A1000,LARGE(ROW(A2:A1000)*(A2: A1000<""),26)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"gotta know" wrote in message
ps.com...
1. I would like to sum the last 25 days values in a column
automatically once the data are input


example:


a. I want to add the sum of the last 25 values in column A
b. the cell that provides me the figure will be cell A1
c. as soon as the data are entered at the bottom of the worksheet, A1
updates automatically
d. some cells in column A are blank

- How do I keep a running total of the last 25, or any values?

Biff, I rewrote for better explanation.

Thanks in advance,
E