User Guide
forthe
Recovery Potential Screening (RPS)
100
90
80
70
85 60
¦O
c
50
"oo
_o
O
lu 40
30
20
10
0
0 10 20 30 40 50 60 70 80 90 100
Stressor Index
Scoring Spreadsheet Tool
January 2017
Watershed % Impervious
I I 0-0.02
I I 0.02-0.05
IB 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 29
7.4. Label Bubbles 30
Basics 30
Advanced Tips 32
7.5. Edit Bubble Sizes 33
7.6. Edit Bubble Colors 34
7.7. Save As Image File 37
8. Map Worksheet 38
8.1. Overview 38
8.2. Create Custom Map 39
8.3. Save Map as PDF File 42
9. Indicator Data Worksheet(s) 43
10. Indicator Info Worksheet 44
10.1. Overview 44
10.2. Filter Table 45
2
-------
10.3. Add Indicator Info 46
11. Add Indicators Worksheet 48
11.1. Overview 48
11.2. Enter New Indicator Information 49
11.3. Enter New Indicator Data 50
11.4. Add Indicators Button 52
12. HUC Subsets Worksheet 53
12.1. Overview 53
12.2. Add a Subset List 54
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 June 2016 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; and
¦ Can export data and screening results for use in other geospatial tools, documents and presentations.
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
Please make it a habit to re-name and save your RPS tool file before you begin a new screening, in order to ensure that
you will know what topic is addressed in each screening run and a separate, unused tool copy always remains available.
The Instructions worksheet provides an abridged set of instructions for using the RPS Tool. Green-shaded areas within
other worksheets also provide more specific instructions. The Instructions worksheet and other green-shaded areas are
intended to serve as a built-in reference for users of the RPS Tool. They do 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).
¦ 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 1 F | G | H | 1 | i
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 Office 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.
1 1
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
. . 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
. c 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_Dato tab for the watershed scale of interest (e.g., HUC12_Dato).
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
2g 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.
I INSTRUCTIONS I Notes Setup Results I Bubble_Plot Bubble_Plot_Options I 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 Co include in the screening by dsc&ng
the Setter wafers**#} Button below To deai your
s elections, elide the Clear lVatersfied Selections Button
HUCS Ihi/C12\
Select Ecological Indicators
Select ecological indicators 3o include in toe screening by
didongthe S4HM EcaKxiK*' Indicators button below To dear
your selections, cbefc the Clear Ecological indicator Selections
button.
Select Stressor Indicators
Select s&essor indicators to include in Che screening by dlcfcing
tn» Sated Stressor ind/caton buflon below To dear your
selections, dick me Clear Stressor Indicator Selections button
Select Social Indicators
Select social indicators to indude in tfie screening by dieting
the Select Social indicators Button below To dear your
selections. dickBie Oear Social indicator Selections button
Select Watersheds
Select Ecological Indicators
Select Stressor Indicators
Select Social Indicators
Clear Wafershed Selections
Clear Ecological Indicator Selections
Clear Stressor Indicator Selections
CJear Social Indicator Selections
H IK 12 ID
Ecotogical Indicator
Weight
Stressor Indicator
Weight
Social Indicator
Weight
INSTRUCTIONS Notes
Setup
1 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 of this guide provide step-by-step instructions for how to:
¦ Choose a Watershed Scale
m Select Watersheds to Screen
¦ Select Indicators and Assign Weights
m Use the Run Screening Button
m 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 (e.g.,
remove or add selected indicators, change their weights, or change watersheds) 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 RPSTool files are designed to screen watersheds at a single scale (e.g., HUC12 subwatersheds only) and offer no
choice of scales. 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 clear your selections, click the Clear
Watershed Selections button.
f HUC8 r 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.
HIIC8 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:
I
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.
HUC8 r HUC12
Select Watersheds
Clear Watershed Selections
HUC8 ID
SclKl Watersheds...
Select watersheds to include m your screening by highlghting their names in the box
below and cklong the Add Selected Watersheds button.
Or ckk the Add Al Watersheds button to select al watersheds for your screemng.
03150101 (Conasauqa}
05110002 (Barren)
05130101 (Upper Cimberfaod)
OS 130103 (Upper Cumberiand-Lake Cumberland)
05130104 (South Fork Curfcerland)
05130105 (Obey)
05130106 (Upper Cixnberlar*J-Corde« ttJ)
05130107 (Cofcns)
05130108 (Caney)
05130201 (Lower Cunbertand-Ofd Hickory Lake)
05130202 (Lower Omberfand-Sytamore)
05130203 (Stones)
05130204 (Barpeth)
05130205 (Lower Cumberland)
05130205 (Red)
06010101 (North Fork Holston)
06010102 (South Fork Hcfc ton)
06010103 (Watauga)
zi
AM Water***
Add All Watersheds
Select w;
iVa/ereftf
IVatersfte
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
HUC12
Select Watersheds
Clear Watershed Selections
HUC8 ID
103150101(Conasauga)
105110002 (Barren)
105130101 (Upper Cumberland)
05130103 (Upper Cumberland-Lake Cumberland)
05130104 (South Fork Cumberland)
9
-------
Setup Worksheet-Select Watersheds to Screen
10
-------
Setup Worksheet-Select Watersheds to Screen
Select Watersheds to Screen Ootion 3 -
Codv and Paste Watershed IDs
To use this option 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.
13
14
15
16
17
18
19
20
21
22
T3
Select Watersheds
Select watersheds to include in the screening by clicking the Select
Watersheds Button below. To clear your selections, click the Clear
Watershed Selections Button.
HUC8
HUC12
Select Watersheds
Clear Watershed Selections
24
Select "Paste Values" from the Excel menu to
paste the watershed IDs into the Select
Watersheds section.
INSI
1
x Format Painter
Clipboard i»
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).
13 I Select Watersheds
14 Select watersheds to include in the screening by clicking the Select
15 IVate/sfteds button below. To dear your selections, click the Clear
16 Watershed Selections button.
17
18
19 Select Watersheds
HUC8
HUC12
^ Format Pain
Clear Watershed Selections
HUC8 ID
Paste
24 10260006
25 10260007
D fx % 26 10260008
27 10260009
28 10260010
29 10260011
30 10260012
31 10260013
32 10260014
°-~te 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
Clear Ecological Indicator Selections
Ecological Indicator
% Woody Vegetation (2006) in Riparian Zone
% Natural Cover, N-indexl (£006) in Watershed
NFHAP Habitat Condition Index
Weight
Select Ecological Indicators
Select ecological indicators to include in the screening by clicking the
Select Ecological Indicators button below. To clear your selections, click
the Clear Ecological Indicator Selections button.
Select Ecological Indicators
Clear Ecological Indicator Selections
Ecological Indicator
Weight
Select Ecological Indicators™
Select ecological indicators to include in your screening by hghllghting their names in the
box below and cfcking the Add Selected Indicators button.
Double-click any indicator name in the box to view its description.
% Woody Vegetation (2006) in 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
Exit
Select Ecological indicators
Select ecolBical indicators to include in the screening by clicking the
SeiectEco iical Indicators button below.To clear your selections, click
the Clear ^logical Indicator Selections button.
Se/ecf Ecological Indicators
Clear Ecological Indicator Selections
Ecological Indicator
% Woody Vegetation (2006) in Riparian Zone
% Natural Cover, N-indexl (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 -
Copy 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
% NaiioaaJ-ejtflogica! Framework (MEF) in WS (2001)
2
1
1
HOME
INSI
Pasti
* Format Painter
Clipboard n
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
Select Ecological indicators button below. To clear your selections, click
the Clear Ecological Indicator Selections button.
Select Ecological Indicators
Clear Ecological Indicator Selections
Prnlfniiral [nrijfah
Ai.
Select "Paste Values" from the Excel
menu to paste the ecological indicators
and weights into the Select Ecological
Indicators section.
°-te Options
Values (V) a a.
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).
l—J IFgCopy *
Paste #
* Format Pain
Q B
Li Lh s>
Select Ecological Indicators
Select ecological indicators to include in the screening By dicking the
Select Ecological Indicators button below. To dear your selections, dick
the Clear Ecological Indicator Selections button.
Select Ecological Indicators
Clear Ecological Indicator Selections
% N-lndex1 in WS (2011)
Ecological indicator
Soil Stability. Mean in WS
% 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
Select watersheds lo Include in the screening by cfucfcmg
the Select watersheds Button freiow To dear your
selections, dick Ehe Clear Watershed Selections button
<* HUC8 & HUC12
Select Watersheds
Select Ecological Indicators
Select ecological indicators to include in llie screening by
dicing the Select SccKogtca! indicators button below To dear
your selections, dick Hie Clear Ecological Indicator Selections
button
Select Ecological ir&cators
Select Stressor Indicators
Seled stressor investors to indude in Hie screening Oy dieting
the Select Stressor indicators button below. To dear your
selections, dick the Clear Stressor Indicator Selections button
Select Stressor Indicators
Select Social Indicators
Seled social indicators to include m the screening by dicking
me Select Social indicators button below. To dear your
selections, dick the Clear Social indicator Selections button
Select Social Indicators
Clear Watershed Selections
Clear Ecological Indicator Selections
Clear Stressor Indicator Selections
Clear Social Indicator Selections
HUC1? ID
Ecological indicator
Weight
Stressor Indicaior
Weight
Social (indicator
Weight
[0315O101O1O1 (Headwaters Conasuaga River)
% Woody Vegetation in R2 (2011)
3
% Developed. Low Intensity in RZ (2011)
2
% GAP Status 1 and 2
1
031501010102 (Jacks Rwer)
%N-index2 inHCZ(20l1)
3
% Agncutture in W3 (2011)
2
% Streamlengtti Assessed (2015)
1
031501010103 (Ball Play Creefc-Conasauga River)
Habrtat Condition Index WS (2015)
2
% Streamlength Near * 15% Imperious Cover (2011)
2
% Waterbody Area Assessed (2015)
1
031501010104 (Old Fori Creek-Mill Creek)
Soil Stability. Mean in KCZ
2
Synthetic N Fertilizer Application in WS
3
Count Ratio TMDLs to Impairments (2015)
1
031501010105 (Perry Creek-Conasuaga River)
Watershed Unique 303d-Listed Causes Count (2015)
3
Watershed Groups (INSTATE)
2
031501010106 (Sugar Creek)
% Streamlengih 303d-Listed Nutrients (2015)
3
Jurisdictional Complexity (INSTATE)
3
031501010301 (Coahulla Creek Headwaters)
031501010302 (UIII8 Cr&ek)
031501010303 (Coahulla Creek)
050500010102 (8io Laurel Creek)
050500010103 (Headwaters North Fork New River)
050500010105 (Big Horse Creese)
051100020101 (Uffle Trace Creek-Line Creek)
051100020102 (Trace Creefc-Line Creek)
051100020105 (Long Fort)
051100020106 (Salt Lick Creflk)
051100020108 (Puncheon Creek)
« ... Notes Setup Results Bubbk.Mot Bubble JMotOptions HUC8_Map HUC12_Map HUCS.Data WJC12,Data Indicator Jnfo HUC.Subseis Add Jn creators Summary.All {£)
Microsoft Excel
After auto-calculating index scores and populating
Project Successfully Created!
Go to the Summary_Scores, Bubble_Plot, and Map worksheets to view results.
OK ^
worksheets with screening results, a message box
will appear to notify you that your screening
successfully ran to completion.
If your screening includes several thousand
watersheds, processing times of up to 1 minute can
be expected before the message box appears.
Click OK and then proceed to other worksheets to
review screening results.
"
17
-------
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:
« 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.
The user is given an opportunity to decide whether they will accept the data gap or terminate the screening. 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.
¦ 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. As above, the user is given an opportunity to decide whether they will continue including the equal-value
indicator or terminate the screening.
After clicking the RUN SCREENING button, a message box will appear if the tool finds any indicator data issues.
The warning message will notify users if missing indicator data or equal-value indicators were found and will list
the names of indicators with issues. Users have the option to continue the screening by clicking Yes on the
message box or to stop the screening to adjust indicator selections by clicking No.
Warnings
One or more watersheds in this screening run are missing values in the
HUC12_Data sheet for 1 indicator(s):
¦Synthetic N fertilizer application (kg M/ha/yr) WS
Summary scores for these watersheds will be calculated from non-missing
indicators only or will not be calculated if all ecological, stressor, and /or social
indicator values are missing.
All watersheds in this screening run have the same value for 1 indicators):
•% Watershed Waterbody Area Assessed
Normalized values of these indicators will be set to 0-5 to prevent calculation
errors.
Do you still want to run this screening?
Yes
Mo
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. However, an argument can
sometimes be made for using an indicator with values for few watersheds if it is especially relevant to a screening
purpose that involves targeting or prioritizing a small proportion of watersheds.
¦ 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. There are equal-value "neutral variables"
added to the ecological, stressor, and social indicator categories specifically for this purpose.
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
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
I
Select Watersheds
Seied watersheds to indude in the screening By cftctong
the SeJec/ watersheds button frelow To dear your
selections, dick Die Ctee/Watershed Setectforw button
Select Ecological Indicators
Seied ecological indicators to include in one screening by
clicking the .Setecf Ecological Indicators button Below To dear
your selections, dick (he Clear Ecological Indicator Selections
Select Stressor Indicators
Select stressor investors to indude in Hie screening &y dickmg
the Select Stressor indicators button below. To dear your
selections, dick the Dear St'essor Indicator Selections button
Select Social Indicators
Select social indicators to include in the screening by dicking
Hie Setecf Social indicators button below. To dear your
selections, dick the Cfear Social Indicator Selections button
HUC6 HUC12
button
Setecf Watersheds
Select Ecological Indicators
Setecf Stressor Indicators
Setecf Socml Indicators
Clear Watershed Selections
Clear Ecological indicator Selections
Clear Stressor Indicator Selections
Clear Social Indicator Selections
HUC1?ID
Ecological indicator
Weight
Stressor Indicator
Weight
Social Indicator
Weight
i [031501010101 (Headwaters Conasuaga River)
% Woodf' Vegetation in R2 (2011)
3
% Develo ped. Low intensity in R2 <2011J
2
% GAP States 1 and 2
1
031501010102 (Jades River)
% N-tndex2 in HC2 (2011)
3
% Agriculture in WS (2011)
2
% Streamlengtn Assessed (2015)
1
i 031501010103 (Ball Play Creefc-Conasauga River)
Habitat Condition Index WS (2015)
2
% Streamlength Near 115% Impervious Cover (2011)
2
% Waterbody Area Assessed (2015)
1
' 031501010104 (Old Fort CreeR-Mill 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 303d-Listed Causes Count (2015)
3
Watershed Groups (INSTATE)
2
1 031501010106 (Sugar Creek)
% Streamlength 303
-------
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.
3
6
Screening Run Name:
Tennessee Urban/Suburban Nutrient Screening
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Objectives:
Identify Tennessee HUC12s with significant urban/suburban sources of nurtients
that are restoration priorities.
Watershed Scale:
HUC12
Indicator Selection Notes:
Focus is on indicators relevant to recovery from urban/suburban sources of nutrients.
INSTRUCTIONS I Notes I 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.
¦ Higher Social Index scores correspond to higher recovery potential,
« 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
?adwaters 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-Conasauqa Rrver
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
471i
'050500010105
Big 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
051100020106
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
< ~ ... I Setup I Results I 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:
(IticI iYidiwtj-n^)
IndNorm = Weight * —— _ , , T
{inaMax inuM(n)
where lndNorm is the weight-adjusted normalized indicator value, weight is the indicator weight, Ind is 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)]
¦ 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). This ensures that the #1 rank for any index is consistently 'good'.
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).
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.
EcoRegton (2010) Level 3,1 st Code (Largest Area)
J f I Sort Smallest to Largest
[ i! S^rt Largest to Smallest
J ! Sort by Color ~
1*
Number Filters
TTT
•C Oinarian 7nno fP7\ in ¦»
67705639
Cut
Si) £°py
hQ Paste Options:
A*4 0
Paste Special...
Insert...
Delete-
Clear Contents
Quick Analysis
Filter
Sfiit
J Insert Comment
[!7] Format Cells...
Pick From Drop-down List.
Define Name...
§3 Hyperlink...
75266778
56091445
58351704
63491534
06067519
03809443
60551596
23969159
23682768
60131782
55719195
61391447
08013937
H Hydrologtcally Connect!
v 43142814
~ Ji Sort A to Z
a I Sort Z to A
Put Selected Cell Color On Top
Put Selected Font Color On Top
Put Selected Cell Icon On Top
[If] Custom Sort...
. I.JUUUI t IUI
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 worksheet displays no bubbles until a
screening has been successfully run.
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. Secondary horizontal and vertical axes also appear at the
median values of the Stressor Index and Ecological Index scores, respectively.
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
OS
_o
o
30
10
m,
10
20
30
jQL
Oo CP °
O
O
70
40 50 60
Stressor Index
Note: Circle size increases with Social Index score
80
90
100
Setup Resultsl Bubble_Piot 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 of Ecological Index (horizontal axis)
and Stressor Index (vertical axis) scores listed In Hie Summary_Soores
sheet. Axes can be repositioned to:
|(a) User-Defined Percentiles ~^\
15 This option will position axes to percentiles entered below
16
17
18
19
20
21
Enter a new Stressor Index percentile for the y-axis (0-100):
sol
Enter a new Ecological Index percentile for the x-axis (0-100):
1
REPOSITION AXES
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 gener ally above or betow average for the project area.
Cheung the "ALL WATERSHEDS SCREENING" option below will plot
scores from a screening that includes att watersheds in the project
area, with axes set to Hie median of all Ecological and Slressor 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 ONLY SCREENING
C ALL WATERSHEDS SCREENING
ADD LABELS TO BUBBLES
Select a watershed identifier for labeling:
Watershed Name
"3
Click the button below to label all bubbles:
APPALL LABELS |
Or. select watersheds individually Irom the list
below (repeat to label multiple watersheds):
"3
F Hign/ignt Severed watefsrwl
Mote: To htgMkgtit Che water afled, the box above must b«
checked BEFORE aetectmg from the drop-do wn tel
Label locations can be changed by clicking on the
label in the bubWe plot and dragging to the
desired location
Click the button below to remove labels and
highlighting:
REMOVE LABELS I
EDIT BUBBLE SIZES
Enter a bubble size (1 - 300):
The default bubble sis® is 20.
Ctictc the button below to resize:
RESIZE BUBBLES |
RESET BUBBLE SIZES
SAVE AS IMAGE FILE
Click the button below to save the
bubble plot as an image (jpg) file
SAVE BUBBLE PLOT I
Setup | Results Bubble_Plot 11 Bubble_Plot_Options | |HUC12_Map | HUC8_Data | HUC12_Data
EDIT BUBBLE COLORS
Select the indicator that will determine bubble colors
Select a classification method [~
Select the number of classes. |
Select a color scheme: | *3
Click the button below to update bubble colors:
UPDATE BUBBLE COLORS
"3
"3
Click the button below to reset bubble colors:
RESET BUBBLE COLORS
The following sections provide step-by-step instructions for how to use the Bubble Plot Options worksheet to:
¦ Reposition Plot Axes
a Toggle Between Plotting a Subset Only Screening or an All Watersheds Screening
m Add Labels to Bubbles
m Edit Bubble Sizes
¦ Edit Bubble Colors
m Save an Image File of the Bubble Plot
26
-------
Bubble Plot Options Worksheet-Reposition Axes
7.2. Reposition Secondary Axes
The secondary (median) 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:
n 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).
27
-------
Bubble Plot Options Worksheet-Reposition Axes
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.
¦
REPOSITION AXES
RESET AXES
To move Bubble Plot axes back to their default
position, click the RESET AXES button.
28
-------
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 all 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 W3nt to know if Ecological and Stressor Index scores for
your subset are generally above or below average forthe 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
C? SUBSET ONLY 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.
29
-------
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.
Select a watershed identifier for labeling:
^[Watershed Name
ADD LABELS TO BUBBLES
Click the button below to la bel all bubbles:
ADD ALL LABELS
Or. select watersheds individually from the list
below (repeat to label multiple watersheds):
r* Highlight Selected Watershed
Note: To hightitght the watershed, the box above must be
checked BEFORE selecting from the drop-down Sst,
Label locations can be changed by clicking on the
label in the bubble plol and dragging to the
desired location
Click the button below to remove labels and
highlighting:
REMOVE LABELS
Add Labels Option 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.
30
-------
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
20
10
o
•
jm
J
'
Oq
V
(
$ ^
o
>o
o
vc
L
c—ii \,
o
o
6i
ft
3)0
<~\ f)
Qq
(%
rO
rail creek
?
?
(
»
w
o
<
0
o
[7 Highlight Selected Watershed
A label will be automatically added to the
bubble for the selected watershed.
$ 60
"O
3 50
'I
O 40
o
in
30
20
10
0
10 20
30 40
50
Stres
60
>or Index
70
80
0
O
S.
J
X
° r\
V
^ -
o
..
0
O&Q
C-*ll WaaL-
o
O
GO
#
o
CP
rati LreeK
1
r
c
J
w
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.
10 20
40 SO 60 70 80
Stressor Index
To remove labels from the Bubble Plot, click the
REMOVE LABELS button. This will also remove
any highlighting from labeled bubbles.
REMOVE LABELS
31
-------
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.
32
-------
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
Enter a bubble size (1 - 300):
10]
The default bubble size is 20.
Click the button below to restee:
_ RESIZE BUBBLES
RESET BUBBLE SIZES
33
-------
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:
Quantile Breaks result in classes that have an equal number of watersheds. For example, if four classes are
selected then then quantile breaks option wouid 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-indexi (2006) in Watershed
Select a classification method: [~
Selectthe number of classes: |
I
Select a color scheme | 3
Click the button below to update bubble colors:
UPDATE BUBBLE COLORS
"3
"3
Legend
% Natural Cover, N-index1 (2006) in Watershed
Click the button below to reset bubble colors:
RESET BUBBLE COLORS
34
-------
Bubble Plot Options Worksheet- Edit Bubble Colors
35
-------
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
)% Natural Cover. N-index,i (2006) in Watershed 3
Select a classification method: (user-Defined Breaks "*|
EDIT BUBBLE COLORS
Select the indicator that will determine bubble colors:
Select the number of classes: 4
Selects color scheme: fBlue-Green ^
Click the button below to update bubble colors:
UPDATE BUBBLE COLORS
Legend
% Natural Cover, N-indexl (2006) in Watershed
26.80 - 40
40.01 - 60
60.01 - SO
30.01 - 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
10 20 30 40 50 60 70 80
Stressor Index
Note: Circle size increases with Social Index score
90
100
To return to the default bubble plot (all bubbles shaded
RESET BUBBLE COLORS
| """
36
-------
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.
Chart Export
Please enter a filename.
The plot will be saved in the current directory as filename.jpg
'Test
OK
Cancel
Microsoft Excel
i-£3~r
File saved in current directory as Test.jpg
1
OK
1
1
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 RPS Tool 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.
37
-------
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 RPS Tool will reflect the scale of watersheds
selected for screening. For example, if you screened HIJC12 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 HUCSs. RPS Tools that are setup to screen other watershed scales may not have a map for
those additional watershed scales.
»~
2 I
3 i
4
5 r
6
7
3
9
16
17
IS
19
20
21
22
23
24
25
4
RPI Score For HUC12 Watersheds
Legend
RPI Score
4,51 - 37,81
37 82 - 41.39
41 40 - 4379
43.80 • 45.34
45.35 - 46.95
46 96 - 48 52
48,53 - 50.07
50 08 • 51.39
51.40 - 52.84
52.85 - 85.25
Not Analyzed / Wo Date
Map Options
Seied an indicator to update HUC colors
RPI Score
Select the number of classes:
10 jzi
Select a classification method:
d
[ Quan&le Breaks w\
Seled a color scheme
Blue-Green Tl
Click the outton below to update the map:
Update Map
Reset Map
Click the button below to save the map as a PDF file
Save Map as PDF file
Setup j Results [ Bubble_Plot | Bubble_Plot_Options I HUC12_Map I HUC3_Data HUC12_Data [ Indicatorjnfo
The following sections describe how to:
¦ Create a Custom Map
m Save the Map as a PDF File
38
-------
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:
Quantile 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.
|% Natural Cover. N-index1 (2006) in Watershed
Select the number of classes:
Map Options
Select an indicator to update HUC colors:
Click the button below to update the map:
Update Map
Select a classification method:
Select a color scheme:
3J
3*
Reset Map
ClicKthe button below to save the map as a PDF file:
Save Map as PDF file
39
-------
Map Worksheet-Create Custom Map
Then select the number of classes to use for
mapping (between 2 and 10).
SNext, select a method for defining class breaks
(Quantile, Equal-Interval, or User-Defined).
Select the number of classes:
5
6
7
3
9
10
Select a classification method: Quantile Breaks
i :
Quantile Breaks
"3
Equal-interval Breaks
User-Defined Breaks
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
Upper
Bound
Class 1:
Class 2:
Class 3:
Class 4;
Class 5:
Class 6:
Class 7:
Class 8:
Class 9:
Class 10:
OK
to
25
to
50
50.01
to
75|
to
'
to
to
to
to
to
to
Cancel
40
-------
Map Worksheet-Create Custom Map
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.
Map Options
Select an indicator to update HUC colors:
I % Natural Cover, N-index1 (2006) in Watershed » I
Select the number of classes:
|4 jJ
Select a classification method.
| User-Defined Breaks j^J
Select a color scheme:
j Blue-Green w I
Click the button below to update the map:
Update Map
Reset Map
Click the button below to save the map as a PDF file:
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 any time by revising selections in the Map Options section of the Map
worksheet and clicking the UPDATE MAP button.
% Natural Cover, N-index1 {2006) in Watershed For HUC12 Watersheds
To return to the default map (displaying
Recovery Potential Integrated Index scores),
Reset Map
click the RESET MAP button.
41
-------
Map Worksheet - Save Map as PDF File
8.3. Save Map as PDF File
The watershed map on the Map worksheet can be saved as a PDF file for use in reports or presentations.
Map Options
Select an indicator to update HUC colors:
1 jd
Select the number ot classes:
1 _ll
Select a classification method:
1 -d
Select a color scheme:
1 d
Click the button below to update the map:
Update Map
Reset Map
Click the button below to save the map as a PDF
Save Map as PDF file
1
The watershed map can be saved as a PDF by
clicking the SAVE MAP AS PDF FILE button from
the Map Options section of the Map
worksheet.
You will then be prompted to enter a filename
for the PDF file.
Type a filename into the space provided and
click OK.
Microsoft Excel
1
File saved in current directory as Test.pdf
A record of the Setup and Notes worksheets have also been saved.
OK
A message box will notify you that the PDF file
saved using the filename you provided.
The file will be saved in the same directory that
the RPS Tool 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 watershed map.
42
-------
Indicator Data Worksheet(s)
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)
S Riparian Zone (RZ) in Watershed
8
031501010101
Headwaters Conasuaga River
95344200.0000
23.6771
9
031501010102
Jacks River
127184400.0000
21.7527
10
031501010103
Ball Play Creek-Gonasauga River
77161500.0000
26.5609
11
031501010104
Old Fort Creek-Mill Creek
63687600.0000
225835
12
031501010105
Perry Creek-Conasuaga Rrver
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
242368
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
758871000000
18.4098
25
051100020109
Sugar Creek-Barren River
93788100.0000
22.1411
26
051100020201
Upper Long Creek
134667000.0000
17.9656
27
051100020203
Pinchgut Creek-Barren Rrver
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
703629000000
194088
31
051100020601
West Fork Drakes Creek Headwaters
138340800 0000
19.1800
32
051100020602
Sharps Branch-West Fork Drakes Creek
136977300 0000
143689
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 j Bubble_Plot Bubble_Plot_Options HUC12_Map HUC8_Data I HUC12 Data I Indicatorjnfo HUC.Subsets
43
-------
Indicator Info Worksheet - Overview
10. Indicator Info Worksheet
10.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(s)
Description
7
Hydrotogic Unit Code 8-Digit (HUGS)
Base
HUC8 &HUC12
HUC8 Codo (TEXT}
8
Hydrologic Unfl Code t2-Diqit (HUC12)
Base
HUC 12
HUC 12 Code (TEXT)
9
Name HUGl? Watershed
Base
HUC 12
Name ol primary stream draining area or description ol area bounded by HUC12 polygon (TEXT)
10
Name HUC8 Watershed
Base
HUC8 & HUC12
Name ol primary stream draining area or description of area bounded by HUC08 polygon (TEXT)
11
Area Of Watershed (HUC8) In Acres
Base
HUC8
Total area of the HUC8 watershed Units Acres
12
Area Of Watershed (HUC12) In Square Meiers (Grid)
Base
HUC 12
Total area of tho HUC 12 watershed Value calculated from 30 meter grid ol HUCi2s Units Square Meters
13
% Riparian Zone (RZ) in Waters!>ed
Base
HUC12
% of HUC12 that is in the Riparian Zone The Riparian Zone includes the 100-meter buffer on both sides of (t) water
features in the medium-resolution National Hydrography Dataset (NHO), and (2) areas classified as water in the National
Land Cover Database (NLCD) 2006 land cover dataset
14
% Hydrotogicaly Connected Zone (HCZ) iri Watershed
Base
HUC 12
% of HUC 12 that is in the Hycfrotogicaly Connected Zone The HCZ consists of areas estimated to have htgh runoff potential
based on a topographic wetness index that are contiguous with water
15
% 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 tend (100 - «& Water)
17
Watershed NHDPtus2 Streamlength
Base
HUC 12
Length of NHD stream features in HUC 12 (kiometers) Calculated from the "NHDPIus2 NHD Snapshot" dataset Includes al
NHDF lowline features with FTYPE (feature type) equal to StreamRrver, CanalDitch, or Conneclor NHOFtowtne tenures with
FTYPE equal to ArtjficialPath are onty included if they pass through a MHDArea feature with FTYPE equal to StreamRrver
18
Watershed NHDPtus2 Waterbody Area
Base
HUC 12
Area ol NHD waterbody features in HUC 12 (square kilometers) Calculated from the "NHDPhs2 NHD Snapshot" dataset
19
EPA Region
Base
HUC 12
EPA Reg son Codes for al regions containing HUC12 (Comma delimited, TEXT)
20
Insists Area Ol Watershed (HUC8) In Acres
Base
HUC8
Instate area of tho HUC8 watershed Units Acres
21
Al Slates in HUC8 (WBO)
Base
HUC8
2-lette? state abbreviations for states intersecting HUC8 polygon, ordered alpha be tx-afy and coosma-sepa rated From
"STATES* field of NHDPKis Version 2 Watershed Boundary Dataset snapshot
22
Bofder HUC
Base
HUC8
Flag indicating whether the HUC boundary intersects the TN border
23
Al States in HUC 12 2014
Base
HUC12
2-letter slate abbreviations for states containing at least 1% of HUC 12 instate area, ordered alphabetical/ and comma-
separaled Anatyse based on mhDP*js2 WBD snapshot HUC 12 dataset and 2013 slate boundaries dataset horn
Bubble_Plot Bubble_Plot_Options
HUC12_Map
HUC8_Data
| HUC12_Data | IndicatorJnfo | |l-IUC_Subsets | Addjndicators
The following sections describe how to:
¦ Filter the indicator Info Table
¦ Add Indicator Info
44
-------
Indicator Info Worksheet-Filter Table
10.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.
Indicator Name
6 I
7 Hydrologic Unit Code 3-Digit (HUC8)
3 Hydrologic Unit Code 12-Digit (HUC12)
9 Name HUC12 Watershed
10 Name HUC8 Watershed
11 Area Of Watershed (HUC8) In Acres
12 Area Of Watershed (HUC12) In Square Meters
13 % Riparian Zone (RZ) in Watershed
14 % Hydroloqically Connected Zone (HCZ) in W
15 % Water in Watershed
16 i% Land in Watershed
17 Watershed NHDPIus2 Streamlength
18 Watershed NHDPIus2 Waterbody Area
19 EPA Region
-1 Indicator Type
z J, Sort A to Z
51 Sort Z to A
Sort by Color ~
Clear Filter From "Indicator Type"
Filter by Color ~
Tect Filters ~
Search
S (Select All)
Hjj
Ecological
LI
. . boc
] Stn
20 Instate Area Of Watershed (HUC8) In Acres
21 All States in HUC8 (WBD)
22 Border HUC
OK
D L
Cancel
| past:
Indicator Scale(s)
HUC8 & HUC12
HUC12
HUC12
HUC8 & HUC12
HUC8
HUC12
HUC12
HUC12
HUC12
HUC12
HUC12
HUC12
HUC12
HUC8
HUC8
HUC8
| Indicator Name
~ Indicator Type -T
Indicator Scale(s) ~
% Deciduous Forest (2006) in Watershed
Ecoloqical
HUC12
% Evergreen Forest (2006) in Watershed
Ecological
HUC12
|l% Mixed Forest (2006) in Watershed
Ecological
HUC 12
% Forest (2006) in Watershed
Ecological
HUC12
% Forest (2006) in HCZ
Ecological
HUC 12
% Forest (2006) in Ripanan Zone
Ecological
HUC 12
% Shrub/Scrub (2006) in Watershed
Ecoloqical
HUC12
% Shrub/Scrub (2006) in HCZ
Ecological
HUC12
% Shrub'Scrub (2006) in Riparian Zone
Ecological
HUC12
% Woody Vegetation (2006) in Watershed
Ecological
HUC12
45
-------
Indicator Info Worksheet-Add Indicator Info
10.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.
\
jilo/
3- Cu*
3 ITg) Copy
* *• o „ .
q Paste Options:
3j ifc
5. Paste Special...
(2006) in Watershed
2006) in Watershed
6) in Watershed
'atershed
rian Zone
Delete
4 Clear Contents
4 pi
[TH Format Cells...
4 Row Height...
Hide
4
4 Unhide
i) in HCZ
i) in Riparian Zone
(2006) in Watershed
(2006) in HCZ
(2006) in Ripanan Zoi
sous (2006) in Waters
6
Indicator Maine | ] *
Indicator Type »
30
Tribal Lards in HUC12 Flaq
Base
31
% Tribal Lands in HUC12
Base
1 33
T1JC12 Adjacent to Tribal HUC12s
Base
Lit
M Deciduous Forest (2006) in Watershed
Ecological
35
4# Evergreen Forest (2006) in Watershed
% Mixed Forest (2006) in Watershed
Ecological
Ecological
36
% Forest (2006) in Watershed
Ecological
37
% Forest (2006) in HCZ
Ecological
After right-clicking, select "Insert" from the Excel popup
menu.
A new row will be inserted into the indicator
information table.
Type the name of the new indicator in the Indicator
Name column.
Indicator Name
G1Q Adjooont to Tribal IIUC1e
33 I New Base Indicator
7o ueciauous forest (^uub) in vvatersnea
35 I % Evergreen Forest (2006) in Watershed
36 |% Mixed Forest (2006) in Watershed
f
Indicator Type
ase
Ecological
Ecological
Ecological
46
-------
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 Adjacent to Tribal HUC12s
33
New Base Indicator
Base
34
% Deciduous Forest (2006) in Watershed
CC0t0tJ!C3^
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 ~
Indicator Scale(s) "
32
HUC12 Adiacent to Tribal HUC12s
Base
33
New Base Indicator
Base
HUC12
34
% Deciduous Forest (2006) in Watershed
Ecoloqical 1
hul i z
35
% Evergreen Forest (2006) in Watershed
Ecoloqical
HUC12 c
Type a description of the new indicator in the Indicator
Description column.
6
Indicator Name
Indicator Type"*"*^
—Scale(s) ~
Description
32
HUC12 Ad]acent to Tribal HUC12s
Base
Reservation Trust Lands, and Census 0
33
New Base Indicator
Base
HUC12
Example new Base indicator.
34
% Deciduous Forest (2006) in Watershed
Ecological
HUC12
% of HUC12 with deciduous forest covei
35
% Evergreen Forest (2006) in Watershed
Ecoloqical
HUC12
% of HUC12 with evergreen forest cover
Repeat these steps for each new indicator added to the Indicator Data worksheet.
47
-------
Add Indicators Worksheet - Overview
11. Add Indicators Worksheet
11.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 first
add the HUC8 indicators, clear the worksheet, and then add the HUC12 scale indicators. Up to 50 indicators can be
added at a time for each scale.
ADD MDiCA TORS
Ent»r Indicator Information
£nt#r now md/catex names Aid types betow
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 (row 10) must contain indicator names.
Indicator Name
Indicator Type
Watershed ID
Bubble_Plot_Optioris 1-
UC12_Ma
p
HUCS.Data
HUC12.C
ata Ir
dicatorjr
fo HI
JC.Subset
1
Add
.Indicato
©
The Add Indicators worksheet is organized into two sections:
¦ In the Enter Indicator Information section you will enter a list of new indicator names and types (Ecological,
Stressor, Social, or Base).
¦ In the Enter Indicator Data section you will paste in a table of indicator values for watersheds in the Study Area.
The following sections provide step-by-step instructions for how to:
¦ Enter New Indicator Information
s Enter New Indicator Data
m Use the Add Indicators Button
48
-------
Add Indicators Worksheet-Enter New Indicator Information
11.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 is used to update the indicator menus on the Setup, Bubble Plot Options, and Map worksheets.
Enter New Indicator Information Option 1 -
Type 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 -
Copy 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 Type
'u
Index of Biotic Integrity Score
Ecological
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
15
03 B *5- c*-
Select "Paste Values" from the Excel menu
to paste the new indicator names and
types into the Enter Indicators
Information section.
FILE
| Home
A
Rs
Cut
Paste
Copy ~
% Format Pain
Paste
Index of Biotic Integrity Score
Ecological
Number of CAFOs in WS
% MS4 Area
Stressor
Social
Format Painter
Clipboard U
3
9
10
Enter Indicator Information
Enter new indicator names and types below.
Indicator Name
IndicatorType
111
1
ii
13
14
15
Enter Indicator Information
Enter new indicator names and types below.
Indicator Name
IndicatorType
Index of Biotic Integrity Score
Ecological
Number of CAFOs in WS
Stressor
% MS4 Area
Social
°~te Options
Values (V) A A
49
-------
Add Indicators Worksheet-Enter New Indicator data
11.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
9
1 A »
8
c
D 1
1 1
HUC12
Index of Biotic Integrity Score
Number of CAFOs in WS
% MS4 Area
2
^031501010101
98.50
5
0.00
3
r031501010102
4b. 9935
3
0.00
4
r031501010103
¦9 78.42
0
18.02
S
r031501010104
1
7.96
6
r031501010105
47.08
0
5.88
7
r031501010106
53.29
0
9.56
8
'031501010301
46.89
0
4.21
9 J
r031501010302
50.24
6
4.52
10
'031501010303
57.43
2
9.08
11
r050500010102
1
3.06
12
r050500010103
83.32
1
37.50
13
r050500010105
80.97
0
2.13
14
r051100020101
42.89
0
0.92
15
r051100020102
46.93
0
0.82
16
r051100020105
0
0.41
17
r051100020106
55.19
1
0.69
18
r051100020108
50.26
0
0.00
19
r0S 1100020109
50.61
0
0.34
20
r051100020201
0
0.38
21
r051100020203
54.46
2
0.83
22
"051100020501
53.91
0
0.00
r
50
-------
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
031501010101
031501010102
031501010103
031501010104
031501010105
031501010106
031501010301
"71501010302
031501010303
050500010102
050500010103
050500010105
051100020101
051100020102
051100020105
051100020106
Index of Biotic Integrity Score
98.50
99.35
78.42
47.08
53.29
46.89
50.24
57.43
83.32
80.97
42.89
4693
55.19
Number of CAFOs in WS
1
% MSA Area
0.00
0.00
18.02
7.96
5.88
9.56
4.21
4,52
9.08
3.06
37.50
Past!
HOME INSI
£> Cut
% Format Painter
Clipboard ft
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.
D
8 Enter Indicator Data
10
Watershed ID
12
13!
1A
ow. Your table must have watershed IDs
Select "Paste Values" from the Excel
menu to paste the indicator data table
into the Add Indicators worksheet.
e f G
Enter Indicator Data
Paste your indicator table below. Your table must have watershed IDs in column D arid indicator valu
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
51
-------
Add Indicators Worksheet-Add Indicators Button
11.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 CAFOs in WS
Stressor
(% MS4 Area
Social
Enter Indicator Data
Paste your indicator table below. Your table must have wate
HUC12
Index of Biotic Integrity Score
031501010101
98.49562401
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.
The tool will then check to verify that indicator names, types, and values have been correctly entered on the Add
Indicators worksheet. The tool will also look for potential issues with new indicator data.
If no data issues are found, the new indicators will be automatically added as new columns to the Indicator Data
worksheet and will be added to menus on the Setup, Bubble Plot Options, and Map worksheets. If issues are
identified, an error message will appear.
\
Microsoft Excel
Indicators successfully added. New indicator data is stored in the HUC12_Data
worksheet. New indicators can be selected for screening on the Setup worksheet.
Would you like to clear the Addjndicators worksheet?
Yes
No
Please specify a watershed scale for the new indicators.
c HUC8
IHJC12!
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.
52
-------
HUC Subsets Worksheet - Overview
12. HUC Subsets Worksheet
12.1. Overview
The HUC Subsets worksheet is provided to store lists of watershed subsets that can be copied and pasted into the Select
Watersheds section of the Setup worksheet for screening.
Watershed subsets are comprised of only a portion of all watersheds in the Project Area.
7
8
Conasauga 03160101(7)
Barren 0511CMW2 (13)
Upper Cumberland 05130101 [10)
Upper Cumberland-Lake Cumberland 05130103 |3J
South Fork Cumberland 05130104 |27f
Obey 05130105(22)
Upper Cumberland ¦Corded Hull 05130106 <22)
9
031501010102
061100020101
051301010201
'051301030708
051301040101
051301050101
051301060101
10
031501010103
051100020102
051301010501
051301030709
051301040102
051301050102
O513O1O601O2
11
"031501010104
051100020105
051301010502
"051301030710
051301040103
051301050103
061301060103
12
031501010106
"051100020106
051301010503
051301040201
051301050104
051301060104
13
031501010301
051100020103
051301010601
051301040202
051301050105
051301060105
14
031501010302
051100020201
051301010602
051301040203
051301050106
051301060201
15
031501010303
"051100020203
051301010603
051301040204
051301050107
051301060202
16
051100020501
051301010604
051301040205
051301050108
051301060203
17
051100020503
051301010605
051301040301
051301050109
051301060204
18
"061100020601
051301010701
051301040302
0513O1O5O201
051301060205
19
"051100020602
051301040303
051301050202
051301060206
20
051100020604
051301040304
051301050203
051301060207
n
061100020605
051301040305
051301050301
051301060301
22
'051301040306
051301050302
051301060302
23
051301040307
051301050303
051301060303
24
051301040308
051301050401
051301060304
25
051301040401
[051301050402
051301060305
26
051301040402
^>51301050403
k051301060306
27
051301040501
051301050406
051301060307
28
051301040602
0613011050502
051301060308
29
051301040503
051301050503
051301060309
30
051301040504
051301050504
051301060310
31
051301040506
32
051301040606
33
051301040507
34
051301040506
15
Bubble_Plot_Options HUC12_Map HUC8_Data HUC12_Data Indicatorjnfo I HUC_Subsets I Addjndicators
The following section describes how to Add a Subset List to the HUC Subsets worksheet.
53
-------
HUCSubsets Worksheet-Add a Subset List
'051301070102
"051301070103
'051301070201
'051301070202
'051301070203
'051301070204
'051301070301
'051301070302
'051301070303
v Format Painter
Clipboard ft
Set the cursor to row 8 in the first open column
on the HUC Subsets worksheet.
Options
Values A A
12.2. 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.
7 Conasauoa 03150101 (7) Barren 05110002 (13) Upper Cumberland 05130101 (10)
8 f031501010102
'051100020101
'051301010201
9 1)31501010103
'051100020102
'051301010501
10 '031501010104
t>51100020105
'051301010502
11 "031501010105
"bsi 10002010$
^51301010503
12 '031501010301
'051100020108
¦&51301010601
13 '031501010302
'051100020201
'051301010602
14 '031501010303
'051100020203
'051301010603
15
'051100020501
'051301010604
16
'051100020503
'051301010605
17
'051100020601
'£©1301010701
18
"b51100020602
19
'051100020604
20
'051100020605
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.
54
------- |