Poorman's Space Program
 

Processing Near Space Data

Part 1. Creating Workbooks Using the Excel Spreadsheet Program

L. Paul Verhage

The BalloonSat has been recovered and its data downloaded. Now what? Near space data is useless until it is processed to tell the story of the mission. Near space data does this with the help of a spreadsheet. The spreadsheet's graphs are interesting, and, by correlating the data set to the near spacecraft's APRS reports, the graphs become even more interesting. However, before getting to this point, we need to take a look at Excel, a widely used spreadsheet program.

5.1 Workbooks in Excel

The author of the BalloonSat Principia works in Excel, therefore, this chapter explains near space data processing using the Microsoft's Excel spreadsheet. However, the explanations will apply to most other spreadsheet programs, including Calc in OpenOffice. (OpenOffice is much improved over early versions and is freely available here. Editor.)

5.1.1 Layout of a Workbook

A file in Excel is a called a workbook and each page in the workbook is called a worksheet. Each worksheet consists of cells arranged in vertical columns and horizontal rows. A column of cells is named after a letter of the alphabet. A row of cells is named after a number. The rows and columns are named sequentially. An Excel worksheet can contain a maximum of 65,536 rows and 256 columns. Column addresses greater than Z are named with two letters of the alphabet, for example, AA (the first double letter column) and HV (the last double letter column). A particular cell has an address consisting of its row and column name.

Figure 5.1. One corner of a workbook sheet. Cell A2 contains the value 5.3.

5.1.2 Importing Comma Delimited Files

Most data logger programs can save data in a single file that this book calls a data set. A data set consists of readable characters broken into lines of text called records and each record is divided into fields separated by commas. Each record contains the same number and types of fields. For example, in Fig. 5.2, the data set contains eight records and each record contains three fields. The first record has a time field containing "10:15", an altitude field containing "245", and a temperature field containing "77".

Figure 5.2. An example of data in a comma delimited file. Each record in this file contains three pieces of data: the time, altitude, and temperature.

Not described in data set example of Fig. 5.2 are the units of each field. Therefore, it is not known if the time field represents AM or PM, if the altitude field represents meters or feet, or if the temperature is in Fahrenheit or Celsius. Fortunately, most data sets contain a record with the units of each field. However, if the data set does not contain this record then it is up to the BalloonSat team to assign the units once the data set is loaded into a spreadsheet.

Excel opens comma delimited files in a simple process called importing. When performed properly, the data set's records become rows, and its fields form columns with each field occupying its own cell. When a comma delimited file is not imported properly, all the fields appear within a single spreadsheet cell making any data processing all but impossible. The procedure to import comma delimited data sets into Excel is described below.

? Click File.

? Click Open.

? In the Files of Type window, select All Files.

? Browse to find the file.

? Double click the file.

? Select Delimited.

? Click the Next> button.

? Select Comma (in the Data Preview window, fields will appear in their own

columns).

? Click the Finish button.

The data set now appears in the spreadsheet with each record occupying a row and every cell in a column filled with a field of the same type of data. Before doing anything else, save the spreadsheet as an Excel workbook, and not as comma delimited file. This preserves the original file should your Excel file somehow become corrupted.

Figure 5.3. The comma delimited data set before and after importing into a spreadsheet.

Now that the data set is loaded into an Excel workbook, it is necessary to create headings for the various fields. Adding the heading now reduces the chances of forgetting the purpose of a field. Not only must the fields have names, but they also need labels with their proper units. Figures 5-4 to 5-8 explain this process.

Figure 5.4. The newly imported spreadsheet.

Figure 5.5. Click the number 1 in the row names and notice that the entire row is highlighted.

Figure 5.6. While over the number 1, right click the mouse to bring up the menu and then click INSERT.

Figure 5.7. Clicking on INSERT moves the rows of the spreadsheet down by one to create room for the new row.

Figure 5.8. Insert a second row by repeating the process. Then write the field description in the first row and the appropriate units into the second row.

An improperly placed row is removed by right clicking the number of the row. Then click Delete, and the row will disappear. New columns are added and removed just like a row, except the column letter is clicked to highlight it first.

After importing the data set into a worksheet, give the worksheet a meaningful name by right clicking the tab at the very bottom of the worksheet. In the pop-up menu click Rename and then edit the highlighted name in the tab. Press the Enter button to finish editing the name.

There is no reason that more than one data set cannot be imported into an Excel spreadsheet; in fact, it is the preferred way for saving all the data sets created during a single near space mission. Follow the procedure below to insert a new worksheet for each data set

? Right click the worksheet tab at the bottom of the page.

? Click Insert… in the menu.

? Click Worksheet and the OK button.

? Right click the new worksheet tab and rename it.

? Open a previously saved workbook.

? Highlight an entire worksheet in the workbook.

? Right click and select Cut.

? Click cell A1 in the new worksheet.

? Right click and select Paste.

? Repeat for every new worksheet.

? Save the new workbook.

? Do not save the old workbook. This keeps it unchanged.

5.1.4 Writing Formulas in Excel

An equation, called a formula in Excel, is written into a cell where it mathematically manipulates the numbers in one of more cells and then writes the results into the cell containing the formula. The most frequent formulas begin with an equal sign (=) followed by a + (plus) or – (minus) to indicate whether the cell will have a positive or negative value of the formula's result. The rest of the formula consists of numbers, cell references, and mathematical operations. Figure 5.9 is an example of a spreadsheet formula that doubles a number.

5.9 GOES HERE.]

Figure 5.9. An Excel formula entered into cell B1.

In Fig. 5.9, the formula in cell B1 takes the positive value in cell A1 (which is 10), multiplies it by 2 (to get 20), and then places the result in cell B1.

Once written into a single cell, a formula is often copied and pasted into additional cells. Why? Because formulas are capable of converting data in one column into new data in a different column. An example is when a column of air temperatures in units of degrees Fahrenheit is converted into a new column of air temperatures in units of degrees Celsius or when two columns containing MET and altitude are converted into a column of the BalloonSat's ascent rate. The original column(s) does not get written over or disappear, but its data creates the values in the new column.

Spreadsheets are smart enough to increment the cell reference of a formula when the formula is copied into new cells. Figure 5.10 shows an example of the automatic incrementing that takes place when a formula is copied from cell B1 to cell B2. Notice that the cell address A1 became A2 when the formula was copied into cell B2. The address A1 is called relative references since it changed when the formula was copied into a new cell.

Figure 5.10. Example of an Excel formula (cell B1) copied into a new cell (B2).

After pasting the formula into every cell in the column B, the spreadsheet appears like Fig. 5.11.

Figure 5.11. The results of formulas copied into column B.

There are times when a formula must reference a value that only appears within a single cell. For example, converting an altitude in units of meters into altitude in units of feet requires that the meters be multiplied by the conversion factor 3.28. Instead of writing 3.28 into the formula however, the value 3.28 is written into a cell and that cell address becomes part of the formula that is copied into very cell of the column. If a more precise conversion factor is desired at some future time, it only needs to be written into a single cell to update the entire spreadsheet.

However, if the formula in this example is copied into new cells, the conversion factor's relative cell address changes every time it is copied into a new cell and will no longer points to the conversion factor. To prevent a cell address from changing when a formula is copied into a new cell, the letter and number of the cell address must be preceded with a dollar sign ($). A cell address proceeded with a $ is called an absolute reference and will not change when it is copied into a new cell. Notice how the absolute reference $C$1 is used in the spreadsheet in Fig. 5.12.

Figure 5.12. An example of the use of an absolute reference in a spreadsheet.

Since formulas are typically written into new columns, the new column must include its name and units in the top cells and the formula in the rest of the column. When properly edited, Fig. 5.12 becomes Fig. 5.13.

Figure 5.13. An example of a spreadsheet using absolute and relative references along with heading information.

5.1.5 Formatting Columns for Precision

Significant digits of accuracy refer to the number of digits after a number's decimal point. For example, 2.3 has one significant digit, 2.34 as two significant digits, and 2.345 has three. The result of a computation can never have more significant digits of precision than the number with the lowest number of significant digits of precision going into the calculation. Writing an answer with more significant digits (or more precision) than the least precise number used in the computation implies a greater precision for the answer than is justified by the calculation's inputs. Therefore, while a calculator says 2.3 X 4.55 = 10.465, the answer is written as 10.5 since the number with the lowest precision (2.3) only has one digit of precision.

Excel formulas perform computations with 15 significant digits of precision. This far exceeds the number of significant digits that any near space data that will ever have. To correct this level of “pseudo-precision,” format the columns containing formulas to reflect their proper level of precision with the following procedure.

? Click the letter of the column (this highlights the entire column).

? Right click the highlight column to bring up its menu.

? Click Format Cells.

? In the Category window, click Number.

? Select the proper number in the Decimal places window.

? Click the OK button

5.1.6 Useful Functions and Constants in Excel

Excel has several functions and constants that are useful for processing near space data. Some of them are described below along with their syntax.

The Tangent function calculates angles (like the elevation of a BalloonSat) given a rise and run (range and altitude of the balloon). If the rise (altitude) is in column B, the run (range) is in column C, and the units of both columns are the same, then the angle (elevation) is calculated as follows:

The result of the tangent function is an angle in units of radians. Since there are pi radians in an angle of 180 degrees, multiplying the calculated angle by 180 degrees and dividing by pi radians converts the angle into units of degrees.

Summation adds all the values in a column. In this example, the formula written below adds together all the numbers from cell C3 to C10.

     = SUM(C3..C10)

There are at least two benefits of using the summation function. First, a single formula calculates the sum of any number of values. Second, the sum can be updated by editing a single cell address in the formula.

The Average function sums the values in a column and then divides the result by the number of cells in the column. Like the summation function, the average function is a simple function that works for any number of cells in the column. In Excel, the format for the average function is illustrated below.

     = AVERAGE(C3..C20)

The first useful constant is pi (π) or 3.14159…. In Excel, the constant pi is written as PI(). To convert the angle in cell D3 from radians into degrees, multiply the angle by 180 and divide by pi as illustrated below.

     = D3*180/PI()

Combining the tangent function with a conversion from radians to degrees yields the following formula.

     = TAN(B3/C3)*180/PI()

The second useful mathematical constant is e , or 2.7182….. In Excel, e is written as EXP(). To raise e to a power, write the number inside its parentheses. So raising e to the power of 2 (two) becomes,

     = EXP(2)

However, it is more likely e will be raised to a power written in a cell or to a power resulting from the mathematical manipulation of two cells. The formula for e in these two examples is illustrated below.

A BalloonSat Update

In Part 2, L. Paul Verhage will cover how to create charts using Excel. 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)