Statistical Data Analysis
 Page ID
 221399
This Excel file contains real data collected during actual experiments that can be used for the purpose of conducting a dry lab or practicing calibration and statistical data analysis. The file is organized in folders, each one containing data respectively for nitrate, phosphate, calcium and magnesium and pH. Within each folder, first a calibration set is provided so that students, using Excel or similar software, can calculate the best fit line and correlation coefficient.
Next, replicate measurements for that analyte at three different locations (North, South and East ponds) are provided so that specific concentrations can be calculated for each location.
Finally, ten independent measurements of the analyte at the three locations are provided so that mean and standard deviation can be calculated and tests of significance conducted to determine whether the results at each location are comparable.
Following are answers to some fundamental calculations:
 Nitrate (by Ion Selective Electrode)
Nitrate (N) concentration (mg/L) 
Electrode Potential (mV) 

0.1 
144.8 
0.2 
131.0 
0.4 
115.5 
0.6 
105.2 
1.0 
93.5 
2.9 
65.8 
4.7 
52.7 
Calibration equation: y = 55.504x + 91.826
R² = 0.9975
The table below shows predicted nitrate (N) concentrations based on the above calibration:

Electrode potential (mV) 
Nitrate (N) concentration (mg/L) 

North Pond 
72.6 
2.2 
75.1 
2.0 

71.5 
2.3 

South Pond 
80.1 
1.6 
79.2 
1.7 

81.5 
1.5 

East Pond 
110.6 
0.5 
106.1 
0.6 

107.3 
0.5 
These data can be used to show how to calculate the concentration in an unknown sample from the electrode potential and the calibration equation. Additional data are provided (ten independent measurements on each pond water) so that students can calculate the average and standard deviation nitrogen concentration in the three ponds. Following is a table summarizing the results.
North pond 
South pond 
East pond 

2.0 ± 0.3 
1.7 ± 0.4 
0.7 ± 0.2 
Since the original research question was to determine whether differences in water quality among different ponds affect frog and snails survival, the means obtained above could be compared using tests of significance such as an unpaired ttest.
For a more indepth review of tests of significance, please review Chapter 4, section 4.6 in Analytical Chemistry 2.0 by David Harvey.
As an example, let’s compare the nitrate concentration in the North pond (2.0 ± 0.3) with the nitrate concentration in the East pond (0.7 ± 0.2). In order to run an unpaired ttest, the Ftest needs to be applied first to determine whether we can pool the variances of the two sets. Since we have 10 replicate measurements in each set, we have 9 degrees of freedom (df) for each set.
\[\mathrm{F_{exp} = \dfrac{0.3^2}{0.2^2} = 2.25}\nonumber\]
From Appendix 5 (Analytical Chemistry 2.0), the critical value for F(0.05,9,9) is 3.179. Since F_{exp} < F(0.05,9,9), there is no significant difference between the variances of the two datasets so we can apply the ttest.
The results of the ttest show a t_{exp} = 9.7. From Appendix 4 (Analytical Chemistry 2.0), the critical value for t(0.05,18) is 2.101. Since t_{exp} = 9.7 >than the critical value for t(0.05,18), the two means are significantly different at the 95% confidence level.
 Phosphate (by Spectrophotometry)
Phosphate (P) concentration (mg/L) 
Absorbance 

0.00 
0.0000 
0.05 
0.0164 
0.10 
0.0421 
0.20 
0.0758 
0.30 
0.1257 
0.40 
0.1657 
The calibration equation is: y = 0.4184x  0.0023
R² = 0.9974
The table below shows predicted phosphorous (P) concentrations based on the above calibration:

Absorbance 
Phosphorous (P) concentration (mg/L) 

North Pond 
0.0372 
0.094 
0.0375 
0.095 

0.0337 
0.086 

South Pond 
0.0299 
0.077 
0.0248 
0.065 

0.0258 
0.067 

East Pond 
0.0534 
0.133 
0.0395 
0.100 

0.0406 
0.103 
These data can be used to show how to calculate the concentration in an unknown sample from the absorbance measured on unknown samples and the calibration equation. Additional data are provided (ten independent measurements on each pond water) so that students can calculate the average and standard deviation phosphorous concentration in the three ponds. Following is a table summarizing the results.
North pond 
South pond 
East pond 

0.08 ± 0.02 
0.08 ± 0.02 
0.11 ± 0.02 
These results could be used to determine whether differences in phosphorous concentrations exist among the three ponds using tests of significance, much as demonstrated for nitrogen measurements.
 Calcium and Magnesium (by Atomic Absorption Spectrophotometry)
Calcium concentration (mg/L) 
Absorbance 

0.00 
0.0000 
0.50 
0.0278 
1.25 
0.0688 
2.50 
0.1336 
The calibration equation is: y = 0.0534x + 0.0009
R² = 0.9997
Magnesium concentration (mg/L) 
Absorbance 

0.00 
0.0000 
0.50 
0.3518 
1.25 
0.8469 
2.50 
1.5068 
The calibration equation is: y = 0.5997x + 0.0392
R² = 0.9953
The table below shows predicted calcium and magnesium concentrations based on the above calibrations:

Calcium Absorbance 
Calcium Concentration (mg/L) 
Magnesium Absorbance 
Calcium concentration (mg/L) 

North Pond 
0.1290 
2.4 
0.7992* 
2.5 
0.1243 
2.3 
0.7812* 
2.5 

0.1276 
2.4 
0.8089* 
2.6 

South Pond 
0.0725* 
2.7 
1.1786* 
3.8 
0.0731* 
2.7 
1.1688* 
3.8 

0.0711* 
2.6 
1.1899* 
3.8 

East Pond 
0.0329 
0.60 
1.1187 
1.8 
0.0345 
0.63 
1.1089 
1.8 

0.0377 
0.69 
1.1198 
1.8 
* water sample was diluted 1:1 prior to analysis
Again, these data can be used to show how to calculate the concentration of calcium and magnesium in an unknown sample from the absorbance measured on the unknown sample and the calibration equation. Additional data are provided (ten independent measurements on each pond water) so that students can calculate the average calcium and magnesium concentration along with the standard deviation in samples collected at the three ponds. Following is a table summarizing the results.

North pond 
South pond 
East pond 

Calcium 
2.37 ± 0.05 
2.70 ± 0.05 
0.11 ± 0.02 
Magnesium 
2.62 ± 0.04 
3.80 ± 0.05 
0.62 ± 0.03 
These results could be used to determine whether differences in calcium and magnesium concentrations exist among the three ponds using tests of significance, much as demonstrated for nitrogen measurements.
 pH (electrode)
Following is a table summarizing the results of pH measurements:
North pond 
South pond 
East pond 

7.44 ± 0.05 
7.61 ± 0.03 
7.03 ± 0.04 
These results could be used to determine whether differences in pH exist among the three ponds using tests of significance.