Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data from a Table
Here's the situation.
I have a table of information (temperature readings) taken every minutefor 7 days, but I don't need the information from every minute, just every 30 minutes. How do I go about setting up a table which will only read and display the values for only the 30 minute intervals? Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data from a Table
Assuming your data is in Column A
In B1 enter =INDIRECT("A"&ROW()*30) Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Tue, 27 Nov 2007 12:18:01 -0800, Iceman wrote: Here's the situation. I have a table of information (temperature readings) taken every minutefor 7 days, but I don't need the information from every minute, just every 30 minutes. How do I go about setting up a table which will only read and display the values for only the 30 minute intervals? Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data from a Table
Thanks Gord, it does work. However, one problem. it seems to want to start at
row A90 instead of A3 (the start of the data in the column). Any idea how to fix this. Thanks again in advance. "Gord Dibben" wrote: Assuming your data is in Column A In B1 enter =INDIRECT("A"&ROW()*30) Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Tue, 27 Nov 2007 12:18:01 -0800, Iceman wrote: Here's the situation. I have a table of information (temperature readings) taken every minutefor 7 days, but I don't need the information from every minute, just every 30 minutes. How do I go about setting up a table which will only read and display the values for only the 30 minute intervals? Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data from a Table
Try this
In B1 enter =OFFSET($A$3,ROW()*30-10*3,) Gord On Wed, 28 Nov 2007 08:45:03 -0800, Iceman wrote: Thanks Gord, it does work. However, one problem. it seems to want to start at row A90 instead of A3 (the start of the data in the column). Any idea how to fix this. Thanks again in advance. "Gord Dibben" wrote: Assuming your data is in Column A In B1 enter =INDIRECT("A"&ROW()*30) Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Tue, 27 Nov 2007 12:18:01 -0800, Iceman wrote: Here's the situation. I have a table of information (temperature readings) taken every minutefor 7 days, but I don't need the information from every minute, just every 30 minutes. How do I go about setting up a table which will only read and display the values for only the 30 minute intervals? Thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data from a Table
Hi Gord, I tried it but it only rolled back to row A63. SO I modified it to
=OFFSET($A$3,ROW()*30-90,) and it worked perfectly. Don't ask me how .... just got lucky. Any way, thanks for the prompt help. I appreciate it. "Gord Dibben" wrote: Try this In B1 enter =OFFSET($A$3,ROW()*30-10*3,) Gord On Wed, 28 Nov 2007 08:45:03 -0800, Iceman wrote: Thanks Gord, it does work. However, one problem. it seems to want to start at row A90 instead of A3 (the start of the data in the column). Any idea how to fix this. Thanks again in advance. "Gord Dibben" wrote: Assuming your data is in Column A In B1 enter =INDIRECT("A"&ROW()*30) Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Tue, 27 Nov 2007 12:18:01 -0800, Iceman wrote: Here's the situation. I have a table of information (temperature readings) taken every minutefor 7 days, but I don't need the information from every minute, just every 30 minutes. How do I go about setting up a table which will only read and display the values for only the 30 minute intervals? Thanks in advance! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data from a Table
Where are you entering the formula? B1 or ?
I tested =OFFSET($A$3,ROW()*30-10*3,) in B1 with 1 to 900 starting in A3 and returned B1 1 B2 31 B3 61 B4 91 B5 121 etc. Your fomula =OFFSET($A$3,ROW()*30-90,) starting in B1 returns B1 #REF! B2 #REF! B3 1 B4 31 B5 61 "Perfectly" is a relative term it seems<g Gord On Wed, 28 Nov 2007 10:39:02 -0800, Iceman wrote: Hi Gord, I tried it but it only rolled back to row A63. SO I modified it to =OFFSET($A$3,ROW()*30-90,) and it worked perfectly. Don't ask me how .... just got lucky. Any way, thanks for the prompt help. I appreciate it. "Gord Dibben" wrote: Try this In B1 enter =OFFSET($A$3,ROW()*30-10*3,) Gord On Wed, 28 Nov 2007 08:45:03 -0800, Iceman wrote: Thanks Gord, it does work. However, one problem. it seems to want to start at row A90 instead of A3 (the start of the data in the column). Any idea how to fix this. Thanks again in advance. "Gord Dibben" wrote: Assuming your data is in Column A In B1 enter =INDIRECT("A"&ROW()*30) Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Tue, 27 Nov 2007 12:18:01 -0800, Iceman wrote: Here's the situation. I have a table of information (temperature readings) taken every minutefor 7 days, but I don't need the information from every minute, just every 30 minutes. How do I go about setting up a table which will only read and display the values for only the 30 minute intervals? Thanks in advance! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data from a Table
I should have been a little more detailed.
My data is contained in columns A - D; row 3 - 9239. I have placed the formula in column H - K. "Gord Dibben" wrote: Where are you entering the formula? B1 or ? I tested =OFFSET($A$3,ROW()*30-10*3,) in B1 with 1 to 900 starting in A3 and returned B1 1 B2 31 B3 61 B4 91 B5 121 etc. Your fomula =OFFSET($A$3,ROW()*30-90,) starting in B1 returns B1 #REF! B2 #REF! B3 1 B4 31 B5 61 "Perfectly" is a relative term it seems<g Gord On Wed, 28 Nov 2007 10:39:02 -0800, Iceman wrote: Hi Gord, I tried it but it only rolled back to row A63. SO I modified it to =OFFSET($A$3,ROW()*30-90,) and it worked perfectly. Don't ask me how .... just got lucky. Any way, thanks for the prompt help. I appreciate it. "Gord Dibben" wrote: Try this In B1 enter =OFFSET($A$3,ROW()*30-10*3,) Gord On Wed, 28 Nov 2007 08:45:03 -0800, Iceman wrote: Thanks Gord, it does work. However, one problem. it seems to want to start at row A90 instead of A3 (the start of the data in the column). Any idea how to fix this. Thanks again in advance. "Gord Dibben" wrote: Assuming your data is in Column A In B1 enter =INDIRECT("A"&ROW()*30) Drag/copy down as far as you wish. Gord Dibben MS Excel MVP On Tue, 27 Nov 2007 12:18:01 -0800, Iceman wrote: Here's the situation. I have a table of information (temperature readings) taken every minutefor 7 days, but I don't need the information from every minute, just every 30 minutes. How do I go about setting up a table which will only read and display the values for only the 30 minute intervals? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Data from a table using a keyword | Excel Discussion (Misc queries) | |||
Extracting Data From A Table | Excel Discussion (Misc queries) | |||
Extracting saved data from pivot table | Excel Discussion (Misc queries) | |||
Pivot Table - Extracting specific data | Excel Worksheet Functions | |||
Pivot Table - Extracting specific data | Excel Worksheet Functions |