Www.bu.edu



Guidance Note to Water Quality Analysis in ExcelPeiying Huang, Yunhan ChenHere is a brief guide to analyzing water quality data with Microsoft Excel. The main objective is to calculate how much and how frequently the parameters (DO, E.coli, pH, and Phosphorus) exceed the relevant environmental limits.Use “Filter” to select values that exceed the water quality standards for each parameter. Then copy and paste what is left to a new sheet. Repeat for each parameter.Graph 1: Filtering ValuesThe standards are:E.coli:Primary recreation: <235 CFUSecondary Recreation: <630 CFUTotal Phosphorus:Ponds: 0.025 mg/LStreams: 0.050 mg/LpH: 6.5-8.3Dissolved Oxygen: > 5.0 mg/LUse PivotTable to calculate and analyze the exceedances and frequency. Switch to the sheet with the parameter you want to analyze. Click on “Insert” and create PivotTable. For the purpose of illustration, we will use Total P (milligram phosphorus concentration in a liter of water) as an example. Same steps can be taken for other parameters. Graph 2: Creating PivotTableTo calculate how much the average Total P exceeds the environmental limit: After generating a PivotTable, drag Site ID to Rows in the field list, Weather (Wet/Dry) to Columns, and Total P mg/I under Values, as shown in the image below. Here we will start to calculate the exceedances for each site under different weather conditions.Graph 3: Selecting Values by Site ID and WeatherRight-click the value of total P in the table and select “Average” under “Summarize values by”. Here we attain the average value of all Total P records for each individual site. Graph 4: Calculating Average Create two columns next to the PivotTable for the percentage of exceedance in dry/wet weather. Then, to calculate the percentage, use the formula “=(value of the parameter - standard)/standard” (0.05 mg/L in this case). Note that here we calculate the phosphorus in stream instead of pond which has the standard of <0.025 mg/L.Graph 5: Calculating Percentage of ExceedanceTo calculate frequency of the exceedances in these water quality criteria: Generate another PivotTable in the same Excel sheet. Drag Site ID to Rows and Count of Total in the PivotTable Fields. Here we acquire the number of Total P in each site that exceed the mean. Graph 6: Selecting Values by Site IDCreate a new column with a header of frequency of exceedance. Divide “Count of Total P mg/L” from the PivotTable by the number of total samples for each site respectively. The results are the frequencies of the exceedances of phosphorus for all the sites. Graph 7: Calculating FrequencyRepeat the same steps for the remaining parameters to obtain percentage and frequency values. For E. coli, repeat what is done for P but assign "average of E. coli" to the Values field. Use 235 CFU (primary recreation) and 630 CFU (secondary recreation) as thresholds respectively.Image 8For dissolved oxygen (DO), repeat previous steps and assign "average of DO" to the Values field. Use > 5.0 mg/L as the standard.Image 9For pH, assign pH to Filters field, Site ID to Rows, and Count of pH to Values in the PivotTable Fields. By selecting the pH values that are less than 6.5 or greater 8.3 in the filter, we can acquire the sites which have pH records level that is out of the normal range and the frequency of each site exceeding the standard.Image 10 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download