Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
manipulating large data sets for graphing
I use a data logging program that records data points in an access
database that I export to excel for graphing. A data log may have many thousands of readings. I need a quick way to put the data into a form that I can graph with Excel. The data is in three columns. The first column contains a repeating list of the sensor names, which always appear in the same order (e.g.: [sensor 1, sensor 2, sensor 3...] {sensor 1, sensor 2, sensor 3...] ). The second column has the time of the sensor reading, with one time per group of sensor readings. And the third column has the temperature associated with the read time and the sensor name. I want to graph the data with time on the x axis, temperature on the y axis, and a line (or series of data points) for each sensor. Based on my understanding of how data must appear in Excel in order to graph it, I have struggled using if statements and data sorting to put the data into a form where there is a column for each sensor with temperature readings in the column cells, and a time column with a line for each time reading. Is there an easier way? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
manipulating large data sets for graphing
Assume source data in Sheet1's cols A to C (Sensor - Time - Temp),
data running in row2 down (data presumed to be fully populated for each col) In Sheet2, List the sensors in B1 across, viz: sensor 1, sensor 2, sensor 3, .. List the times in chrono order in A2 down, eg: 12:00 pm, 1:00 pm, 2:00 pm etc (The above listings must match with the data in Sheet1's cols A and B) Put in B2, array-enter the formula by pressing CTRL+SHIFT+ENTER: =INDEX(Sheet1!$C$2:$C$100,MATCH(1,(Sheet1!$A$2:$A$ 100=B$1)*(Sheet1!$B$2:$B$100=$A2),0)) Copy across and fill down to populate the table. Adapt the ranges to suit. Now you can easily plot a line graph on the table, using as data range, eg: =Sheet2!$A$1:$F$50 with the series in "columns". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "steveb" wrote in message ups.com... I use a data logging program that records data points in an access database that I export to excel for graphing. A data log may have many thousands of readings. I need a quick way to put the data into a form that I can graph with Excel. The data is in three columns. The first column contains a repeating list of the sensor names, which always appear in the same order (e.g.: [sensor 1, sensor 2, sensor 3...] {sensor 1, sensor 2, sensor 3...] ). The second column has the time of the sensor reading, with one time per group of sensor readings. And the third column has the temperature associated with the read time and the sensor name. I want to graph the data with time on the x axis, temperature on the y axis, and a line (or series of data points) for each sensor. Based on my understanding of how data must appear in Excel in order to graph it, I have struggled using if statements and data sorting to put the data into a form where there is a column for each sensor with temperature readings in the column cells, and a time column with a line for each time reading. Is there an easier way? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
manipulating large data sets for graphing
Make a pivot table from the imported data (or link the pivot table to the
Access database). Time can go into the Rows area of the pivot table, Sensor into the Columns area, and the readings into the Data area. Copy the pivot table, paste it special as values, and create your chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "steveb" wrote in message ups.com... I use a data logging program that records data points in an access database that I export to excel for graphing. A data log may have many thousands of readings. I need a quick way to put the data into a form that I can graph with Excel. The data is in three columns. The first column contains a repeating list of the sensor names, which always appear in the same order (e.g.: [sensor 1, sensor 2, sensor 3...] {sensor 1, sensor 2, sensor 3...] ). The second column has the time of the sensor reading, with one time per group of sensor readings. And the third column has the temperature associated with the read time and the sensor name. I want to graph the data with time on the x axis, temperature on the y axis, and a line (or series of data points) for each sensor. Based on my understanding of how data must appear in Excel in order to graph it, I have struggled using if statements and data sorting to put the data into a form where there is a column for each sensor with temperature readings in the column cells, and a time column with a line for each time reading. Is there an easier way? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manipulating subsets of large datasets | Excel Worksheet Functions | |||
VLOOKUP in large Data sets of more than 16384 rows | Excel Worksheet Functions | |||
Question about graphing four sets of data | Charts and Charting in Excel | |||
Manipulating large worksheets and hardware capabilities | Excel Discussion (Misc queries) | |||
how to save large data sets in excel | Excel Discussion (Misc queries) |