PHA United States °^ce Environmental Protection Agency November 2021 Alternative Methods Calculator Tool User Guide Summary This user guide is for EPA's Alternative Methods Calculator Tool (AltCalc Tool; 821-B-21-002). The information in this user guide is also included in the first tab of the AltCalc Tool's excel file. Background EPA's Site-Specific Alternative Recreational Criteria Technical Support Materials for Alternative Indicators and Methods was published in 2014 (Alternative Methods TSM; EPA-820-R-14-011). The Alternative Methods TSM provides support materials for state and local authorities that want to develop site-specific alternative criteria using new methods for fecal indicator detection or enumeration that EPA has not validated and issued. The Alternative Methods TSM outlines the scientific information needed before an alternative indicator/method can replace the use of a recommended or approved method on a site-specific basis. The AltCalc Tool provides a user-friendly way to compute the index of agreement and R-squared statistics for paired indicator data as described in EPA's Alternative Methods TSM (pages 16-19). File Name The file name for the AltCalc Tool is: site-specific-alt-calculator-tool System Requirements Microsoft Excel (2007 or more recent). Windows PC recommended. User Guide Step 1: Water Quality Data Entry • On the Tool Operation tab, enter the names of the EPA approved indicator (Method 1, X-axis) in cell A3 and the alternative indicator (Method 2, Y-axis) in cell B3; for clarity, units of measurement can also be included. Note that the values entered in cells A3 and B3 will be used as axis labels in the resulting graph displayed in the Figure tab. • Enter the paired indicator data values starting in cells A4-B4 and continuing below. The entered data should be the actual measured values and not log-transformed values. • Only data pairs with non-text, non-zero, and non-negative values can be input in the tool. If zero or negative values are entered, the tool will generate warning messages and the analysis will not proceed. Blank cells and text strings to indicate data below the level of detection or quantitation (such as the terms "BLOQ" or "BDL") are permitted, but the corresponding data pair will not be considered in the analysis or displayed in the graph. The Alternative Methods TSM recommends a minimum of 30 data ------- pairs within the quantification limits of the assay. If fewer than 30 valid data pairs are entered, a warning will be generated. However, the tool will generate results with fewer than 30 data pairs but requires at least 3 data pairs to run. If fewer than 3 data pairs are entered, an error message is generated. • Note that data may only be entered in cells A4-B4 and below; other cells in the Tool Operation tab are protected. • Optional Feature - Add threshold line to the figure: In cell D3, users may enter a value that corresponds to a threshold for the X-axis (EPA approved) indicator. In the figure that is generated, a vertical dotted line will appear at that value after the user clicks on run. This vertical line would correspond to the EPA recommended RWQC values. Please refer to EPA-820-R-14-011 document (referenced above) to select the appropriate threshold (i.e., criteria value) for your use (see page 21 for Geometric Mean (GM) and pages 23-24 for Statistical Threshold Value (STV) thresholds). This vertical threshold line is optional. If you prefer not to have a threshold line on the figure, leave cell D3 blank. • In cell D6, users may enter a value that corresponds to a threshold for the Y-axis (alternative) indicator. In the Figure, a horizontal dotted line will appear at the threshold value after the user clicks on run. This horizontal line corresponds to the criteria value for your new indicator. Please refer to EPA-820-R- 14-011 document (referenced above) to calculate the threshold (criteria value) that would correspond to your new indicator (see Section 3 starting on page 20 of the Alternative Methods TSM). This horizontal threshold line is optional. If you prefer not to have a Y-axis threshold line on the figure, leave cell D6 blank. Step 2: Launch Computation • Click on the Run button. Step 3: View Results • The computed index of agreement is output in cell G2. The computed R-squared is output in cell G3. The number of valid data pairs considered in the analysis is output in cell G4. A scatter plot of the valid data pairs is generated in the Figure tab. Step 4: Enter Optional Data Documentation • The Optional Data Documentation tab may be used to store dataset documentation so that future users may understand the origin of the values in the input data tab. This tab helps with organizing, archiving, and pointing to documentation that contains the detailed sampling information. Information recorded for documentation may include: o Name of dataset o Name of institution/agency that developed the data o Name of person who entered data into the tool o Date of analysis o Site location o Date of sampling window (e.g., summer 2014) o Units for each of the indicators o Sources of fecal contamination o Name and location of supporting documentation such as: Sampling and Analysis Plans, Quality Assurance Project Plans, Sanitary Surveys for the site, publications, and reports 2 ------- Step 5: Save Analysis • Click on the Save button. A message box appears, querying whether to save the analysis as a worksheet in the current workbook. If yes, the user is prompted for a sheet name. If not, a message box appears, prompting the user to save the analysis file as an xlsx file with a default name that the user may modify. The user may choose to save the file as an xlsm file, which will allow the use of the macros in that file. However, if the file is being saved solely for purposes of documenting the results of the analysis conducted then the default xlsx format is recommended. Clear Analysis Click on the Clear button to delete all data and results. Users must clear previous analyses before commencing a new analysis. Note that once the clear button is deployed, it cannot be undone. Troubleshooting • Enable macros in Excel. • Check to ensure that non-zero, non-negative data are not input. • Check that more than two pairs of non-text, non-zero, and non-negative data are available. • Analysis will not run if there is no variance in the Method 1 or Method 2 data (i.e., all values are identical). • Try clearing and re-running the analysis. • Try exiting the file and re-running the analysis. • Certain Excel formats such as date and currency should not be entered in the measurement data entry columns on the Tool Operation tab. • Axis scale in the Figure tab should be manually adjusted if values below 0.01 are not visible. • If running this tool on Mac OS X, you may experience issues using the Save button on the Tool Operation tab. This is related to limited VBA feature support on certain version combinations of Mac OS X and Excel. In such an event, users may save the analyses using standard menu features in Excel. Quality Assurance Quality Assurance was performed by the AltCalc Tool developer (ICF; EPA contract EP-C-16-011) in order to confirm accuracy of the calculations, duplicate analyses in R using the code published in the Alternative Methods TSM (Appendix F). The AltCalc Tool accurately replicated the analyses that were originally performed for the Alternative Methods TSM (Appendix C and D) using the R tool. In order to assess whether the tool generates the necessary warnings, tested edge cases with not enough data for comparison were provided, for cases with less than 30 valid data pairs, and where alternative symbols for below detection level data were used. The tool generates the necessary warnings. The spreadsheet is Section 508 compliant for accessibility. Where can I find more information? The Alternative Methods Calculator Tool is available at: https://www.epa.gov/svstem/files/other-files/2021- 11/site-specific-alt-calculator-tool.xlsm Visit EPA's Recreational Water Quality Criteria and Methods webpage, which includes information on the Alternative Methods TSM: https://www.epa.gov/wqc/recreational-water-quality-criteria-and-methods For technical support, email Shamima Akhter at: Akhter.Shamima@epa.gov 3 ------- |