Water: Recovery Potential
Step 4: Calculate Summary Scores
Screening Methodology Tutorial
At the completion of step 3, your raw data table contains complete, quality-checked values for all your indicators. Most of the level of effort involved in Recovery Potential Screening already occurred in the preceding steps when you developed and evaluated candidate indicators, measured your indicators, and generated your data table. At this point, you have your dataset and now can carry out different screening runs through relatively quick and easy manipulations of the data table. You can vary these screening runs by selecting new combinations of indicators or by weighting existing indicators differently. The best news is that screening tools developed for this purpose make it quick and easy to perform multiple screening runs and evaluate your results. In fact, repeating a screening run with a different selection of indicators can produce a re-analysis of a statewide dataset of thousands of waters in approximately one hour.
The recovery potential scoring spreadsheet (MS Excel xls, 2.65MB) is a downloadable tool customized to help in compiling, organizing and transforming raw to final indicator scores as well as calculating summary indices with minimal human error. The spreadsheet uses data mass-copied and pasted from your raw data table, performs calculations automatically, and generates output data tables of processed indicator scores and four summary indices (ecological, stressor, social, and integrated scores). Complete instructions appear within the spreadsheet file as well as on the scoring guidance page. Although the calculations below are automatic when the spreadsheet is used, brief descriptions are provided to explain the process.
Select indicators for the screening run. Your data by now should contain at least 5 to 10 indicators in each of the ecological, stressor, and social context classes. For your first run you may choose to use some or all of the indicators in each of the three groups. A smaller number (e.g., 3 to 5 per class) is preferable for the first run because it is easier to manage while learning the process, and also because fewer, minimally redundant indicators do a better job of revealing and emphasizing the differences among your waters or watersheds. Correlation analysis can help you select less duplicative combinations from the indicators available.
Once you have made your selection, save a new renamed copy of your raw data table and reduce this working copy to only the indicators selected for this screening run and a few baseline data fields such as watershed ID and watershed name. Enter all the indicator names from your new data table into the spreadsheet's Parameter Setup worksheet in the same order as they occur in the data table, and click on "Setup". Move to the Indicator Data Entry worksheet and paste in all the data values (without the headers) for your selected indicators in one block-and-copy move, then click "Calculate". The spreadsheet then automatically completes the four mathematical operations below, without further action needed:
Normalize indicator values. This process is necessary to correct the unintentional weighting that would happen in a multi-metric index when some indicators measure values in thousands while others may be measured in fractions. The raw values for each indicator are normalized to a maximum value of 1.0 in order to become equally weighted initially, even if you plan to assign different weights during the screening runs. Either the highest possible value or the highest observed value may be the appropriate choice for the maximum of 1.0. Note that although other methods for normalizing can be used manually, normalizing is performed automatically in the scoring spreadsheet (MS Excel xls, 2.65MB) by assigning the maximum value of 1.0 to the highest observed value for each indicator and adjusting the rest of its value range proportionally. These values for all indicators appear automatically after calculation on the Normalized Indicator Values worksheet.
Assign weights if desired. You may choose to leave your indicators equally weighted for your first screening run and then experiment with weighting, or you may feel strongly that assigning weights is needed for all your screening runs. Weighting is also supported by the scoring spreadsheet (MS Excel xls, 2.65MB), where weights are entered on the Parameter Setup worksheet (the default is equal weighting if no weights are entered.) This enables very rapid recalculation while evaluating different weighting scenarios, simply by entering new weights on the Parameter Setup worksheet and remembering to click "Calculate" on the Indicator Data Entry worksheet again to cause the new weighting scheme to be used in calculations.
Calculate ecological, stressor and social indices. Summary indices are automatically calculated within the spreadsheet and are available on the Summary Scores and the Values-Only Summary worksheets. The manual instructions below describe what calculations are involved, both to explain the unseen automated functions of the spreadsheet and provide directions for users working manually in formats other than the scoring spreadsheet.
Within each of the three classes (ecological, stressor, social), a summary index is calculated for each water or watershed in your dataset by adding along each row all the normalized indicator values, dividing by the number of indicators you selected in that class, and then multiplying by 100. This approach assumes the indicators are equally weighted. If the indicators have been assigned unequal weights, each indicator's normalized values must first be multiplied by its weight. Then, the sum of the normalized, weighted indicator values is divided by the sum of the weights instead of by the number of indicators, before multiplying by 100. This calculation generates ecological, stressor, and social context indices for each water body. The indices have a maximum possible value of 100 no matter how many indicators are used or how they were weighted. Just as with the directionality of individual indicators, the higher ecological and social context indices and the lower stressor indices imply greater recovery potential. Because of the separate indices, the often substantially different ecological, stressor, and social influences on recovery potential of each screened water body can be considered simultaneously or separately. If Recovery Potential Screening used only a single index, however, the relative influence of ecological versus social factors could easily be masked by combining them into a single value.
Calculate RPI integrated score. Although the three indices are useful for distinguishing between different influences on restorability, some users also prefer to have a single, integrated final score available. A simple integrated score called the RPI score is automatically calculated within the spreadsheet and should already be available on the Summary Scores and the Values-Only Summary worksheets. RPI score is calculated by adding Ecological and Social Context index values and divide by the Stressor index value for each watershed. If all three classes have been directionally aligned as recommended in Step 3, a higher RPI score implies better recovery potential.
Perform a QA/QC check. Automation reduces or eliminates substantial opportunities for human error, but even if the scoring spreadsheet has been used, quality control checks should still take place on the calculations in step 4. Some places to check for errors in manual or automated calculations are:
- including the intended selection of indicators in the spreadsheet;
- properly assigned weights;
- whether each indicator's value was directionally aligned properly relative to recovery potential;
- whether missing data affects any summary index values;
- multiplying an indicator's values by its weight when weighting is used;
- properly counting indicators and/or weights as the denominator for each summary index; and
- whether the three indices were used properly in RPI score calculation.