{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["User Guide","Tool Operation","Figure","Figure Data","Optional Data Documentation"],"not_rendered":{"charts":2,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[{"sheet":"Figure","index":2,"reason":"not_a_worksheet"}],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"User Guide","index":0,"n_rows":32,"n_cols":3,"rows_kept":32,"cut":false,"hidden":false,"merged":[[30,1,31,1]]}
[]
[null,"Original Filename ","site-specific-alt-calculator-tool.xlsm "]
[null,"EPA Document Number","EPA-821-B-21-002"]
[null,"Publication Date","November. 2021"]
[]
[null,"System Requirements","Microsoft Excel (2007 or more recent). Windows PC recommended."]
[]
[null,"Summary","\nThis user guide is for EPA’s Alternative Methods Calculator Tool (AltCalc Tool). The information in this user guide is also included in the stand-alone User Guide (EPA-821-B-21-001).\n  "]
[]
[null,"Background","\nEPA’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). \n  "]
[null,"Link to Alternative Methods TSM","https://www.epa.gov/wqc/recreational-water-quality-criteria-and-methods\n"]
[null,"Alternative Methods TSM PDF","https://www.epa.gov/sites/default/files/2015-11/documents/sitespecific-alternative-recreational-indicators-methods.pdf"]
[]
[null,"User Guide\nStep 1","\nStep 1: Water Quality Data Entry\n• 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.\n• 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.\n• 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.\n• Note that data may only be entered in cells A4-B4 and below; other cells in the Tool Operation tab are protected.\n     "]
[null,"User Guide\nOptional Feature","\nOptional - add threshold line to the figure\n• 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.\n• 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.\n   "]
[]
[null,"User Guide\nStep 2","\nStep 2: Launch Computation\nClick on the Run button.\n  "]
[]
[null,"User Guide\nStep 3","\nStep 3: View Results\nThe 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.\n   "]
[]
[null,"User Guide\nStep 4","\nStep 4: Enter Optional Data Documentation\nThe 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:\n• Name of dataset \n• Name of institution/agency that developed the data\n• Name of person who entered data into the tool  \n• Date of analysis\n• Site location\n• Date of sampling window (e.g. summer 2014)\n• Units for each of the indicators\n• Sources of fecal contamination\n• Name and location of supporting documentation such as: Sampling and Analysis Plans, Quality Assurance Project Plans, Sanitary Surveys for the site, publications, and reports\n    "]
[]
[null,"User Guide\nStep 5","\nStep 5: Save Analysis\nClick on the Save button. A message box appears, querying if user wishes 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 documentation of the results of the analysis conducted then the default xlsx format is recommended.\n   "]
[]
[null,"User Guide\nClear Data Feature","\nClear Analysis\nClick on the Clear button to delete all data and results. Users must clear previous analyses before commencing a fresh analyses. Note that once the clear button is deployed, it cannot be undone.\n   "]
[]
[null,"Troubleshooting","\nEnable macros in Excel. \nCheck to ensure that non-zero, non-negative data are not input.\nCheck that more than two pairs of non-text, non-zero, and non-negative data are available.\nAnalysis will not run if there is no variance in the Method 1 or Method 2 data (i.e., all values are identical). \nTry clearing and re-running the analysis.\nTry exiting the file and re-running the analysis.\nCertain Excel formats such as date and currency should not be entered in the measurement data entry columns on the Tool Operation tab.\nAxis scale in the Figure tab should be manually adjusted if values below 0.01 are not visible.\nIf 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 analyses using standard menu features in Excel. \n   "]
[]
[null,"Quality Assurance","\nQuality 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. \nIn 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.\nThis spreadsheet is Section 508 compliant for accessibility.\nThe cells with equations are protected to prevent accidental modifications to the equations. If future developers need to modify these cells they can be unlocked with the password EPAtool.   "]
[]
[null,"For More Information","Visit EPA’s website for the Alternative Methods TSM:   https://www.epa.gov/wqc/recreational-water-quality-criteria-and-methods\n "]
[null,null,"Contact EPA at: Akhter.Shamima@epa.gov for technical support"]

{"sheet":"Tool Operation","index":1,"n_rows":148,"n_cols":6,"rows_kept":148,"cut":false,"hidden":false,"merged":[[0,5,0,6],[3,3,4,3],[0,0,0,1],[0,3,1,3]]}
["Please Enter Indicator Names in Row 3 and Paired Data Values from Row 4",null,null,"Please Enter X-Axis Threshold Value (RWQC Value), If Any",null,"OUTPUT"]
["EPA-Approved Indicator (Method 1)","Alternative Indicator (Method 2)",null,null,null,"Index of Agreement:"]
["E. coli by Colilert Quanti-Tray/2000 (MPN/100 mL)","Lume TLF screening index (CFU/100 mL equiv.)",null,126,null,"R-squared:"]
[1986,79.91079999999999,null,"Please Enter Y-Axis Threshold Value (new indicator criteria value), If Any",null,"Paired samples (n):"]
[16,12.4305]
[27,12.4331,null,126]
[53,78.32599999999999]
[15,78.6819]
[17,12.4199]
[50,78.21120000000001]
[7,12.4224]
[21,79.2225]
[5,12.4152]
[152,79.55549999999999]
[517,136.5984]
[2,12.3121]
[44,80.0641]
[36,137.7653]
[3,12.3604]
[30,12.3923]
[105,132.5555]
[23,12.4545]
[1986,136.5939]
[9,12.1115]
[74,79.4365]
[46,125.6506]
[61,127.911]
[10,11.284]
[30,79.0979]
[48,128.358]
[26,79.6371]
[32,133.9503]
[51,129.1087]
[10,11.2519]
[34,79.1371]
[260,128.9236]
[84,127.8658]
[34,79.7704]
[6,11.2282]
[34,79.883]
[53,127.9924]
[99,127.6026]
[261,79.4806]
[32,11.1182]
[124,79.7349]
[276,127.7708]
[14,11.6077]
[378,80.5766]
[1046,139.0161]
[192,12.2037]
[155,23.09]
[210,52.0851]
[228,80.8137]
[365,133.0134]
[866,100.4462]
[88,101.2814]
[93,127.1931]
[29,78.21299999999999]
[50,52.1216]
[15,22.7599]
[10,11.6272]
[7,11.6054]
[10,22.7559]
[33,52.084]
[272,78.5881]
[71,126.394]
[74,101.593]
[23,11.572]
[28,22.7533]
[28,52.0993]
[63,77.8959]
[96,127.5338]
[137,100.6392]
[14,11.5752]
[10,22.7643]
[19,52.0833]
[48,78.40000000000001]
[121,126.0079]
[99,100.6565]
[4,11.5674]
[10,22.7455]
[21,52.0907]
[46,79.2885]
[73,126.2846]
[69,100.0912]
[15,11.5228]
[39,22.8202]
[49,52.0089]
[66,79.0034]
[54,125.8076]
[115,96.28]
[10,11.5515]
[19,22.8139]
[44,52.0535]
[38,77.5925]
[49,126.8154]
[39,126.9374]
[16,11.6512]
[28,22.8872]
[72,51.9672]
[133,79.4481]
[65,126.1108]
[109,95.66670000000001]
[7,11.542]
[18,22.8702]
[36,52.0227]
[113,78.2921]
[54,125.031]
[69,122.9373]
[26,15.7201]
[41,22.5369]
[51,51.7911]
[69,78.20780000000001]
[55,125.0374]
[65,94.78]
[58,49.5614]
[34,50.8104]
[249,50.9347]
[41,50.8807]
[41,50.8587]
[44,50.7588]
[50,50.7331]
[32,50.7455]
[43,50.7029]
[10,12.2732]
[38,23.785]
[108,51.229]
[159,79.7927]
[178,129.995]
[78,96.3937]
[214,97.25109999999999]
[870,130.9138]
[548,79.501]
[53,97.4081]
[89,128.4974]
[54,77.6493]
[37,51.3879]
[13,30.8278]
[9,12.8315]
[43,97.2803]
[276,127.7494]
[74,78.7878]
[68,51.2689]
[14,22.6131]
[2450,123.8619]
[299,103.783]
[153,50.9705]
[47,22.5843]

{"sheet":"Figure Data","index":3,"n_rows":3,"n_cols":7,"rows_kept":3,"cut":false,"hidden":true,"merged":[[0,3,0,4],[0,5,0,6],[0,0,0,1]]}
[null,null,null,"Please Enter X-Axis Value for Threshold",null,"Please Enter Y-Axis Value for Threshold"]
[null,null,null,{"$f":"'Tool Operation'!$D$3","v":null},1e-05,{"$f":"'Tool Operation'!$D$6","v":null},1e-05]
[null,null,null,{"$f":"'Tool Operation'!$D$3","v":null},{"$f":"MAX(B4:B1048576)+ (0.1*MAX(B4:B1048576))","v":null},{"$f":"'Tool Operation'!$D$6","v":null},{"$f":"MAX(A4:A1048576)+0.5*MAX(A4:A1048576)","v":null}]

{"sheet":"Optional Data Documentation","index":4,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Optional Data Documentation","Enter information for the data in this spreadsheet"]
["Name of dataset","Boulder Creek Segment 2b - paired Lume / Colilert, in-situ deployment"]
["Institution","Virridy / Mortenson Center in Global Engineering and Resilience, CU Boulder"]
["Site location","Boulder Creek Segment 2b, Boulder, Colorado (6 sites)"]
["Units","Method 1: MPN/100 mL (Colilert Quanti-Tray/2000). Method 2: CFU/100 mL equivalent."]
["Limits of quantification","Method 1 >= 1 MPN/100 mL; Method 2 >= 10 CFU/100 mL. Non-detects excluded."]
["Supporting documentation","Method LUME-1; validation.thelume.ai/TLF-Method/Boulder"]
["• Method 1 name and units"]
["• Method 2 name and units"]
["• Sources of fecal contamination at the site (known or likely)"]
["• Name and location of supporting documentation such as: Sampling and Analysis Plans, Standard Operating Procedure (SOP), Quality Assurance Project Plans, Sanitary Surveys for the site, publications, and reports"]