Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas to numbers | New Users to Excel | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions |