Environmental Data Analysis BC
ENV 3017
Homework 4 - due Tu 10/6 (& Th 10/8)
1.) (10p) Histogram
(due on Tu 10/6)
The attached sheet
contains a list of grades from a class taught at Barnard. The numerical
grades were converted into letter grades using the given key.
a) (7p)
Import the data into Excel. Go through the list and and make two
histograms
by using the appropriate Excel or Statplus function to do so.
Make one histogram that uses D, C, B, A as bins, the other D, D+, C-, C
etc. Calculate the average grade and highlight it on the two
histgrams. Tip: you'll need to use the numeric versions of the grades
as bins. However, edit the final version of the hstogram so that it
shows the l;etter grades on the x-axis.
b) (3p) Calculate the 10th and
90th
percentile
of the numeric grades using Excel's percentile function.
What
letter grade do these corrospond to and what is the number of students
that has a grade above the 90th percentile and below the 10th
percentile?
2.) (20p) IC data analysis (draft due Tu 10/6 on courseworks, final
version due Th 10/8)
- all chromatogram and the log sheet are posted as pdf files in a folder
on the website
- download template & raw data from website,
save
file
as
XY_IC_EDA09.xlsx (XY being your initials)
- orient yourself in the workbook
- note everything you do in the 'comment' worksheet (first tab)
- separate samples from standards; don't alter the raw data sheet,
do manipulations on a seperate worksheet in the same workbook
- determine concentrations of the standards and fill in the
'Standards' spreadsheet, using the dilution
factors you calculated
- plot peak area versus standard concentrations of each anion,
using
a scatter plot
- note: Excel is always interpreting the first highlighted column
as the parameter plotted on the X-axis
- you can highlight multiple cells, rows, and columns by holding
the 'Ctrl' button while clicking the cells
- place each chart as new sheet into your workbook
- graphs should show a linear trend, concentrations should increase
with peak area
- note that we had problems with the first standards of the run,
check chromatograms of all run standards, decide which measurements to
use
- use the Excel trendline
function to make a line fit
- go to one of the chart
- then go to the Chart/Add
Trendline menu
- select the linear trendline
- under options, click Display
equation on chart
- the line and equation are displayed, but the equation has too
few significant digits
- select the equation and go to menu Forward/selected data labels/Number
and set the format to scientific with
2
decimals
- write down (or copy/paste) the equations for each anion
- enter the sample data (all
samples, including DI water) similarly to the standard in your
spreadsheet,
- fill out the top part of the "Samples summary" worksheet with
detailed info on 'your' samples
- use calibration equations derived above to determine the
concentrations of your
samples
- Y in the equation is the peak area, X the anion concentration
- watch out for dilutions of the samples: use the equations of
the standard curves first to convert
peakarea into concentration, then multiply by the dilution factor
- For each of your
samples sample, check if the peak areas fall within the range
of
the calibration curves and highlight all the ones that exceed them
(look at on-line pdf files, compare peak areas to those of the samples)
- Note any other problems with the runs of your samples that are apparent in
thechromatograms
- for the samples that were measured diluted and undiluted, take
the measurements that fall in or the closest to the range covered by
the peakareas of the standards
- this can mean that you take the Cl measurement from the diluted
sample, and the Fl measurement from the undiluted sampl
- Put the concentrations of your samples into the sample summaries
tab
- Transfer the file (one Excel file per person) to the appropriate
folder on the courseworks website by Tuesday, 1pm.