sum of last 7 numbers

T

tzvarza`

I have a row where I enter a number every day. I need a cell which show
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 sho
28 (11+5+4+2+6
 
B

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

Ron Rosenfeld

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)
Click to expand...


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
 
B

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