Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select only last 30 days of a work sheet
I need to filter out only the last x number of days/hours of data from my
work sheet. The worksheet posts new data every 5 minutes. I need to extract the newest data points. The time will vary from 7 days to 90 days. My existing data has a date/time combination. It looks like this,"12/19/2008 19:10" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select only last 30 days of a work sheet
Something along these lines might deliver it here ..
Let's say you want to dynamically extract the last (ie "lowest") 7 lines of source data into another sheet, based on a key col, where the key col would contain contiguous source data populated down (ie with no empty cells in-between data) Source data assumed in Sheet1, cols A to G, col headers in row1, data in row2 down, with key col A (date). It's assumed there's at least 7 lines of source data to start with. In Sheet2, with the same col headers pasted in A1:G1 Place this in A2: =OFFSET(INDIRECT("'Sheet1'!A"&COUNTA(Sheet1!$A:$A)-6),MOD(ROWS($1:1)-1,7),COLUMNS($A:A)-1) Copy A2 across to G2, fill down to G8. Format the cols accordingly. A2:G8 will dynamically return the last 7 lines of source data in Sheet1 as source data is continuously populated there. Adapt to suit the number of lines desired: in ..COUNTA(Sheet1!$A:$A)-6 : the "6" is 7 lines -1 in ..MOD(ROWS($1:1)-1,7) : the 7 = 7 lines -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Tyler" wrote: I need to filter out only the last x number of days/hours of data from my work sheet. The worksheet posts new data every 5 minutes. I need to extract the newest data points. The time will vary from 7 days to 90 days. My existing data has a date/time combination. It looks like this,"12/19/2008 19:10" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select only last 30 days of a work sheet
Tyler wrote:
I need to filter out only the last x number of days/hours of data from my work sheet. The worksheet posts new data every 5 minutes. I need to extract the newest data points. The time will vary from 7 days to 90 days. My existing data has a date/time combination. It looks like this,"12/19/2008 19:10" Here's a possibility. I set up a time series in A:A, and random values in B:B. First determine the date of the last entry: D2=INT(MAX(A:A)) If you don't want to roll back to midnight, eliminate the INT() portion. Next figure out the value of the first entry that is, say, 30 days back: E2=D2-30 Next determine where the 30-day back record is located: F2=MATCH(E2,A:A,1) And determine where the last record is located: G2=COUNTA(A:A) Lastly, create a range based on what we know: H2=SUM(OFFSET($A$1,F2,1,G2-F2,1)) This will sum the values in the last 30-day range. If you are charting, you can just use the OFFSET piece as a data value source: =OFFSET($A$1,F2,1,G2-F2,1) The corresponding x- or category value source would be: =OFFSET($A$1,F2,0,G2-F2,1) Of course you could do all this without the extra helper columns, but I thought it might help to see the steps. Hope this helps! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work Days | Excel Discussion (Misc queries) | |||
extracting totals from 1 work sheet to another work work sheet | Excel Discussion (Misc queries) | |||
Print Macro both sheets at once with option to select days etc | Excel Worksheet Functions | |||
GETTING SELECTED DATA FROM A WORK SHEET TO OTHER WORK SHEET | Excel Discussion (Misc queries) | |||
Populating work sheet combox with another work sheet values | Excel Discussion (Misc queries) |