\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Poor man's siunitx \newcommand\unit[1]{\mathrm{#1}} \newcommand\num[1]{#1} \newcommand\qty[2]{#1~\unit{#2}}\\\newcommand\per{/} \newcommand\squared{{}^2} \newcommand\cubed{{}^3} % % Scale \newcommand\milli{\unit{m}} \newcommand\centi{\unit{c}} \newcommand\kilo{\unit{k}} \newcommand\mega{\unit{M}} % % Percent \newcommand\percent{\unit{\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{}^\circ}} % % Time \newcommand\second{\unit{s}} \newcommand\s{\second} \newcommand\minute{\unit{min}} \newcommand\hour{\unit{h}} % % Distance \newcommand\meter{\unit{m}} \newcommand\m{\meter} \newcommand\inch{\unit{in}} \newcommand\foot{\unit{ft}} % % Force \newcommand\newton{\unit{N}} \newcommand\kip{\unit{kip}} % kilopound in "freedom" units - edit made by Sri % % Mass \newcommand\gram{\unit{g}} \newcommand\g{\gram} \newcommand\kilogram{\unit{kg}} \newcommand\kg{\kilogram} \newcommand\grain{\unit{grain}} \newcommand\ounce{\unit{oz}} % % Temperature \newcommand\kelvin{\unit{K}} \newcommand\K{\kelvin} \newcommand\celsius{\unit{{}^\circ C}} \newcommand\C{\celsius} \newcommand\fahrenheit{\unit{{}^\circ F}} \newcommand\F{\fahrenheit} % % Area \newcommand\sqft{\unit{sq\,\foot}} % square foot % % Volume \newcommand\liter{\unit{L}} \newcommand\gallon{\unit{gal}} % % Frequency \newcommand\hertz{\unit{Hz}} \newcommand\rpm{\unit{rpm}} % % Voltage \newcommand\volt{\unit{V}} \newcommand\V{\volt} \newcommand\millivolt{\milli\volt} \newcommand\mV{\milli\volt} \newcommand\kilovolt{\kilo\volt} \newcommand\kV{\kilo\volt} % % Current \newcommand\ampere{\unit{A}} \newcommand\A{\ampere} \newcommand\milliampereA{\milli\ampere} \newcommand\mA{\milli\ampere} \newcommand\kiloampereA{\kilo\ampere} \newcommand\kA{\kilo\ampere} % % Resistance \newcommand\ohm{\Omega} \newcommand\milliohm{\milli\ohm} \newcommand\kiloohm{\kilo\ohm} % correct SI spelling \newcommand\kilohm{\kilo\ohm} % "American" spelling used in siunitx \newcommand\megaohm{\mega\ohm} % correct SI spelling \newcommand\megohm{\mega\ohm} % "American" spelling used in siunitx % % Inductance \newcommand\henry{\unit{H}} \newcommand\H{\henry} \newcommand\millihenry{\milli\henry} \newcommand\mH{\milli\henry} % % Power \newcommand\watt{\unit{W}} \newcommand\W{\watt} \newcommand\milliwatt{\milli\watt} \newcommand\mW{\milli\watt} \newcommand\kilowatt{\kilo\watt} \newcommand\kW{\kilo\watt} % % Energy \newcommand\joule{\unit{J}} \newcommand\J{\joule} % % Composite units % % Torque \newcommand\ozin{\unit{\ounce}\,\unit{in}} \newcommand\newtonmeter{\unit{\newton\,\meter}} % % Pressure \newcommand\psf{\unit{psf}} % pounds per square foot \newcommand\pcf{\unit{pcf}} % pounds per cubic foot \newcommand\pascal{\unit{Pa}} \newcommand\Pa{\pascal} \newcommand\ksi{\unit{ksi}} % kilopound per square inch \newcommand\bar{\unit{bar}} \end{aligned}\end{align} \]

Oct 24, 2024 | 385 words | 4 min read

2.2.3. Task 3

Learning Objectives

By the end of this task, you will be able to proficiently import and manipulate data in MS Excel, convert units using built-in functions, and create and format a plot to analyze the relationship between roadway concentration and chloride concentration in streams. You will also develop the skills to critically evaluate and interpret the data, identifying independent and dependent variables and proposing practical solutions to environmental issues based on your analysis.

Introduction

Sodium and chloride concentration of streams can often be attributed to two major sources:

1. sewage disposal systems and

2. road salt application during winter.

An engineer is studying the occurrence of sodium and chloride in surface streams as related to the roadway concentration in 18 different watersheds (for a definition of “watershed,” visit http://water.epa.gov/type/watersheds/whatis.cfm). The engineer collected the data shown below.

For example, in Watershed 1, \(0.19\%\) of the land is roadway, and the chloride concentration in the stream sampled was \(\qty{0.26}{\grain\per\gallon}\) US.

Table 2.3 Watershed information

Watershed	Chloride concentration (\(\grain\per\gallon\) US)	Roadway concentration in watershed (\(\%\))
1	0.257038456	0.19
2	0.385557685	0.15
3	0.566652961	0.57
4	0.619229009	0.7
5	0.630912575	0.67
6	0.636754358	0.63
7	0.689330406	0.47
8	0.706855755	0.7
9	0.835374983	0.6
10	0.858742116	0.78
11	0.876267465	0.81
12	1.010628476	0.78
13	1.121622355	0.69
14	1.349451896	1.3
15	1.600648569	1.05
16	1.618173919	1.06
17	1.857687026	1.74
18	2.307504325	1.62

Task Instructions

1. Open the answer sheet ex2_team_3_teamnumber.xlsx. Save it with your team number replacing teamnumber in the file name. Import the data from ex2_team_3_chloride.csv into it.

2. Under the Calculation Section, convert the chloride concentration from grain per US gallon (\(\grain\per\gallon\)) to milligrams per liter (\(\milli\gram\per\liter\)). Use the built-in function CONVERT() to perform this task. Use the MS Excel help if you are not familiar with this function.

3. In the Output Section of the answer sheet, identify the type of plot you will create to plot the relationship between roadway concentration and chloride concentration. Justify your choice.

4. Create and format your plot using the chloride concentration in \(\milli\gram\per\liter\).

   • Set the x-axis range from \(0\%\) to \(1.8\%\) and the y-axis range from \(\qty{0}{\milli\gram\per\liter}\) to \(\qty{40}{\milli\gram\per\liter}\).

   • Make sure that you format your plot for a technical presentation (add a descriptive title, axis labels with units, gridlines, etc.).

5. Copy the following questions into the Output Section of the spreadsheet and then add your answers.

   1. Which variable is the independent variable? Which variable is the dependent variable?

   2. What is the relationship between roadway concentration in the watershed and the concentration of chloride in streams?

   3. If the engineer determines that most of the chloride in the streams is coming from road salt, identify one thing that could be done to reduce the chloride concentrations of streams within the watersheds.

6. Save the ex2_team_3_teamnumber.xlsx file as ex2_team_3_values_teamnumber.pdf displaying the values and ex2_team_3_formulas_teamnumber.pdf displaying the formula.

7. Submit both files to Gradescope.