sum of last 7 numbers
 

I have a row where I enter a number every day. I need a cell which shows
the sum of the last 7 entered numbers.

For example, if I have the numbers 7 2 4 11 5 4 2 6, the cell must show
28 (11+5+4+2+6)


--
tzvarza`
------------------------------------------------------------------------
tzvarza`'s Profile: http://www.excelforum.com/member.php...o&userid=28765
View this thread: http://www.excelforum.com/showthread...hreadid=484523

sum of last 7 numbers
 

If your data is in row 2, starting in column A, then the formula =sum(a2:g2),
entered in g3 will give you the total of the first seven days. You can
autofill this formula to the right, and each day will have the sum of the
seven days ending with the latest.
Be aware that you run out of columns in Excel way before you run out of
rows. If this is a long-term project, you may want to restructure your data
so that each day's info is in the next row (rather than in the next column).

"tzvarza`" wrote:


I have a row where I enter a number every day. I need a cell which shows
the sum of the last 7 entered numbers.

For example, if I have the numbers 7 2 4 11 5 4 2 6, the cell must show
28 (11+5+4+2+6)


--
tzvarza`
------------------------------------------------------------------------
tzvarza`'s Profile: http://www.excelforum.com/member.php...o&userid=28765
View this thread: http://www.excelforum.com/showthread...hreadid=484523

sum of last 7 numbers
 

On Sat, 12 Nov 2005 07:39:33 -0600, tzvarza`
wrote:


I have a row where I enter a number every day. I need a cell which shows
the sum of the last 7 entered numbers.

For example, if I have the numbers 7 2 4 11 5 4 2 6, the cell must show
28 (11+5+4+2+6)


Not sure exactly what you want. In your text, you want the sum of the last 7;
but in your example, you are only summing the last 5.

Assumptions:
1. SUM formula is in cell A2
2. Data is in B2:IV2
3. Data is entered consecutively with no intervening blanks that need to be
ignored.
4. nums = the number of most recent entries to be SUM'd (5 or 7 or whatever)

The **array entered** formula:

=IF(COUNT(B2:IV2)<=nums,SUM(B2:IV2),SUM(OFFSET(A2, 0,-1+MAX(ISNUMBER(B2:IV2)*COLUMN(B2:IV2)),1,-nums)))

To **array-enter** a formula, after typing/pasting it into the formula bar,
hold down <ctrl<shift while hitting <enter. Excel will place braces {...}
around the formula.




--ron

sum of last 7 numbers
 

does this work?
=SUM(OFFSET(A2,MAX(0,COUNTA(A2:A65536)-7),0,7,1))

--
Don Guillett
SalesAid Software

"tzvarza`" wrote in
message ...

I have a row where I enter a number every day. I need a cell which shows
the sum of the last 7 entered numbers.

For example, if I have the numbers 7 2 4 11 5 4 2 6, the cell must show
28 (11+5+4+2+6)


--
tzvarza`
------------------------------------------------------------------------
tzvarza`'s Profile:
http://www.excelforum.com/member.php...o&userid=28765
View this thread: http://www.excelforum.com/showthread...hreadid=484523

sum of last 7 numbers
 

=SUM(LARGE((COLUMN(1:1))*(1:1<""),{1,2,3,4,5,6,7} ))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tzvarza`" wrote in
message ...

I have a row where I enter a number every day. I need a cell which shows
the sum of the last 7 entered numbers.

For example, if I have the numbers 7 2 4 11 5 4 2 6, the cell must show
28 (11+5+4+2+6)


--
tzvarza`
------------------------------------------------------------------------
tzvarza`'s Profile:
http://www.excelforum.com/member.php...o&userid=28765
View this thread: http://www.excelforum.com/showthread...hreadid=484523