sum of last 7 numbers

[tzvarza`]

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)

[bpeltzer]

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

[Ron Rosenfeld]

On Sat, 12 Nov 2005 07:39:33 -0600, tzvarza`
<[email protected]> 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

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
"tzvarza`" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

=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`" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>