\[ \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 | 744 words | 7 min read

3.2.1. Task 1

Learning Objectives

By the end of this task, you will be able to understand the importance of suspended solids as a key water quality characteristic and recognize the sources contributing to their presence in natural water bodies like lakes, rivers, and streams. You will learn to analyze real-world environmental data, specifically focusing on Total Suspended Solids (TSS), using the dataset provided. Through this analysis, you will enhance your Excel skills by calculating descriptive statistics, creating histograms, and organizing the data for a technical presentation. This process will also enable you to assess the potential impact of human activities, such as nearby construction projects, on water quality and effectively communicate your findings in a professional manner.

Introduction

An important quality characteristic of water is the concentration of suspended solid material, referred to as suspended solids. Suspended solids are materials that can be trapped by a filter. Sources of suspended solids in lakes, rivers, and streams include industrial discharges, sewage, fertilizers, road runoff, and soil erosion. High concentrations of suspended solids can lead to problems for aquatic life and vegetation. See the green box below for more information on total suspended solids (TSS).

Total Suspended Solids

Total Suspended Solids (TSS) includes all particles suspended in water that can be trapped by a filter. Although it’s commonly collected to estimate the scale of sediment run-off from the watershed, TSS includes much more than just soil. TSS can include inorganic materials like industrial waste, and organic materials like dead plants and animal matter, live organisms and sewage. Large amounts of TSS can reduce water clarity, reduce light availability necessary for plant growth, and harm fish and other aquatic organisms. Sediment can clog fish gills and fill in spawning and other habitat areas. High TSS can also cause an increase in water temperature as the particles trap heat from the sun. Additionally, high TSS measurements can indicate high levels of nutrients, bacteria, metals and other chemicals since many of these pollutants attach to sediment. TSS even has an economic impact, since it must be filtered out of surface water used as a drinking water source.

Source: State of Indiana. (n.d.) Common Watershed Parameters.

An environmental engineer has been asked to analyze the TSS in Lake Wilson, NC. It is suspected that run-off from a nearby construction project is affecting the TSS concentration in the lake. The dataset in ex3_team_1_suspended_solids.csv contains \(60\) measurements of TSS in \(\milli\gram\per\liter\) obtained in July \(2014\).

Task Instructions

1. Open the answer sheet ex3_team_1_teamnumber.xlsx. Save it with your team number replacing teamnumber in the file name.

2. Import the data from the file ex3_team_1_suspended_solids.csv into the Input Section of the spreadsheet.

3. In the Calculation Section, use built-in MS Excel functions to calculate the descriptive statistics for the data; including minimum, maximum, range, mean, median, mode, variance, and standard deviation.

4. Determine the skewness by comparing the measures of central tendency, and by using MS Excel’s built-in function SKEW() in the Calculation Section. Do they agree?

5. Create a histogram in MS Excel using the Data Analysis ToolPak Add-in. See Installing the Data Analysis ToolPak.

6. Follow the steps from the Pre-Class Materials to create a histogram for the data set. See Creating a histogram using the Data Analysis ToolPak.

   1. In the Calculation Section, calculate the number of bins using the following rule.

      • Number of bins = Square root of the number of data points, rounded to the nearest whole number

   2. Calculate the width of each bin:

      • Bin width = Range/number of bins

      Select a width that will produce bin ranges that make it is easy to analyze the histogram. You may have to adjust the number of bins you calculated in the previous step.

   3. Determine the bin upper limits.

   4. Use the Data Analysis ToolPak to create a histogram.

   5. Format your histogram for technical presentation.

7. In the Calculation Section, write a function to display yes or no next to each data point, with yes signifying the value being smaller than \(\qty{45}{\milli\gram\per\liter}\) and no being larger or equal to \(\qty{45}{\milli\gram\per\liter}\).

8. In the Calculation Section, calculate the number of TSS measurements larger than \(\qty{55}{\milli\gram\per\liter}\) using the COUNTIF function.

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

   1. Do the different methods for determining the skewness agree? Justify your answer.

   2. How did you choose the number of bins for the histogram?

   3. Indiana, like many other states, does not have a water quality standard for TSS. Review the information on TSS at http://www.in.gov/idem/nps/3484.htm. Does this lake contain reasonable amounts of suspended solids, or should action be taken to reduce the concentration of TSS? Explain your reasoning and cite in APA format any additional sources you used.

10. Print two PDF files of ex3_team_1_teamnumber.xlsx, one with values showing (ex3_team_1_teamnumber_values.pdf) and the other with formulas (ex3_team_1_teamnumber_formulas.pdf).