I-23_WQSummaryTableSOP_FINAL_2021.07.09_signed

Title:
Version
Approvals
Approved by:
Approved by:
CITY OF
DURHAM
City of Durham, North Carolina
Department of Public Works
Stormwater and GIS Services
Water Quality Unit
Standard Operating Procedures for the Calendar Year Water
Quality Summary Table
1.0
Date: July 9, 2021
Michelle Woolfolk, Water Quality Manager, Stormwater & GIS Services
Date: July 9, 2021
Paul Wiebke, Assistant Director for Public Works, Stormwater & GIS Services
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 1 of 16
07/09/2021

Revision Record
Revision
Date
Updated by
Description of change
1.0
July 9, 2021
Jonathan Baker
SOP finalized
Distribution
Current copies of this approved SOP should be provided to the following positions with the City of
Durham's Public Works Department, Stormwater and GIS Services Division, Water Quality Unit:
• Water Quality Manager
• Assistant Water Quality Manager
• Senior Environmental Planning and Compliance Analyst
• Environmental Planning and Compliance Analyst
Abbreviations
BOD
biochemical oxygen demand
CCC
Criterion Continuous Concentration
Cu
Copper
CY
calendar year
DO
dissolved oxygen
EPA
Environmental Protection Agency
MS
Microsoft
NO2+NO3
nitrite + nitrate
NCAC
North Carolina Administrative Code
NPDES
National Pollutant Discharge Elimination System
SOP
Standard Operating Procedures
TKN
total Kjeldahl nitrogen
TN
total nitrogen
TP
total phosphorus
WQI
Water Quality Index
WQU
Water Quality Unit
Zn
Zinc
Units
cfu
colony forming units
mg/L
milligrams per liter
mL
milliliter
NTU
nephelometric turbidity units
µg/L
micrograms per liter
µS/cm
micro Siemens per centimeter
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 2 of 16
07/09/2021

Table of Contents
Procedures.................................................................................................................................................... 4
Purposeand scope...................................................................................................................................4
Methodsummary....................................................................................................................................4
Healthand safety warnings......................................................................................................................5
Personnel qualifications and responsibilities...........................................................................................5
Equipmentand supplies...........................................................................................................................6
Data and records management...............................................................................................................6
Methods................................................................................................................................................... 6
Retrieving the Ambient Stream Monitoring Data.................................................................................6
Reviewing the Ambient Stream Monitoring Data................................................................................7
Performing the Summary Calculations.................................................................................................8
Calculation and Data Consistency.........................................................................................................9
Finalizing the CY Water Quality Summary Table................................................................................10
Quality Assurance and Quality Control (QA/QC)........................................................................................10
References..................................................................................................................................................10
Appendix 1. Example of Calendar Year Water Quality Summary Table.....................................................12
Appendix 2. Optional Method for the CY Water Quality Summary Table Template..................................13
Appendix 3. Example of Data Review Notes...............................................................................................16
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 3 of 16
07/09/2021

Procedures
Purpose and scope
The purpose of this Standard Operating Procedure (SOP) is to describe the methods used by the City of
Durham's Stormwater and GIS Services Division, Water Quality Unit (WQU) for:
• calculating summary statistics for ambient stream monitoring water chemistry results collected
during a given calendar year (CY), i.e., January 1 through December 31; and
• preparing the CY Water Quality Summary Table, also known as the City of Durham Ambient
Stream Sampling Summary of Compliance with Water Quality Standards and Criteria.
The CY Water Quality Summary Table provides an overview of water chemistry results at the city's
designated ambient stream monitoring sites. The WQU uses a rotating watershed approach for its
Ambient Stream Monitoring program with water chemistry monitoring occurring in each watershed
every two years. More information about the city's ambient stream monitoring program can be found in
the city's Stormwater Management Plan (Durham 2019) and the Standard Operating Procedures for
Water Chemistry Sampling (Durham 2020).
The CY Water Quality Summary Table is completed each year for the Water Quality Assessment and
Monitoring section of the city's NPDES Stormwater Permit Annual Report. The city's NPDES Stormwater
Permit Annual Report is posted on the city's website under the Stormwater & GIS Services Division
webpage. Data summarized in the CY Water Quality Summary Table are used in reports and infographics
to inform the public about the general health of Durham's streams.
Method summary
In 2011, the WQU implemented the rotating -watershed approach to monitor streams in the city with a
subset of sites monitored each year to calculate pollutant loads (Durham 2019). The rotating -watershed
monitoring schedule is shown below in Table 1. The list of sites included in the CY Water Quality
Summary Table varies from year to year due to the rotating monitoring schedule. Monitoring sites may
also be added to or removed from the rotating monitoring schedule by WQU staff as part of the annual
review process for the Ambient Stream Monitoring Program.
Table 1. Rotating Monitoring Schedule
Neuse River Basin Watersheds
Cape Fear River Basin Watersheds
Odd Years
Ellerbe Creek
Crooked Creek
Panther Creek
Little Creek
Stirrup Iron Creek
New Hope Creek
Even Years
Eno River
Northeast Creek
Lick Creek
Third Fork Creek
Little Lick Creek
Little River
The Ambient Stream Monitoring Program assesses water chemistry, aquatic macroinvertebrate
communities, and hydrology in Durham's streams. The ambient water chemistry portion of the
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 4 of 16
07/09/2021

monitoring program focuses on surface water quality. WQU monitoring staff collect surface water grab
samples for laboratory analysis and in -situ field measurements every month. Data from this sampling
are then summarized annually in the CY Water Quality Summary Table.
The WQU has prepared the CY Water Quality Summary Table each year since 2004. An example of the
CY Water Quality Summary Table is shown in Appendix 1. The CY Water Quality Summary Table includes
the following information and calculations that are based on monitoring results for each stream site
sampled during the calendar year:
• river basin name
• downstream reservoir name
• major stream name
• site ID for the monitoring location
• number of samples collected during the CY
• average Water Quality Index (WQI) for the CY
• geometric mean of fecal coliform concentration results for the CY
• percent of fecal coliform concentration results greater than 400 cfu/100mL for the CY
• percent of dissolved oxygen (DO) concentration results less than 4.0 mg/L for the CY
• average dissolved oxygen concentration for the CY
• average biochemical oxygen demand (BOD) concentration for the CY
• average total nitrogen (TN) concentration for the CY
• average total phosphorus (TP) concentration for the CY
• percent of dissolved copper (Cu) concentration results greater than the Environmental
Protection Agency's (EPA) Criterion Continuous Concentration (CCC) for Cu for the CY
• percent of dissolved zinc (Zn) concentration results greater than the EPA's CCC for Zn for the CY
• average turbidity results for the CY
• percent of turbidity results greater than 50 nephelometric turbidity units (NTU) for the CY
• average conductivity concentration for the CY
Health and safety warnings
There is no fieldwork associated with this SOP. The procedures outlined in this document are conducted
in an office setting. All City of Durham office safety policies and guidelines should be followed when
carrying out the procedures in this document.
Personnel qualifications and responsibilities
Staff members developing the CY Water Quality Summary Table should have knowledge of water quality
standards, univariate statistics, and basic programming functions. The Senior Environmental Planning
and Compliance Analyst is responsible for following these standard procedures to complete the CY
Water Quality Summary Table annually; the Environmental Planning and Compliance Analyst may also
contribute to the completion of this table, as appropriate. If the Senior Environmental Planning and
Compliance Analyst position is vacant, then the responsibility to complete the CY Water Quality
Summary Table will shift to the Assistant Water Quality Manager. This manual will be reviewed regularly
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 5 of 16
07/09/2021

and revised as necessary. It will be the responsibility of the Senior Environmental Planning and
Compliance Analyst to maintain a current version of this SOP in the unit records.
Equipment and supplies
A City -assigned computer or laptop with a connection to the internet and the city's server is required to
perform these standard operating procedures. An internet browser is used to access the Water Quality
Unit's online data portal. A current version of MS Excel and Adobe Acrobat may be used to create the CY
Water Quality Summary Table.
Data and records management
The CY Water Quality Summary Table may be created using MS Excel software. The final versions of the
CY Water Quality Summary Tables are saved as Adobe Acrobat PDF files and stored on the Public Works
Department server. The server folder path for the CY Water Quality Summary Table files and templates
can be found in the document titled File and Folder References for the CY Water Quality Summary Table
SOP.
The CY Water Quality Summary Table files should have the following naming convention: "YYYY Stream
Ambient Summary DDMmmYYYY", e.g., 2018 Stream Ambient Summary 28Feb2019. "YYYY" is the four -
digit calendar year that represents when the water quality data was collected and "DDMmmYYYY"
signifies the date, i.e., two -digit day, three -letter month, and four -digit year) when the file was finalized.
The PDF file is given the same file name as the corresponding MS Excel file.
Methods
The following sections describe the sequential order and procedures for:
1. retrieving ambient stream monitoring data from the Water Quality Unit's online data portal;
2. reviewing the ambient stream monitoring data for duplicate values, nondetects, missing values,
and errors;
3. preparing the data and completing summary calculations; and
4. preparing the final summary table
MS Excel or other data processing computer programs may be used to organize, review, and summarize
the ambient stream monitoring data. Refer to Appendix 2 for an optional method to set up the CY Water
Quality Summary Table using MS Excel templates.
Retrieving the Ambient Stream Monitoring Data
The WQU stores all ambient stream monitoring data in an online data portal. Refer to the SOP for the
Use of the Water Quality Web Portal (Durham 2018) for instructions on how to access and use the
online data portal. Retrieve and download data for all ambient stream monitoring sites for the desired
calendar year. Download the data for the following parameters:
a. Biochemical oxygen demand (mg/L)
b. Conductivity (µS/cm)
c. Dissolved copper (µg/L)
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 6 of 16
07/09/2021

d. Dissolved copper criterion continuous concentration (µg/L)
e. Dissolved oxygen (mg/L)
f. Dissolved zinc (µg/L)
g. Dissolved zinc criterion continuous concentration (µg/L)
h. Fecal coliform (cfu/100mL)
i. Hardness (mg/L)
j. Nitrate + nitrite as N (mg/L)
k. Total Kjeldahl nitrogen (mg/L)
I. Total phosphorus (mg/L)
m. Turbidity (NTU)
n. Water Quality Index
The data that are downloaded from the Water Quality Unit's online data portal to create the CY Water
Quality Summary Table are referred to in this SOP as the Water Quality Summary Table dataset.
Reviewing the Ambient Stream Monitoring Data
Following the retrieval of the Water Quality Summary Table dataset, the data should be reviewed for
suitability to include in the CY Water Quality Summary Table. Data retrieved should be reviewed for
quality issues. These issues include but are not limited to duplicate values, erroneous values, missing
values, and nondetect values. Nondetects, also known as left -censored values, are values with a "U"
data qualifier. A nondetect value is a value less than the laboratory method detection limit. In other
words, left -censored means the actual concentration of a sample is below the method detection limit
but it is unknown by how much. Data with a "J7" qualifier indicates that blank sample contamination is
evident and the value may not be accurate; however, data values with a "J7" qualifier are included in
the Water Quality Summary Table dataset. Refer to the City of Durham Standard Operating Procedures
for Use of the Water Quality Web Portal for data qualifier definitions and data quality assurance
guidelines (Durham 2018).
It should be noted that the following procedures are automatically performed in the Water Quality
Unit's online data portal to create the Water Quality Index Report:
a. values from duplicate samples are averaged; and
b. a value is halved if it is a nondetect value (i.e., the data qualifier is "U") and averaged
among duplicates.
Steps to review the Water Quality Summary Table dataset include:
1. Review the list of sites monitored during the calendar year and compare them with the site
names that appear in the Water Quality Summary Table dataset.
2. Remove data for monitoring sites that are only sampled for field parameters (i.e., dissolved
oxygen, conductivity, pH, water temperature, and turbidity).
3. Review all null values in the Water Quality Summary Table dataset. Values may be missing due
to errors with field sheets, chain of custody forms, lab reports, or online data portal uploads.
There may be missing values in the Water Quality Summary Table dataset because samples
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 7 of 16
07/09/2021

were not collected at a monitoring site due to extreme weather, such as drought, flooding, or
ice conditions. When a missing value is identified, it is important to check the paper and
electronic documents that are associated with the data that are uploaded to the online data
portal by WQU staff. Values may also be omitted from the Water Quality Summary Table
dataset when they are rejected in the online data portal due to blank sample contamination or
other data quality issues. If a result is omitted or missing in the Water Quality Summary Table
dataset for a given monitoring date, then the monthly WQI will not be calculated for the
respective monitoring date. Although the monthly WQI may not be calculated for a monitoring
site when a sample result is missing, the annual WQI for the monitoring site will be calculated.
4. Review hardness values in the Water Quality Summary Table dataset. Hardness values are not
included in the CY Water Quality Summary Table; however, hardness values are used in the
equation for the EPA's CCC for dissolved Cu and dissolved Zn. It is important to review the
hardness data because a missing value will result in null values for Cu CCC, Zn CCC, and the WQI
score. Hardness values may be missing because the required calculations have not been
performed and the values have not been uploaded to the online data portal. Typically, hardness
value calculations have been performed by the laboratory providing the water chemistry data.
Communicate with the Environmental Planning & Compliance Analyst in charge of the Monitoring &
Assessment Program regarding any missing values or questions about the data downloaded from the
Water Quality Unit's online data portal. After any corrections are made to the data in the Water Quality
Unit's online data portal, download and review the corrected data. Record notes about missing values,
rejected values, and other data issues to convey reasons for missing data, decisions regarding data
corrections, and other data handling assumptions. Recording these notes will be helpful to staff that are
responsible for reviewing the CY Water Quality Summary Table for errors, omissions, final calculations,
and other quality control purposes. An example of data review notes is provided in Appendix 3.
Performing the Summary Calculations
Following the review of the Water Quality Summary Table dataset, perform the calculations to produce
the results that are entered into the finalized CY Water Quality Summary Table. These calculations are
listed below. In steps 4-11, the monitoring data are compared to State or Federal water quality
standards. For example, the State's water quality standard for turbidity is 50 NTU (15A NCAC
02B.0211(21)). If a turbidity result is less than 50 NTU, then it is meeting the water quality standard.
The calculation steps include:
1. Calculate total nitrogen for each monitoring event by adding the NO2+NO3 concentration and
the TKN concentration. Do not calculate the TN concentration for a monitoring event when the
NO2+NO3 concentration value or the TKN concentration value is rejected or missing.
2. Calculate annual averages for the following parameters at each monitoring site:
a. Water Quality Index
b. Dissolved oxygen (mg/L)
c. Biochemical oxygen demand (mg/L)
d. Total nitrogen (mg/L)
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 8 of 16
07/09/2021

e. Total phosphorus (mg/L)
f. Turbidity (NTU)
g. Conductivity (µS/cm)
3. Calculate the annual geometric mean for fecal coliform at each monitoring site.
4. Calculate the percent of fecal coliform samples greater than 400 cfu/100mL at each monitoring
site. The formula is the number of fecal coliform sampling results greater than 400 cfu/100mL at
a monitoring site divided by the total number of sampling events at the corresponding
monitoring site.
5. Calculate the percent of dissolved oxygen samples less than 4 mg/L at each monitoring site. The
formula is the number of dissolved oxygen sampling results less than 4 mg/L at a monitoring site
divided by the total number of sampling events at the corresponding monitoring site.
6. Calculate the difference between Cu CCC and dissolved Cu for each monitoring event. The
results are used in the calculation of the percent of Cu samples greater than the EPA's CCC.
7. Calculate the percent of dissolved Cu samples greater than the EPA CCC at each monitoring site.
The formula is the number of dissolved Cu sampling results greater than the EPA CCC for Cu at a
monitoring site (calculated in step 6) divided by the total number of sampling events at the
corresponding monitoring site.
8. Calculate the difference between Zn CCC and dissolved Zn. The results are used in the
calculation of the percent of Zn samples greater than the EPA's CCC.
9. Calculate the percent of dissolved Zn samples greater than the EPA CCC at each monitoring site.
The formula is the number of dissolved Zn sampling results greater than the EPA CCC for Zn at a
monitoring site (calculated in step 8) divided by the total number of sampling events at the
corresponding monitoring site.
10. Calculate the percent of turbidity samples greater than 50 NTU at each monitoring site. The
formula is the number of turbidity sampling results greater than 50 NTU at a monitoring site
divided by the total number of sampling events at the corresponding monitoring site.
11. Enter all calculation results into the CY Water Quality Summary Table using the format shown in
Appendix 1.
Calculation and Data Consistency
When finalizing the summary calculations, the following rules are applied to the data presented in the
CY Water Quality Summary Table:
1. Round BOD and DO values to the tenths decimal place.
2. Round TN and TP to the hundredths decimal place.
3. Round all other values to the ones place value using normal rounding rules, except for
parameters that have one or two decimal places (i.e. BOD, DO, TN, and TP). For example, if a
fecal coliform geometric mean value is 1,010.764 cfu/100mL, then round the value to 1,011
cfu/100m L.
4. Round the average WQI value up to the next whole number regardless of normal rounding rules
(e.g. 78.2 is rounded to 79, 78.6 is rounded to 79).
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 9 of 16
07/09/2021

Finalizing the CY Water Quality Summary Table
The CY Water Quality Summary Table should be formatted consistently from year to year. The general
layout and formatting for the finalized CY Water Quality Summary Table should be consistent with the
example shown in Appendix 1. Consistent formatting also includes making values bold that meet certain
criteria. All results in the CY Water Quality Summary Table that meet the following criteria should be
shown in bold font:
a. all percentages greater than 10%;
b. all fecal coliform concentrations greater than 400 cfu/100mL;
c. all TN concentrations greater than 1.60 mg/L;
d. all TP concentrations greater than 0.10 mg/L; and
e. all turbidity concentrations greater than 50 NTU.
The criteria listed above for TN and TP are based on best professional judgment. The analyst may use
best professional judgment and published references to adjust the criteria listed for TN and TP.
Following the formatting steps listed above, convert a copy of the finalized CY Water Quality Summary
Table into a PDF file and save the file in the designated folder path listed in the File and Folder
References for the CY Water Quality Summary Table SOP document. The PDF file name should be
consistent with the same file naming convention as the CY Water Quality Summary Table Excel file,
which is "YYYY Stream Ambient Summary DDMmmYYYY" (e.g., 2018 Stream Ambient Summary
28Feb2019). "YYYY" is the four -digit calendar year that represents when the water quality data was
collected and "DDMmmYYYY" signifies the date (i.e., two -digit day, three -letter month, and four -digit
year) when the file was finalized.
Quality Assurance and Quality Control (QA/QC)
Although data uploaded to the Water Quality Unit's online data portal are reviewed and checked by
WQU staff, it is important to conduct a thorough review of the Water Quality Summary Table dataset
after it has been downloaded from the online data portal. The Senior Environmental Planning and
Compliance Analyst is responsible for reviewing all data, formulas, and formatting in the CY Water
Quality Summary Table. The Assistant Water Quality Manager and the Water Quality Manager may
review the draft and final versions of the CY Water Quality Summary Table for accuracy and
completeness.
References
City of Durham (Durham). 2018. City of Durham Water Standard Operating Procedures for Use of the
Water Quality Web Portal, version 1.0. Public Works Department, Stormwater & GIS Services Division,
Water Quality Unit. DRAFT.
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 10 of 16
07/09/2021

City of Durham (Durham). 2019. City of Durham Stormwater Management Plan. Public Works
Department, Stormwater & GIS Services Division. Revised January 2019.
City of Durham (Durham). 2020. City of Durham Standard Operating Procedures for Water Chemistry
Sampling, version 1.0. Public Works Department, Stormwater & GIS Services Division, Water Quality
Unit. December 23, 2020.
Title 15A, Chapter 0213, Section .0211 of the North Carolina Administrative Code. Fresh Surface Water
Quality Standards for Class C Waters. 15A NCAC 026.0211. Amended Eff. January 1, 2015.
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 11 of 16
07/09/2021

Appendix 1. Example of Calendar Year Water Quality Summary Table
2018
CITY OF DURHAM AMBIENT STREAM SAMPLING
SUMMARY OF COMPLIANCE WITH WATER QUALITY STANDARDS AND CRITERIA
Parameters of concern shown in Bold
Fecal Coliform
(FC)
Dissolved Oxygen
(DO)
Nutrients
Dissolve
d Metals
Turbidity
m
Percent of FC
Average
Average
Percentof
Percentof
m'o
a
GeoMean
samples
Percentof
Average
Average
Total
Total
Cu
Zn
Average
Percentof
Average
v
a
Avg
FC
>400
DO Samples
DO
BOD
Nitrogen
Phosphorus
samples>
samples>
Turbidity
Samples>
Conductivity
ii
cr
Major Stream
Site ID
#
WQI
(cfu/100mL)
cfu/100mL
c4.Omg/L
(mg/L)
[mg/L]
(mg/L)
(mg/L)
EPA CCC
EPACCC
(NTU(
SONTU
(15/cm)
Little River
LR9.6LR
12
89
146
25%
0%
10.0
1.4
0.70
0.08
89/6
0%
15
0%
90
Eno River
EN8.9ER
12
89
105
17%
0%
9.5
1.3
0.76
0.07
8%
0%
16
8%
108
s
Eno River
EN13.3ER
12
90
92
17%
0%
9.1
1.3
0.61
0.05
8%
0%
13
0%
113
Crooked Creek
EN9.6CRC
12
90
205
259Yo
0%
9.8
1.7
0.62
0.04
0%
0%
8
0%
147
m
Y
Warren Creek
EN10.3WC
12
80
281
42%
8%
7.4
1.3
0.85
0.10
8%
0%
21
8%
197
y
Ellerbe Creek
EL1.9EC
12
83
225
42%
0%
8.4
1.2
1.85
0.11
0%
0%
9
0%
397
rx
m
�
Ellerbe Creek
EL7_1EC
12
89
110
33%
0%
8.2
1.3
0.61
0.13
17%
0%
9
0%
202
'm
=
Little Lick Creek
LL3.4LLC
12
70
1011
67%
17%
7.3
1.2
0.79
0.16
33%
0%
37
33%
184
z
Little Lick Creek
LL4.6LLT2
12
73
1307
58%
0%
11.8
1.3
0.71
0.28
0%
0%
127
50%
266
Lick Creek
LC1.1LC
11
75
813
7335
9%
8.0
1.4
0.75
0.35
0%
0%
76
45%
173
Rocky Branch
LC2.0RBC
11
79
464
45%
27%
7.7
1.2
0.71
0.07
0%
0%
17
0%
236
Sandy Creek
NH3.3SC
12
71
1283
75%
(1%
8.1
1.9
1.31
0.16
17%
0%
42
25%
240
Third Fork Creek
TFI.OTC
12
71
1293
83%
0%
7.9
1.2
0.96
0.23
17%
0%
51
25%
229
=
s
a
Third Fork Creek
TF2.5TCTC
12
68
3023
83%
0%
8.8
1.2
1.54
0.20
17%
0%
25
17%
225
ca
=
Third Fork Creek
TF3.4TC
12
68
2136
100%
0%
7.4
1.3
1.20
0.20
0%
0%
45
25%
256
`m
a
Third Fork Creek
TF4.4TC
12
65
2960
92%
0%
7.9
1.8
1.18
0.22
17%
0%
43
33%
306
°C
Third Fork Creek
TF5.6TC
12
69
2813
92%
8%
7.3
1.1
1.1b
0.13
8%
0%
18
8%
283
m
Rock Creek
TF5.1RC
12
61
5370
100%
17%
7.1
1.3
1.04
0.21
25%
0%
34
17%
301
aa
Rock Creek
TF6.5RCUT
12
55
2232
83%
25%
5.7
6.9
1.32
0.18
896
0%
13
0%
432
Northeast Creek
NE0.0NE
11
68
674
64%
18%
6.3
1.6
0.80
0.19
36%
0%
75
55%
190
Northeast Creek
NE1.2NE
12
68
616
50Yo
25%
6.0
2.0
0.77
0.20
42%
0%
81
67%
187
North Prong
NE2.2NP
12
67
702
58%
33%
5.9
1.7
0.76
0.15
0%
0%
74
42%
162
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 12 of 16
07/09/2021

Appendix 2. Optional Method for the CY Water Quality Summary Table
Template
Template Structure
To keep the MS Excel workbook structure consistent, two workbook template files have been created to
assist with setting up the Excel worksheets. The template file for even -numbered years is labeled as "CY
Water Quality Summary Table —EVEN YEAR TEMPLATE.xlsx" and the template file for odd -numbered
years is labeled as "CY Water Quality Summary Table —ODD YEAR TEMPLATE.xlsx". The workbook
templates are stored on the Public Works Department's server, and the folder paths can be found in the
document titled File and Folder References for the CY Water Summary Table SOP.
The workbook templates contain the following worksheets, which may be modified as needed:
1. Info — this worksheet is used to describe the name, content, and creation date of each
worksheet.
2. Formatted Summary —this worksheet stores the water quality summary table with specific
formatting that is converted into the final PDF version.
3. WQIReport— this worksheet contains the Water Quality Index Report data that are downloaded
from the Water Quality Unit's online data portal.
4. Conductivity —this worksheet contains the conductivity concentration data that are downloaded
from the Water Quality Unit's online data portal.
5. SummaryCalc — this worksheet contains the data copied over from the WQIReport worksheet
and the Conductivity worksheet. The summary calculations for each parameter can be
performed in this worksheet.
6. MonitoringSites — this worksheet contains the ambient stream monitoring sites for the
corresponding calendar year.
7. Notes — this worksheet contains the analyst's notes concerning missing values, rejected values,
and other data issues to convey decisions regarding data corrections and data handling
assumptions.
Methods
Creating the MS Excel Workbook File for the CY Water Quality Summary Table
1. Navigate to the CY Water Quality Summary Table template files on the Public Works
Department's server. The folder path for the template files can be found in the document titled
File and Folder References for the CY Water Quality Summary Table SOP.
2. If you are summarizing data for an even -numbered year, e.g., 2018, open the template file
named "CY Water Quality Summary Table —EVEN YEAR TEMPLATE". Conversely, if you are
summarizing data for an odd -numbered year, e.g., 2019, open the template file named "CY
Water Quality Summary Table —ODD YEAR TEMPLATE".
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 13 of 16
07/09/2021

3. After opening the preferred workbook template, save the file as a new document and rename
the file using the file naming convention described in the Standard Operating Procedures for the
Calendar Year Water Quality Summary Table.
4. There are cells on the "Info" worksheet and the "Formatted Summary" worksheet with prompts
in brackets, i.e., [INSERT DATE]. Replace the bracketed text with the appropriate information,
e.g., four -digit calendar year, to update the worksheets.
5. Data downloaded from the Water Quality Unit's online data portal can be copied and pasted
into the "WQIReport" worksheet and the "Conductivity" worksheet.
Reviewing the Ambient Stream Monitoring Data
1. The data review is performed in the "SummaryCalc" worksheet since the raw data are preserved
in the "WQIReport" worksheet and the "Conductivity" worksheet.
2. Add the ambient stream monitoring site list to the "MonitoringSites" worksheet. Compare the
monitoring site list to the sites shown on the "Formatted Summary" worksheet and the
"SummaryCalc" worksheet. Add or remove monitoring sites as necessary to ensure that the
monitoring sites on the "Formatted Summary" and "SummaryCalc" worksheets agree with the
calendar year monitoring site list on the "MonitoringSites" worksheet.
3. Perform the data review steps described in the Standard Operating Procedures for the Calendar
Year Water Quality Summary Table.
4. Record notes about missing values, rejected values, and other data issues in text boxes at the
top of the "Notes" worksheet to convey reasons for missing data, decisions regarding data
corrections, and other data handling assumptions.
Performing the Summary Calculations
1. All summary calculations are performed on the "SummaryCalc" worksheet.
2. Copy the data on the "wgiReport" worksheet, excluding the data column headings, and paste
the data below the corresponding data column headings on the "SummaryCalc" worksheet.
3. There are four additional columns on the "SummaryCalc" worksheet for the following
parameters: Conductivity (µS/cm), Copper CCC minus Copper (µg/L), Zinc CCC minus Zinc (µg/L),
and Total Nitrogen (mg/L). These columns have been added to the "SummaryCalc" worksheets
on the even -numbered year and the odd -numbered year workbook templates
4. Copy and paste the conductivity values on the "Conductivity" worksheet to the "Conductivity"
column on the "SummaryCalc" worksheet and ensure that monitoring station names and
monitoring dates match.
5. Perform all calculations described in the Standard Operating Procedures for the Calendar Year
Water Quality Summary Table.
6. Enter the results of the summary calculations into the corresponding cells of the draft CY Water
Quality Summary Table which is located at the top of the "SummaryCalc" worksheet.
Finalizing the CY Water Quality Summary Table
1. In the draft CY Water Quality Summary Table at the top of the "SummaryCalc" worksheet,
change cells to bold formatting that meet the following criteria:
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 14 of 16
07/09/2021

a. all percentages greater than 10%;
b. all fecal coliform concentrations greater than 400 cfu/100mL;
c. all TN concentrations greater than 1.60 mg/L;
d. all TP concentrations greater than 0.1 mg/L; and
e. all turbidity concentrations greater than 50 NTU.
2. Copy all values in the draft summary table at the top of the "SummaryCalc" worksheet and
paste as values with formatting into the "Formatted Summary" worksheet.
3. Convert a copy of the "Formatted Summary" worksheet into a PDF file and save the file in the
designated folder path listed in the File and Folder References for the CY Water Quality Summary
Table SOP document. The PDF file name should be consistent with the file naming convention
described in the Standard Operating Procedures for the Calendar Year Water Quality Summary
Table.
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 15 of 16
07/09/2021

Appendix 3. Example of Data Review Notes
Notes:
1) EL8.6SECUT was removed from the summary table because it was only monitored for
field parameters and total/dissolved Zinc (See the 2018Sites worksheet). NH1.7SCTA was
removed from the summary table because it was only monitored for field parameters in
February, June, August, and November.
2) The WQI was calculated 7 out of 12 months for NH3.3SC due to several results missing
from that site in April, May, August, November, and December. NH3.3SC was missing TKN
and TP results in December and hardness calculations for May, August, and November.
Hardness, TKN, and TP results for NH3.3SC were calculated/uploaded by S. Gale on
2/26/2019. Similar results were missing in December for all monitoring sites on Day 3. Upon
further review of the December data, the date for the field measurements (12/11/2018) did
not match up with the date for the lab results (12/12/2018). The date was incorrectly
recorded as 12/12/2018 on the lab COC forms. S. Gale was aware of this issue in the web
portal and made corrections on 2/26/2019.
3) There are no BOD values for sites monitored on 4/17/2018 (Day 3, Sandy Creek and
Third Fork Creek sites) because the results were rejected due to blank contamination.
Therefore, the WQI could not be calculated for all sites monitored on 4/17/2018 except site
TF5.6TC. Site TF5.6TC had a non -detect value (<2.Omg/L, 'U' QCode) for BOD and was not
rejected based on current web portal data QC protocols. After discussion with S. Gale and
J.V. Loperfido, the decision was made to keep the value for TF5.6TC (1mg/L - for WQI
Report calculations, the value is halved if QCode is'U') and include it the summary table
calculations.
4) LC1.1LC and NEO.ONE were not able to be sampled in January due to thick ice covering
the water surface. LC2.0RBC was not sampled in July due to disconnected pools of water in
the channel and no streamflow.
City of Durham, WQ Unit, Calendar Year Water Quality Summary Table SOP, v. 1.0 Page 16 of 16
07/09/2021