\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Differential \newcommand\dd[2][]{\mathrm{d}^{#1}{#2}} % use as \dd, \dd{x}, or \dd[2]{x}\\% 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{{\kern-4mu}\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{\kern-4mu}^\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 % % Capacitance \newcommand\farad{\unit{F}} \newcommand\F{\farad} \newcommand\microfarad{\micro\farad} \newcommand\muF{\micro\farad} % % 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} \]

Jan 19, 2026 | 492 words | 5 min read

9.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 9.4 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 ex1_team_3_teamnumber.xlsx. Save it with your team number replacing teamnumber in the file name.

2. Import the data from ex1_team_3_chloride.csv into the Input Section of the answer sheet. Ensure that the data is organized correctly, with appropriate column headings.

3. 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.

   Hint

   You may need to use the CONVERT() function more than once.

4. 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.

5. 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.).

6. 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.

7. Save the ex1_team_3_teamnumber.xlsx file as ex1_team_3_teamnumber_values.pdf displaying the values and ex1_team_3_teamnumber_formulas.pdf displaying the formula.

Deliverables

• Each team member is expected to contribute to every team task.

• You and your team members will be held responsible for all material.

• All collaborators should be clearly listed, and their contributions are properly referenced.

• One team member should upload all deliverables to Gradescope as a single submission.

• Be sure to assign all team members to the submission.

• For help with submitting team assignments, click here.

Table 9.5 Deliverables

Deliverables	Description
ex1_team_3_teamnumber_values.pdf	PDF of your MS Excel document with values showing.
ex1_team_3_teamnumber_formulas.pdf	PDF of your MS Excel document with formulas showing.