If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can you sum the last 5 columns of data from a range of data
I have a range of data that ranges from a3 to g43. Some of the range is empty
of data but I enter new data daily. So say the data is currently entered through row 15. Is there a way I can sum just the last 5 rows of entered data and have it update automatically whenever I add in a new row of data into the already established range? Hope that is clear enough. |
#2
|
|||
|
|||
How can you sum the last 5 columns of data from a range of data
Son you want to sum the last row like if A15 is the last row you want to sum
A11:G15? =SUM(OFFSET($A$3,COUNT($A$3:$A$43)-1,,-5,7)) -- Regards, Peo Sjoblom Portland, Oregon (No private emails please) "By-Tor" wrote in message ... I have a range of data that ranges from a3 to g43. Some of the range is empty of data but I enter new data daily. So say the data is currently entered through row 15. Is there a way I can sum just the last 5 rows of entered data and have it update automatically whenever I add in a new row of data into the already established range? Hope that is clear enough. |
#3
|
|||
|
|||
How can you sum the last 5 columns of data from a range of dat
Not exactly, let me rephrase it on a smaller scale.
I have a range to enter data from say a3 to a43 and I want to sum only the last 5 fields of data entered. So say I have data entered up to a15, then I am trying to figure out a way to sum all data from only a11 to a15, but then when I enter data on line a16 I want it to sum only the data from a12 to a16. I tried to play with the formula written in the last post but the best result I could get was volatile. Thanks! "Peo Sjoblom" wrote: Son you want to sum the last row like if A15 is the last row you want to sum A11:G15? =SUM(OFFSET($A$3,COUNT($A$3:$A$43)-1,,-5,7)) -- Regards, Peo Sjoblom Portland, Oregon (No private emails please) "By-Tor" wrote in message ... I have a range of data that ranges from a3 to g43. Some of the range is empty of data but I enter new data daily. So say the data is currently entered through row 15. Is there a way I can sum just the last 5 rows of entered data and have it update automatically whenever I add in a new row of data into the already established range? Hope that is clear enough. |
#4
|
|||
|
|||
How can you sum the last 5 columns of data from a range of dat
Hang on that did it, I just had to adjust the offset command a little better.
Thanks Much! "By-Tor" wrote: Not exactly, let me rephrase it on a smaller scale. I have a range to enter data from say a3 to a43 and I want to sum only the last 5 fields of data entered. So say I have data entered up to a15, then I am trying to figure out a way to sum all data from only a11 to a15, but then when I enter data on line a16 I want it to sum only the data from a12 to a16. I tried to play with the formula written in the last post but the best result I could get was volatile. Thanks! "Peo Sjoblom" wrote: Son you want to sum the last row like if A15 is the last row you want to sum A11:G15? =SUM(OFFSET($A$3,COUNT($A$3:$A$43)-1,,-5,7)) -- Regards, Peo Sjoblom Portland, Oregon (No private emails please) "By-Tor" wrote in message ... I have a range of data that ranges from a3 to g43. Some of the range is empty of data but I enter new data daily. So say the data is currently entered through row 15. Is there a way I can sum just the last 5 rows of entered data and have it update automatically whenever I add in a new row of data into the already established range? Hope that is clear enough. |
#5
|
|||
|
|||
How can you sum the last 5 columns of data from a range of dat
=SUM(OFFSET($A$3,COUNT($A$3:$A$43)-1,,-5,))
will sum the last 5 entries in A3:A43 you can't obviously put the formula within that range or =SUM(INDEX($A$3:$A$43,COUNT($A$3:$A$43)-4):INDEX($A$3:$A$43,COUNT($A$3:$A$43))) -- Regards, Peo Sjoblom Portland, Oregon (No private emails please) "By-Tor" wrote in message ... Not exactly, let me rephrase it on a smaller scale. I have a range to enter data from say a3 to a43 and I want to sum only the last 5 fields of data entered. So say I have data entered up to a15, then I am trying to figure out a way to sum all data from only a11 to a15, but then when I enter data on line a16 I want it to sum only the data from a12 to a16. I tried to play with the formula written in the last post but the best result I could get was volatile. Thanks! "Peo Sjoblom" wrote: Son you want to sum the last row like if A15 is the last row you want to sum A11:G15? =SUM(OFFSET($A$3,COUNT($A$3:$A$43)-1,,-5,7)) -- Regards, Peo Sjoblom Portland, Oregon (No private emails please) "By-Tor" wrote in message ... I have a range of data that ranges from a3 to g43. Some of the range is empty of data but I enter new data daily. So say the data is currently entered through row 15. Is there a way I can sum just the last 5 rows of entered data and have it update automatically whenever I add in a new row of data into the already established range? Hope that is clear enough. |
#6
|
|||
|
|||
How can you sum the last 5 columns of data from a range of dat
Thanks again, those came back as 0 when I tried them. I got it to work using:
=SUM(OFFSET($A$3:$A$43,$D$1-5,0,5,1)) $D$1 is a count of how many entries I have and it updates with each new entry. "Peo Sjoblom" wrote: =SUM(OFFSET($A$3,COUNT($A$3:$A$43)-1,,-5,)) will sum the last 5 entries in A3:A43 you can't obviously put the formula within that range or =SUM(INDEX($A$3:$A$43,COUNT($A$3:$A$43)-4):INDEX($A$3:$A$43,COUNT($A$3:$A$43))) -- Regards, Peo Sjoblom Portland, Oregon (No private emails please) "By-Tor" wrote in message ... Not exactly, let me rephrase it on a smaller scale. I have a range to enter data from say a3 to a43 and I want to sum only the last 5 fields of data entered. So say I have data entered up to a15, then I am trying to figure out a way to sum all data from only a11 to a15, but then when I enter data on line a16 I want it to sum only the data from a12 to a16. I tried to play with the formula written in the last post but the best result I could get was volatile. Thanks! "Peo Sjoblom" wrote: Son you want to sum the last row like if A15 is the last row you want to sum A11:G15? =SUM(OFFSET($A$3,COUNT($A$3:$A$43)-1,,-5,7)) -- Regards, Peo Sjoblom Portland, Oregon (No private emails please) "By-Tor" wrote in message ... I have a range of data that ranges from a3 to g43. Some of the range is empty of data but I enter new data daily. So say the data is currently entered through row 15. Is there a way I can sum just the last 5 rows of entered data and have it update automatically whenever I add in a new row of data into the already established range? Hope that is clear enough. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
From several workbooks onto one excel worksheet | steve | General Discussion | 6 | December 1st, 2005 08:03 AM |
Select updated data from a range of columns | Alylia | Worksheet Functions | 5 | August 30th, 2005 01:53 PM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
VBA Code problem error 9 | Speedy | General Discussion | 19 | October 15th, 2004 09:05 PM |
Setting chart data range automatically | LoucaGreen | Charts and Charting | 4 | July 27th, 2004 02:06 AM |