Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 30th 07, 04:29 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 1
Default 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   Report Post  
Old October 30th 07, 06:38 AM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old October 30th 07, 03:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Manipulating subsets of large datasets Astrofin Excel Worksheet Functions 4 March 7th 07 05:11 PM
VLOOKUP in large Data sets of more than 16384 rows Bluewolf Excel Worksheet Functions 11 April 3rd 06 09:39 PM
Question about graphing four sets of data RawlinsCross Charts and Charting in Excel 2 January 5th 06 12:36 AM
Manipulating large worksheets and hardware capabilities beata Excel Discussion (Misc queries) 1 October 21st 05 05:15 PM
how to save large data sets in excel sheck Excel Discussion (Misc queries) 1 August 2nd 05 07:02 AM


All times are GMT +1. The time now is 03:02 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017