Excel exercises (Office 2007 version) pg. 1 of 8
Exercise 1-Importing Data
1. Open Microsoft Excel.
2. Click on the tab “Data,” “Get External Data,” “From Text”
3. Locate the file CO2.txt file and download it to your computer (if you have your own text file from your analyzer or datalogger that could be used as well, but the columns for the data manipulation will be different.)
4. Follow the steps in the dialog box:
q Step 1:
- Select “delimited” since tabs separate the values (you could also use fixed width in this case but usually commas separate the fields in a text file), and select “tabs” as the delimiter.
- Use Line 2 as the line to start importing the data—it does not matter particularly where you import it for the purposes of this exercises, but you generally only want to import the data and headers and file ID fields rather than the miscellaneous info at the top of the file. (Make sure that you do not lose this information, however, if this is real data! This is where you would use your database logbook, and make an entry describing what you are doing, the number of rows you are importing, beginning and end time/date, instrument, filenames, etc.)
q Step 2:
- Preview the data in the preview pane to ensure that it looks as you want it to. If not, change the delimiter type until the data is displayed correctly. Note that you may have to pull the right scroll bar down in the dialog box to see the data after all the header information.
q Step 3:
- Change the Date field to the “Date” data type. Leave the other fields as “General.” This can be an important step—be sure to check the type of date field, as there are several options (month-date-year is MDY, etc.) In this case the date field is in fractions of an hour with zero as the beginning of the data-gathering, and it can be reformatted to make more sense later.
q Finalize the Import Wizard by telling Excel where to import the data (use cell A2 for this example)
5. Next, insert a column for the “average” concentration value. Starting in the first row where the data begins (the cell below the header row, and this example assumes this is row 7), enter the equation: =Average(B7:C7) (you can do this by typing “=average” into the cell and then selecting the two values with your mouse or typing the cell addresses of the cells that contain the data you wish to average; depending on your spreadsheet the cell addresses may differ).
6. Insert another column to the right titled “Difference” and below that enter the equation =B7-C7 into the cell
7. The next column should be labeled “Abs Value” and in the first cell enter the equation =Abs(E7) to calc the absolute value of the difference.
8. The last column is labeled “RPD” or “Relative Percent Difference.” In the first cell, enter the equation =(F7/D7) where F7 is the Abs Value and D7 is the Average.
9. To copy the equations down each entire column:
q FIRST select the four cells with the equations (D7, E7, F7 and G7)
q Click on the lower right-hand corner of the selected range that you want to copy (called the “grabber bar”). When your cursor turns into a “skinny” plus sign, click and drag the cursor to the last row in the dataset and this will copy the equations down. HINT: If you have lots and lots of rows of data, you can do the task automatically by double-clicking the grabber bar on the selected cells and the equations will copy all the way until there is no more data in the column to the left of the selected cells.
10. Now you may want to format your columns of data so that they have fewer decimal points. Select the entire column you want to format and then click on “Home,” then the tab “Format,” “Cells,” then select Format Cells: Then format the cells however makes the data most meaningful. For example, the first column in terms of fractions of an hour, and we know from reading the user’s manual for the CO2 detector that this data was gathered with that the concentration was logged every four minutes. Selecting cell A8, then Format Cells, Time, changes the format of the first column to allow us to make more sense of it, so that the first value was at time “zero” and the second value was logged at time “0:04:00,” meaning 0 hours, four minutes, and 0 seconds. To extend this format to the rest of the column, you can use the format painter icon. Click on the cell whose format you wish to copy (cell A8 in this case), then rapidly double-click the paintbrush so that it stays dark (depressed) and the cell A7 is surrounded by blinking “las vegas lights” then click on cell A7, hold down the control key, click and hold the shift key, and click the down arrow to copy the format down the column all the way to the last unfilled cell (cntrl-shift-down). Now you can see the time column in units of minutes elapsed since the start of the data-gathering. Click on the format paintbrush again to un-depress it, or whatever you click on next will copy that same format. Use the format cells button to change the format of the other columns. For example, the values for ppm of CO2 do not really make sense with six values after the decimal, because the instrument cannot really discriminate such low levels. In this example, change the format to Number and four after the decimal. (One after the decimal is fine if results are in ppb, for example, but if in ppm then 4 would be more appropriate).
then select Number, then four places after the decimal to look like: and do the same with the other columns, so that for the calculated values in columns D, E, F and G there is only one value shown after the decimal:
11. Note that THIS FORMATTING DOES NOT SUPPLY SUFFICIENT INFORMATION! See the column G (relative percent difference). Excel rounded all the calculations to one significant figure, so that any value greater than 0.05 (5%) is rounded to 0.1. So, all of the relative percent difference values are 0.1. If we are concerned with keeping the RPD less than 10%, that does not work since they all read 0.1 (10%) (don’t worry—you have not lost the data—Excel keeps all the decimals in its memory for that cell),. To put only two decimals and also in terms of percent there are 2 steps:
q Set the number of decimals to 3 and now it looks like:
q Use the % format for the percent difference column , and verify that it looks something like:
12. Now set up a column for bias, assuming that CO1 is the field instrument and CO2 is the auditor’s instrument. After typing in the header “Bias,”, enter the equation under it as (field – auditor)/auditor, or =((B7-C7)/C7)*100
It should look something like:
SUBMITTING ASSIGNMENT
To submit this assignment, save the excel file with the name Excel 1 xxx-mmddyy, with xxx being your initials, mm the month, dd the date, and yy the year. Submit the assignment as an attachment using the Assignment tool.