Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook (2015)

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

52 A P P E N D I X A Introduction The ActiveTrans Priority Tool (APT) was developed based on previous research, transpor- tation agency input, professional guidelines and reports, and practical experience, which is described in detail in the NCHRP Project 07-17 Final Report. This appendix explains how to use the programmed spreadsheet, which is built around the APT methodology. The programmed spreadsheet is intended to facilitate prioritization based on the APT methodology; however, the APT can be implemented independently of the pro- grammed spreadsheet using a variety of technological tools. The programmed spreadsheet includes worksheets for all steps in the APT. Figure A-1 shows the relationship of these steps schematically. Users are encouraged to read through the APT Guidebook prior to using this tool. In the programmed spreadsheet, the steps are arranged in order from left to right as individual worksheet tabs. In general, users should go through these steps in sequential order; however, it may be necessary to revisit Step 2: Select Factors and Step 4: Select Variables based on data avail- ability and technical resources. The spreadsheet includes code allowing users to adjust factor and variable selections prior to Step 9: Scale Variables. Users wishing to adjust factors and variables after completing Step 9 should open a new iteration of the programmed spreadsheet and work through the spreadsheet again from Step 1. Improvement locations may be copied and pasted to save time. Programmed Spreadsheet User Guide Tip Be sure to enable macros when opening the programmed spreadsheet by clicking “Enable Content” when the yellow security warning comes up (See Figure A-2). If this warning does not appear, then go to Excel options and confirm that macros are enabled. Step 1: Define Purpose This worksheet (Figure A-3) corresponds to Step 1 of the APT. On this sheet, users must indi- cate the mode and location type being prioritized. For mode, the user must select “pedestrian” or “bicycle.” For location type, the user must select “intersection or crossing,” “roadway segment,” “roadway corridor,” or “neighborhood/area.”

Programmed Spreadsheet User Guide 53 Figure A-1. APT methodology. Figure A-2. Security warning with “enable content” button in Excel 2010/2013.

54 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook The four location types are defined in general below. Transportation agencies can make these definitions more specific for their own prioritization purposes. Location Types Intersection or Crossing An intersection or crossing occurs where two roadways intersect, where a multi-use trail and a roadway intersect, or at a designated mid-block pedestrian crossing. When considering inter- sections, many agencies also include a certain distance (e.g., 50 feet) away from the intersection along each approach leg as a part of the intersection study area. Roadway Segment A roadway segment is any length of roadway between intersections. Some agencies may analyze the full distance between intersections as a segment, while others may choose to divide segments at regular intervals (e.g., every 0.1 miles). Some agencies may also choose to divide segments at mid-block crossings. Both sides of roadway segments may be considered separately (e.g., sidewalk presence on the north versus south side). Roadway Corridor A roadway corridor is a length of roadway that includes more than one segment or inter- section. Agencies often analyze the attributes of both intersections and roadway segments when analyzing roadway corridors. Figure A-3. View of Step 1: Define Purpose worksheet. Tip If the prioritization purpose is focused on identifying high-priority intersection locations or high-priority segment locations, then the respective location type should be selected. However, agencies may also be interested in prioritizing cor- ridors or neighborhoods as a first step before prioritizing specific locations. For example, it may be important to select high-priority multimodal corridors in a regional plan in order to dedicate funding for further prioritization analysis within those corridors. In this case, the “roadway corridor” location type should be selected. Similarly, if the prioritization purpose is to identify areas or neighbor- hoods in which to focus funding or field assessments before identifying specific improvements, then the “neighborhood/area” location type is appropriate.

Programmed Spreadsheet User Guide 55 Neighborhood/Area A neighborhood/area is a geographic region that is not constrained to a single roadway or roadway corridor. Agencies often analyze the attributes of both intersections and roadway seg- ments when analyzing neighborhoods or areas. Step 2: Select Factors This worksheet (Figure A-4) corresponds to Step 2 in the APT. Users must select the factors that will be considered as part of the prioritization process by switching values in the “Select” column from “No” to “Yes.” “Yes” means the factor is selected and related variables will be dis- played on subsequent sheets and included in the calculation of priorities. Tip The factors listed on the Step 2 worksheet are the nine factors identified in the APT. Users are encouraged to read Step 2: Select Factors in the APT to understand how each of these factors is defined. The factor names can be edited, if necessary, and any edits will carry forward to subsequent sheets. However, it is not possible to add factor rows, i.e., the pro- grammed spreadsheet is limited to nine factors. Step 3: Establish Factor Weights This worksheet (Figure A-5) corresponds to Step 3 of the APT. The purpose of Step 3 is to assign weights to each factor. Only the factors that were selected in Step 2 will appear on this sheet. The maximum and default weight is 10. Figure A-4. View of Step 2: Select Factors worksheet.

56 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook Step 4: Select Variables This worksheet (Figure A-6) corresponds to Step 4 of the APT. On this sheet, users select vari- ables for the prioritization. The variables shown depend on the mode and improvement location type selected in Step 1 and the factors selected in Step 2. To include a particular variable, change the value in the “Select” column from “No” to “Yes.” “Yes” means the variable is selected and will be displayed on subsequent sheets and included in the calculation of priorities. Variable names can be edited, and any edits will carry forward to subsequent sheets. Each factor also includes one or more generic variables. Generic variables have names like “Vari- able 1” and “Variable 2.” These generic variable slots provide space for additional variables, if needed, since it is not possible to add new variable rows. For some factors (e.g., Existing Conditions) there are a large number of variables, some of which are relevant for many different prioritization purposes and some of which may be most relevant for very specific purposes. Users should review Step 4: Select Variables in the APT Guidebook for the factors they selected in Step 2: Select Factors before selecting variables on this sheet. Users will also need to consider in Step 5 whether the data required to measure each vari- able is currently available or can be collected. If the data is not currently available and cannot be collected, the variable should not be selected. Tip Factor weights will depend on the prioritization purpose and community values. There are many reasons to weight factors differently and there is no single “right” way to weight any particular set of factors. However, the process should be transparent, and opportunities for public input on the proposed weighting strategy should be provided. Existing research and public input should be incorporated into weighting decisions where possible and applicable. Existing plans and policies can also provide a strong and defensible rationale for weight- ing decisions. Finally, the rationale should be carefully documented, so it can be explained to stakeholders. Figure A-5. View of Step 3: Factors Weight worksheet.

Figure A-6. View of Step 4: Select Variables worksheet.

58 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook Variable Weighting The programmed spreadsheet is set up to apply weights at the factor rather than variable level. However, variable weights can be applied by adjusting the formulas on the Step 10: Calc Priority Score tab to incorporate variable weights. For reasons of simplicity and transparency, it is generally recommended that agencies choose between assigning weights at the factor level and assigning weights at the variable level rather than trying to apply weights at both levels. For additional information about the advantages and disadvantages of variable weighting, see Tip: Variable Weighting in Step 3: Establish Weights of the APT. Step 5: Assess Data This worksheet (Figure A-7) corresponds to Step 5 in the APT. This sheet is included as a reminder that determining the availability of the data needed to measure the variables selected in Step 4 is an important step. Users should review Step 5: Assess Data in the APT Guidebook for guidance regarding the types of data that may be used to express the variables identified in Step 4 and sources for these data. Step 6: Assess Technical Resources This worksheet (Figure A-8) corresponds to Step 6 in the APT. This sheet is included as a reminder that users will need to assess their existing technical resources and capabilities to determine whether they are sufficient to measure the variables selected in Step 4, since the pro- grammed spreadsheet itself does not calculate measures. Users should review Step 6: Assess Technical Resources in the APT Guidebook for guidance regarding technologies and tools that may be used to measure variables they have selected. Tip Using more variables to express a particular factor will not increase the weight of that factor. As additional variables are added to a factor, each of the vari- ables will contribute a smaller proportion to that factor’s overall score. The process for weighting factors is described in Step 3: Establish Factor Weights in the APT. Figure A-7. View of Step 5: Assess Data worksheet.

Programmed Spreadsheet User Guide 59 Step 7: Set Up Prioritization Tool This worksheet (Figure A-9) corresponds to Step 7 in the APT. The programmed spreadsheet is already set up, so there should be nothing for users to do during this step. Step 8: Measure and Input Data This worksheet (Figure A-10) corresponds to Step 8 of the APT. This step is the first step in which rows of the worksheet are used to represent individual improvement locations (i.e., intersections/ crossings, roadway segments, roadway corridors, or neighborhoods/areas). Figure A-8. View of Step 6: Assess Technical Resources worksheet. Figure A-9. View of Step 7: Set Up Prioritization Tool worksheet. Figure A-10. View of Step 8: Measure and Input Data worksheet.

60 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook Before entering any other data, users should fill in the location identification field (“ID”), or com- mon key, in Column A with unique numbers (e.g., 1, 2, 3, . . . or other specific ID numbers already used by the agency) and the “Location” field in Column B with the name of each improvement loca- tion (Figure A-10). This will ensure that each row corresponds to exactly one improvement location. After the improvement locations are identified, users must import the raw variable values for the variables selected in Step 4 for each improvement location. This data will be carried over to subsequent sheets automatically. Raw variable values may be numeric, representing: • Counts of features (e.g., number of lanes, number of public requests). • Measurements of features (e.g., length of a pedestrian crossing in feet, posted speed limit in miles per hour, duration of pedestrian crossing interval in seconds). • Proportions (e.g., percentage of neighborhood households without access to an automobile). Raw variable values may also be non-numeric (categorical), representing: • User-defined categories (e.g., “low,” “medium,” or “high”). • Binary values (“yes” or “no”). • Other types of qualitative data. Tip Using a unique identification field or common key in Column A ensures that the data order and integrity is maintained as raw variable values from several sources are combined together in the worksheet in Step 8: Measure and Input Data. Users must input a value for each improvement location and variable combination. In some cases when data are transferred from another existing source, it may be necessary to “clean” data as it is inputted. This may require users to correct data that does not make sense (e.g., a posted speed limit of 250 mph on a residential street probably has an extra “0” on the end) and to ensure that missing data (blank values) are reviewed and converted to 0s or other numerical values, as neces- sary. This can be done by filtering the ID column to remove blanks and then filtering each variable column in turn to show only the improvement locations with blank values in that column. The blank values can then be reviewed and an appropriate numerical value entered before removing the filter for that variable and moving onto the next one. In some cases, field checks or inquiries to other agencies may be necessary. Bulk edits can be accomplished using the spreadsheet’s “Find and Replace” function. Step 9: Scale Variables This worksheet (Figure A-11) corresponds to Step 9 of the APT, which involves converting non-numeric values to numeric values, selecting a common numeric scale, and adjusting raw values to fit the common scale. Scaling is necessary so that variables have a comparable impact on the prioritization score in the absence of weighting. Scaling should not be confused with weighting. Scaling is a more objective, technical func- tion, while weighting is based on community/agency values. In other words, agencies should not attempt to increase or decrease the influence of variables through scaling.

Programmed Spreadsheet User Guide 61 It is important for users to understand that the process of scaling will have an impact on the final prioritization rankings, so it should be done thoughtfully and transparently. Table A-1 shows how different scaling methods can produce different scaled values. Scaling in the Programmed Spreadsheet The programmed spreadsheet includes default formulas for adjusting the raw variable values entered in Step 8 to a common scale of 0 to 10. To apply one of the default formulas, click the Select Scaling Method box at the top of the scaling column for each variable, select the appropri- ate scaling method, and then click “Apply Scaling.” Users can also enter custom scaling formulas manually by copying the custom formula to the appropriate cells for each variable. Selecting the Appropriate Scaling Method There are several ways to adjust the raw variable values to the common scale, depending on the distribution and relative importance of the values associated with each variable. Methods for scaling numeric values will be discussed first, followed by methods for scaling non-numeric values. Each method includes both an option that assigns the maximum scaled value to the highest raw value and an “inverse” option that assigns the maximum scaled value to the lowest raw value. Users should carefully consider which of these options is appropriate for each variable given their prioritization purpose, recognizing that a higher scaled value will result in a higher prioritization score. Tip Users wishing to adjust factors and variables after completing Step 9 should open a new iteration of the programmed spreadsheet and work through the spreadsheet again from Step 1. Improvement locations may be copied and pasted to save time. Figure A-11. View of Step 9: Scale Variables worksheet.

62 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook Methods for Scaling Numeric Values to the Common Scale Proportionate Scaling and Inverse Proportionate Scaling If the range of values does not include outliers (i.e., minimum or maximum values that are much larger or much smaller than other values), then it is appropriate to adjust the raw numeric values proportionately to fit the common scale. • Proportionate scaling involves assigning the highest value in the common scale to the maxi- mum raw value for a particular variable and assigning 0 to the lowest raw value. Raw Variable Value Scaled Variable Value with Proportionate Scaling Scaled Variable Value with Quantile Scaling (4 Quantiles) Scaled Variable Value with Rank Order Scaling 16 0.0 0.0 0.0 17 0.1 0.0 1.4 22 0.4 3.3 2.9 24 0.6 3.3 4.3 26 0.7 6.7 5.7 32 1.2 6.7 7.1 33 1.3 10.0 8.6 150 10.0 10.0 10.0 Table A-1. Example showing how scaled variable values can vary depending on the chosen scaling method. Tip Normal scaling (assigns maximum scaled value to the highest raw value) and inverse scaling (assigns maximum scaled value to the lowest raw value) can be applied to any variable, depending on the overall prioritization purpose. The key to scaling appropriately is to understand that improvement locations with higher scaled values will be given higher priority in the final prioritization ranking. For example, bicycle facility coverage (0% = no bicycle facilities; 50% = half of seg- ments within corridor have facilities; 100% = all segments within corridor have facilities) may be used as a Connectivity variable for prioritizing corridors for new bicycle lanes. If an agency is interested in providing continuous bicycle facilities along a few important corridors, it may use normal scaling to give the highest value to corridors that already have some facilities, allowing it to fill small gaps and provide continuous bikeway connections in those corridors. In contrast, if an agency is interested in increasing the presence of designated bicycle facilities in more parts of their community, it may use inverse scaling to give the highest value to corridors that currently have few facilities.

Programmed Spreadsheet User Guide 63 • Inverse proportionate scaling involves assigning 0 to the maximum raw value and the highest value in the common scale to the lowest raw value. In Table A-2, the maximum raw value is 5, the scale is 0 to 10, and the raw values are adjusted using proportionate scaling. Table A-3 is the same as Table A-2 except that the raw values are scaled using inverse pro- portionate scaling. To scale raw numeric values in the programmed spreadsheet using proportionate scaling, users should select the “Proportionate Scaling” option from the “Select Scaling Method” drop- down. To scale raw numeric values in the programmed spreadsheet using inverse proportionate scaling, users should select the “Inverse Proportionate Scaling” option. Proportionate scaling and inverse proportionate scaling may not be appropriate if the range of values to be scaled includes outliers. In this case, proportionate scaling may result in a maxi- mum or minimum scaled value that is much higher or lower than the next highest or lowest scaled value, which may be undesirable because it diminishes the level of differentiation among the majority of values and may skew the final prioritization rank for the outlier improvement location. There are several methods for addressing outliers when they are a concern, including quantile scaling and rank order scaling. Quantile Scaling and Inverse Quantile Scaling If the range of values includes outliers, it may be more appropriate to calculate scaled values based on quantiles. Quantile scaling involves assigning each raw value to a quantile (i.e., equal groups containing the same number of values) and scaling the quantile values proportionately to fit the selected scale. In Table A-4, raw values for a variable are divided into four equal groups. Then, the quantile values are scaled proportionately to fit on a 0 to 10 scale. Note that there are two data values for each quantile. Raw Value Scaled Value 4 8 0 0 3 6 4 8 5 10 3 6 2 4 0 0 5 10 1 2 Table A-2. Example of proportionate scaling for a scale of 10.

64 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook Table A-5 is the same as Table A-4 except that the raw values are scaled using inverse quantile scaling. To scale raw numeric values in the programmed spreadsheet using quantile scaling, users should select either the “Quantile Scaling 4 Quantiles” or “Quantile Scaling 10 Quantiles” options from the “Select Scaling Method” dropdown. To scale raw numeric values in the programmed spreadsheet using inverse quantile scaling, users should select the “Inverse Raw Value Scaled Value 4 2 0 10 3 4 4 2 5 0 3 4 2 6 0 10 5 0 1 8 Table A-3. Example of inverse proportionate scaling for a scale of 10. Raw Value Quantile Scaled Value 16 1 0 17 1 0 22 2 3.3 24 2 3.3 26 3 6.7 32 3 6.7 33 4 10 150 4 10 Table A-4. Example of quantile scaling using 4 quantiles.

Programmed Spreadsheet User Guide 65 Quantile Scaling 4 Quantiles” or “Inverse Quantile Scaling 10 Quantiles” options. The choice between 4 or 10 quantiles depends on the number of improvement locations and how the data is distributed. Rank Order Scaling and Inverse Rank Order Scaling Rank order scaling is another method for addressing outliers. Rank order scaling involves calculating the rank of each value in the range and then scaling the rank values proportionately to fit the selected scale. In Table A-6, the raw values for a variable are ranked from low to high. Then the ranked value is adjusted proportionately to fit a 0 to 10 scale. Table A-7 is the same as Table A-6 except that the raw values are scaled using inverse rank scaling. To scale raw numeric values in the programmed spreadsheet using rank order scaling, users should select the “Rank Order Scaling” option from the “Select Scaling Method” dropdown. To scale raw numeric values in the programmed spreadsheet using inverse rank order scaling, users should select the “Inverse Rank Order Scaling” option. Raw Value Quantile Scaled Value 16 1 10 17 1 10 22 2 6.7 24 2 6.7 26 3 3.3 32 3 3.3 33 4 0 150 4 0 Table A-5. Example of inverse quantile scaling using 4 quantiles. Tip Quantile scaling is not appropriate when multiple instances of the same data value would have to be separated into more than one quantile. For example, if there are 20 data values for a variable and 10 of them are 0, dividing the data into 10 quantiles results in two 0s being classified in the first quantile, two 0s being classified in the second quantile, and so on through the fifth quantile. In such cases, methods such as rank order scaling may be more appropriate.

66 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook Scaling Non-Numeric Values to the Common Scale Variables with non-numeric values must be converted to numeric values as part of the scaling process. Converting these values requires users to rank the non-numeric values and convert the ranked values to the common scale. The highest numeric value should go to the non-numeric value with the highest rank, the next highest numeric value to the non-numeric value with the next highest rank, and so on. A higher ranking (i.e., a higher numeric value) will result in a higher prioritization score, and a lower ranking will result in a lower prioritization score. Raw Value Rank Scaled Value 0 1 0 0 1 0 0 1 0 0 1 0 5 2 2 7 3 4 9 4 6 10 5 8 32 6 10 Table A-6. Example of rank order scaling. Raw Value Rank Scaled Value 0 1 10 0 1 10 0 1 10 0 1 10 5 2 8 7 3 6 9 4 4 10 5 2 32 6 0 Table A-7. Example of inverse rank order scaling.

Programmed Spreadsheet User Guide 67 Table A-8 illustrates this process for a case in which the non-numeric values to be scaled are “excellent,” “good,” “fair,” and “poor.” In the programmed spreadsheet, non-numeric values can be converted to the common scale by copying and pasting them into the “SCALED” column and using the spreadsheet’s “Find and Replace” feature to convert them to numeric values. Table A-9 provides guidance for this type of conversion based on the number of discrete non-numeric values, how the discrete non-numeric values are ranked, and a scale of 0 to 10. For example, a variable with two discrete values (e.g., “Yes” or “No”) would be assigned the value of 10 for “Yes” and 0 for “No.” A variable with five discrete values (e.g., “Very Good,” “Good,” “Average,” “Poor,” “Very Poor”) would be assigned the values of 10, 7.5, 5, 2.5, and 0, respectively. Users should record the details of this conversion above the “SCALED” column. Step 10: Create Ranked List This step is divided into two sheets. The first sheet is labeled “Step 10A: Calculate Prior- ity Score” (Figure A-12). The second sheet is labeled “Step 10B: Rank Priority Scores” (Fig- ure A-13). Together, these sheets correspond to Step 10 of the APT methodology. The goal of Step 10 is to create a ranked list. This involves summing the weighted values for each factor (or Non Numeric Value Numeric Value Excellent 4 Good 3 Fair 2 Poor 1 Table A-8. Example of converting non-numeric values to numeric values. Scaled Value for Non Numeric Values Number Discrete Non Numeric Values Highest Rank 2nd Highest Rank 3rd Highest Rank 4th Highest Rank 5th Highest Rank 6th Highest Rank 2 10 0 3 10 5 0 4 10 6.67 3.33 0 5 10 7.5 5 2.5 0 6 10 8 6 4 2 0 Table A-9. Conversion of non-numeric values to scaled numeric values.

68 Pedestrian and Bicycle Transportation Along Existing Roads—ActiveTrans Priority Tool Guidebook variable) to derive a prioritization score for each improvement location. The improvement loca- tions are then ranked based on the prioritization score. All calculations on “Step 10A: Calculate Priority Scores” and on the “Step 10B: Rank Prior- ity Score” are done automatically in the spreadsheet (unless the user wishes to apply individ- ual variable weights). The “Step 10A: Calculate Priority Score” sheet includes columns for the unweighted scores for each factor, columns for the weighted score for each factor, and a column for the prioritization score. The “Step 10B: Rank Priority Scores” sheet includes a column for prioritization score and prioritization rank. Users can use the dropdown menu in the prioriti- zation rank column header to sort this column from smallest to largest, so that the top ranked improvement location appears at the top of the list. Figure A-13. View of Step 10B: Calculate Priority Rank worksheet. Figure A-12. View of Step 10A: Calculate Priority Score worksheet.

Programmed Spreadsheet User Guide 69 Tip It is important for practitioners to review the results of any prioritization scoring and ranking process carefully to understand how weighting, scaling, correlation of variables, and other issues may affect the results. The level of review should be proportional to the level of complexity of the process (i.e., the more factors and variables used, the more scrutiny the process demands). Recommended review steps include: • Review the ranked list and/or a visual representation of the ranked list on a map. Do some improvement locations rank unexpectedly high or unexpectedly low? If so, do the raw variable values make sense? Have the weighting and scaling calculations been done correctly? • Review the scaled values for each variable to understand the impact of scaling and verify that data values are scaled appropriately. • Review the unweighted and weighted scores for each factor to understand the impact of weighting and verify that weighting is having the intended effect. • Review the factors and variables used. Are key policy objectives or community values being fully represented by the chosen factors or variables? Agencies have the ability to use factors and variables that are not presented in the APT methodology.