Water: Recovery Potential
This page provides assistance of two types concerning the indicator scoring process:
- Basic ways to score a single indicator, and
- How to use this site's automated tools for calculating recovery potential summary indices.
Continuous values. The indicator can have any numerical value along a gradient of possible values (Examples: 3,212.4 acres of protected riparian buffer; 32% highly erodible soils in the watershed). This scoring approach is important when useful to know the differences in magnitude among different entities the indicator is comparing. Most recovery potential indicators are scored in this manner.
Rank ordering. The raw, continuous value of the indicator is used to arrange the entities from highest to lowest and give each a rank number (Examples: 15th highest bioassessment score; smallest watershed size). This method still provides comparisons among entities but the magnitude of differences among ranks is unknown and may involve abrupt or gradual changes.
Intervals. Ranges of indicator value are established and all members within the same interval have the same score (Examples: Percent protected land in 25% increments based on land measurement; number of impairment causes in 25% increments based on quartering the rank-ordered list of waters). This method trades off detail for simplicity, but can be appropriate when all members of each interval can be legitimately generalized to the same value. Intervals may be equal in value ranges or numbers of members, or may be unequal but based on natural breaks in the range of values.
Thresholds. This approach combines continuous and interval valuing concepts, and involves scoring continuously on one side of a threshold value while assigning a simplified, single value to entities on the other side of the threshold (Example: use actual % of impervious cover below 14%, assigning a uniform value of 1 if above 14%).
Absolute value scoring. Some characteristics may have a key target value most meaningful for recovery potential somewhere in the mid-range of values instead of at the maximum or minimum. Values closest to a target value on either side are desirable, and greater distance to either side diminishes the value (Example: nearness to a numeric water quality criterion - waters barely failing the criterion have greater recovery potential than waters severely below the criterion, and threatened waters barely achieving the criterion are of greater priority for restoration than unthreatened waters well above the criterion). Using the mathematical concept of absolute value enables such situations to be scored by calculating the absolute value of the target value minus the individual water's value.
Binary values. The indicator has just two values, 1 or 0. This type of scoring reflects simple presence or absence of a recovery-relevant characteristic (Examples: existence of a TMDL or watershed plan; absence of a target fish species being assessed).
Ordered categorical values. This approach starts with non-numeric categories and assigning them in sequence of importance according to a stated criterion (Example: assigning urban-dominated, agriculture-dominated, and forest-dominated subwatersheds different category values based on general restoration cost and complexity). The method enables consideration of non-numeric concepts that may significantly affect recovery, but assignment of relative value differences should be supportable.
Regardless of what scoring method or methods are used on individual indicators, the objective is to compile, for each indicator used, a numeric value for each water or watershed being screened. As described in Step 3, your raw data should be compiled in data table (e.g., database file) format with water bodies or watersheds as rows and indicators as columns. This format will set the stage for summary score calculation, below.
Calculating recovery potential summary scores. Below, you can download an auto-scoring spreadsheet programmed to normalize and weight individual indicators and automatically calculate ecological, stressor, and social context index scores and the overall Recovery Potential Integrated (RPI) score. As described in Steps 3 and 4, scoring involves several mathematical steps that are individually simple but tedious and error-prone if done manually. Using the scoring spreadsheet below makes score calculations significantly faster and simpler while controlling many sources of error. Briefly, the procedure is:
Open the spreadsheet in Microsoft Excel and save under a new name. Note that there are minor differences in using the spreadsheet in Excel 2003 and Excel 2007 (Compatibility Mode or 2007 mode), but it does work in either version. Version-specific instructions are in the spreadsheet. You may need to adjust your security settings to allow macros to do the calculations.
After reading through the opening worksheet called INSTRUCTIONS, go to the worksheet named SET UP PARAMETERS
In the spaces provided under Standard Baseline Fields, name the baseline attributes you plan to include in the spreadsheet. You can use the default names or re-name the fields yourself. The names you enter on this worksheet will carry over and be reproduced on all the other worksheets. At a minimum, you must include one baseline data field (a name or an ID) and enter the number of baseline fields you are using.
In the cells provided under Ecological Indicator Fields, name the ecological indicators you will include in this screening run. You may use the default names but using more descriptive names specific to each indicator is strongly recommended. At this point you can also assign a weight to each indicator in whole numbers. Default weight is equal to one if left blank. You also need to enter the number of ecological fields you are using in the cell provided.
Repeat the process with the Stressor indicators and the Social Context indicators as done with the Ecological indicators. Then at the worksheet bottom, click the button called SET UP INDICATORS.
Next, open the worksheet named INDICATOR DATA ENTRY and read its instructions.
Back in your raw data table, first ensure that your records are in the ordered sequence you want them to be in (e.g., arrange the water body IDs by ascending value.) Then, copy and paste the columns containing water body ID and name, watershed ID and name, or both from your database file into the appropriately named columns of the scoring spreadsheet. Do not copy the column headers from your data table, just copy the column of data under each header and paste it into the correct baseline data column on the worksheet
Copy and paste each indicator's data into its appropriately named column in the scoring spreadsheet, as you did with the baseline data. In Excel, always use the "Paste Special" option and select "Values" to ensure that your data are treated as numbers not text - or the calculations may fail to execute. If your raw data table and your spreadsheet contain the same indicators in the same exact order, you can select and copy all the data in one move. When all baseline and indicator data have been entered in this worksheet, then click on the CALCULATE button.
All other operations are carried out instantaneously by formulas programmed into the scoring spreadsheet. This includes, for each indicator, calculating the normalized value and normalized-weighted value for each water or watershed being screened (see the worksheet NORMALIZED INDICATOR VALUES). Summary indices for the ecological, stressor, and social context indicator classes are also auto-calculated, as is the RPI score, for every water body or watershed in the dataset (see the worksheet SUMMARY SCORES).
Because the formulas coded to perform these calculations can interfere with copying and pasting these results to other data tables or applications, A final worksheet called VALUES ONLY SUMMARY is included to summarize, without codes, all the normalized and weighted indicator values as well as the four types of summary scores. Use copies of this worksheet for exporting data to all other applications, including the 3D bubble plotting procedure.
- Creating alternative screening runs with different indicator selections or weights can be quickly accomplished in less than an hour by re-saving the whole spreadsheet file under a new name, and then:
- deleting and/or adding indicator names on the PARAMETER SETUP worksheet;
- changing indicator weights;
- re-clicking SETUP;
- adding new raw data for the revised set of indicators; and
- re-clicking CALCULATE.
Downloading the scoring spreadsheet. This Microsoft Excel 2003 spreadsheet is read-only but can be opened for your use by saving under a new name. We recommend that you download and save the read-only version under its current name, then rename and save for each screening run you perform. The spreadsheet is also compatible with Excel 2007.
Download scoring spreadsheet (MS Excel xls, 2.65MB)