|
Poorman's Space Program
Processing Near Space Data
Part 2. Creating Charts in Excel
L. Paul Verhage
While spreadsheets are useful for manipulating large amounts
of data, the data are not nearly as meaningful until they are displayed in a
graph. A properly made graph illustrates
the relationship between two or more variables (two or more columns in an Excel
worksheet).
5.2.1 About Graphs
The type of graph most often used to display near space data
contains two perpendicular axes, generically called the X-axis and the
Y-axis. Drawn on the graph is a line
showing the relationship between the values in the two axes. In many cases, the resulting line is
equivalent to a mathematical relationship called a function.
Figure 5.14. Example
of a basic graph.
Some variables in a near space data set change because
another variable changed first. For
instance, the air temperature changes in response to the BalloonSat’s change in
altitude. The converse does not occur; i.e. a change in air temperature does not force the altitude of the BalloonSat
to change. This relationship between
variables, where one changes in response to changes in another, highlights the
major difference between variables. A
variable that changes in response to changes in another variable is called a dependent variable. The variable doing
the change and affecting the dependent variable is called an independent
variable. In the example at the
beginning of this paragraph, the altitude is the independent variable that
forces the air temperature, the dependent variable, to change.
A variable can be dependent or independent depending on its
context. In every case, the MET is the
independent variable because nothing in the BalloonSat flight can change the
rate at which MET changes (the BalloonSat doesn’t travel at relativistic
speeds). When the altitude of the
BalloonSat is compared to MET, the altitude becomes the dependent
variable. However, when the altitude is
compared to the air temperature, the altitude is the independent variable and
the air temperature is the dependent variable.
Within the dataset, each variable, both dependent and
independent, contains a minimum and maximum value, or its range of values. However, in reality, the ultimate range of
the variable may exceed the range of values recorded in the near space
dataset. The range of
independent variables in the dataset is called the domain and the range of
dependent variables in the dataset is called the range.
The bottom, horizontal axis of a near space graph represents
the domain of the dataset’s independent variable. The left, horizontal axis represents the
range of the dataset’s dependent variable. The extremes of both axes should not greatly exceed the domain and range
of the dataset’s variables. Each axis
needs its variable’s name listed next to it along with its units as well as a
few representative values. When
formatted this way, a reader sees the relationship between the variables in the
graph along with their units and the domain and range of the graph’s dataset.
Figure 5.15. Example of a well designed graph. Notice
that the axes are labeled with their variables, their units and a few
values to give the chart a scale. The
length of the axes in this graph does not greatly exceed the domain and range
of the dataset.
5.2.2 Making a Chart
in Excel
Excel calls a graph a chart, so that term is used when
describing how to convert data in two or more columns of an Excel worksheet
into a graph. Excel contains several
types of charts, and which chart to use depends on the variables being
plotted. When comparing two or more
variables that change more or less continuously, use an XY graph. For variables that are a snapshot of several
events, use a bar graph. For example,
use the XY graph for comparing altitude to air temperature, and use the bar
graph for comparing the sprouting rates of seeds before and after a near space
mission.
Excel has a bad habit
(in the author’s opinion) of assuming which columns to use. So here is a procedure that gives full
control over the creation of an Excel chart.
● Open Excel and load the
processed dataset
● Click the Chart Wizard icon
● Under Chart Type, select XY
(Scatter)
● Under Chart sub-type, select Scatter with data points connected by lines without markers
● Click NEXT button
● Click the Series tab
● Click the Remove button until there are no series
in the Series window
● Click the Add button
● Type a name in the Name window related to the dependent
variable’s name
(only if there’s more than one dependent variable being graphed)
● Left click the button in
the X Values window
● Move cursor to top cell of
independent column
● Click, hold, and drag to
end of column
● Press the Enter key
● Left click the button in
the Y Values window
● Move cursor to top cell of
independent column
● Click, hold, and drag to
end of column
● Press the Enter key
Repeat the add series step if another dependent variable is
being added.
● Click the Next button
● Click the Title tab
●
Type a name for the chart in Chart title window
●
Type name of independent variable and its units into Value (X) axis window
●
Type name of dependent variable and its units into Value (Y) axis window
● Click the Gridlines tab
● Check both Major gridlines option
● Click the Legend tab
● Only check Show Legend if more than one
dependent variable is graphed
● Click the Next button
● Select As new worksheet and enter a name for the worksheet
● Click the Finish button
5.2.3 Modifying Excel Charts
The default Excel chart settings leave something to be
desired. Therefore, this section shows
how to modify the chart into something more acceptable by first giving it a
white background and black lines.
● Right click within the plot
area (the gray box)
● Click Format Plot Area…
● In Border, click on down arrow in Color and select black
● In Area, click the white square
●
Click the OK button
Then change the default settings for the numbers along the
Y-axis.
● Right click on vertical
axis (Y axis or dependent variable)
● Click Format Axis…
● Click the Scale tab
● Change Maximum:, Minimum:, and Major unit: if necessary
● Enter the minimum Y value
displayed in Y axis in Value (X) axis
Crosses at:
● Click Font tab
● Change font type and size
as desired
● Click Number tab
● Under Category, click Number
● Change Decimal places: to proper precision
● Click the OK button
Now change the X-axis number settings to match those on the
Y-axis.
● Right click on vertical
axis (X axis or independent variable)
● Click Format Axis…
● Click the Scale tab
● Change Maximum:, Minimum:, and Major unit: if necessary
● Enter the minimum X value
displayed in Y axis in Value (Y) axis
Crosses at:
● Click Font tab
● Change font type and size
same settings as Y axis
● Click Number tab
● Under Category, click Number
● Change Decimal places: to proper precision
● Click the OK button
Next, change the font settings for the chart. The first changed are those in the chart’s
title.
● Right click on chart title
● Click Format Chart Title…
● Click Font tab
● Change font type and size
same settings as Y axis
● Click the OK button
Change the fonts in the Y-axis to match the title
● Right click on Y axis title
● Click Format Axis Title…
● Click Font tab
● Change font type and size
same settings as Y axis
● Click the OK button
Change the X-axis fonts to match the Y-axis fonts
● Right click on X axis title
● Click Format Axis Title…
● Click Font tab
● Change font type and size
same settings as Y axis
● Click the OK button
Update the thickness, pattern, and color of the graph line
● Right click on one graph
line in the chart
● Click Format Data Series:
● Click Patterns tab
● Change Color:, Style:, and Weight: as desired
● Click the OK button
Repeat the changes for other graph lines in the chart
If more than one dependent variable is to be plotted, then
follow the next procedure to update the legend in the chart.
● Right click on the chart’s
legend
● Click Format Legend…
● Click Font tab
● Change font type and size
same settings as Y axis
● Click the OK button
● Save the spreadsheet
5.2.4 Copying Charts
The image of a chart can be copied into MS Paint and saved
as a graphics file. Then the image of
the chart can be copied into other software packages that accept graphics. In addition, a chart can be pasted directly
into a MS Word document. Pasting the
image of a chart from Excel directly into MS Word generates a more detailed
image of the chart within the document.
5.3 Processing HOBO and Thermochron Data
The following procedures explain how to generate Excel charts for Thermochrons and Hobos. The software that comes with these products
can generate their own graphs, but they cannot combine outside data to generate
other graphs.
5.3.1 Thermochron Data
After downloading data from a Thermochron, the two options
are either generating a graph or exporting the results. The BalloonSat Principia recommends clicking
the Export Result button. After exporting the results, give the
Thermochron data a meaningful name so it is easy to locate and identify in the
future.
Both Notepad and WordPad are text editors capable of opening
and editing the Thermochron data file. However, because of Notepad’s limited file size, Windows will probably
switch to WordPad when you open the file. In any case, use either one of these programs to open the Thermochron’s
exported file. If neither of the icons
of Notepad and WordPad are displayed in Windows, then
click the Start button at the bottom
of the screen, click Run, type
either Notepad or WordPad into the window, and then click
the OK button. Open the Thermochron file like you would a
Word document.
Editing a Thermochron
File
A Thermochron file contains a lot
of unneeded information. Therefore,
begin processing the Thermochron file by deleting text from the beginning of
the file until the words, Log Data appear. At the end of the Log Data is essentially a memory dump of the
Thermochron. Since this data is not
required, delete it also. After deleting
this information, the text file will only contain text as shown in Fig. 5.16.
Format:
[Time/Date , Temperature] (Fahrenheit)
08/03/2003 05:54 , 73.4°F
08/03/2003 05:55 , 73.4°F
08/03/2003 05:56 , 73.4°F
08/03/2003 05:57 , 73.4°F
Figure 5.16. Example
of Thermochron data.
Manually remove the following text
from the first line:
Format:[/Date] (Fahrenheit)
Delete the dates from the file
since they are not needed.
● Click Edit
● Click Replace
● Type the Thermochron’s date
into the Find what: window
● Leave the Replace with: window blank
● Click the Replace All button
Change the colon in the file to a
comma.
● Click Edit
● Click Replace
● Type a colon (:) into the Find what: window
● Type a comma (,) into the Replace with: window
● Click the Replace All button
Remove the extra space around the
comma.
● Click Edit
● Click Replace
● Type a space, comma, space
( , ) into the Find what: window
● Type a comma (,) into the Replace with: window
● Click the Replace All button
Remove the F.
● Click Edit
● Click Replace
● Type a F into the Find what: window
● Leave the Replace with: window blank
● Click the Replace All button
Remove the degree symbol (°)
manually.
● Save the file
5.3.2 Hobo Data with
Boxcar
After downloading data from a Hobo in Boxcar, you have the
option of either generating a graph or exporting the results. . The procedure below will export the
results.
● Click File
● Click Export
● Click Date Format: window
● Select a time format of, Hr:Min:Sec
● Click the Data Separator: window
● Select Comma
●
Click the Export… button.
● Give the file a meaningful
name so it can be identified later
Editing a Hobo File
Created by Boxcar
Start a text editor like Notepad or WordPad and open the
exported Hobo file. Figure 5.17 is an
example of the data in a four external channel Hobo file.
Time,Voltage (V) (*1),Voltage (V) (*2),Voltage (V)
(*3),Voltage (V) (*4)
19:25:00,0.874,0.659,0.366,0.288
19:25:02,0.132,0.122,0.103,0.103
19:25:04,0.073,0.073,0.073,0.083
Figure 5-17. Example
of a Hobo file exported by Boxcar.
Change colons in the file into
commas.
● Click Edit
● Click Replace
● Type a colon (:) into the Find what: window
● Type a comma (,) into the Replace with: window
● Click the Replace All button
● Type Time, into the Find what: window
● Type Time,,, into the Replace with: window
● Click the Replace All button
● Save the file
5.3.3 Hobo Data with HOBOWare Lite
After downloading data from a Hobo with HOBOware Lite, you have the option of either generating a
graph or exporting the results. The
following procedure will export the results.
● Click File
● Click Export Points as Excel Text…
● In Export Options, click Export to a single file
● Click Export button
● Type a meaningful name in
the File name: window
● Click Save button
Start a text editor like Notepad or
WordPad and open the exported Hobo file (saved as a CSV file). Figure 5-18 is an example of the data in an
accelerometer file.
"Plot Title: UNO Flight "
#","Time, GMT-06:00","X Accel, g","Y Accel, g","Z Accel,
g","CouplerAttached","Host Connected","Stopped","End Of File"
1,10/28/07 6:00:00 AM,0.950,0.025,-0.075,,,,
2,10/28/07 6:00:10 AM,0.975,0.025,-0.075,,,,
3,10/28/07 6:00:20 AM,0.950,0.025,-0.075,,,,
4,10/28/07 6:00:30 AM,0.950,0.025,-0.100,,,,
Figure 5.18. Another example of a Hobo
file, but from an accelerometer exported by HOBOWare Lite.
Editing Hobo Files
Created in HoboWare
Delete the date from the file.
● Click Edit and Replace
● Enter the date into the Find what: window
● Leave the Replace with: window blank
● Click the Replace All button
Remove the AM or PM from the file.
● Click Edit and Replace
● Enter AM or PM into the Find what: window
● Leave the Replace with: window blank
● Click the Replace All button
Change the colon in the file to a
comma.
● Click Edit and Replace
● Type a colon (:) into the Find what: window
● Type a comma (,) into the Replace with: window
● Click the Replace All button
● Save the file
5.4 Creating Sensor Equations in Excel
Spreadsheets like Excel are capable of generating equations
that describe the behavior of sensors. The example below illustrates this process for a temperature sensor
built according to section 3.1.7. The
same steps can generate an equation for any sensor with known resistances at
various sensor values. Once the sensor’s
equation has been generated, it can be converted into a formula and added to a
spreadsheet.
5.4.1 Creating the Spreadsheet
● Start Excel
● Create a new worksheet
The first column consists of the independent variable of
temperature in degrees Celsius.
● Type Temperature in the top cell of the first column
● Type *C in the second cell of the first column
● Type the temperatures from
the thermistor package into the remaining cells
The second column consists of the
dependent variable of thermistor resistance.
● Type Resistance in the top cell of the second column
● Type k-ohms in the second cell of the second column
● Type the resistances from
the thermistor package into the remaining cells
● Type Temperature in the top cell of the third column
● Type *F in the second cell of the third column
● Enter the following
equation in the third cell:
= ((+A3+40)*1.8)-40
● Copy and paste the equation
in the rest of the column
The fourth column consists of the
dependent variable of sensor voltage.
● Type Voltage in the top cell of the fourth column
● Type volts in the second cell of the fourth column
● Enter the following
equation in the third cell:
= 2.5*(B3/(B3+51.1))
Note: The value
51.1 is the resistance of the fixed resistor in a practice temperature sensor (in
units of kilo-ohms). Change this value
to match the resistance (in kilo-ohms) of the fixed resistor in your
temperature sensor.
● Copy and paste the equation
in the rest of the column
Note: If there
are no errors, the voltage in fourth column will be between 0.0 and 2.5
volts.
Figure 5.19. Example of the
sensor spreadsheet.
5.4.2 Creating the Sensor Chart
● Click the Chart Wizard icon
● Under Chart Type, select XY
(Scatter)
● Under Chart sub-type, select Scatter
with data points connected by lines without markers
● Click the Next button
● Click the Series tab
● Click the Remove button until every series are
removed from the Series window
● Click the Add button
● Under X Values, select the independent variable Voltage column
● In Y Values, select the dependent variable Temperature (F) or (C)
column
● Click the Next button
● Click the Finish button
5.4.3 Creating Equations from the Sensor Chart
● Right click the line in the
new chart
● Select Add Trendline…
● Click the Type tab
● Click the Polynomial icon
● Enter 3 for the order
● Click the Options tab
● Click the Display equation on chart button
● Click the OK button
Figure 5.20. Example
of the generated chart, its trendline, and equation.
Note: A proper trendline closely matches the original data series as
illustrated in the figure above.
● Write down the equation
Note: The equation should look something like:
y = -72.035x3 + 364.15x2 - 677.4x + 431.79
The above equation is equivalent to the following Excel
formula when the E column is the temperature sensor voltage (or X in the
equation).
=
-72.035*E3^3+364.15*E3^2-677.4*E3+431.79
A BalloonSat Update
In Part 3, L. Paul Verhage will describe how to analyze position reports transmitted to the surface by a balloonsat GPS system. Paul's series on near space experiments using BalloonSats is among the most important collection of amateur science articles to appear in The Citizen Scientist . Paul will eventually publish the series as a book. Meanwhile, be sure to stay tuned to Paul's latest installments in the series. If you have missed any of his articles, you can find them in the list below. For an excellent presentation on the importance of BalloonSats, be sure to check out Paul's BalloonSat Principia . Editor.
Poorman's Space Program (07 September 2007).
Federal Regulations Regarding Near Space Flights (05 October 2007).
What and Where is Near Space? (02 November 2007).
What you can Expect at your BalloonSat Launch (07 December 2007).
The Thermal Test Chamber (TTC) for Near Space Instruments (01 February 2008).
Designing and Constructing BalloonSat Airframes (Part 1) (04 April 2008).
Designing and Constructing BalloonSat Airframes (Part 2) (02 May 2008)
Designing Near Space Experiments (Part 1) (04 July 2008)
Designing Near Space Experiments (Part 2) (01 August 2008)
Designing Near Space Experiments (Part 3) (05 September 2008)
Designing Near Space Experiments (Part 4) (03 October 2008)
Testing BalloonSats (Part 1) (07 November 2008)
Testing BalloonSats (Part 2) (05 December 2008)
Processing Near Space Data. Part 1. Creating Workbooks Using the Excel Spreadsheet Program (02 January 2009)
Processing Near Space Data. Part 2. Creating Charts in Excel (06 February 2009)
|
