summing last values in column

[gotta know]

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

 

[Bob Phillips]

=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)

 

[RagDyeR]

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!
=====================================================

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

 

[gotta know]

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)

 

[David Biddulph]

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.).

 

[gotta know]

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

 

[gotta know]

*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)))))

 

[RagDyeR]

Anything wrong with my suggestion?
--

Regards,

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

*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)))))

 

[gotta know]

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)

 

[vezerid]

RD,
this was ingenious.

Kostis

 

[T. Valko]

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(A2: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:A100)-ROW(A2)+1),25)))

Biff

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------



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(A2: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:A100)-ROW(A2)+1),25)))

Biff