"Excel Spreadsheet Storage Tank"
Welcome to the CT Calculator Macros MUST be enabled in Micro for CT Calculator to work pro Intended Audience: Public drinking water systems that use ground water and want to determine if the chemical disinfection uses provides 4-log inactivation of viruses, in order for the water system to qualify for compliance monitoring under the Groun assess need for infrastructure improvements. (Supporting documentation must be prepared and submitted as required by yo justification of 4-log inactivation for the Ground Water Rule. The results provided by this calculator cannot be used as justifica inactivation on their own.) Information Needed to for a Complete Sample Siite 1. Type of Disinfection (Free Chlorine, Chlorine Dioxide, Chloramines, or Ozone) 2. For each sample site being used the: 3. For each sample sit Residual Disinfection Concentration Water pH Water Temperature in Celsius Peak Flow in gallons per minute Review Instructions and Example Instructions User's Guide - Walkthrough of Inputs User's Guide how the calculator work Background Information and System Specifications Background and explanations of the of the User's Guide is a Begin Data Entry Enter Data alculator Macros MUST be enabled in Microsoft Excel for CT Calculator to work properly. o determine if the chemical disinfection their water system compliance monitoring under the Ground Water Rule or to epared and submitted as required by your state for his calculator cannot be used as justification of 4-log Chloramines, or Ozone) 3. For each sample site being used either the: Volume in Gallons. OR Total volume of water storage tank(s) for each GWTF. Length (in feet) and diameter (in inches) for each GWTF that has a cylindrical pipe. User's Guide provide guidance to users on how the calculator works, appropriate inputs, and explanations of the formulas. Appendix B of the User's Guide is a quick reference guide of the formulas used in this calculator. Spreadsheet Overview of "CT Calculator." In order for this spreadsheet to run properly, macros MUST be enabled in Microsoft Excel. This calculator was designed to be a simple tool to help ground water systems determine their system's log inactivation. The CT Calculator is designed to help your ground water system determine its minimum virus log inactivation at each ground water treatment facility (GWTF). A GWTF refers to the disinfection infrastructure of a ground water source. It does not refer to the ground water source itself. A GWTF can either be a single well where disinfection is applied or the location where multiple wells are joined together and disinfection is applied. So if a water system has 2 wells and the output of the 2 wells are disinfected at a single point using the same infrastructure that system would have 1 GWTF. If each well had its own disinfection infrastructure then the water system would have 2 GWTFs. Systems should understand that their states may have additional requirements to ensure that a sufficient amount of disinfection is being reliably provided under all conditions at the water system. Certain safety factors may be established for the water system and its operation (e.g., maximum flow, maximum pH during disinfection contact time) to ensure sufficient disinfection CT is achieved. This spreadsheet will calculate the log inactivation achieved by your facility. The spreadsheet has been designed to calculate the log inactivation provided by your system's treatment train based on various operating parameters (e.g., flow rate, type of disinfectant, temperature, etc.). This report may assist you in determining whether a change in treatment process will benefit or adversely impact your ability to meet your system’s disinfection goals. This spreadsheet is designed for systems that have only one disinfection segment with one treatment stage (such as a clearwell or pipeline) in the disinfection segment. The spreadsheet contains one row for each disinfectant residual measurement. Step 1: Input the number of GWTF at your system in Cell F2 on the Input Sheet. (Note: Up to 20 GWTF can be calculated using this tool.) Step 2: For each GWTF enter the number of sample sites you have. Step 3: Complete the volume at your water system after disinfection is applied but before the first customer: only volumes that are actively in service should be included in the volume calculation. There are three ways to enter the volume. 1) The known total volume of the system after disinfection but before the first customer, 2) The total volume of storage prior to the first customer, or 3) The diameter and length of pipe after disinfection but before the first customer. User's Guide See the CT Calculator's user's guide for for information on how to calculate your system's volume. If your system will use storage tanks and cylindrical pipe to determine CT, enter the storage tank volumes and length and diameter of the pipe and the spreadsheet will add the volume in gallons together. When calculating contact time, use the lowest volume of water in a tank under peak flow conditions. Step 4: Follow instructions for accessing GWTF sheets and click on the "Add a Sheet for Each Ground Water Treatment Facility" button. Step 5: Enter the appropriate data for your system in the blue highlighted cells in Columns D through I on each GWTF Sheet. The baffling factor must be entered in Cell F6 for each GWTF. The values in the remaining cells are automatically calculated by the spreadsheet. The example below demonstrates how a system with one disinfection segment would enter data into the spreadsheet for the first three sample sites. This spreadsheet restricts values for pH, temperature, and chlorine residual concentration to those values included in the CT tables in the LT1ESWTR Disinfection Profiling and Benchmarking Technical Guidance Manual . If values are entered outside of these ranges, one of the following error messages will occur: "check pH", "check temp", or "check residual". Valid ranges are provided under the example below. This spreadsheet does not address ozone system configurations. For more information on calculating log inactivation for systems using ozone, refer to Example D-3 in Appendix D of the LT1ESWTR Disinfection Profiling and Benchmarking Technical Guidance Manual (EPA 816-R-03-004, 2003). If you are a ground water system using ozone, please contact your state for more information. EXAMPLE C D E F G H I J K L M N O Q 5 6 Enter Baffling Factor for GWTF: 1.00 Minimum Log Inactivation Provided: 3.00 7 Additional Log Residual Peak Contact Log Sample Temp. Volume TDT 4-log Inactivati 8 Sample Site Disinfectant Conc. (C) pH Flow Time (T) CTcalc Treatment Date (Celsius) (gal) (min) CTreq on (mg/L) (GPM) (min) Needed for 4- (viruses) Log 9 1 3/14/2008 Free Chlorine 1.00 7.40 9.00 20.0 100.0 5.0 5.0 5.0 6.4 3.13 0.88 10 2 4/13/2008 Free Chlorine 0.90 7.40 10.00 20.0 100.0 5.0 5.0 4.5 6.0 3.00 1.00 11 3 5/13/2008 Free Chlorine 0.99 7.40 10.00 20.0 100.0 5.0 5.0 5.0 6.0 3.30 0.70 12 4 6/12/2008 Free Chlorine 1.00 7.40 10.00 20.0 100.0 5.0 5.0 5.0 6.0 3.33 0.67 Note: Columns P and R are hidden Columns. The following restrictions are imposed in the calculation of CT required for 4-log inactivation of viruses. For Free Chlorine – pH is restricted to between 6 and 10. A pH value lower than 6 and higher than 10 (rounded) will result in a “check pH” error. Chloramines and Chlorine Dioxide – pH is restricted to between 6 and 9. A pH value lower than 6 and higher than 9 (rounded) will result in a “check pH” error. For Free Chlorine – Temperature (T) is restricted to between 0.5 (lower can be entered but the spreadsheet will use 0.5) and 25 degrees C. A T value that is greater than 25 degrees (rounded) will result in a “check temp” error. For Chlorine Dioxide, Chloramines, and Ozone – Temperature (T) is restricted to between 1 (lower can be entered but the spreadsheet will use 1) and 25 degrees C. A T value that is greater than 25 degrees (rounded) will result in a “check temp” error. Step 1: Enter the total number of Disinfection Ground Water Treatment Facilities (GWTF) at your water system. (Up to 20 facilities can be calculated using this tool.) Step 2: Number of Complete Sample Sites Per GWTF: Step 3: By answering "Yes" or "No" to a series of questions the appropriate fields will appear to allow you to determine your Did the state provide or do you know the total volume of each of your GWTF after disinfection but before the first customer? (Enter Yes or No in the cell to the right (C10)) To access the GWTF sheets click on the button below. A box will GWTF 1” click yes and the sheet will appear and another box wil all the way through to GWTF 20. Click no and the sheet will not a unhide the sheets. Unhide as many GWTF sheets as needed. Total Volume after disinfection and prior to first customer: Step 4: GWTF Volume(s) Missing GWTF 1 GWTF 2 GWTF 3 GWTF 4 GWTF 5 te Sample Sites Per GWTF: ppear to allow you to determine your system's volume. ets click on the button below. A box will appear asking “Unhide the following sheet? e sheet will appear and another box will appear asking to unhide the next GWTF sheet WTF 20. Click no and the sheet will not appear and the program will stop asking to e as many GWTF sheets as needed. Enter Length in Feet Enter Diameter in Inches Volume of Pipe - - - - - in Gallons and prior to first customer: - - - - - GWTF 6 GWTF 7 GWTF 8 GWTF 9 GWTF 10 GWTF 11 - - - - - - - - - - - - GWTF 12 GWTF 13 GWTF 14 GWTF 15 GWTF 16 GWTF 17 - - - - - - - - - - - - GWTF 18 GWTF 19 GWTF 20 - - - - - - *Note: Clicking on the button will open the User's Guide in Microsoft Word format