User Guide
for the
Recovery Potential Screening (RPS)
100
90
80
70
g 60
"O
c
1 50
J?
o
LU 40
30
20
10
0
0 10 20 30 40 50 60 70 80 90 100
Stressor Index
Scoring SpreadsheetTool
October 2018
I I 0-0.02
I I 0.02-0.05
¦ 0.05-0.09
¦ 0.09-0.14
¦ 0.14-0.54
-------
Table of Contents
1. Introduction & Background 4
1.1. RPS Overview 4
1.2. RPS Scoring Spreadsheet Tool 4
1.3. User Guide Organization 4
2. Instructions Worksheet 5
3. Setup Worksheet 6
3.1. Overview 6
Basics 6
Advanced Tips 7
3.2. Choose Watershed Scale 8
Basics 8
Advanced Tips 8
3.3. Select Watersheds to Screen 9
Basics 9
Advanced Tips 12
3.4. Select Indicators and Weights 13
Basics 13
Advanced Tips 16
3.5. Run Screening Button 17
Basics 17
Advanced Tips 18
3.6. Respond to Warning Messages 19
Basics 19
Advanced Tips 20
3.7. Reset Screening Button 21
4. Notes Worksheet 22
5. Results Worksheet 23
5.1. Overview 23
Basics 23
Advanced Tips 23
5.2. Sort and Filter 24
6. Bubble Plot Worksheet 25
7. Bubble Plot Options Worksheet 26
7.1. Overview 26
7.2. Reposition Axes 27
7.3. Toggle Screening to Plot 28
7.4. Label Bubbles 29
2
-------
Basics 29
Advanced Tips 31
7.5. Edit Bubble Sizes 32
7.6. Edit Bubble Colors 33
7.7. Save As Image File 36
8. Map Worksheet 37
8.1. Overview 37
8.2. Create Custom Map 38
8.3. Highlight Watersheds of Interest 41
8.4. Save Map as PDF File 42
9. Indicator Data Worksheet(s) 43
10. Explore Indicator Data Menu 44
10.1. Overview 44
10.2. Select an Indicator to Explore 46
10.3. Select a Covariate Indicator 47
10.4. Save or Copy a Chart 48
10.5. Change Watershed Selection Button 49
11. Indicator Info Worksheet 53
11.1. Overview 53
11.2. Filter Table 54
11.3. Add Indicator Info 55
12. Add Indicators Worksheet 57
12.1. Overview 57
12.2. Enter New Indicator Information 58
12.3. Enter New Indicator Data 59
12.4. Add Indicators Button 61
13. HUC Subsets Worksheet 62
13.1. Overview 62
13.2. Create a New Watershed Subset Button 63
13.3. Manually Add a Subset List 67
3
-------
1. Introduction & Background
1.1. RPS Overview
Recovery Potential Screening (RPS) is a systematic method for comparing watersheds based on characteristics that may
influence the relative likelihood of successful watershed restoration or protection. RPS was developed to provide states
and other planners with a flexible screening tool to guide prioritization of watersheds according to differences in key
environmental and social factors affecting prospects for restoration and protection success.
RPS involves identifying a group of watersheds to be compared and a specific purpose for comparison, selecting
appropriate indicators of recovery potential in three categories (Ecological, Stressor, and Social; Figure 1), and
calculating recovery potential index scores for the watersheds. Index scores include the Ecological Index, Stressor Index,
and Social Index and are calculated by combining indicators from each category. For example, the Ecological Index is
calculated from all ecological indicators while the Stressor Index is calculated from all stressor indicators. In addition, an
overall Recovery Potential Integrated (RPI) Index score is calculated by combining the Ecological Index, Stressor Index,
and Social Index.
Figure 1. RPS uses three categories of recovery potential indicators to compare watersheds.
1.2. RPS Scoring Spreadsheet Tool
The RPS Scoring Spreadsheet Tool (RPS Tool) is a custom-coded Microsoft Excel workbook designed to facilitate setting
up an RPS screening run, perform all recovery potential index calculations, and display results. The State RPS Tool series
was first released in July 2014 and updated in September 2018 as a set of ready-to-use RPS spreadsheet tools for each of
the lower 48 States. Each RPS Tool has pre-calculated indicator data already embedded for all 12-digit hydrologic unit
(HUC12) subwatersheds of the National Watershed Boundary Dataset that are wholly or partially within the State's
boundary. In addition to storing pre-calculated indicator data, each State RPS Tool:
¦ Contains a user-friendly interface for setting up a screening;
¦ Automatically calculates RPS index scores and ranks for the HUC12 subwatersheds of interest;
¦ Displays results in customizable table, plot, and map form;
¦ Can be readily updated with user-generated indicator data.
The RPS Tool is designed for use by anyone with basic familiarity with Microsoft Excel. Instructions for each step are
contained within the RPS Tool and this User Guide provides more detailed directions on tool use.
1.3. User Guide Organization
The RPS Tool is made up of several worksheets within a Microsoft Excel workbook. Some worksheets contain interactive
menus and buttons while others store data for viewing and use in RPS calculations.
Each section of this User Guide describes a worksheet in the RPS Tool. Worksheet contents are summarized and step-by-
step instructions are provided for interactive features within the worksheet. For some worksheets and features, a set of
advanced tips are also provided for users that have become proficient in basic use of the tool.
4
-------
Instructions Worksheet
2. Instructions Worksheet
The Instructions worksheet provides an abridged set of instructions for using the RPS Tool. The Instructions worksheet is
intended to serve as a built-in reference for users of the RPS Tool, It does not supplant the more detailed instructions
presented in this User Guide.
Listed at the top of the Instructions are three key pieces of information for the tool file you are working with:
¦ Project Area - The state, river basin, or other geographic area that the RPS Tool file is designed for. Each tool file
comes with a table of pre-calculated indicator data for all watersheds in the Project Area at one or more watershed
scales (defined below). Users can decide to screen all watersheds within the Project Area or only a subset of
watersheds of interest.
¦ Watershed Scales - The watershed scales that the RPS Tool file is able to screen. The term "watershed scale" refers
to pre-defined watershed delineations. A typical tool file is able to screen, at minimum, 12-digit hydrologic units
(HUC12s) of the National Watershed Boundary Dataset (WBD). Tool files for some Project Areas are able to screen
additional watershed scales such as 8-digit hydrologic units (HUCSs).
m Version Date - The date the RPS Tool file was last updated to add new user features or modify existing features.
A | B C D
E F G H 1 1
K
L
M
1
Scoring Spreadsheet for Recovery Potential Screening
2
Project Area: Tennessee
3
Watershed Scales: HUC8, HUC12
4
Version Date: July 28, 2016
5
6 TROUBLESHOOTING NOTE: In December 2014, Microsoft released Off ice updates that affect the functionality of Excel macros and
7 controls (drop-down menus, buttons, etc.). This tool file contains a patch to address these issues, but the file must be opened twice for
8 the patch to be fully implemented. If this is your first time opening the file, please close and re-open a second time before proceeding. If
9 you continue to receive error messages while using the Setup worksheet, you will need to download and run the Administrator level
10 Microsoft Fix it solution (#51031) from http://support.microsoft.com/kb/3025036/EN-US.
11
12 This MS Excel file is specially coded to perform Recovery Potential Screening (RPS) calculations for the project area and watershed
13 scales listed above using indicator data stored in this file. This procedure supports the assessment and comparison of relative
"' restorability of impaired waters or watersheds by measuring ecological, stressor, and social indicators and calculating summary indices.
14 This file should function properly in Excel 2003, 2007, 2010, or 2013.
15 j
t; Each of the worksheets in this spreadsheet file (see tabs along the bottom of the screen) begins with instructions and a summary of its
contents. Below is an overview of all the steps involved to perform Recovery Potential Screening from this spreadsheet tool. For
general background information about Recovery Potential Screening, please visit www.epa.gov/recoverypotential/.
18
Print a copy of this Instructions worksheet, if desired, for reference when using other worksheets.
20 1. ENABLE THE MACROS THAT RUN THIS FILE. If the message "Security warning - macros have been disabled" appears - click on
21 Options and select Enable this Content. If any other message appears regarding security, click on Enable Macros or Enable this Content.
The message may vary with different versions of Excel. You should only need to do this step once to activate all of the tool's functions.
23 2. REVIEW INDICATOR DATA AND DESCRIPTIONS. Click on the HUC_Data tab for the watershed scale of interest (e.g., HUC12_Data).
24 There will be one worksheet for each watershed scale included in this file. Each worksheet stores the complete data table of raw
25 indicator values for all indicators measured and for all watersheds in the project area at a given scale.
26
27 Indicator data (values of specific attributes for every watershed, in a data table) have been pre-loaded to this file. Some pre-loaded
-, indicators are measured from the in-state portion of each watershed only. Other indicators are measured from the entire watershed
area (including the out-of-state portion). Indicators that consider in-state conditions only contain "(INSTATE)" in their names.
INSTRUCTIONS I Notes Setup Results | Bubble_Plot Bubble_Plot_Options HUC8_Map
5
-------
Setup Worksheet- Overview
3. Setup Worksheet
3.1. Overview
Basics
The Setup worksheet is your "home base" for configuring and running a Recovery Potential Screening. It is the first
worksheet you should use after opening the tool file, reviewing instructions, and planning your Recovery Potential
Screening run. The Setup worksheet is the only worksheet where you can choose (and change) the watersheds and
indicators that are used in your screening run.
RUN SCREENING RESET SCREENING
Select Watersheds
Select watersheds to indude m the screening by dtdang
tin® Seftct miMAM) button below To deaf your
selections, dick me dear watershed Sefecbons button
c HUCS \HUC12]
Select Ecological Indicators
Seled ecological indicators lo Indude in me screening by
clicking trie EcotOQ*a< indicators button btlow To d«ar
your selections. d»ck the Ci&ar Ecotogtcat indicator Selections
button
Select Stressor Indicators
Seled stressor Indicators to indude in me screening by dieting
me Sstect smsor indicators button Miow. To dear your
selections, dldctfie Clear Stressor indicator Selections button
Select Social Indicators
Seled social indicators to Indude in me screening by didang
the Select Sooat inOKatorz button below To dear your
selections dick me dear Sooat indicator Selections button
Select Watersheds
Setect Ecological Indicators
I
Select Stressor Indicators
Select Social Indicators
Clew Watershed Selections
Clear Eco/ogicaJ Indicator Selections
I
Gear Stressor Indicator Selections
dear Social Indicator Selections
HUC12ID
Ecological Indicator
Weight
Stressor Indicator
Weight
Social Indicator
Weight
INSTRUCTIONS Notes
I
Setup
Results Bubble_Plot Bubble_Plot_Options | HUC8_Map
The Setup worksheet is organized into four sections:
¦ In the Select Watersheds section you will specify which watersheds will be included in the screening.
¦ In the Select Ecological Indicators section you will specify which ecological indicators will be used in the screening and
how those ecological indicators will be weighted.
¦ In the Select Stressor Indicators section you will specify which stressor indicators will be used in the screening and
how those stressor indicators will be weighted.
¦ In the Select Social Indicators section you will specify which social indicators will be used in the screening and how
those social indicators will be weighted.
After selecting watersheds, indicators, and weights, you will click the RUN SCREENING button to populate other
worksheets in the tool with screening results. The Setup worksheet also includes a RESET SCREENING button to clear the
current watershed/indicator selections from the Setup worksheet and screening results from other worksheets so that a
new screening run can be configured from scratch.
The following sections provide step-by-step instructions for how to:
¦ Choose a Watershed Scale
m Select Watersheds to Screen
m Select Indicators and Assign Weights
¦ Use the Run Screening Button
¦ Respond to Warning Messages
m Use the Reset Button
6
-------
Setup Worksheet-Overview
Advanced Tips
¦ Completing the setup process is far easier if you have already planned out your screening by identifying the screening
purpose, watersheds of interest, and relevant indicators (from the full list available) beforehand.
¦ The process of selecting watersheds and indicators to screen can be streamlined by copying and pasting watershed
IDs, indicator names, and indicator weights that have already been compiled in other spreadsheets. This is faster than
typing or selecting individually from popup menus on the Setup worksheet. Instructions for copying and pasting
watersheds and indicators into the Setup worksheet are provided in the following sections.
¦ After setting up and running a screening, you may want to make minor adjustments to your screening by adding or
removing watersheds, adding or removing indicators, or adjusting indicator weights. Do not click the RESET
SCREENING button on the Setup worksheet if you want to make minor adjustments to an existing screening since
clicking the RESET SCREENING button will clear all selections from the Setup worksheet. Instead, to adjust an existing
screening, simply make the desired changes on the Setup worksheet and then re-click the RUN SCREENING button.
This will update results stored on other worksheets to reflect the changes made to watershed and indicator selections
on the Setup worksheet.
¦ Save completed screening runs and change the file name before beginning a new run with different watersheds or
indicators, or before using RESET SCREENING.
7
-------
Setup Worksheet- Choose Watershed Scale
3.2. Choose Watershed Scale
Basics
Some RPS Tool files are designed to screen watersheds at a single scale (e.g., HUC12 subwatersheds only). Others allow
users to select between multiple watershed scales for screening (e.g., either HUC8 watersheds or HUC12 subwatersheds).
If your tool file includes multiple watershed scales, you must specify which scale your screening will consider on the Setup
worksheet.
Watershed scale options are displayed right below the
Select Watersheds section title on the Setup
worksheet.
Select Watersheds
Select watersheds to include in the screening by clicking the Select
Watersheds button below. To dear your selections, click the Clear
Watershed Selections button.
H HUC8 HUC12
To select a watershed scale, click the option button
next to the watershed scale name.
Select Watersheds
If your RPS tool contains watershed data at one scale
Clear Watershed Selections
only, there will be no options buttons visible.
HUC8 ID
Advanced Tips
¦ Users must select only one watershed scale per screening run (i.e., you cannot screen watersheds from multiple scales
in a single screening run).
¦ Clicking a watershed scale button will prepare the watershed and indicator popup menus on the Setup worksheet for
next steps. For example, clicking the HUC12 option button will add HUC12 IDs to the popup menu displayed when the
Select Watersheds button is clicked.
¦ Be aware that changing to a different watershed scale will reset the Setup worksheet by clearing any selected
watersheds, indicators, and weights. If you have begun to setup your screening run by entering watersheds, indicators,
or weights on the Setup worksheet then those entries will be deleted if you click a different watershed scale button
and will not be recovered by returning to the original scale.
8
-------
Setup Worksheet-Select Watersheds to Screen
3.3. Select Watersheds to Screen
Basics
A Recovery Potential Screening run can include all watersheds in the Project Area that the RPS Tool is designed for or a
smaller subset of watersheds of interest. Users must decide which watersheds to screen based on the purpose and goals
of their screening.
In the Select Watersheds section of the Setup worksheet, you will specify which watersheds to include in the screening
by entering a list of watershed IDs (one ID per row). Three options are available for entering watersheds IDs.
Select Watersheds
Select watersheds to include in the screening by clicking the Se/ecf
Watersheds button below. To dear your selections, click the Clear
Watershed Selections button.
HUC8
C HUC12
Select Watersheds
Clear Watershed Selections
HUC8 ID
Select Watersheds-..
i—
Select watersheds to include ri your screenrig by highlghting their names n the box
below and dtfting the Add Setected Watersheds button.
Or ckk the Add AJ Watersheds button to select al watersheds for your screenrig.
05110002 (Barren)
05130101 (Upper Cunberfand)
05130103 (Upper Cumberland -Lake Cumberland)
05130104 (South Fork Curberland)
05130105 (Obey)
05130106 (Upper Cunbertand-Cordel Hul)
05130107 (Cots)
05130103 (Caney)
05130201 (Lower Omberland-Old Hickory Lake)
05130202 (Lowe Cimberiand-Sycamore)
05130203 (Stones)
05130204 (Karpeth)
05130205 (Lower Cumberland)
05130206 (Red)
06010101 (North Fork Hois ton)
06010102 (South Fork Mote ton)
06010103 (Watauga)
d
AHH SHrrtrH WxtrrchrH*
Add All Watersheds
t w;
Select
Waterght
Watersht
Exit
Select Watersheds
rsheds to include in the screening by clicking the Select
button below. To clear your selections, dick the Clear
Selections button.
HUC8
C HUC12
Select Watersheds
Clear Watershed Selections
HUC8 ID
03150101(Conasauqa)
05110002 (Barren)
05130101 (Upper Cumberland)
05130103 (Upper Cumberland-Lake Cumberland)
05130104 (South Fork Cumberland)
9
-------
Setup Worksheet-Select Watersheds to Screen
Select Watersheds
Select watersheds to Include in the screening by clicking the Select
Watersheds button below. To dear your selections, click the Clear
Watershed Selections button.
<• HUCS
c HUC12
Select Watersheds
Clear Watershed Selections
HUC8 ID
In the popup menu, highlight the watersheds you
would like to include in your screening by clicking on
their IDs/names.
Multiple watersheds can be highlighted by dragging
the mouse cursor down or by holding the Ctrl key and
then clicking on each watershed ID individually.
Click the Add Selected Watersheds button to add the
highlighted watersheds to the list of watershed IDs for
screening.
You still have the opportunity to select additional
watersheds after clicking the Add Selected
Watersheds button. Repeat the previous step until all
watersheds of interest have been selected for the
screening, then click the Exit button to close the
popup menu.
Select Watersheds-
Select watersheds to include in your screening by highlighting their names fi the box
below and dsdting the Add Selected Watersheds button.
Or ckk the Add Al Watersheds button to select al watersheds for your screening.
06010207 (Lower Clinch)
06010208 (Emory)
06020001 (Middle Tennessee-Chickamauga)
06020002 (Hiwassee)
06020003 (Ocoee)
06020004 (Sequatchie)
06030001 (Guntersville Lake)
06030002 (Wheeler Lake)
06030003 (Upper Elk)
06030004 (Lower Elk)
06030005 (Pickwick Lake)
06040001 (Lower Tennessee-Beech)
06040002 (Upper Duck)
06040003 (Lower Duck)
06040004 (Buffalo)
J
Add Selected Watersheds
Select wai
Watershed
Watershed
Add AH Watersheds
Exit
Select Watersheds
sheds to include in the screening by clicking the Select
button below. To dear your selections, click the Clear
Selections button.
HUCS
HUC12
Select Watersheds
Clear Watershed Selections
HUC8 ID
106020001 (Middle Tennessee-Chickamauga)
06020002(Hiwassee)
06020003(Ocoee)
06020004 (Sequatchie)
06030001 (Guntersville Lake)
06030002 (Wheeler Lake)
06030003 (Upper Elk)
06030004 (Lower Elk)
06030005 (Pickwick Lake)
10
-------
Setup Worksheet-Select Watersheds to Screen
Select Watersheds to Screen Option 3 -
Codv and Paste Watershed IDs
To use this options you must already have a
pre-existing list of watershed IDs to screen in a
separate worksheet. The list of watershed IDs
must be formatted with one ID per row.
Open the worksheet containing the pre-existing
list of watershed IDs to screen. Select the list
and click "Copy" on the Excel menu.
10260006
10260007
INSI
10260010
10260011
10260012
* Format Painter
Clipboard n
10260013
10260014
10260015
10270101
13
14
15
16
17
IS
19
20
21
22
Select Watersheds
Select watersheds to include in the screening by clicking the Select
Watersheds button below. To de3r your selections, dick the Clear
Watershed Selections button.
HUC8
c HUC12
Select Watersheds
Go back to the Setup worksheet and set the
cursor to cell A24 (the first cell of the
watershed ID list in the Select Watersheds
section.
Clear Watershed Selections
| 24
Select "Paste Values" from the Excel menu to
paste the watershed IDs into the Select
Watersheds section.
Select Watersheds
Select watersheds to include in the screening by dieting the Select
Watersheds button below. To dear your selections, click the Clear
Watershed Selections button.
C HUCS HUC12
Select Watersheds
Clear Watershed Selections
HUC8 ID
24 10260006
25 10260007
26 10260008
27 10260009
28 10260010
29 10260011
30 10260012
31 10260013
32 10260014
°~1e Options
Values (V) ^ ^
33 10260015
34 10270101
11
-------
Setup Worksheet-Select Watersheds to Screen
Advanced Tips
¦ The watershed ID list in the Select Watersheds section must begin in cell A24 of the Setup worksheet and must contain
one watershed ID per row.
¦ Watershed names alone cannot be added to the watershed ID list. Watershed names can be included in the text but
must occur after the watershed ID. For example, the Jacks River HUC12 (HUC12 ID 031501010102) can be entered as
"031501010102" or "031501010102 (Jacks River)" but not "Jacks River".
¦ When manually typing watershed IDs or copying and pasting from another worksheet, be sure that all IDs are present
in column A of the Indicator Data worksheet for the watershed scale you are screening (e.g., the HUC12_Data
worksheet HUC12 subwatersheds are being screened). The tool cannot screen watersheds that are not present in the
Indicator Data worksheet.
¦ Use the HUC Subsets worksheet to store lists of watershed IDs that will be of interest for future screenings. For
example, you can use the HUC Subsets worksheet to store lists of HUC12 IDs within each HUC8 in the Project Area.
You can then copy and paste a group of HUC12s from the HUC Subsets worksheet to the Setup worksheet for a
screening run.
¦ Do not omit leading zeros from watershed IDs (e.g., "04030001" cannot be entered as "4030001"). Omitting leading
zeros will cause errors in the screening run.
¦ If HUC8 watersheds are being screened, the first 8 characters in each watershed ID are used to extract indicator values
from the indicator data worksheet. Any text after the eighth character is ignored.
¦ If HUC12 subwatersheds are being screened, only the first 12 characters in each watershed ID are used to extract
indicator values from the indicator data sheet. Any text after the twelfth character is ignored.
12
-------
Setup Worksheet-Select Indicators and Weights
3.4. Select Indicators and Weights
RPS index scores and ranks are calculated for each watershed included in a screening using ecological, stressor, and social
indicators. The choice of which indicators to use for a screening depends on the purpose of the screening and the nature
of the watersheds being screened. Thus, users should review the full list of indicators and their definitions in the Indicator
Info worksheet before selecting indicators on the Setup worksheet.
Basics
At least one ecological indicator, one stressor indicator, and one social indicator must be selected in order to perform a
Recovery Potential Screening run. Neutral indicator values are available in each category if one or more categories are not
of interest. In general, three to ten indicators per category are recommended. If too few indicators are selected, index
scores will not adequately reflect the factors influencing recovery potential. If too many indicators are selected, index
scores will reflect random noise in the indicator data rather than true patterns in recovery potential.
Indicator weights determine the relative influence of each indicator on index scores. Weights must be numeric but any
set of numeric values can be used. A typical approach is to select a certain number of weight categories (e.g., 3 = high; 2
= medium; 1 = low) and assign weights to indicators based on their relevance to the purpose of the screening and data
quality considerations.
Two options are available for selecting indicators and weights on the Setup worksheet. Step-by-step instructions for each
option are provided on the following pages.
13
-------
Setup Worksheet-Select Indicators and Weights
Select Indicators and Weights Option 1 - Select
from Popup Menu
Click on the Select Ecological Indicators button.
A popup menu will display with a list of
ecological indicators that can be selected for
screening.
In the popup menu, highlight the indicators you
would like to include in your screening by
clicking on their name.
Double-clicking an indicator name will display a
popup box with its description.
Click the Add Selected Indicators button to add
the highlighted indicators to the list of
indicators selected for screening.
You still have the opportunity to select
additional indicators after clicking the Add
Selected Indicators button. Repeat the previous
step until all indicators of interest have been
selected for the screening then click the Exit
button to close the popup menu.
Select Ecological Indicators
Select ecological indicators to include in the screening by clicking the
Select Ecological Indicators button below To clear your selections, dick
the Clear Ecological Indicator Selections button.
Select Ecological Indicators
Select Ecological Indicators
Select ecological indicators to include in the screening by clicking the
Select Ecological Indicators button below. To dear your selections, dick
the Clear Ecological Indicator Selections button
Select Ecological Indicators
Clear Ecological Indicator Selections
Ecological Indicator
Weight
Select Ecological Indicators™
|, ^ ,|
Select ecological indicators to indude in your screening by highlighting their names In the
box below and ckking the Add Selected Indicators button.
Double-dick any hdicator name in the box to view its description.
% Woody Vegetation (2006) fn Riparian Zone
% Natural Cover, N-indexl (2006) in Watershed
% Natural Cover, N-index2 (2006) in Watershed
% Natural Cover, N-indexl (2006) in HCZ
% Natural Cover, N-index 2 (2006) in HCZ
% Natural Cover, N-index (2006) in Riparian Zone
% Natural Cover, N-index 2 (2006) in Riparian Zone
% National Ecological Framework (NEF) in WS (2001)
NFHAP Habitat Condition Index
Ratio of Natural to Recycled N Inputs
Ratio of Natural to New N Inputs
Add Selected Indicators
Clear Ecological Indicator Selections
Ecological Indicator
% Woody Vegetation (2006) in Riparian Zone
% Natural Cover, N-index1 (2006) in Watershed
NFHAP Habitat Condition Index
Seled ecol
Select Ecol
the Clear
Exit
r£
Select Ecological Indicators
ileal indicators to include in the screening by dicking the
ical Indicators button below. To dear your selections, dick
'ogical Indicator Selections button.
Select Ecological Indicators
Clear Ecological Indicator Selections
Ecological Indicator
% Woody Vegetation (2006) in Riparian Zone
% Natural Cover, N-index1 (2006) in Watershed
NFHAP Habitat Condition Index
Weight
By default, indicators selected from the popup
menu will be assigned a weight of 1.
Indicator weights can be adjusted by placing the
cursor on the cell containing the weight and
typing in a new weight.
Repeat these steps in the Select Stressor Indicators section to select stressor indicators and weights and in the
Select Social Indicators section to select social indicators and weights.
14
-------
Setup Worksheet-Select Indicators and Weights
Select Indicators and Weights Option 2 -
Cody and Paste Indicator List
To use this option you must already have a
pre-existing list of ecological indicator names
and weights stored in a separate worksheet.
The pre-existing indicator list must be
formatted with one indicator per row with
the indicator name in the first column and
indicator weight in the second column.
Open the worksheet with the pre-existing
indicator list. Select the indicator names and
weights and click "Copy" on the Excel menu.
% N-lndexl in WS (2011)
Soil Stability, Mean in WS
% National rJdogical Framework (NEF) in WS (2001)
Past
HOME INSI
V Format Painter
Clipboard r>
D
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Select Ecological Indicators
(Select ecological indicators to include in the screening by clicking the
Se/ect Ecological Indicators button below. To clear your selections, click
the Clear Ecological indicator Selections button.
Select Ecological Indicators
Clear Ecological Indicator Selections
£rr>lAniral Inriiratrtr
Go back to the Setup worksheet and set the
cursor to cell C24 (the first cell of the indicator
list in the Ecological Indicators section).
Select "Paste Values" from the Excel
menu to paste the ecological indicators
and weights into the Select Ecological
Indicators section.
o
Select Ecological Indicators
Select ecological indicators to include in the screening by clicking the
Se/ec( Ecological Indicators button below. To clear your selections, click
the Clear Ecological Indicator Selections button.
Select Ecological Indicators
Clear Ecological Indicator Selections
% N-lndex1 in WS (2011)
Ecological Indicator
Soil Stability. Mean inWS
% National Ecological Framework (NEF) in WS (2001)
Weight
Repeat these steps in the Select Stressor Indicators section to select stressor indicators and weights and in the
Select Social Indicators section to select social indicators and weights.
15
-------
Setup Worksheet-Select Indicators and Weights
Advanced Tips
¦ The Ecological Indicator list must begin in cell C24 of the Setup worksheet and must contain one indicator per row.
¦ The Stressor Indicator list must begin in cell G24 of the Setup worksheet and must contain one indicator per row.
¦ The Social Indicator list must begin in cell K24 of the Setup worksheet and must contain one indicator per row.
¦ The Ecological, Stressor, and Social Indicator lists must be continuous (no blank rows). If blank rows are present, any
indicators below the first blank row will not be included in the screening.
¦ When copying and pasting from another worksheet, be sure that indicator names exactly match names in the header
row (row 7) of the Indicator Data worksheet for the selected watershed scale (e.g., the HUC12_Data worksheet if
HUC12 subwatersheds are being screened). Any differences in indicator spelling, capitalization, or whitespace will
result in errors when the screening is run.
¦ Indicator weights can be set to zero. A weight of zero means that the indicator has no effect on index scores.
¦ Indicator weights can be set to negative values. A negative weight will reverse the directional effect of the indicator
on index scores. The standard directionality is for larger indicator values to increase Ecological Index, Stressor, and
Social Index scores. A negative weight will reverse directionality so that smaller values increase Ecological Index,
Stressor, and Social Index scores.
¦ For ecological and social, higher numbers indicate watershed health, in stressor, higher numbers indicate watershed
stress.
¦ Indicator selections within each category (Ecological, Stressor, and Social) should be topically diverse. A diverse set of
indicators will reflect a range of factors relevant to recovery potential rather than one single topic.
¦ Users may want to consider numeric redundancy as part of the indicator selection process. Non-redundant indicators
can be identified by examining correlation coefficients between indicator pairs.
¦ Up to 20 indicators can be selected in each category.
16
-------
Setup Worksheet-Run Screening Button
3.5. Run Screening Button
Basics
After selecting watersheds, indicators, and weights for your screening, use the RUN SCREENING button to auto-calculate
Ecological Index, Stressor Index, Social Index, and Recovery Potential Integrated (RPI) Index scores for each watershed.
Clicking the RUN SCREENING button will populate other worksheets in the RPS Tool with screening results. Any results
from previous screenings will be cleared from other worksheets in the RPS Tool when the RUN SCREENING button is
clicked.
When the RUN SCREENING button is clicked from the Setup worksheet, the tool will first check to verify that
watersheds, indicators, and weights have been selected by the user. The tool will then look for potential issues
with indicator data.
If no data issues are found, index scores will be automatically calculated and screening results will be added to
other worksheets in table, plot, and map form. If indicators data issues are identified, a warning message will
appear. Indicator data warnings are described in the next section.
RUN SCREENING RESET SCREENING
Select Watersheds
Seied watersheds »o include in the screening by diddng
the Select Watersheds button below To dear your
selections, dick the Clear Watershed Selections button
HUC8 HUC12
Select Watersheds
Select Ecological Indicators
Seied ecological indicators to indude m the screening by
clicking the Select Ecological indicators button below To dear
y our selections, dick the Clear Ecological indicator Selections
button
Select Ecological Indicators
Select Stressor Indicators
Seled stressor indicators to indude in me screening by clicking
the Select Stressor incteators button betow To dear your
selections, dick the Clear Stressor Indicator Selections button
Select Stressor Indicators
Select Social Indicators
Seied social indicators to indude in the screening by dicking
the Select Social indicators button below To dear your
selections, did: the Clear Social Indicator Selections button
Select Socal Indicators
Clear Watershed Selections
Char Ecological Indicator Selections
Clear Stressor Indicator Selections
Clear Social Indicator Selections
HUC12ID
Ecological indicator
Weight
Stressor Indicator
Weigh!
Social fornicator
Weight
[031501010101 (Headwaters Conasuaga River)
% Woo
3
% Agncutture in VVS (2011)
2
% Streamlength Assessed (2015)
1
031501010103 (Ball Play Creek-Conasauga River)
Habitat Condition Index WS (2015)
2
% Streamlength Near » 15% Imperious Cover (2011)
2
% Water&odv Area Assessed (2015)
1
031501010104 (Old Fort Creek4ttll Creek)
Soil Stability Mean in HCZ
2
Synthetic N Fertilizer Application in WS
3
Count Ratio TMDLs to Impairments (2015)
1
031501010105 (Perry Creek-Conasuaga River)
Watershed Unique 303
-------
Setup Worksheet-Run Screening Button
Advanced Tips
¦ After clicking the RUN SCREENING button an error message may appear. A screening will not successfully run to
completion and an error message will be displayed if any of the following errors occur:
Watersheds, indicators, and/or weights are not specified.
One or more watershed IDs entered in the Select Watersheds section do not have an exact match in column
A of the Indicator Data worksheet for the watershed scale you are screening (e.g., the HUC12_Data worksheet
if HUC12 subwatersheds are being screened).
One or more indicator names entered in the Select Ecological Indicators, Select Stressor Indicators, or Select
Social Indicators sections do not have an exact match in the header row of the Indicator Data worksheet (e.g.,
the HUC12_Data worksheet if HUC12 subwatersheds are being screened).
One or more of the indicators selected for screening contain non-numeric data in the Indicator Data
worksheet. Indicator data must be numeric (blank cells are allowed for missing data).
18
-------
Setup Worksheet-Respond to Warnings
3.6. Respond to Warning Messages
Basics
After clicking the RUN SCREENING button on the Setup worksheet, the tool will automatically check for potential issues
with indicator data, including:
n Missing indicator values. In most cases, each indicator will have a numeric value for every watershed selected for
screening in the Indicator Data worksheet. However, indicator values may be missing for one or more watersheds.
Missing indicator values are denoted as blank cells in the Indicator Data worksheet. If a watershed is missing indicator
values, then RPS index scores for that watershed will be calculated from non-missing indicators only. In rare cases, a
watershed will be missing values of all ecological, stressor, and social indicators selected for the screening. If a
watershed is missing values for all indicators, RPS index scores cannot be calculated for that watershed,
n Equal-value indicators. Equal value indicators are indicators that have the same exact value for every watershed
selected for screening. Equal value indicators therefore provide no information on recovery potential differences
between watersheds. RPS index calculations are based on the assumption that indicator values vary between
watersheds and cannot be applied to equal value indicators. An equal-value indicator can be included in a screening
but normalized values of the equal-value indicator are set to 0.5 for all watersheds to prevent index calculation errors.
19
-------
Setup Worksheet-Respond to Warnings
Advanced Tips
¦ Users can remove indicators with missing values or equal-value indicators by clicking No on the warning message box
and deleting the names of indicators from the Select Ecological Indicators, Select Stressor Indicators, and/or the
Select Social Indicators sections.
¦ Decisions on whether to remove indicators with missing values can be based on the number of watersheds with
missing data. Missing indicator values are denoted as blank cells on the indicator data worksheet. As the number of
watersheds with missing data increases, the value of the indicator for describing recovery potential differences
between watersheds decreases. If most of the watersheds selected for screening are missing values of an indicator, it
is likely advantageous to remove that indicator from the screening, particularly if it can be replaced with another
indicator with a more complete record that describes a similar watershed characteristic.
¦ Equal-value indicators should be removed from a screening since they provide no information on recovery potential
differences between watersheds. The exception is when a user intentionally selects an equal-value indicator as the
only indicator for one of the Ecological, Stressor, or Social groups to evaluate the effect on RPI index scores. For
example, selecting an equal-value indicator as the only Social indicator will nullify the effect of Social Index scores on
the RPI Index since all watersheds will receive an equal Social Index score.
20
-------
Setup Worksheet - Reset Screening Button
3.7. Reset Screening Button
The RESET SCREENING button on the Setup worksheet can be used to produce a "clean slate" version of the RPS Tool Do
not click the RESET SCREENING button unless you have saved the current screening setup and results under a separate
filename or you are sure you do not want to store the current screening setup and results for future use.
Clicking the RESET SCREENING button will clear s
II watershed and indicator selections from the Setup worksheet
and any screening results stored on other worksheets. The only worksheets not affected are the Indicator Data
worksheets (e.g., HUC12_Data).
RUN SCREENING
RESET SCREENING
1
Select Watersheds
Select watersheds to include in the screening by chtfong
the Select Watersheds button below To clear your
seledlons, dick the Clear Watershed Selections button
Select Ecological Indicators
Select ecological indicators to include in me screening By
clicking the Select Ecological Indicators button below To dear
your selections, dick Die Clear Ecological Indicator Selections
Select Stressor Indicators
Seied stressor in<»c3tors to indude In the saeening by dlckjng
the Setecf Stressor lnd
-------
Notes Worksheet
4. Notes Worksheet
The Notes worksheet provides users with a space to document information related to their screening run, including a
screening run name, screening objectives, watershed scale screened, and notes on indicator selection and weighting.
Filling the Notes worksheet is optional, its contents are not used in any of the auto-calculation methods in the RPS Tool.
j
6
Screening Run Name:
Tennessee Urban/Suburban Nutrient Screening
7
8
9
Objectives:
Identify Tennessee HUC12s with significant urban/suburban sources of nurtients
that are restoration priorities.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Watershed Scale:
HUC12
Indicator Selection Notes:
Focus is on indicators relevant to recovery from urban/suburban sources of nutrients.
INSTRUCTIONS I Notes | Setup Results Bubble_Plot Bubble_Plot_Options HUC8_Map
22
-------
Results Worksheet - Overview
5. Results Worksheet
5.1. Overview
Basics
The Results worksheet displays a table of Ecological Index, Stressor Index, Social Index, and Recovery Potential Integrated
(RPI) Index scores and ranks for each watershed selected for screening. Also displayed are values of the ecological,
stressor, and social indicators selected for the screening. All base indicator values are also displayed to allow for sorting
or filtering according to base indicator values.
Index scores and ranks are automatically calculated and added to the Results worksheet after clicking the RUN SCREENING
button on the Setup worksheet. Watersheds are displayed in the same order as they are entered in the Select Watersheds
section of the Setup worksheet.
Ecological Index, Stressor Index, and Social Index scores are calculated as the average of weight-adjusted, normalized
indicator values. RPI scores are calculated from Ecological Index, Stressor Index, and Social Index scores. Index scores can
be interpreted using the following guidelines:
¦ Higher Ecological Index scores correspond to higher recovery potential.
¦ Lower Stressor Index scores correspond to higher recovery potential,
m Higher Social Index scores correspond to higher recovery potential
n Higher RPI Index scores correspond to higher recovery potential.
Watershed ID
Watershed Name
Ecological Index -
Ecological Rank ¦
Stressor Index •
Stressor Rank •
Social Index
Social Rank
RPI Score •
RPI Rank •
[031501010101
sadwaters Conasuaga River
53.98
78
2.10
93
19.38
743
57.09
177
031501010102
Jacks River
53.17
84
0.01
7
43.97
58
65.71
7
'031501010103
Ball Play Creek-Conasauga Rfver
50.06
160
3.13
156
39.44
125
62.13
42
'031501010104
Old Fort Creek-Mill Creek
41.22
582
21.81
917
38.87
131
52.76
451
'031501010105
Perry Creek-Conasuaga River
43.14
481
11.60
599
31.48
287
54,34
333
'031501010106
Sugar Creek
45.38
362
13.53
671
21.59
652
51.15
598
031501010301
Coahulla Creek Headwaters
42.16
537
14.59
717
26.53
471
51.37
573
031501010302
Mills Creek
37.20
812
13.53
671
21.37
662
48.35
809
031501010303
Coahulla Creek
44.65
408
9.14
475
15.52
897
50.34
672
'050500010102
Big Laurel Creek
51.47
118
4.44
235
7.50
1103
51.51
560
'050500010103
Headwaters North Fork New River
46.73
292
4.08
212
14.83
928
52.49
471
'050500010105
Biq Horse Creek
49.44
173
6.26
311
7.73
1099
50.30
675
"051100020101
Little Trace Creek-Line Creek
35.39
910
14.89
730
22,32
629
47.61
842
'051100020102
Trace Creek-Line Creek
35.82
885
14.94
732
22.94
602
47.94
826
051100020105
Long Fork
39.16
705
25.14
979
15.63
891
43 22
1005
05110 0020106
Salt Lick Creek
40.38
631
15,10
737
19.82
729
48.37
806
'051100020108
Puncheon Creek
36.82
833
15.30
747
18.29
789
46.60
901
051100020109
Sugar Creek-Barren River
41.01
597
15.37
750
2.98
1139
42.87
1020
'051100020201
Upper Long Creek
36.27
863
16.23
787
16.23
875
45.42
942
'051100020203
Pinchgut Creek-Barren River
40.81
606
13.25
656
22.58
620
50.05
699
'051100020501
804
19.23
863
28.57
394
48.88
767
< ~
Setup 1 Results
Bubble.Plot Bubble.Plot.Options HUC12_Map HUC8_Data
HUC12_Data
Advanced Tips
The formula used to calculate each weight-adjusted normalized indicator value is:
(Ind — hid.Mi„)
IndN0rm = Weight « — — -
VlnaMaX ~ maMin)
where lndNorm is the weight-adjusted normalized indicator value, weight is the indicator weight, Ind\s the raw indicator
value, lndMin is the minimum indicator value for watersheds selected for screening, and lndMax maximum indicator
value for watersheds selected for screening.
RPI scores are calculated from Ecological Index, Stressor Index, and Social Index scores using the formula:
[Ecological Index + Social Index + (100 — Stressor Index)]
P - -
Ecological Index, Social Index, and RPI Index ranks are determined by sorting index scores from highest to lowest (i.e.,
the watershed with the highest Ecological Index score receives a rank of 1).
Stressor Index ranks are determined by sorting index scores from lowest to highest (i.e., the watershed with the lowest
Stressor Index score receives a rank of 1).
23
-------
Results Worksheet - Sort and Filter
5.2. Sort and Filter
By default, the Results table displays indicator data and index scores for all watersheds included in the screening and
sorted by watershed ID. Users can sort the Results table based on values in any column in the table (e.g., from highest to
lowest Ecological Index score). Users can also filter the Results table to only display watersheds that meet certain criteria
(e.g., watersheds within a particular ecoregion).
Dinarian 7nno ID7V in VA/atopsh6d
67705639
75266778
56091445
58351704
63491534
06067519
03809443
60551596
23969159
23682768
60131782
55719195
61391447
08013937
43142814
*1 Sort A to Z
SgrtZtoA
Put Selected £ell Color On Top
Put Selected Font Color On Top
Put Selected Cell Icon On Top
rm Ciistom Sort...
J Insert Comment
[HI Format Cells...
Picl; From Drop-down List.
Define Name,..
Hyperlink...
EcoRegion (2010) Level 3,1st Code (Largest Area) ~T
21 Sort Smallest to Largest
II Sort Largest to Smallest
| Sott by Color » .
Number Filters
Search
Cancel
To sort the Results table, right-click on the
name of the column that you would like to use
for sorting. Scroll to Sort in the popup menu
and select a sorting option.
<& cm
life Copy
l|Q Paste Options:
i—t tA< A
.•?. 0
Paste Special...
Insert...
Delete-
Clear Contents
Quick Analysis
H Hydroloqically Connecti
To filter the Results table, click the drop-down
arrow next to the column you would like to use
to filter and select a filter option.
24
-------
Bubble Plot Worksheet
6. Bubble Plot Worksheet
The Bubble Plot worksheet contains a bubble plot that displays RPS results.
The Bubble Plot is automatically created when the RUN SCREENING button is clicked on the Setup worksheet. It contains
one "bubble" for each watershed selected for screening, with the Stressor Index score plotted on the horizontal (x) axis,
Ecological Index score plotted on the vertical (y) axis, and Social Index plotted as the bubble size. Larger bubbles have
higher Social Index scores relative to smaller bubbles.
The bubble plot can be customized using controls on the Bubble Plot Options worksheet.
The bubble plot displays Ecological Index, Stressor Index, and Social Index scores for watersheds included in your
screening. Hover over any bubble with your mouse cursor to view a popup information box with index scores and
ranks for that watershed.
Falling Water River Upper (051301080702)
Ecological Index: 17
Stressor Index: 59
Social Index: 24
RPI Score: 27.216
RPI Rank: 48
X
V
"O
OS
o
m,
10
20
30
n
Oo CP
o
o
o
70
40 50 60
Stressor Index
Note: Circle size increases with Social Index score
80
90
100
Setup Results I Bubble_Plot I Bubble_Plot_Options HUC12_Map HUC8_Data HUC12_Data
25
-------
Bubble Plot Options Worksheet-Overview
7. Bubble Plot Options Worksheet
7.1. Overview
The Bubble Plot Options worksheet provides users the ability to customize the Bubble Plot worksheet that displays
screening results.
REPOSITION AXES
By default axes cross at the median or Ecological Index {horizontal axis)
and Stressor Index (vertical axis) scores listed In the Summary.Scores
sheet. Axes can be repositioned to:
|(a) User-Defined Percentiles
This option will position axes to percentiles entered below
Enter a new Stressor Index percentile for the y-axis (0-100):
I Ml
Enter a new Ecological Index percentile for the x-aas (0-100):
1
REPOSITION AXES
SELECT SCREENING TO PLOT
If your screening focuses on a subset
-------
Bubble Plot Options Worksheet-Reposition Axes
7.2. Reposition Axes
The horizontal axis and vertical axis of the Bubble Plot can be set to any user-defined position.
By default, the horizontal axis is positioned at the median of Ecological Index scores for the active screening and the
vertical axis is positioned at the median of Stressor Index scores.
Users have two options for repositioning Bubble Plot axes:
¦ Reposition to new index percentiles — Under this option, users can reposition axes to any desired percentile of
Ecological Index and Stressor Index scores (25th percentile, 75th percentile, etc.);
¦ Reposition to specific index scores - Under this option, users can reposition axes to any desired Ecological and Stressor
Index score (e.g., index scores of 50).
Reposition Axes Option 1 -
Reposition to New Percentiles
To reposition axes to new percentiles, select
the "User-Defined Percentiles" option from the
drop-down menu in the Reposition Axes
section of the Bubble Plot Options worksheet.
Then enter new a new Stressor Index percentile
for the vertical axis and a new Ecological Index
percentile for the horizontal axis in the
designated cells.
REPOSITION AXES
By default axes cross at the median of Ecological Index (horizontal axis)
and Stressor Index (vertical axis) scores listed in the Summary_Scores
sheet. Axes can be repositioned to:
| (a) User-Defined Percentiles
This option will position axes to percentiles entered below.
Enter a new Stressor Index percentile for the y-axis (0-100):
"3
Enter a new Ecological Index percentile for the x-axis (0-100):
REPOSITION AXES
RESET AXES
Enter a new Stressor Index percentile for the y-axis (0-100):
75I
Enter a new Ecological Index percentile for the x-axis (0-100):
751
Reposition Axes Option 2 -
Reposition to Specific Index Scores
To reposition axes to specific index scores,
select the "User-Defined Index Scores" option
from the drop-down menu in the Reposition
Axes section of the Bubble Plot Options
worksheet.
Then enter new Stressor Index score for the
vertical axis and a new Ecological Index score
for the horizontal axis in the designated cells.
REPOSITION AXES
By default, axes cross at the median of Ecological Index (horizontal axis)
and Stressor Index (vertical axis) scores listed in the Summary_Scores
sheet Axes can be repositioned to:
|(b) User-Defined Index Scores *]
This option will position axes to Index Scores entered below.
Enter a new Stressor Index score for the y-axis (0-100):
Enter a new Ecological Index score for the x-axis (0-100):
I
REPOSITION AXES
RESET AXES
Enter a new Stressor Index score for the y-axis (0-100):
40
Enter a new Ecological Index score for the x-axis (0-100):
60l
After entering new index percentiles or scores,
click the REPOSITION AXES button.
Axes on the Bubble Plot will be automatically
moved to the new index percentiles or scores.
RESET AXES
REPOSITION AXES
To move Bubble Plot axes back to their default
position, click the RESET AXES button.
27
-------
Bubble Plot Options Worksheet- Toggle Screening to Plot
7.3. Toggle Screening to Plot
By default, the Bubble Plot displays Ecological Index, Stressor Index, and Social Index scores listed in the Summary Scores
worksheet. These scores are derived from indicators that are normalized to minimum and maximum values for the subset
of watersheds included in the screening, Index scores are therefore relative rather than absolute, they depend on the
subset of watersheds selected for screening. A given watershed will have two different sets of index scores for two
screenings that include different groups of watersheds.
Users may be interested in how the subset of watersheds selected for screening compares to ail other watersheds in the
Project Area. For example, users may want to know whether ecological conditions in the screened subset are above- or
below-average for the Project Area as a whole. Such questions can be answered by running the same screening on all
watersheds in the Project Area and then reviewing the resulting index scores for the watershed subset of interest. To
streamline this process, the RPS Tool allows users to view an alternate version of the bubble plot with index scores that
result from a screening that includes all watersheds in the Project Area. This can be done on-the-fly in the Select Screening
to Plot section of the Bubble Plot Options worksheet without adjusting settings on the Setup worksheet or creating a new
file.
The Select Screening to Plot section contains option buttons to toggle between a "Subset Only" screening or an "All
Watersheds" screening. A "Subset Only" screening includes only those watersheds selected for screening on the Setup
worksheet. An "All Watersheds" screening includes all watersheds in the Project Area. Note that if you selected all
watersheds in the Project Area on the Setup worksheet then there is no difference between the two options.
SELECT SCREENING TO PLOT
If your screening focuses on a subset of watersheds from the project
area, you may want to know if Ecological and Stressor Index scores for
your subset are generally above or below average for the project area.
Clicking the "ALL WATERSHEDS SCREENING" option below will plot
scores from a screening that includes all watersheds in the project
area, with axes set to the median of all Ecological and Stressor Index
scores. Clicking the "SUBSET ONLY SCREENING" option will return to
the default option of plotting scores from the screening that only
includes those watersheds selected on the Setup sheet.
® SUBSET ONL Y SCREENING
C ALL WATERSHEDS SCREENING
By default, the Bubble Plot displays index scores resulting from "Subset Only" screening.
Click the ALL WATERSHEDS SCREENING option button to view index scores resulting from a screening that
includes all watersheds in the Project Area. The Bubble Plot worksheet will automatically update with new index
scores for the "All Watersheds" screening.
If you have viewed bubble plot for the "All Watersheds" screening and want to return to the "Subset Only"
screening bubble plot, click the SUBSET ONLY SCREENING button. The Bubble Plot worksheet will automatically
update with index scores for the "Subset Only" screening.
28
-------
Bubble Plot Options Worksheet- Label Bubbles
7.4. Label Bubbles
Bubbles in the Bubble Plot worksheet can be labeled to identify one or more watersheds. Bubbles can be labeled with the
watershed name or the watershed ID. Users also have the option to highlight labeled bubbles to make them stand out
from other unlabeled bubbles.
Basics
To label bubbles, first select a label option from
the drop-down menu in the Add Labels to
Bubbles section of the Bubble Plot Options
worksheet.
Users can choose to have labels display
watershed names or watershed IDs.
ADD LABELS TO BUBBLES
Select a watershed identifier for labeling:
d
Click the button below to label all bubbles:
ADD ALL LABELS
Or. select watersheds individually from the list
below (repeat to label multiple watersheds):
31
r Highlight Selected Watershed
Note: To hightiight the watershed, the box above must be
checked BEFORE selecting from the drop-down list
Label locations can be changed by clicking on the
label in the bubble plot and dragging to the
desired location.
Click the button below to remove labels and
highlighting:
REMOVE LABELS I
Add Labels Ootion 1 - Label All Bubbles
If you would like to add labels to all bubbles on the Bubble
Plot worksheet, click the ADD ALL LABELS button. Labeling all
Click the button below to label all bubbles:
_ ADD ALL LABELS
bubbles is generally not recommended if your screening
includes more than 20 watersheds.
29
-------
Bubble Plot Options Worksheet- Label Bubbles
Add Labels Option 2 - Label Bubbles Individual!
To label individual bubbles in the Bubble Plot, select the
watershed to label from the drop-down menu in the Add
Labels to Bubbles section of the Bubble Plot Options
worksheet. Watersheds names are displayed in the drop-
down menu alphabetically from A to Z. Watershed IDs are
displayed in ascending numerical order.
To highlight the labeled bubble, check the Highlight Selected
Watershed box before selecting a watershed from the drop-
down menu.
Or, select watersheds individually from the list
below (repeat to label multiple watersheds):
Fall Creek
Falling Water River Headwaters
Falling Water River Lower
Falling Water River Middle
Falling Water River Upper
Glade Creek
Hickman Creek
Indian Creek
3 »
'5b
20
10
90
80
70
60
50
40
30
20
10
0
o
o
M
c
V
j
i _
o
L
..
O
Cn
p8
CP
Fall Creek
o>
Oq
o
o
y
(
fc~!
o
[7 Highlight Selected Watershed
A label will be automatically added to the
bubble for the selected watershed.
10 20
3C
40
SO 60
Stressor Index
70
80
o
"V*
• -
o
0
u
L
rC
L ^
C_|i /¦_„„I.
o
Ol
r\
&
Oo
A
CP
rail Creek
#-
I
(
D
o
1
0
O
The labeled bubble will also be highlighted if
the Highlight Selected Watershed box is
checked when selecting the watershed from
the drop-down menu.
SO 60
Stressor Index
REMOVE LABELS
To remove labels from the Bubble Plot, click the
REMOVE LABELS button. This will also remove
any highlighting from labeled bubbles.
30
-------
Bubble Plot Options Worksheet-Label Bubbles
Advanced Tips
Label text, formatting, and position can be edited manually after labels are added.
To edit label text, click on the label in the Bubble Plot worksheet and revise the text as desired
To edit label formatting (font size, color, etc.) click on the label in the Bubble Plot worksheet and use Excel's font
formatting menu.
To edit the position of a label, click on the label in the Bubble Plot worksheet and drag it to the desired location.
31
-------
Bubble Plot Options Worksheet-Edit Bubble Sizes
7.5. Edit Bubble Sizes
The size of each bubble in the Bubble Plot is based on the Social Index score for the corresponding watershed (i.e., bubbles
for watersheds with higher Social Index scores are larger than bubbles for watersheds with lower Social Index scores).
While users cannot adjust the size of an individual bubble, they can collectively increase or decrease the size of all bubbles
to improve the look of the Bubble Plot.
A common reason for editing bubble sizes is to reduce overlap between bubbles with similar Ecological Index and Stressor
Index scores.
To adjust bubble sizes, enter a new size in the
Edit Bubble Sizes section of the Bubble Plot
Options worksheet.
Bubble sizes can range from 1 (small) to 300
(large). The default bubble size is 20.
Click the RESIZE BUBBLES button to apply the
new bubble size. The Bubble Plot worksheet
will automatically be updated with the new
bubble sizes.
To return to the default size, click the RESET
BUBBLE SIZES button.
EDIT BUBBLE SIZES
Enters bubblesize (1 -300):
iol
— ¦
The default Bubble size is 20.
Click the button below to resize
RESIZE BUBBLES
RESET BUBBLE SIZES
32
-------
Bubble Plot Options Worksheet- Edit Bubble Colors
7.6. Edit Bubble Colors
The Bubble Plot worksheet displays three pieces of information for each watershed included in your screening: the
Ecological Index score on the y-axis, Stressor Index score on the x-axis, and Social Index score using the bubble size. The
Bubble Plot worksheet can also display a fourth piece of information by shading bubbles different colors to reflect different
values of an indicator. This feature is implemented in the Edit Bubble Colors section of the Bubble Plot Options worksheet.
To display indicator values using bubble colors, users must select the number of Classes to display, a method for defining
Class Breaks, and a Color Scheme. These terms are defined below.
¦ Classes - The number of groups or bins to separate watersheds into. Up to 10 classes can be selected.
¦ Class Breaks - The cutoff indicator values used to define class membership. Three options are available for defining
class breaks:
Ouantile Breaks result in classes that have an equal number of watersheds. For example, if four classes are
selected then then quantile breaks option would result in each class containing one-quarter of the total
number of watersheds using the 25th percentile, 50th percentile, and 75th percentile of indicator values as class
breaks.
Equal-Interval Breaks result in classes that cover an equal range of indicator values. For example, if four classes
are selected for an indicator with a minimum value of 0% and a maximum value of 100%, then the equal-
interval breaks option would result in each class spanning 25 percentage points use values of 25%, 50%, and
75% as class breaks. Under the equal-interval option, the definition of breaks is independent of the number
of watersheds within each class. Depending on the distribution of indicator values, some classes may contain
zero watersheds.
User-Defined Breaks are supplied by the user.
¦ Color Scheme - The primary color(s) used to shade bubbles. Options are blue, green, or blue-green.
To display values of an indicator on the Bubble
Plot worksheet using bubble colors, first select
which indicator you would like to plot from the
indicator drop-down menu in the Edit Bubble
Colors section of the Bubble Plot Options
worksheet.
Any indicator can be selected for plotting,
regardless of whether it was included in your
screening or not.
Index scores such as the Recovery Potential
Integrated (RPI) Index can also be selected.
EDIT BUBBLE COLORS
Select the indicator that will determine bubble colors:
|% Natural Cover. N-indexl (20061 in Watershed
Select a classification method |~~
Select the number of classes: |
"3
Select a color scheme:
Click the button below to update bubble colors:
UPDATE BUBBLE COLORS
3]
"3
Legend
% Natural Cover, N-indexl (2006) in Watershed
Click the button below to reset bubble colors:
RESET BUBBLE COLORS
33
-------
Bubble Plot Options Worksheet- Edit Bubble Colors
34
-------
Bubble Plot Options Worksheet- Edit Bubble Colors
After you have selected the indicator to plot,
the method for defining class breaks, the
number of classes, and the color scheme, a
legend will display in the Edit Bubble Colors
section that illustrates your selections.
Click the UPDATE BUBBLE COLORS button to
apply the selected settings to the Bubble
Plot worksheet.
EDIT BUBBLE COLORS
Select the indicator that will determine bubble colors
|% Natural Cover. N-indexl (2006) in Watershed 3
Select a classification method: [user-Defined Breaks j*|
Select the number of classes: 4
Select a color scheme ] Blue-Green
Click the button below to update bubble colors:
UPDATE BUBBLE COLORS
Legend
% Natural Cover, N-indexl (2006) in Watershed
26.80
40.01
60.01
80.01
40
60
SO
94.07
After clicking the UPDATE BUBBLE COLORS button the Bubble Plot worksheet will automatically update to
shade bubbles according to the selected indicator. Users can adjust bubble color settings at any time by revising
selections in the Edit Bubble Colors section of the Bubble Plot Options sheet and clicking the UPDATE BUBBLE
COLORS button.
100 r
90 j~-
-------
Bubble Plot Options Worksheet-Save As Image File
7.7. Save As image File
A copy of the Bubble Plot worksheet can be saved as an image file for use in reports or presentations.
SAVE AS IMAGE FILE
Click the button below to save the
bubble plot as an image (jpg)file:
SAVE BUBBLE PLOT
The Bubble Plot can be saved as an image file
from the Save As Image File section of the
Bubble Plot Options worksheet.
First, click the SAVE BUBBLE PLOT button.
You will then be prompted to enter a filename
for the image file.
Type a filename into the space provided and
click OK.
Microsoft Excel
Chart Export
Please enter a filename.
The plot will be saved in the current directory as filename.jpg
-Test
OK
Cancel
File saved in current directory as Test.jpg
A record of the Setup and Notes worksheets have also been saved. ^
OK
A message box will notify you that the image
file saved in JPEG format using the filename you
provided.
The file will be saved in the same directory that
the RPSTool file is saved in.
Two PDF files storing the contents of the Setup
sheet and Notes sheet are also saved to provide
a record of the screening settings used to
produce the Bubble Plot image file.
36
-------
Map Worksheet- Overview
8. Map Worksheet
8.1. Overview
The Map worksheet that displays screening results in map form. The Map worksheet is automatically created when the
RUN SCREENING button is clicked on the Setup worksheet. By default, the Map worksheet displays Recovery Potential
Integrated (RPI) index scores.
The name of the Map worksheet displayed in the bottom tab of your RPSTool will reflect the scale of watersheds selected
for screening. For example, if you screened HUC12 subwatersheds, then the Map worksheet will be named HUC12_Map.
Most RPS tools have a map worksheet for HUC12 subwatersheds and, if the tool is also setup to screen HUC8 watersheds,
a map for HUC8s. RPS Tools that are setup to screen other watershed scales may not have a map for those additional
watershed scales.
RPI Score For HUC12 Watersheds
Legend
RPI Score
26.33 - 39.29
39.30 - 40.69
40.70 - 41.40
41.41 - 41.88
41.89 - 42.41
42.42 - 42.96
42.97 - 43.60
§ 43.61 - 44.55
44.56 - 46.27
46.28 - 53.78
Not Analyzed/No Data
« ... Setup Results Bubble_Plot | Bubble_Plot_Options | HUC12_Map | HUCS_Data
Map Options
Select an indicator to update HUC colors:
| RPI Score
Select the number of classes:
pTo 3
Select a classification method:
I Quantile Breaks
Select a color scheme:
| Blue-Green 3
Click the button below to update the map:
Update Map
Reset Map
Click the button below to highlight watersheds of interest:
Highlight Watersheds |
Click the button below to save the map as a PDF file:
Save Map as PDF file
HUC12_Data | Indicatorjnfo
The following sections describe how to:
m Create a Custom Map
¦ Highlight Watersheds of Interest
¦ Save the Map as a PDF File
37
-------
Map Worksheet-Create Custom Map
8.2. Create Custom Map
Users can customize the Map worksheet to shade watersheds on the map using any indicator or index. To customize the
Map worksheet, users must select the number of Classes to display, a method for defining Class Breaks, and a Color
Scheme. These terms are defined below.
¦ Classes - The number of groups or bins to separate watersheds into. Up to 10 classes can be selected.
¦ Class Breaks - The cutoff indicator values used to define class membership. Three options are available for defining
class breaks:
Ouantile Breaks result in classes that have an equal number of watersheds. For example, if four classes are
selected then the quantile breaks option would result in each class containing one-quarter of the total number
of watersheds using the 25th percentile, 50th percentile, and 75th percentile of indicator values as class breaks.
Equal-Interval Breaks result in classes that cover an equal range of indicator values. For example, if four classes
are selected for an indicator with a minimum value of 0% and a maximum value of 100%, then the equal-
interval breaks option would result in each class spanning 25 percentage points using values of 25%, 50%, and
75% as class breaks. Under the equal-interval option, the definition of breaks is independent of the number
of watersheds within each class. Depending on the distribution of indicator values, some classes may contain
zero watersheds.
User-Defined Breaks are supplied by the user.
¦ Color Scheme - The primary color(s) used to shade watersheds on the map. Options are blue, green, or blue-green.
Map Options 1
Select an indicator to update HUC colors:
Select a color scheme:
Click the button below to update the map:
Update Map
Select a classification method:
Select the number of classes:
% N-lndex1 in WS (2011)
Reset Map
Click the button below to highlight watersheds of interest:
Highlight Watersheds
Click the button below to save the map as a PDF file:
Save Map as PDF file
38
-------
Map Worksheet- Create Custom Map
Then select the number of classes to use for
mapping (between 2 and 10).
^ Next, select a method for defining class breaks
(Quantile, Equal-Interval, or User-Defined).
Select the number of classes:
Select a classification method:
If the Quantile or Equal-Interval options are
selected for defining class breaks, then break
values will be calculated automatically.
If the User-Defined option is selected, a popup
form will display for entering class breaks.
In the class break form, the lower bound of
the first class will automatically be set to the
minimum value of the selected indicator. The
upper bound of the last class will be set to the
indicator's maximum value.
Enter break values for each class in the "Upper
Bound" column. The lower bound of the next
class will be calculated automatically by
adding 0.01 to the upper bound you entered
for the preceding class.
When all breaks have been entered, click the
OK button.
Define Class Breaks.,,
Enter the upper bound for each class below.
Click OK when finished.
Lower
Bound
Class 1:
Class 2;
Class 3:
Class 4:
Class 5:
Class 6:
Class 7:
Class 8:
Class 9:
Class 10:
OK
Upper
Bound
to
25
to
SO
50.01 to
to
75|
100
to
to
to r
to r
to
to
Cancel
39
-------
Map Worksheet- Create Custom Map
Map Options
Select an indicator to update HUC colors:
|% N-lndex1 in WS (2011)
Select the number of classes:
[4 3
Select a classification method:
|user-Defined Breaks
Select a color scheme:
[Blue-Green"
Click the button below to update the map:
Reset Map
Click the button below to highlight watersheds of interest:
Highlight Watersheds [
Click the button below to save the map as a PDF file:
"3]
After you have selected the indicator to map,
the number of classes, the method for defining
class breaks, and the color scheme click the
UPDATE MAP button to apply the selected
settings to the watershed map.
Save Map as PDF file
The Map worksheet will automatically update to shade watersheds according to the selected indicator or index.
Users can adjust map settings at anytime by revising selections in the Map Options section of the Map
worksheet and clicking the UPDATE MAP button.
% natural Cover, U indexl (2006) in Watershed For HUC12 Watersheds
Legend
Reset Map
To return to the default map (displaying
Recovery Potential Integrated Index scores),
click the RESET MAP button. ^
40
-------
Map Worksheet-highlight Watersheds
8.3. Highlight Watersheds of interest
Map Options
Select an indicator to update HUC colors:
|% N-lndex1 in WS (2011)
d
Select the number of classes:
Select a classification method:
|user-Defined Breaks
Select a color scheme:
|Blue-Green *
Click the button below to update the map:
Update Map
Reset Map
Click the button below to highlight watersheds of inteu^r
Highlight Watersheds T
You can highlight one or more watersheds of
interest on the map by clicking the HIGHLIGHT
WATERSHEDS button.
Select Watersheds...
Choose watersheds to highbght n the map by selecting ther names in the box below and
clicking the Highlight Selected Watersheds button.
031501010101 (Headwaters Conasuaga River)
031501010102 (Jacks River)
031501010103 (Ball Play Creek-Conasauga River)
031501010104 (Old Fort Creek-Mi Creek)
031501010105 perry Creek-Conasuaga River)
031501010106 (Sugar Creek)
031501010301 (Coahulla Creek Headwaters)
031501010302 (Mills Creek)
031501010303 (Coahulla Creek)
050500010102 (Big Laurel Creek)
050500010103 (Headwaters North Fork New River)
050500010105 (Bia Horse Creek)
051100020101 (Little Trace Creek-Ine Creek)
051100020102 (Trace Creek-Line Creek)
051100020105 (Long Fork)
051100020106 (Salt Lick Creek)
051100020108 (Puncheon Creek)
051100020109 (Sugar Creek-Barren River)
A popup box will appear displaying a list of
watersheds in your tool's study area and design
options for highlighting the selected watersheds.
Choose the watersheds to highlight from the list
and your preferred options for the outline width
and color. Then click the HIGHLIGHT SELECTED
WATERSHEDS button.
The map will automatically update to add an outline
to the selected watersheds with the selected width
and color.
You can remove the highlighting from the map at
any time by clicking the REMOVE HIGHLIGHTING
button.
41
-------
Map Worksheet - Sa ve Map as PDF File
8.4, Save Map as PDF File
The watershed map ori the Map worksheet can be saved as a PDF file for use in reports or presentations.
42
-------
Indicator Data Worksheet
9. Indicator Data Worksheet(s)
The Indicator Data worksheet contains a table of indicator values for all watersheds in the Project Area covered by your
RPS Tool file.
Indicators are grouped by category in the Indicator Data worksheet and are ordered from left-to-right as Base, Ecological,
Stressor, and Social indicators. Indicator categories are displayed in row 6. Indicator names are displayed in row 7.
The name of the Indicator Data worksheet displayed in the bottom tab of your RPS Tool will reflect the watershed scale
of the indicator data it stores. For example, the Indicator Data worksheet for HUC12 subwatersheds is named
HUC12_Data. If your tool file includes multiple watershed scales (e.g., both HUC8 and HUC12) then it will contain multiple
Indicator Data worksheets, one for each scale (e.g., HUC8_Data and HUC12_Data).
6
BASE INDICATORS
7
Hydrologic Unit Code
12 Digit (HUC12)
Name HUC12 Watershed
Area Of Watershed (HUC12) In Square Meters (Grid)
Riparian Zone (R2) in Watershed
8
031501010101
Headwaters Conasuaga River
95344200.0000
23.6771
9
031501010102
Jacks River
127184400.0000
21.7527
10
031501010103
Ball Play Creek-Conasauga River
77161500.0000
26.5609
11
031501010104
Old Fort Creek-Mill Creek
63687600.0000
22.5835
12
031501010105
Perry Creek-Conasuaga River
54007200,0000
25.6349
13
031501010106
Sugar Creek
52865100.0000
24.0607
14
031501010301
Coahulla Creek Headwaters
117395100.0000
29.0381
15
031501010302
Mills Creek
34851600.0000
23.6055
16
031501010303
Coahulla Creek
67117500.0000
23.2397
17
050500010102
Big Laurel Creek
75533400.0000
24.2368
18
050500010103
Headwaters North Fork New River
107905500,0000
19.6013
19
050500010105
Big Horse Creek
91665000.0000
21.5572
20
051100020101
Little Trace Creek-Line Creek
102894300.0000
19.6139
21
051100020102
Trace Creek-Line Creek
75423600.0000
21.0801
22
051100020105
Long Fork
162519300.0000
22 4314
23
051100020106
Salt Lick Creek
144657000.0000
24.2095
24
051100020108
Puncheon Creek
75887100.0000
18,4098
25
051100020109
Sugar Creek-Barren River
93788100.0000
22.1411
26
051100020201
Upper Long Creek
134667000.0000
17.9656
27
051100020203
Pinch gut Creek-Barren River
75322800.0000
21.8456
28
051100020501
Headwaters Trammal Creek
66985200 0000
20.3203
29
051100020502
Rough Creek-Trammel Creek
62374500.0000
18.6740
30
051100020503
Little Trammel Creek
70362900.0000
19.4088
31
051100020601
West Fork Orakes Creek Headwaters
138340800.0000
19.1800
32
051100020602
Sharps Branch-West Fork Drakes Creek
136977300.0000
14.3689
33
051100020604
Dutch Creek-Middle Fork Drakes Creek
148890600.0000
17.9993
34
051100020605
Sulphur Fork Creek
115533000.0000
16.3613
35
051301010201
Little Yellow Creek-Yellow Creek
17 8163
Results Bubble.Plot Bubble.Plot.Options HUC12_Map HUCS.Data I HUC12_Data I Indicatorjnfo HUC.Subsets
43
-------
Explore Indicator Data Menu-Overview
10. Explore Indicator Data Menu
10.1. Overview
The RPS Tool includes built-in functions for exploring the statistical properties of indicator data. This information can be
useful when making decisions on which indicators to include in a screening.
Indicator statistics are displayed within the Explore Indicator Data menu. To open the menu, select an indicator data
worksheet tab (HUC12_Data, HUC8_Data, etc.). Then click the EXPLORE INDICATOR DATA button
^ This worksheet contains the complete base, ecological, stressor, and social indicator dataset for all HUC12 watersheds in the project area (sta
2 Indicators can be added to the table from the Add_lndicators worksheet.
2 Click the Explore Indicator Data button below to review indicator summary statistics, histograms, ecoregional variation, and correlations.
4
EXPLORE INDICATOR DA TA
5 A
Result
Bubble_Plot Bubble_Plot_Options HUC12_Map HUC8_Data
HUC12 Data
Indicator Info
HUC Subsets
I
Explore Indicator Data.,.
Use this interface to explore indicator data. To begin, select an indicator of interest from the drop-down menus below.
Select Indicator Type: |Ecological ~ Select Indicator Name: [~
By default, all watersheds in the project area are included in the data summary. Click the button below to select a subset of watersheds for data exploration.
Watershed Selection: I^ j ¦ 'stersheds Change Watershed Selection.., I
The shape of the histogram
reveals information about the
distribution of indicator values.
Histogram for data with normal
(bell-shaped) distribution.
Ecoregional Variation j Correlation |
This panel displays summary statistics and a histogram for the indicator selected above.
r
\
J
\
V
Value
Histogram for data with skewed
distribution.
Frequency
I
Value
Histogram for data with even
distribution.
n T~l
44
-------
Explore Indicator Data Menu - Overview
The Explore Indicator Data menu contains drop-down menus for selecting an indicator to explore and three tabs for
reviewing the statistical properties of the selected indicator:
¦ The Summary Stats tab displays summary statistics (minimum, maximum, mean, etc.) and a histogram of indicator
values;
¦ The Ecoregional Variation tab displays a boxplot of indicator summary statistics by Level 3 Ecoregion
(https://www.epa.gov/eco-research/level-iii-and-iv-ecoregions-continental-united-states). The tab also contains a
table listing the number of watersheds in each ecoregion and the number of watersheds with blank or missing values
of the selected indicator per ecoregion. The Ecoregional Variation tab is only visible when reviewing indicator data
for HUC12 watersheds.
¦ The Correlation tab contains additional drop-down menus for users to select a second covariate indicator to evaluate
correlation between two indicators. Correlation statistics and a scatterplot of indicator values are displayed in the tab.
By default, all watersheds in your tool's study area are included in the indicator data summary. The menu also contains
the CHANGE WATERSHED SELECTION button for selecting a custom subset of watersheds for the indicator data summary.
The following sections describe how to:
¦ Select an Indicator to Explore
¦ Select a Covariate Indicator to Review Correlation
¦ Save or Copy a Chart
¦ Use the Change Watershed Selection Button
45
-------
Explore Indicator Data Menu-Selectan Indicator to Explore
10.2. Select an indicator to Explore
To explore the statistical properties of an indicator, select the indicator of interest from the drop-down menus at
the top of the Explore Indicator Data menu.
By default, ah watersheds in the project area are included in the data summary. Click the button below to select a subset of watersheds for data exploration.
Watershed Selection: |/l;? Watersheds Change Watershed Selection... j
j| Ecoregional Variation ] Correlation |
First choose the indicator type (Base, Ecological, Stressor,
or Social) from the Select Indicator Type menu.
Select Indicator Type:
Ecological -
Base
Ecotoqol
Stressor
Social
Select Indicator Name: |
I hen choose the indicator name from the
Select Indicator Name menu.
1
1
% Forest m WS f2011)
¦ |
% Forest in HCZ (2011)
% Forest n R2 (2011)
% Forest Change ii WS (2001-11)
% Forest Change in HCZ (2001-11)
% Forest Change m RZ (2001-11)
% Forest RemaininQ in WS
v .
The Summary Stats and Ecoregional Variation tabs will automatically populate with statistical information for the
selected indicator. Click on each tab name to view its contents.
Summary Stats Ecoregional Variation | Correlation |
WflHIWflBWWiWraWHWBBlimBWirfll IUI Ull! IIIULlo, selected above.
<-4742 4.742- 9 484. 14.226- IS. 9GB- 23 71- 28.451- 33.193 - 37.935 • 42.G77- 47.419-
9464 14.226 18SG8 23.71 28*51 33. S3 37 935 42.677 47 419 52.161
52 161-
56 903
% Forest in WS (2011)
Save Chart to Fie Copy Chart to dpboard
Hinirum [o~~
Median 12.8366 25th Percentile 10.5312
Maximum 56.903
75th Percentile 11.2432
Standard Deviation
Number/Percent Blanks
|i!i(
0 (0%)
The shape of the histogram
reveals information about the
dstrtout/on of indicator values.
Histogram for data vrth normal
(be/shaped) distribution.
J.
Hetogram for data wth skewed
dstrbuton.
LA
Histogram for data with even
46
-------
Explore Indicator Data Menu - Select a Covariate Indicator
10.3. Select a Covariate Indicator
The Correlation tab of the Explore Indicator Data menu contains additional drop-down menus for users to select
a second covariate indicator to evaluate correlation between two indicators.
Summary Stats | Ecoregional Variation
Correlation
This panel displays information on the strength of correlation between th<
:or selected above and any other covariate indicator.
Select Covariate Indicator Type:
Ecological
Select Covariate Indicator Name:
First choose the indicator type (Base, Ecological, Stressor,
or Social) from the Select Covariate Indicator Type menu.
, Select Covariate Indicator Type: Ecological
-I Base
Ecological
Stressor
Social
Then choose the indicator name from the
Select Covariate Indicator Name menu.
% N-Indexi in WS (201!)
¦w
% N-lndsxl h WS (2011)
H
% N-Indexl in HCZ (2011)
% N-Indexl in RZ (2011)
% N-Indexl in HAZ (2011)
% N-Indexl Change ri WS (2001-11)
% N-Indexl Chang# in HCZ (2001-11)
% N-lndexl Change ri RZ (2001-11)
% N-Index2 in WS (2011)
~
The scatterplot and correlation statistics will automatically populate with values for the selected indicator and
the covariate indicator.
Summary Stats | Ecoregional Variation Correlation |
This panel displays information on the strength of correlation between the indicator selected above and any other covariate indicator.
Select Covariate Indicator Type: | Ecological j*] Select Covariate Indicator Name: |% N-Indexl in WS (2011)
o'
$ o
« *
20.000 30.000 40.000
% Forest in WS(2011)
The scatterplot ikistrates
whether values for the two
indicators vary together or
randomly.
Scatterplot showing strong
positive correlation
Scatterplot showing
moderate positive correlation
Scatterplot showing weak
correlation
Save Chart to File
Copy Chart to Clipboard
Correlation Coefficient (R)
10.9062
Coefficient of Determination (R-Squared)
|o.8211
47
-------
10.4. Save or Copy a Chart
Explore Indicator Data Menu-Save or Copy a Chart
The Summary Stats, Ecoregional Variation, and Correlation tabs each have buttons that allow users to save or
copy an image of the histogram, ecoregional boxplot, or correlation scatterplot.
Summary Stats | Ecoregional Variation Correlation
This panel displays summary statistics and a histogram for the indicator selected above. ^ drape of the histogram
reveals nfomaQon about the
(Hstr&vtMi of initiator values.
Hstoffrsm for data nth norm#
(bet-shaped) dstrtutm.
Uiijjji
Save Chart to File
Copy Chart to Clipboard
&
k
fUstooram for data with skeweC
dismbutm.
& L
1 j\
Histogram for data with even
dstrtvtm.
H~U r~n_
A copy of your chart can be saved as an image file
in JPEG format by clicking the SAVE CHART TO
FILE button.
Enter a filename for the chart image in the Chart
Export box. Click OK.
A message box will then display to notify
you that the image was successfully saved.
The file will be saved in the same folder as
your RPS tool file.
Save Chart to File
Chart Export
Please enter a filename.
The plot will be saved in the current directory as filename.jpg
^ercentForestVVS
OK
Cancel
Microsoft Excel
An image of your chart can also be copied to the
clipboard by clicking the COPY CHART TO
CLIPBOARD button.
You can then paste your chart into a report
document or other file using the Paste menu
button or by typing Ctri-V.
- Copy Chart to Clipboard
48
-------
Explore Indicator Data Menu - Change Watershed Selection Button
10.5. Change Watershed Selection Button
By default, the Explore Indicator Data menu displays indicator statistical properties for all watersheds in your
tool's study area. You can instead explore indicator data for just a subset of watersheds using the CHANGE
WATERSHED SELECTION button at top of the Explore Indicator Data menu. Clicking the button will launch the
Select Watersheds menu.
Select Indicator Type: |Ecological Select Indicator Name:
By default, all watersheds in the project area are Included in th\
electa subset of watersheds for data exploration.
Watershed Selection:
Select Watersheds...
Indicator data can be reviewed for all watersheds in the project area or for a subset
of watersheds of interest. Use the buttons below to specify which watersheds you
would like to explore then click OK.
(* All Watersheds
C Watersheds Selected for Screening on the Setup Sheet
r Named Subset from the HUC_Subsets Sheet
This option is available if you have already defined one or more watershed subsets on the HUC_Subsets
sheet. Selecting a subset name from the menu below will only indude those watersheds within the selected
subset in the data summary.
3
C Other Subset of Watersheds
A subset of watersheds can be selected based on indicator values. Watersheds that meet the conditions
specified below will be induded in the data summary.
Select Indicator Type: P
Select Indicator Name: |~~
Select Subset Method: I
"3
3
"3
Indude Watersheds with
Values Between:
To
Min =
Max =
Indude Watersheds with
Values Of:
OK
The Select Watersheds menu contains four option buttons for
choosing watersheds to include in your indicator data summary.
By default, the ALL WATERSHEDS button is selected.
'•* All Watersheds
The second option button selects watersheds
that have already been chosen for an RPS
screening on the Setup worksheet.
To use this option, you must have previously
entered a list of watershed IDs in the Select
Watersheds section of the Setup worksheet
(see Section 3.3).
~f Watersheds Selected for Screening on the Setup Sheet
49
-------
Explore Indicator Data Menu - Change Watershed Selection Button
The third option button selects watersheds that
have already been defined in a named
watershed subset on the HUC_Subsets
worksheet.
To use this option, you must have previously
entered one or more watershed subsets on the
HUC_Subsets worksheet (see Section 13).
Named Subset from the HUC Subsets Sheet
Named Subset from the HUC .Subsets Sheet
This option is avaSable if you have already defined one or more watershed subsets on foe HUC_Subsets
sheet. Selecting a subset name from the menu below Mi only indude those watersheds withn the selected
subset in the data summary.
A subset of watersheds can be selected based on indicator values, Watersheds that meet the conditions
specified below will be induded in the data summary.
Select Indicator Type:
Select Indicator Name:
Select Subset Method:
Include Watersheds with
Values Between:
Indude Watersheds with
Values Of:
"3
"3
To
Min =
Max =
50
-------
Explore Indicator Data Menu - Change Watershed Selection Button
After clicking the OTHER SUBSET OF
WATERSHEDS option button, choose the type
of indicator for selecting watersheds (Base,
Ecological, Stressor, or Social) from the Select
Indicator Type drop-down menu.
-Select Indicator Type: Base
Base
a
Base
Ecological
Stressor
SdcqI
Select Makr riye:
Then choose the name of the indicator for
selecting watersheds from the Select
Indicator Name drop-down menu.
Unt C®<3» (HUC12)
Hum HUCli Watershed
Hydremic Ur t Code 6-D«a3 yHUOB)
Hame HUC8 VrttwsfiM]
furr* HUC6 W«»sh«i
C>Own*r«»m WJCJ2
Uga^eam WCl? Cwnj. Wgt incfattei ThsHUC12
Next, choose a subset method for selecting watersheds from the
Select Subset Method drop-down menu.
Ranqe of Values
The Range of Values option allows you to specify minimum and
maximum values of interest for the selected indicator. Watersheds
with indicator values in the specified range will be included in the
indicator data summary.
Select Subset Method:
r~
The Unique Values option allows you to select one or more unique
values of interest for the selected indicator. Watersheds with the
specified values will be included in the data summary. The unique
values option will typically only be used for categorical variables.
If you choose the Range of Values method,
enter the range of values of interest for the
selected indicator in the text boxes.
For reference, the tool will display
minimum and maximum values of the
selected indicator below the boxes.
Select Indicator Name: % Agriculture in WS (2011)
Select Subset Method: [Range of Values
Indude Watersheds with
^¦Values Between:
50 To
Min = 6.4517
[loof
Max = 95.8199
If you choose the Unique Values method, a
list of unique indicator values will
automatically display in the selection box
below the Select Subset Method drop-
down menu.
Choose one or more of the unique indicator
values by highlighting the values in the
selection box.
Select Indicator Name: Name HUC8 Watershed
Select Subset Method: | Unique Values
Indude Watersheds with
Values Between:
To
Min =
Indude Watersheds with
Values Of:
CT-Tggr-"-:
:
Max =
Bear-Wyaconda
Big Papillion-Mosquito
Blackbird-Soldier
Blue Earth
Boone
51
-------
Explore Indicator Data Menu - Change Watershed Selection Button
After choosing an option for selecting watersheds to include in the indicator data summary, click the OK button
at the bottom of the Select Watersheds menu.
Select Watersheds...
Indicator data can be reviewed for all watersheds in the project area or for a subset
of watersheds of interest. Use the buttons below to specify which watersheds you
would like to explore then click OK.
r All Watersheds
C Watersheds Selected for Screening on the Setup Sheet
C Named Subset from the HUC_Subsets Sheet
This option is available if you have already defined one or more watershed subsets on the HUC_Subsets
sheet Selecting a subset name from the menu below will only indude those watersheds within the selected
subset'm the data summary.
"3
(* Other Subset of Watersheds
A subset of watersheds can be selected based on indicator values. Watersheds that meet the conditions
specified below will be included in the data summary.
Select Indicator Type: Base
3]
Select Indicator Name: Name HUCB Watershed
"3
Select Subset Method: Unique Values
"3
Indude Watersheds with
Values Between:
To
Min =
Max =
Indude Watersheds with
Values Of:
Apple-Plum
Bear-Wyapl^J
Biq Papillion-Mosquito
Blackbird-Soldier
Blue Earth
Boone
OK
The Explore Indicator Data menu will now only display indicator summary statistics, histograms, correlations,
and other statistical properties for the group of watersheds specified on the Select Watersheds menu. You can
adjust your watershed selection at any time by clicking the CHANGE WATERSHED SELECTION button on the
Explore Indicator Data menu.
52
-------
Indicator Info Worksheet - Overview
11. Indicator Info Worksheet
11.1. Overview
The Indicator info worksheet contains a table of indicator information, including the category of each indicator (Base,
Ecological, Stressor, or Social), the watershed scale(s) that each indicator is quantified at, and a description of what
watershed characteristic the indicator measures.
5
6
Indicator Name
Indicator Type
Indicator Scale<«)
Description
7
Kydrotogic Un< Codo 8-Digit (HUC8)
Baso
HUC8 &HUC12
HUC8 Code (TEXT)
8
Hydrologic Una Code 12-Digrf (HUC12)
Base
HUC12
HUC12 Code (TEXT)
9
Nam© HUC12 Watershed
6ase
HUC12
Name o< primary stream draining area or description of area bounded by HUC12 polygon (TEXT)
10
Name HUC8 Watershed
Base
HUC8& HUC12
Name of primary stream draining area or description of area bounded by HUC08 polygon (TEXT)
11
Area Of Walershed (HUC8) In Acres
Base
HUC8
Total area of the HLIC8 watershed Units Acres
12
Area CM Watershed (HUC12) In Square Meiers (Grid)
Base
HUC12
Total area of the HUC12 watershed VaJuo calculated from 30 motor god of HUC12&. Unfls Square Meters
13
% Riparian Zone (RZ) in Watershed
Base
HUC12
% of HUC12 that is in the Reparian Zone The Riparian Zone includes the 100-meter buffer on both sides of. <1) water
features in the medium-resolution National Hydrography Dataset (NHO). and (2) areas classified as water in ttie National
Land Cover Database (NLCD) 2006 land cover dataset
14
% HydrotogicaBy Connected Zone (HCZ) in Walershed
Base
HUC12
% of HUC12 that is in the Hyttrotogicaly Connected Zone The HCZ consists of areas estimated to have high runoff potential
based on a topographic wetness indes that are contiguous w»th water
IS
% Water in Watershed
Base
HUC12
% of HUC12 that is water (NHD CatSeed + 2006 National Land Cover Dataset. 11, 90. 95)
16
% Land in Watershed
Base
HUC12
% of HUC12 that is land (100 - % Water)
17
Walershed NKDPkis2 Slreamlenglh
Base
HUC12
Length of NHD stream features rn HUC12 (iotameters) Catenated from the "NHDPtus2 NHD Snapshot" dataset Includes al
NHDf lowline features with FTYPE (feature type) oqual to StreamRivef. CanaiDitch, or Connector NHDFtowfcne features with
FTYPE equal to ArtificialPatft are only included if they pass through a NHDArea feature with FTYPE equal to StreamRrver
18
Walershed NHDPtus2 Waterbody A/ea
Base
HUC12
Area of NHD watwbody features in HUC12 (square kilometers) Calculated from the "NHDPfas2 NHD Snapshot" dataset
19
EPA Region
Base
HUC12
EPA Regson Codes for al regions containing HUC12 (Comma delimited TEXT)
20
Instate Area CM Watershed (HUC8) In Acres
Base
HUC8
Instate area of the HUC8 watershed Units Acres
21
Al Slates in HUC8 (WBO)
Base
HUC8
2-tetter state abbreviations for states intersecting HUC8 polygon, ordered alphabebcaly and conwna-separated Fran
"STATES* field of NHOPIus Version ? Watershed Boundary Dataset snapshot
22
Border HUC
Base
HUC8
Flag indicating whether the HUC boundary intersects Bw TN border
23
Al States in HUC12 2014
Base
HUC12
2-letter state abbreviations for states containing at leas! 1% of HUC12 instate area, ordered alphabetic a«y and comma-
soparaiod Analysis based on f«DPtus2 WBD snapshot HUC12 dataset and 2013 state boundaries dataset rrom
Bubble_Plot Bubble_Plot_Options
HUC12_Map
HUC8_Data
HUC12_Data 1 Indicator Info jHUC.Subsets Add_Indicators
The following sections describe how to:
¦ Filter the Indicator Info Table
¦ Add Indicator Info
53
-------
Indicator Info Worksheet-Filter Table
11.2. Filter Table
The indicator information table can be filtered to view information for a specific indicator type (Base, Ecological,
Stressor, or Social) or for a specific watershed scale by clicking the drop-down arrow next to the column name and
selecting a filter option.
In the example below, the table is filtered to only display information for Ecological indicators.
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
6
33
34
35
36
37
38
39
40
41
42
Indicator Name
Hydrologic Unit Code 8-Diqit (HUC8)
Hydrologic Unit Code 12-Digit (HUC 12)
Name HUC12 Watershed
Name HUC8 Watershed
Area Of Watershed (HUC8) In Acres
Area Of Watershed (HUC12) In Square Meters
% Riparian Zone (RZ) in Watershed
% Hydrologically Connected Zone (HCZ) in W
% Water in Watershed
% Land in Watershed
Watershed NHDPIus2 Streamlength
Watershed NHDPIus2 Waterbody Area
EPA Region
Instate Area Of Watershed (HUC8) In Acres
All States in HUC8 (WBD)
Border HUC
Indicator Type Ll
Sort A to Z
a! Sort Z to A
Sort by Color ~
^ £lear Filter From "Indicator Type"
Filter by Color ~
T ext Fitters ~
Search
i (Select AH)
|_®i
Ecological
u
. . soc
~ Stre
OK
DC
Cancel
HUC8 & HUC12
HUC12
HUC12
HUC8 & HUC12
HUC8
HUC12
HUC12
HUC12
HUC12
HUC12
HUC12
HUC12
HUC12
HUC8
HUC8
HUC8
I Indicator Name
~ Indicator Type -T
Indicator Scale(s) T
!% Deciduous Forest (2006) in Watershed
Ecological
HUC12
% Evergreen Forest (2006) in Watershed
Ecological
HUC12
!% Mixed Forest (2006) in Watershed
Ecological
HUC12
(% Forest (2006) in Watershed
Ecological
HUC12
% Forest (2006) in HCZ
Ecological
HUC 12
% Forest (2006) in Riparian Zone
Ecological
HUC12
|% Shrub/Scrub (2006) in Watershed
Ecological
HUC12
% Shrub/Scrub (2006) in HCZ
Ecological
HUC 12
% Shrub/Scrub (2006) in Riparian Zone
Ecological
HUC12
% Woody Vegetation (2006) in Watershed
Ecological
HUC12
54
-------
Indicator Info Worksheet-Add Indicator Info
11.3. Add Indicator Info
The Indicator Info worksheet can be updated by users to store information for new indicators added to the Indicator Data
worksheet(s).
To add indicator information for a
new indicator, first insert a row for
the new indicator.
Right-click the margin of the
Indicator Info worksheet (where
row numbers are displayed) where
you would like to insert the new
row.
For example, if you would like to
add information for a new Base
indicator, right-click the margin of
the first Ecological indicator row.
Indicator Name
30 Tribal Lards in HUC12 Flag
\
311 % Tribal Lands in HUC12
la/
3 %
3»
A c (2006) in Watershed
2006) in Watershed
6) in Watershed
ftS
3"
3L
Cut
Copy
Paste Options:
ft
Paste Special...
'atershed
Danan Zone
Insert
m
4-
4
Delete
Clear Contents
Format Cells...
Row Height-
Hide
Unhide
i in HCZ
i) in Riparian Zone
(2006) in Watershed
(2006) in HCZ
\J 11 rtJ;
33 [J
37
€12 Adjacent to Tribal HUC12s
Deciduous Forest (2006) in Watershed
> Evergreen Forest (2006) in Watershed
% Mixed Forest (2006) in Watershed
% Forest (2006) in Watershed
% Forest (2006) in HCZ
Indicator Type
Base
Base
Base
Ecological
Ecological
Ecological
Ecological
Ecological
After right-clicking, select "Insert" from the Excel popup
menu.
A new row will be inserted into the indicator information
table.
(2006) in Riparian Zot
sous (2006) in Waters
Type the name of the new indicator in the Indicator
Name column.
Indicator Name
IUC12 Adjaoorit to Tribal HUG4»
33 | New Base Indicator
i4 7o Deciduous horest
ems
in watershed
35 |% Evergreen Forest (2006) in Watershed
36 % Mixed Forest (2006) in Watershed
I
Indicator Type -
ase
coloqical
Ecological
Ecological
55
-------
Indicator Info Worksheet-Add Indicator Info
Type the category of the new indicator (Base,
Ecological, Stressor, or Social) in the Indicator Type
column.
6
Indicator Name ***——~
Indicator Type | ~
32
HUC12 Adiacent to Tribal HUC12s ^5^
33
New Base Indicator
Base
34
% Deciduous Forest (2006) in Watershed
35
% Evergreen Forest (2006) in Watershed
Ecological
36
% Mixed Forest (2006) in Watershed
Ecological
Type the watershed scale of the new indicator in the
Indicator Scale(s) column. For example, if the indicator
is calculated for HUC12s in the Project Area then type
"HUC12".
6
Indicator Name | •"
TiTte^3rType ~
Indicator Scale(s) ~
32
HUC12 Adiacent to Tribal HUC12s
Base
HWe40
33
34
35
New Base Indicator
Base
HUC12
% Deciduous Forest (2006) in Watershed
Ecological
nuu \A
% Evergreen Forest (2006) in Watershed
Ecological
HUC12 c
Type a description of the new indicator in the Indicator
Description column.
6
Indicator Name
Indicator fype^i
r—Scale(s) '
Description
32
HUC12 Adjacent to Tnbal HUC12s
Base
HUC12—
Reservation Trust Lands, and Census O
33
New Base Indicator
Base
HUC12 ^L^-
Example new Base indicator
34
% Deciduous Forest (2006) in Watershed
Ecological
HUC12
% of HUC12 with deciduous forest covei
35
% Evergreen Forest (2006) in Watershed
Ecological
HUC12
% of HUC12 with evergreen forest cover
Repeat these steps for each new indicator added to the Indicator Data worksheet.
56
-------
Add Indicators Worksheet - Overview
12. Add Indicators Worksheet
12.1. Overview
New indicators can be added to the RPS Tool from the Add Indicators worksheet for use in a screening run. Note that the
RPS Tool does not include features for calculating new indicators. New indicators must be calculated outside of the
RPS Tool and, once calculated, can be added at any time. New indicators are inserted into new columns on the Indicator
Data worksheet and will display in the indicator menus on the Setup, Bubble Plot Options, and Map worksheets.
If your RPS Tool file is setup to screen multiple watersheds scales then new indicators must be added separately for each
watershed scale. For example, if you would like to add new HUC8 indicators and new HUC12 indicators, you must add the
HUC8 indicators in a separate step from the HUC12 indicators. Up to 50 indicators can be added at a time for each scale.
ADD WDiCA TORS
Enter Indicator Information
Enlw rmi indicator names and ryp«s i»low
Enter Indicator Data
Paste your indicator table below. Your table must have watershed IDs In column D and indicator values in subsequent columns. The header row
-------
Add Indicators Worksheet-Enter New Indicator Information
12.2. Enter New Indicator Information
In the Enter Indicator Information section of the Add Indicators worksheet, you will enter the name and type of each new
indicator. This information is used to update the indicator menus on the Setup, Bubble Plot Options, and Map worksheets.
Enter New Indicator Information Option 1 -
Tvpe New Indicator Names and Types
In the Enter Indicator Information section,
select cell All and type the name of the first
new indicator.
Then enter the new indicator type in cell Bll.
Indicator type must be Ecological, Stressor,
Social, or Base.
Repeat until each new indicator name and type
has been added.
Enter New Indicator Information Option 2 -
Copv and Paste New Indicator Names and
Types
To use this option you must already have a
pre-existing list of new indicator names and
types stored in a separate worksheet. The pre-
existing indicator list must be formatted with
one indicator per row with the indicator name
in the first column and indicator type in the
second column.
Open the worksheet with the pre-existing
indicator list. Select the indicator names and
types and click "Copy" on the Excel menu.
Go back to the Add Indicators worksheet and
set the cursor to cell All in the Enter Indicator
Information section.
A
B
8
Enter Indicator Information
9
Enter new indicator names and types below
10
Indicator Name
Indicator Tvoe
»
Index of Biotic Integrity Score
Ecological
ii
13
14
15
A
B
8
Enter Indicator Information
9
Enter new indicator names and types below.
10
Indicator Name
Indicator Type
11
Index of Biotic Integrity Score
Ecological
12
Number of CAFOs in WS
Stressor
13
% MS4 Area
Social
14
IS
H H
Select "Paste Values" from the Excel menu
to paste the new indicator names and
types into the Enter Indicators
Information section.
RLE
| Home
Cut
Paste
tig Copy "
*
Format Pain
Index of Biotic Integrity Score
Ecological
Number of CAFOs in WS
Stressor
% MS4 Area
Social
INSI
• i
* Format Painter
Clipboard r,
8
9
Enter Indicator Information
Enter new indicator names and types below
Indicator Name
Indicator Type
J
HI
li
13
14
c*-
Enter Indicator Information
Enter new indicator names and types below.
Indicator Name
Indicator Type
Index of Biotic Integrity Score
Ecological
| Number of CAFOs tn WS
Stressor
% MS4 Area
Social
58
-------
Add Indicators Worksheet-Enter New Indicator data
12.3. Enter New Indicator Data
In the Enter Indicator Data section of the Add indicators worksheet, you will copy and paste a table of new indicator
values for watersheds in the Study Area. These values are used to update the indicator data table in the Indicator Data
worksheet.
To add new indicators, you must first prepare a table of new indicator values in a separate spreadsheet file. An
example indicator table is displayed below. Note the following characteristics of the table:
1. The table must have watershed IDs entered in the first column. Your table does not need to have a row
for every watershed in the study area. Any watersheds that are missing from your table will have blank
cells for the new indicators when added to the Indicator Data worksheet. Your table can include extra
watersheds that are not in the study area; however, those watersheds will be skipped when updating the
Indicator Data worksheet. Watersheds can be sorted in any order in your table.
2. The table must have indicator values in subsequent columns.
3. The table must have a header row with indicator names. Names of new indicators cannot match an
existing indicator name already in the tool. We recommend limiting indicator names to 50 characters or
less to properly display in tool menus.
4. Values of new ecological, stressor, and social indicators in your table should be numeric or blank. Non-
numeric values will be skipped when updating the Indicator Data worksheet. Non-numeric values are
allowed for base indicators.
0
P
2
3
4
5
6
7
S
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|HUC12
Index of Biotic Integrity Score
Number of CAFOs in WS
% MS4 Area
031501010101
98.50
5
0.00
*031501010102
99.35
3
0.00
*031501010103
^9 78.42
0
18.02
"031501010104
1
7.96
r031501010105
47.08
0
5.88
*031501010106
53.29
0
9.56
'031501010301
46.89
0
4.21
*031501010302
50.24
6
4.52
*031501010303
57.43
2
9.08
r050500010102
1
3.06
*050500010103
83.32
1
37.50
*050500010105
8097
0
2.13
*051100020101
42.89
0
0.92
*051100020102
46.93
0
0.82
*051100020105
0
0.41
*051100020106
55.19
1
0.69
*051100020108
50.26
0
0.00
*051100020109
50.61
0
0.34
*051100020201
0
0.38
*051100020203
54.46
2
0.83
*051100020501
53.91
0
0.00
59
-------
Add Indicators Worksheet-Enter New Indicator data
After you have prepared your indicator
data table in a separate spreadsheet file,
you will copy and paste the table into the
Enter Indicator Data section.
Open the spreadsheet file containing
your pre-calculated new indicators.
Select the cells containing watershed IDs
and new indicator values in that file
(including the header row) and click
"Copy" on the Excel menu.
HUC12
Index of Biotic Integrity Score
Number of CAFOs in WS
% MS4 Area
031501010101
98.50
5
0.00
031501010102
99.35
3
0.00
031501010103
78.42
0
18.02
031501010104
1
7.96
031501010105
47.08
0
5.88
031501010106
53.29
0
9.56
031501010301
46.89
0
4.21
y?1501010302
50.24
6
4,52
031501010303
57.43
2
9.08
050500010102
1
3.06
050500010103
83.32
1
37.50
050500010105
80.97
« I
HOME INS
051100020101
42.89
051100020102
46.93
A cut
051100020105
Jbacopy' 1
V —» n
051100020106
55.19
Go back to the Add Indicators worksheet
and set the cursor to cell D10.
Note that by default cell D10 contains the
text "Watershed ID". This text is included
as a placeholder and should be
overwritten by your indicator data table.
Enter Indicator Data
JaateMaMfcinrliritnrtih1" K
10
Watershed ID
11
12
13
14
Clipboard
ow. Your table must have watershed IDs
'D"te Options
Values (V) ^ ^
Select "Paste Values" from the Excel
menu to paste the indicator data table
into the Add Indicators worksheet.
E F G
8 Enter Indicator Data
9 Paste your indicator table below. Your table must have watershed IDs in column D and indicator valu
10 r
11
14
is!
16 '
19
20 '
21 '
22 '
23 '
|HUC12
Index of Biotic Integrity Score
Number of CAFOs
% MS4 Area
031501010101
98.49662401
5
0
031501010102
99.35390734
3
0
031501010103
78.42011275
0
18.02
031501010104
1
7.96026228
031501010105
47.0843994
0
5.880882549
031501010106
53.28558028
0
9.559236623
031501010301
46.89142972
0
4.214230407
031501010302
50.2423724
6
4.519161244
031501010303
57.42810532
2
9.08079115
050500010102
1
3.058646903
050500010103
83.31772027
1
37.5
050500010105
80.97092932
0
2.125675012
051100020101
42.88632856
0
0.921042273
051100020102
46.93380457
0
0.816190158
051100020105
0
0.408689922
60
-------
Add Indicators Worksheet-Add Indicators Button
12.4. Add Indicators Button
After entering new indicator names, types, and values use the ADD INDICATORS button to update the other tool
worksheets with new indicators.
Clicking the ADD INDICATORS button from the Add Indicators worksheet will display a message box listing the
requirements for adding new indicators. Review that the requirements are met and click YES to continue.
ADD INDICATORS
Enter Indicator Information
Enter new indicator names and types below.
Indicator Name
Indicator Type
Index of Biotic Integrity Score
Ecological
Number of CAFOstn WS
Stressor
% MS4 Area
Social
Enter Indicator Data
HUC12
Index of Biotic Integrity Score
031501010101
98.49662401
031501010102
99.35390734
031501010103
78.42011275
031501010104
If your tool file includes multiple watershed
scales, a message box will prompt you to
specify which scale the new indicators apply to.
Select the option button for the appropriate
watershed scale and click OK.
Please specify a watershed scale for the new indicators.
C HUC8
jrtJC12l
It is strongly recommended that you enter information for the new indicators (name, type, scale, and description)
to the Indicator Info worksheet. Refer to the Indicator Info worksheet section of this guide for instructions on
inserting information for new indicators.
A message box will appear to notify you that your
new indicators were successfully added to the tool
file.
The message box will prompt you to clear new
indicator names, types, and values from the Add
Indicators worksheet. If you would like to
automatically clear the Add Indicators worksheet
click YES. Or click NO to clear manually.
61
-------
HUC Subsets Worksheet - Overview
13. HUC Subsets Worksheet
13.1. Overview
When setting up an RPS screening, you'll often want to focus on just a subset of watersheds of interest rather than aii
watersheds in the Project Area. The HUC Subsets worksheet provides a space to identify and store groups of watersheds
that fit your selection criteria for use in a future screening. For example, within the HUC Subsets tab you can generate a
list of HUC12 watersheds that are located in a particular HUC8 of interest or that have a certain land cover profile.
Watershed subsets include only a portion of all watersheds in the Project Area. A watershed subset "list" is comprised of
the watershed IDs for the subset. Watershed subset lists are stored on the HUC Subsets worksheet with watershed ID per
row. The lists can be copied and pasted into the Select Watersheds section of the Setup worksheet for screening.
Watershed subsets are defined automatically with the CREATE A NEW WATERSHED SUBSET button or entered manually.
This worksheet is provided for users to store watershed subsets {lists of watershed IDs for specific groups of watersheds of interest, e.g., all HUC12 IDs ir
Click the Create a New Watershed Subset button below to launch a menu to define a new watershed subset and add it to this sheet.
Or if you already have a watershed subset identified, copy the watershed IDs and a descriptive header from an outside source and use Paste Special/Vol
available column on this sheet. When copying your own list, you must enter the subset name in row 12 and have one watershed ID per subsequent rc
("Example Subset") followed by example watershed IDs.
To use subset lists from this worksheet in a screening run, select and copy only the ID numbers in a single column (without the subset name); then go to
to insert the ID list into the first line of the Select Watersheds section.
CREATE A NEW WATERSHED SUBSET
| Upper Iowa WMA iTurkey River WMA
^70600020101 ^70600040101
^70600020103 1)70600040102
*070600020105 1)70600040103
ff)70600020106 1)70600040104
^170600020107 1)70600040105
^70600020201 1)70600040201
^170600020202 "&70600040202
r&70600020203 1)70600040203
1)70600020204 1)70600040204
Upper Wapsipinicon River WMA
¦570801020101
1)70801020102
1)70801020103
1)70801020201
1)70801020202
¦570801020203
1)70801020204
1)70801020301
1)70801020302
Upper Cedar River WMA
1)70802010401
1)70802010402
1)70802010403
1)70802010503
1)70802010504
1170802010505
1)70802010601
1)70802010602
1)70802010603
Middle Cedar WMA
1)70802050101
1)70802050102
1)70802050103
1)70802050201
1)70802050202
1)70802050203
1)70802050204
1)70802050301
Ti7nan7nqmn?
HUCSubsets
Middle-South Raccoon WMA
1)71000070101
1)71000070102
1)71000070103
1)71000070104
1)71000070201
1)71000070202
1)71000070203
1)71000070204
2aM)0702Q5
Bubble_Plot_Options HUC12_Map HUC8_Data HUC12_Data Indicatorjnfo
Add Indicators
The following sections provide step-by-step instructions for how to:
¦ Use the Create a New Watershed Subset Button
m Manually Add a Subset List
62
-------
HUC Subsets Worksheet - Manually Add a Subset List
13.2. Create a New Watershed Subset Button
On this menu you can define and store a subset of watersheds of interest based on one or more indicators.
For example, a subset could indude all watersheds with agricultural land cover greater than 50%. Use the controls below to specify which watersheds to include in your subset, The
selected watershed IDs will be stored as a new list on the HUC_Subsets sheet and can be copy/pasted onto the Setup sheet for screening.
Enter Subset Name (required, 50 character limit):
Enter Subset Description (optional):
A 'subset condition" is an indicator, and set of values for that indicator, used to define watersheds to
include in the subset. Use the menus below to define a subset condition then dick the 'Add Condition'
button to add it to the list of conditions defined in the box to the right. Up to 10 subset conditions can
be defined but must be entered one-at-a-time using the menus below.
Select Indicator Type: |j~~
Select Indicator Name: |~~
"3
"3
Select Subset Method:
Indude Watersheds with
Values Between:
Indude Water^eds with
Values Of:
Add Condition
Subset conditons are listed below after they are added with the "Add Condition'
button. Up to 10 conditions can be defined.
Your subset can indude watersheds that meet at least one of the subset
conditions or only those watersheds that meet all of the subset conditions. Use
the option buttons to select a subsetting method.
Watersheds must meet: (* At Least One Condition
C All Conditions
o
You can remove a subset condition by highlighting it in the list above and
dicking the "Remove Selected Condition" button below.
Remove Selected Condition
W
When you have defined all of your subset conditions dick the "Add Subset to HUC_Sul
Check this box to save a copy of the subset name, description, and conditions as a PDFl
Subset to HUC_Subsets Sheet" button below. The PDF file will be saved in the same dire"
0
Ieet" button below to add the list of watershed IDs to the HUC_Subsets sheet,
ir records. If this box is checked, the PDF file will be saved after dicking the "Add
lis tool file and will be named using the subset name.
Add Subset to HUC_Subsets Sheet
63
-------
HUC Subsets Worksheet - Manually Add a Subset List
If your tool file includes multiple watershed scales,
then a popup box will display after you click the
CREATE A NEW WATERSHED SUBSET button.
The popup box will contain a list of watershed
scales and ask you to specify the scale to use for
defining your subset.
A subset can only contain watersheds from a single
scale. Choose your desired scale and click OK.
Select Watershed Scale..,
Please specify a watershed scale for the new watershed subset.
>
f" HUC3
(* IHUC12!
OK
On the Define Watershed Subset menu, type a name for your subset in the Enter Subset Name text box and an
optional description in the Enter Subset Description text box.
Define Watershed Subset...
On this menu you can define and store a subset of watersheds of interest based/
For example, a subset could indude all watersheds with agricultural land cover greater than 50%. Of
selected watershed IDs will be stored as a new list on the HUC_Subsets sheet and can be copy/pi|£«^ffitQ the Setup sheet for screening.
licators.
"Below to sperify which watersheds to indude in your subset. The
Enter Subset Name (required, 50 character limit):
Enter Subset Description (optional):
] Upper Iowa Cropland
HUC 12s in the Upper Iowa HUC8 with at least 50% cropland cover|
You can now begin to define subset conditions.
First select an indicator type (Base, Ecological,
Stressor, or Social) for your subset condition
from the Select Indicator Type drop-down
menu.
Select Indicator Type; Ecological
I Base
Ecological
Stressor
Social
Select Indicator Name: |
Then choose the indicator name from the
Select Indicator Name menu.
% Forest m WS f2011)
% forest in HC2 (2011}
% Forest in RZ (2011)
% Forest Change in WS (2001-11)
% Forest Change ii HCZ (2001-11)
% Forest Change in RZ (2001-11)
% Forest RemainmQ in WS
w
Select Subset Method:
Next, choose a subset method from the Select Subset Method drop-
down menu.
The Range of Values option allows you to specify minimum and
maximum values of interest for the selected indicator. Watersheds
with indicator values in the specified range will be included in the
subset.
The Unique Values option allows you to select one or more unique
values of interest for the selected indicator. Watersheds with the
specified values will be included in the subset. The unique values
option will typically only be used for categorical indicators.
Ranqe of Values
Unique Values
64
-------
HUC Subsets Worksheet - Manually Add a Subset List
If you choose the Range of Values method,
enter the range of values of interest for the
selected indicator in the text boxes.
For reference, the tool will display
minimum and maximum values of the
selected indicator below the boxes.
If you choose the Unique Values method, a
list of unique indicator values will
automatically display in the selection box
below the Select Subset Method drop-
down menu.
Choose one or more of the unique indicator
values by highlighting the values in the
selection box.
After you have selected an indicator type,
name, subset method, and indicator values
for your subset condition, click the ADD
CONDITION button.
The condition will now be added to the list of
subset conditions on the right side of the
interface.
Repeat this process as needed to enter up to
ten conditions for your subset.
Select Indicator Name: 1% Agriculture in WS (2011)
Select Subset Method: | Range of Values
Include Watersheds with ITT
Values Between: I
To
Min
6.4517
3
Max
95.8199
Select Indicator Name: Name HUC8 Watershed
Select Subset Method: Unique Values
Include Watersheds with
Values Between:
To
Min =
^ Include Watersheds with
Values Of:
Apple-Plum
Bear-Wyaconda
Big Papillion-Mosquito
Blackbird-Soldier
Blue Earth
Boone
3
Max
Subset conditons a
button. Up to 10
li
listed below after they are added with the "Add Condition'
litions can be defined.
Your subset can ii
conditions or only t
the option buttons
inc®e watersheds that meet at least one of the subset
se watersheds that meet all of the subset conditions. Use
select a subsetting method.
Watersheds mi
stershe
:et: • At Least One Condition
r All Conditions
Name HUC3 Watershed = Upper Iowa
% Agriculture in WS (2011) = 50 To 100
You can remove a subset condition by highlighting it in the list above and
dicking the "Remove Selected Condition" button below.
i Remove Selected Condition !
Remove Selected Condition
65
You can remove any subset condition from
the list by highlighting it and clicking the
REMOVE SELECTED CONDITION button.
-------
HUC Subsets Worksheet - Manually Add a Subset List
The Define Watershed Subset interface contain two option buttons for choosing how the subset conditions wil
be used to select watersheds to include in your subset. The option buttons are displayed on the right side of
the interface above the subset list.
Subset conditons are listed below after they are added with the 'Add Condition*
button. Up to 10 conditions can be defined.
Your subset can indude watersheds that meet at least one of the subset
conditions or only those watersheds that meet all of the subset conditions. Use
the option buttons to select a subsettinq method.
Watersheds must meet: (* At Least One Condition
C All Conditions
Name HUC3 Watershed = Upper Iowa
% Agriculture in WS (2011) = 50 To 100
If the >4 7" LEAST ONE CONDITION button is selected,
then your subset will include any watershed that meets
at least one condition in the subset condition list.
* At Least One Condition
\f the ALL CONDITIONS button is selected, then your
subset will only include watersheds that meet all of the
conditions in the subset condition list.
* lAll Conditions
After defining your subset conditions and selecting an option for applying the conditions (i.e., At Least One
Condition versus All Conditions), you are ready to add the watershed subset to your tool file with the ADD
SUBSET TO HUC_SUBSETS SHEET button.
By default, a record of the subset name, description, and conditions are saved in a PDF file when the ADD SUBSET
TO HUC_SUBSETS SHEET button is clicked. The PDF file is saved in the same folder as your tool file using the
subset name in the PDF filename. If you would like to create your subset without saving a record of the subset
settings, uncheck the box above the ADD SUBSET TO HUC_SUBSETS SHEET button.
After clicking the ADD SUBSET TO HUC_SUBSETS SHEET button, the tool will automatically identify watersheds
that satisfy your subset conditions. The subset name and list of watershed IDs in the subset will be added to the
first available column in the HUC Subsets worksheet for use in a screening run.
When you have defined all of your subset conditions dick the 'Add Subset to HUC_Subsets Sheet* button below to add the list of watershed IDs to the HUC_Subsets sheet.
Check this box to save a copy of the subset name, description, and conditions as a PDF file for your records. If this box is checked, the PDF file will be saved after clicking the rAdd
Subset to HUC_Subsets Sheetr button below. The PDF file will be saved in the same directory as this tool file and will be named using the subset name.
Add Subset to HUC Subsets Sheet
Upper Iowa Cropland
¦070600020101
^70600020103
¦070600020105
X
'070600020106
¦&70600020107
tl705000202"oT~
r070600020202
^70600020203"
r070600020204
66
-------
HUC Subsets Worksheet - Manually Add a Subset List
13.3. Manually Add a Subset List
To add a new subset list to the HUC Subsets
worksheet, select the pre-existing list of
watershed IDs from a separate worksheet
(formatted with one ID per row) and click
"Copy" on the Excel menu.
"051301070102
'051301070103
'051301070201
'051301070202
'051301070203
'051301070204
'051301070301
'051301070302
'051301070303
INSI
n.yj ^Copy - |
* Format Painter
Clipboard G
7 Conasauga 03150101 (7)
8 [031501010102
9 031501010103
10 "031501010104
11 ^31501010106
12 ^131501010301
13 "031501010302
14 ^31501010303
15
16
17
18
19
20
Barren 05110002(13)
"051100020101
"051100020102
"051100020105
'051100020106
'051100020108
'051100020201
'051100020203
'051100020501
'051100020503
'051100020601
'051100020602
'051100020604
'051100020605
Upper Cumberland 05130101 (10)
'051301010201
^>51301010501
'051301010502
¦651301010503
'051301010601
'051301010602
'051301010603
'051301010604
'051301010605
'051301010701
Set the cursor to row 8 in the first open column
on the HUC Subsets worksheet.
Paste
Format Pain
c fx is m
f Cr ©
JjjJe Values
JffllU) 125
* °—te Options
Values (V) | ,
Select "Paste Values" from the Excel menu to
paste the watershed IDs into the HUC Subsets
worksheet.
If desired, type a descriptive header for the
subset list in row 7. Save the tool file. The new
subset list is now stored for future use in a
screening run.
67
------- |
|