Question

After converting the nitrate into a purple dye, and measuring the absorbance of the purple dye on a spectrophotometer, a standard curve is used to convert the absorbance into concentration.

To make a standard curve, samples with known concentrations of NO₃^- are run on the spectrophotemeter. The samples with known concentrations are called standards. A linear regression is then performed to relate the concentration of NO₃^-  to measured absorbance values.

Here is a spreadsheet containing a simulated data set. There are standards and their related absorbance values, and there are samples from two sites that were diluted, prior to processing and measuring their absorbances. The groundwater originates from the upslope site, and the hope is that the microbes in the soil are removing the NO₃^- from the groundwater before it reaches the downslope site.

Standard ID	mg N per L	Abs	Sample ID	Upslope Absorbance	Dilution	mg N/L Upslope	Downslope Absorbance	Dillution	mg N/L Downslope
Std 0	0	0	1	0.5	0.01		0.2	0.5
Std 0.1	0.1	0.12	2	0.5	0.01		0.251	0.5
Std 0.2	0.2	0.225	3	0.395	0.01		0.951	1
Std 0.4	0.4	0.432	4	0.332	0.1		0.2	1
Std 0.6	0.6	0.585	5	0.551	0.01		0.149	1
			6	0.556	0.01		0.2	0.5
			7	0.359	0.02		0.251	1
			8	0.331	0.01		0.2	1
			9	0.363	0.01		0.951	0.5
			10	0.244	0.02		0.159	1
			11	0.375	0.01		0.2	0.5
			12	0.5	0.02		0.143	0.5
			13	0.541	0.1		0.317	1
			14	0.669	0.01		0.563	1
			15	0.328	0.1		0.925	0.5
			16	0.417	0.01		0.563	1
			17	0.449	0.02		0.653	1
			18	0.5	0.01		0.915	0.5
			19	0.642	0.1		0.167	1
			20	0.5	0.01		0.148	1
			21	0.243	0.1		0.851	0.5
			22	0.375	0.01		0.536	1
			23	0.5	0.02		0.941	0.5
			24	0.868	0.1		0.159	0.5
			25	0.119	0.01		0.897	0.5
			26	0.459	0.01		0.316	0.5
			27	0.532	0.02		0.313	0.5
			28	0.686	0.01		0.458	1
			29	0.5	0.02		0.331	0.5
			30	0.206	0.01		0.797	1

Using the given data create a standard curve in Excel, and use Trendline to add a linear regression with the equation. Then use the standard curve and the dilutions to determine the concentration of NO₃^- in all the samples. Using the data analysis tool pack, perform the appropriate t-test to deduce if the nitrate concentration upslope is less than or greater than the nitrate concentration downslope. When performing a t-test using the data analysis tool pack, the output will include the means for both groups.

What is the average NO₃^- concentration at the upslope site? _____________

Report your answer, from the data analysis tool pak output, to 3 decimal places

What is the average NO₃^- concentration at the downslope site? ______________

Report your answer, from the data analysis tool pak output, to 3 decimal places

Given the EPA drinking water quality standard is 10 mg/L of nitrate, is the upslope site safe to drink based only on nitrate content? _____________ (Enter yes or no)

Is the downslope site safe to drink, based only on NO₃^- concentration? ____________ (Enter yes or no)

Assuming the two sites are hydrologically well connected, the transit time between the two sites is fast, and the two sites cannot be treated as independent samples, what kind of t-test should be performed to show that the upslope site is greater than the downslope site? Enter the letter of your answer choice in the answer blank

_____________

A. one-tailed unpaired t-test
B. two-tailed unpaired t-test
C. one-tailed paired t-test
D. two-tailed paired t-test

What is the calculated t statistic, rounded to 4 decimal places? ___________

Is the calculated t statistic greater or less than the critical t value reported by the data analysis tool pack? ____________ (enter greater or less)

Is the nitrate concentration at the upslope site significantly greater than the downslope site? ____________ (Enter yes or no)

Based on this statistical result, and assuming no diffusion or dilution occurs between the upslope and downslope site, do you think microbes are removing NO₃^- from the ground water? __________ (Enter yes or no)

Answer 1

Given the EPA drinking water quality standard is 10 mg/L of nitrate, is the upslope site safe to drink based only on nitrate content? Yes since  the average NO3- concentration at the upslope site<10 mg/L

Is the downslope site safe to drink, based only on NO3- concentration? Yes since  the average NO3- concentration at the downslope site<10 mg/L.

Since the two sites cannot be treated as independent samples, so we use

D. two-tailed paired t-test

The calculated t statistic=-0.0764

the calculated t statistic is less than the critical t value reported by the data analysis tool pack.

Is the nitrate concentration at the upslope site significantly greater than the downslope site? No

Based on this statistical result, and assuming no diffusion or dilution occurs between the upslope and downslope site, do you think microbes are removing NO3- from the ground water?  yes