{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Freshwater pH RPA","Marine pH RPA","Version notes"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":3,"defined_names":45,"comments":1,"pivot_tables":0},"unopened":[],"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":"Freshwater pH RPA","index":0,"n_rows":83,"n_cols":26,"rows_kept":83,"cut":false,"hidden":false,"merged":[[30,1,31,3],[33,1,33,3],[49,1,49,3],[50,1,50,3],[51,1,52,1],[26,1,26,3],[27,1,28,3],[27,5,29,7],[29,1,29,3],[17,16,17,18],[0,1,0,4],[10,1,10,3],[10,5,10,7],[15,10,15,11],[17,13,17,14],[17,9,17,11],[11,1,12,1],[5,1,8,1],[17,23,17,25],[3,2,3,12],[11,12,11,13],[12,10,13,11],[14,10,14,11],[14,13,14,14],[15,13,15,14],[12,13,13,14],[17,20,17,21],[12,20,15,20]]}
[null,"Freshwater pH RPA v1.8"]
[null,"Facility Name: ","Instructions:",null,null,null,null,null,null,null,null,null,null,null,null,null,"Calculation of pH of a mixture of two flows."]
[null,"Analyst:","1.  Enter information into the white blank cells to the left",null,null,null,null,null,null,null,null,null,null,null,null,null,"Based on the procedure in EPA's DESCON program (EPA, 1988. Technical "]
[null,"Run Date:","2.  Enter ambient and effluent data into columns below to the right (up to 500 data sets), use ICIS summary stats when available or Alkalinity Defaults if needed ",null,null,null,null,null,null,null,null,null,null,null,null,null,"Guidance on Supplementary Stream Design Conditions for Steady State"]
[null,"Run Notes:","3.  Enter mixing zone dilution value (1.), existing pH limits (3.pH), and the pH criterion (4.) in the pH Analysis table",null,null,null,null,null,null,null,null,null,null,null,null,null,"Modeling.  USEPA Office of Water, Washington D.C.)"]
[null,null,"4.  Enter effluent and ambient data source information below pH analysis table"]
[null,null,"5.  If there is reasonable potential for lower or upper pH, click the applicable blue button to calculate pH limit"]
[null,null,"6.  Copy and paste \"pH Analysis\" table into fact sheet - double click video link below for a simple tutorial "]
[null,null,"NOTE: If you need to update the analysis, hit the RESET button then hit the calculate buttons."]
[]
[null,"pH Analysis",null,null,null,"Summary Statistics",null,null,null,null,null,"ICIS Summary Statistic Data for Effluent Temperature"]
[null,"INPUT","Lower pH","Upper pH",null,null,"10th %","90th%",null,null,null,null,"Using ICIS Data?",null,"*"]
[null,null,"Criteria","Criteria",null,"Ambient temperature",{"$f":"IF(ISBLANK($S$20),\"--\",PERCENTILE($S20:$S520,0.1))","v":"--"},{"$f":"IF(ISBLANK($S$20),\"--\",PERCENTILE($S20:$S520,0.9))","v":"--"},null,null,"Does permittee report Monthly or Daily Avg?",null,"*","Does permittee report Monthly or Daily Max?",null,"*",null,null,null,null,"Do I Need Effluent Alkalinity Defaults?"]
[null,"1.  DILUTION AT MZ BOUNDARY (7Q10 from MZ Memo)","enter value","enter value",null,"Ambient pH",{"$f":"IF(ISBLANK($L$20),\"--\",PERCENTILE($L20:$L520,0.1))","v":"--"},{"$f":"IF(ISBLANK($L$20),\"--\",PERCENTILE($L20:$L520,0.9))","v":"--"}]
[null,"2.  UPSTREAM CHARACTERISTICS",null,null,null,"Ambient alkalinity",{"$f":"IF(ISBLANK($Z$20),\"--\",PERCENTILE($Z$20:$Z$520,0.1))","v":"--"},{"$f":"IF(ISBLANK($Z$20),\"--\",PERCENTILE($Z20:$Z520,0.9))","v":"--"},null,null,"Max of avg",null,null,"90th %ile of the max",null,null,null,null,null,null,null,"*"]
[null,"      Temperature (deg C):",{"$f":"IF(ISBLANK($H$13),\"--\",H13)","v":"--"},{"$f":"IF(ISBLANK($G$13),\"--\",G13)","v":"--"},null,"Effluent temperature",{"$f":"IF(AND($O$12=\"Yes\",$M$13=\"Yes\"),M16,IF(AND($O$12=\"Yes\",$P$13=\"Yes\"),P16,IF(ISBLANK($O$20),\"--\",PERCENTILE($O$20:$O$520,0.1))))","v":"--"},{"$f":"IF(AND($O$12=\"Yes\",$M$13=\"Yes\"),M15,IF(AND($O$12=\"Yes\",$P$13=\"Yes\"),P15,IF(ISBLANK($O$20),\"--\",PERCENTILE($O$20:$O$520,0.9))))","v":"--"},null,null,"Min of avg",null,null,"Min of the max"]
[null,"      pH:",{"$f":"IF(ISBLANK($G$14),\"--\",G14)","v":"--"},{"$f":"IF(ISBLANK($H$14),\"--\",H14)","v":"--"},null,"Effluent alkalinity",{"$f":"IF($V$15=\"Yes\", 64,IF(ISBLANK($V$20),\"--\",PERCENTILE($V$20:$V$520,0.1)))","v":"--"},{"$f":"IF($V$15=\"Yes\",134.56,IF(ISBLANK($V$20),\"--\",PERCENTILE($V$20:$V$520,0.9)))","v":"--"}]
[null,"      Alkalinity (mg CaCO3/L):",{"$f":"IF(ISBLANK($G$15),\"--\",G15)","v":"--"},{"$f":"IF(ISBLANK($G$15),\"--\",G15)","v":"--"},null,null,null,null,null,"Ambient pH Data",null,null,null,"Effluent Temp Data",null,null,"Ambient Temp Data",null,null,null,"Effluent Alk Data",null,null,"Ambient Alk Data"]
[null,"3.  EFFLUENT CHARACTERISTICS",null,null,null,null,null,null,null,"Location","Date","pH Value",null,"Date","Deg C",null,"Location","Date","Deg C",null,"Date","mg/l",null,"Location","Date","mg/l"]
[null,"      Temperature (deg C):",{"$f":"IF(ISBLANK($H$16),\"--\",H16)","v":"--"},{"$f":"IF(ISBLANK($G$16),\"--\",G16)","v":"--"}]
[null,"      pH (S.U.) - Enter existing lower and upper limit","enter value","enter value"]
[null,"      Alkalinity (mg CaCO3/L):",{"$f":"IF(ISBLANK($H$17),\"--\",H17)","v":"--"},{"$f":"IF(ISBLANK($H$17),\"--\",H17)","v":"--"}]
[null,"4.  APPLICABLE PH UPPER AND LOWER CRITERIA","enter value","enter value"]
[null,"      pH at Mixing Zone Boundary:",{"$f":"IF(C14=\"enter value\",\"--\",C48-LOG((C47/C46)-1))","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",D48-LOG((D47/D46)-1))","v":"--"}]
[null,"      Is there Reasonable Potential?",{"$f":"IF(OR(C14=\"enter value\",C23=\"enter value\"),\"--\",IF(ROUND(C24,1)<ROUND(C23,1),\"Yes\",\"No\"))","v":"--"},{"$f":"IF(OR(D14=\"enter value\",D23=\"enter value\"),\"--\",IF(ROUND(D24,1)>ROUND(D23,1),\"Yes\",\"No\"))","v":"--"}]
[null,"      Proposed Effluent Limits",{"$f":"IF(OR(C21=\"enter value\",C23=\"enter value\"),\"--\",IF(C25=\"no\",C21,IF(C67=0,\"calculate\",C67)))","v":"--"},{"$f":"IF(OR(D21=\"enter value\",D23=\"enter value\"),\"--\",IF(D25=\"no\",D21,IF(D67=0,\"calculate\",D67)))","v":"--"}]
[null,"Effluent Data Source:"]
[null,null,null,null,null,"Video Tutorial for Copying and Pasting Summary Table into Fact Sheet                   (double click)"]
[]
[null,"Ambient Data Source:"]
[]
[]
[]
[null,"Intermediate Calculations"]
[null,"1.  IONIZATION CONSTANTS"]
[null,"      Upstream/Background pKa:",{"$f":"IF(C14=\"enter value\",\"--\",6.57-0.0118*C16+0.00012*(C16^2))","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",6.57-0.0118*D16+0.00012*(D16^2))","v":"--"}]
[null,"      Effluent pKa:",{"$f":"IF(C14=\"enter value\",\"--\",6.57-0.0118*C20+0.00012*(C20^2))","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",6.57-0.0118*D20+0.00012*(D20^2))","v":"--"}]
[null,"2.  IONIZATION FRACTIONS"]
[null,"      Upstream/Background Ionization Fraction:",{"$f":"IF(C14=\"enter value\",\"--\",1/(1+10^(C36-C17)))","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",1/(1+10^(D36-D17)))","v":"--"}]
[null,"      Effluent Ionization Fraction:",{"$f":"IF(C14=\"enter value\",\"--\",1/(1+10^(C37-C21)))","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",1/(1+10^(D37-D21)))","v":"--"}]
[null,"3.  TOTAL INORGANIC CARBON"]
[null,"      Upstream Total Inorganic Carbon (mg CaCO3/L):",{"$f":"IF(C14=\"enter value\",\"--\",C18/C39)","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",D18/D39)","v":"--"}]
[null,"      Effluent Total Inorganic Carbon (mg CaCO3/L):",{"$f":"IF(C14=\"enter value\",\"--\",C22/C40)","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",D22/D40)","v":"--"}]
[null,"4.  CONDITIONS AT MIXING ZONE BOUNDARY"]
[null,"      Temperature (deg C):",{"$f":"IF(C14=\"enter value\",\"--\",C16+(C20-C16)/C14)","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",D16+(D20-D16)/D14)","v":"--"}]
[null,"      Alkalinity (mg CaCO3/L):",{"$f":"IF(C14=\"enter value\",\"--\",C18+(C22-C18)/C14)","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",D18+(D22-D18)/D14)","v":"--"}]
[null,"      Total Inorganic Carbon (mg CaCO3/L):",{"$f":"IF(C14=\"enter value\",\"--\",C42+(C43-C42)/C14)","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",D42+(D43-D42)/D14)","v":"--"}]
[null,"      pKa:",{"$f":"IF(C14=\"enter value\",\"--\",6.57-0.0118*C45+0.00012*(C45^2))","v":"--"},{"$f":"IF(D14=\"enter value\",\"--\",6.57-0.0118*D45+0.00012*(D45^2))","v":"--"}]
[]
[]
[null,"Effluent Limit Calculations"]
[null,"INPUT","Lower pH","Upper pH"]
[null,null,"Criteria","Criteria"]
[null,"1.  DILUTION AT MZ BOUNDARY",{"$f":"C14","v":"enter value"},{"$f":"D14","v":"enter value"}]
[]
[null,"2.  UPSTREAM CHARACTERISTICS"]
[null,"      Temperature (deg C):",{"$f":"C16","v":"--"},"--"]
[null,"      pH:","--","--"]
[null,"      Alkalinity (mg CaCO3/L):","--","--"]
[]
[null,"3.  EFFLUENT CHARACTERISTICS"]
[null,"      Temperature (deg C):",{"$f":"C20","v":"--"},{"$f":"D20","v":"--"}]
[null,"      Alkalinity (mg CaCO3/L):",{"$f":"C22","v":"--"},{"$f":"D22","v":"--"}]
[]
[null,"4.  APPLICABLE PH CRITERIA",{"$f":"C23","v":"enter value"},"enter value"]
[null,"     Calculated pH at Mixing Zone Boundary",{"$f":"IF(C54=\"enter value\",\"--\",C83-LOG((C82/C81)-1))","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",D83-LOG((D82/D81)-1))","v":"--"}]
[null,"Required Effluent pH:"]
[]
[null,"Intermediate Calculations"]
[null,"1.  IONIZATION CONSTANTS"]
[null,"      Upstream/Background pKa:",{"$f":"IF(C54=\"enter value\",\"--\",6.57-0.0118*C57+0.00012*(C57^2))","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",6.57-0.0118*D57+0.00012*(D57^2))","v":"--"}]
[null,"      Effluent pKa:",{"$f":"IF(C54=\"enter value\",\"--\",6.57-0.0118*C62+0.00012*(C62^2))","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",6.57-0.0118*D62+0.00012*(D62^2))","v":"--"}]
[null,"2.  IONIZATION FRACTIONS"]
[null,"      Upstream/Background Ionization Fraction:",{"$f":"IF(C54=\"enter value\",\"--\",1/(1+10^(C71-C58)))","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",1/(1+10^(D71-D58)))","v":"--"}]
[null,"      Effluent Ionization Fraction:",{"$f":"IF(C54=\"enter value\",\"--\",1/(1+10^(C72-C67)))","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",1/(1+10^(D72-D67)))","v":"--"}]
[null,"3.  TOTAL INORGANIC CARBON"]
[null,"      Upstream Total Inorganic Carbon (mg CaCO3/L):",{"$f":"IF(C54=\"enter value\",\"--\",C59/C74)","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",D59/D74)","v":"--"}]
[null,"      Effluent Total Inorganic Carbon (mg CaCO3/L):",{"$f":"IF(C54=\"enter value\",\"--\",C63/C75)","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",D63/D75)","v":"--"}]
[null,"4.  CONDITIONS AT MIXING ZONE BOUNDARY"]
[null,"      Temperature (deg C):",{"$f":"IF(C54=\"enter value\",\"--\",C57+(C62-C57)/C54)","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",D57+(D62-D57)/D54)","v":"--"}]
[null,"      Alkalinity (mg CaCO3/L):",{"$f":"IF(C54=\"enter value\",\"--\",C59+(C63-C59)/C54)","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",D59+(D63-D59)/D54)","v":"--"}]
[null,"      Total Inorganic Carbon (mg CaCO3/L):",{"$f":"IF(C54=\"enter value\",\"--\",C77+(C78-C77)/C54)","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",D77+(D78-D77)/D54)","v":"--"}]
[null,"      pKa:",{"$f":"IF(C54=\"enter value\",\"--\",6.57-0.0118*C80+0.00012*(C80^2))","v":"--"},{"$f":"IF(D54=\"enter value\",\"--\",6.57-0.0118*D80+0.00012*(D80^2))","v":"--"}]

{"sheet":"Marine pH RPA","index":1,"n_rows":92,"n_cols":26,"rows_kept":92,"cut":false,"hidden":false,"merged":[[19,7,22,10],[90,1,90,3],[91,1,92,3],[37,0,38,4],[39,2,39,3],[68,0,69,4],[70,1,70,3],[71,1,71,3],[72,1,73,1],[0,24,1,29],[2,24,2,29],[87,1,87,3],[88,1,89,3],[21,17,21,19],[20,17,20,19],[9,13,9,15],[10,13,10,15],[19,13,22,15],[0,0,0,3],[7,0,7,4],[11,20,12,21],[14,20,14,21],[14,17,14,18],[13,20,13,21],[13,17,13,18],[27,22,27,23],[27,24,27,25],[1,2,6,4],[27,6,27,8],[27,9,27,11],[27,12,27,14],[27,15,27,17],[27,20,27,21],[17,17,17,19],[18,17,18,19],[19,17,19,19],[15,17,15,23],[26,20,26,25],[26,6,26,17],[24,7,24,25],[11,17,12,18]]}
["Marine pH RPA v1.8",null,null,null,null,null,null,"Instructions:","1."," - Enter ambient and effluent data into the \"Raw Data\" fields below. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Based on the CO2SYS program (Lewis and Wallace, 1998), https://www.nodc.noaa.gov/ocads/oceans/CO2SYS/co2rprt.html"]
[null,"[Enter Facility Name]","Run Notes:",null,null,null,null,null,"2."," - Enter mixing zone dilution (C10,D10), discharge depth (C11,D11) and existing pH limits (C19, D19) into the white cells to the left"]
[null,"[Enter Analyst]",null,null,null,null,null,null,"3."," - Enter pH criterion into cells C35 and D35",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Adapted from Wa. Department of Ecology 3/2019"]
[null,"[EnterOutfall]",null,null,null,null,null,null,"4."," - Press the \"calculate\" buttons in row 32 to calculate pH at edge of MZ (row 30)"]
[null,"[Enter Run Date]",null,null,null,null,null,null,"5."," - If there is RP (row 36), iteratively enter a more stringent pH limit (increments of 0.1 SU) in row 50 and press \"calculate\" button in row 55 until there is no RP (row 67)"]
[null,null,null,null,null,null,null,null,"6."," - When finished, copy and paste the pH Analysis table into the fact sheet - double click video link in the freshwater tab for a simple tutorial "]
[]
["2. Initial Reasonable Potential Analysis"]
[null,"Discharge Area Characteristics","Lower pH RPA Values ","Upper pH RPA Values"]
[null,"Dilution at Mixing Zone boundary (7Q10)","enter value","enter value",null,null,null,"Ambient Data",null,null,null,null,null,"Effluent Data",null,null,null,null,"ICIS Summary Statistic Data for Effluent Temperature"]
[null,"Depth of Discharge (m) (use default of 3m if no info)","enter value","enter value",null,null,null,"Summary Statistics",null,null,null,null,null,"Summary Statistics",null,null,null,null,null,"Using ICIS Data?",null,"*"]
[null,"Ambient Characteristics (@critical condition)",null,null,null,null,null,"pH","Temp","Salinty","Alkalinity",null,null,"Temp","Salinty","Alkalinity",null,"Does permittee report Monthly Avg?",null,"*","Does permittee report Monthly  Max?",null,"*"]
[null,"Temperature (deg C):",{"$f":"IF($I$17=\"--\",\"*\",$I$17)","v":"*"},{"$f":"IF($I$16=\"--\",\"*\",$I$16)","v":"*"},null,null,null,"SU","Deg C","PSU","mg/L (CaCO3)",null,null,"Deg C","PSU","mg/L (CaCO3)"]
[null,"pH:",{"$f":"IF(U20=\"Yes\",7,IF($H$16=\"--\",\"*\",$H$16))","v":"*"},{"$f":"IF(U20=\"Yes\",8.5,IF($H$17=\"--\",\"*\",$H$17))","v":"*"},null,null,"Avg      ",{"$f":"IF(ISNUMBER(I$30),MEDIAN(I$30:I$131),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(L$30),AVERAGE(L$30:L$131),\"--\")","v":"--"},{"$f":"IF($U$22=\"Yes\",35,IF(ISNUMBER(O$30),AVERAGE(O$30:O$131),\"--\"))","v":"--"},{"$f":"IF(U21=\"Yes\",116,IF(ISNUMBER(R$30),AVERAGE(R$30:R$131),\"--\"))","v":"--"},null,"Avg",{"$f":"IF(ISNUMBER(V$30),AVERAGE(V$30:V$131),\"--\")","v":"--"},{"$f":"IF(U19=\"Yes\",0.1,IF(ISNUMBER(X$30),AVERAGE(X$30:X$131),\"--\"))","v":"--"},{"$f":"IF(U18=\"Yes\",100.47,IF(ISNUMBER(Z$30),AVERAGE(Z$30:Z$131),\"--\"))","v":"--"},null,"Max of monthly avg",null,null,"90th %ile of the monthly max"]
[null,"Salinity (psu)",{"$f":"IF($J$14=\"--\",\"*\",$J$14)","v":"*"},{"$f":"IF($J$14=\"--\",\"*\",$J$14)","v":"*"},null,null,"Max.",{"$f":"IF(ISNUMBER(I$30),MAX(I$30:I$131),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(L$30),MAX(L$30:L$131),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(O$30),MAX(O$30:O$131),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(R$30),MAX(R$30:R$131),\"--\")","v":"--"},null,"Max.",{"$f":"IF(ISNUMBER(V$30),MAX(V$30:V$131),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(X$30),MAX(X$30:X$131),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(Z$30),MAX(Z$30:Z$131),\"--\")","v":"--"},null,"Min of monthly avg",null,null,"Min of the monthly max"]
[null,"Total Alkalinity (mg/l)",{"$f":"IF($K$16=\"--\",\"*\",$K$16)","v":"*"},{"$f":"IF($K$16=\"--\",\"*\",$K$16)","v":"*"},null,null,0.1,{"$f":"IF(ISNUMBER(I$30),PERCENTILE(I$30:I$131,0.1),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(L$30),PERCENTILE(L$30:L$131,0.1),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(O$30),PERCENTILE(O$30:O$131,0.1),\"--\")","v":"--"},{"$f":"IF(U21=\"Yes\",116,IF(ISNUMBER(R$30),PERCENTILE(R$30:R$131,0.1),\"--\"))","v":"--"},null,0.1,{"$f":"IF(AND($V$11=\"Yes\",$T$12=\"Yes\"),T15,IF(AND($V$11=\"Yes\",$W$12=\"Yes\"),W15,IF(ISNUMBER(V$30),PERCENTILE(V$30:V$131,0.1),\"--\")))","v":"--"},{"$f":"IF(ISNUMBER(X$30),PERCENTILE(X$30:X$131,0.1),\"--\")","v":"--"},{"$f":"IF(U18=\"Yes\",64,IF(ISNUMBER(Z$30),PERCENTILE(Z$30:Z$131,0.1),\"--\"))","v":"--"},null,"Defaults"]
[null,"Effluent Characteristics",null,null,null,null,0.9,{"$f":"IF(ISNUMBER(I$30),PERCENTILE(I$30:I$131,0.9),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(L$30),PERCENTILE(L$30:L$131,0.9),\"--\")","v":"--"},{"$f":"IF(ISNUMBER(O$30),PERCENTILE(O$30:O$131,0.9),\"--\")","v":"--"},{"$f":"IF(U21=\"Yes\",116,IF(ISNUMBER(R$30),PERCENTILE(R$30:R$131,0.9),\"--\"))","v":"--"},null,0.9,{"$f":"IF(AND($V$11=\"Yes\",$T$12=\"Yes\"),T14,IF(AND($V$11=\"Yes\",$W$12=\"Yes\"),W14,IF(ISNUMBER(V$30),PERCENTILE(V$30:V$131,0.9),\"--\")))","v":"--"},{"$f":"IF(ISNUMBER(X$30),PERCENTILE(X$30:X$131,0.9),\"--\")","v":"--"},{"$f":"IF(U18=\"Yes\",134.56,IF(U18=\"Yes\",134.56,IF(ISNUMBER(Z$30),PERCENTILE(Z$30:Z$131,0.9),\"--\")))","v":"--"}]
[null,"Temperature (deg C):",{"$f":"IF($N$17=\"--\",\"*\",$N$17)","v":"*"},{"$f":"IF($N$16=\"--\",\"*\",$N$16)","v":"*"},null,null,null,null,null,null,null,null,null,null,null,null,null,"Effluent Alkalinity Defaults Needed?",null,null,"*","Alkalinity defaults from DMS data (2012-2017)"]
[null,"Current pH limits:","enter value","enter value",null,null,null,"Ambient Data Source:",null,null,null,null,null,"Effluent Data Source:",null,null,null,"Effluent Salinity Default Needed?",null,null,"*","Salinity assumed to be low for effluent"]
[null,"Salinity (psu)",{"$f":"IF($O$14=\"--\",\"*\",$O$14)","v":"*"},{"$f":"IF($O$14=\"--\",\"*\",$O$14)","v":"*"},null,null,null,null,null,null,null,null,null,null,null,null,null,"Ambient pH Default Needed?**",null,null,"*","Basin Standards of 7.0 to 8.5"]
[null,"Total Alkalinity (mg/l)",{"$f":"IF($P$17=\"--\",\"*\",$P$17)","v":"*"},{"$f":"IF($P$17=\"--\",\"*\",$P$17)","v":"*"},null,null,null,null,null,null,null,null,null,null,null,null,null,"Ambient Alkalinity Default Needed?",null,null,"*","Average alkalinity for seawater (\"Voluntary Estuary Monitoring Manual Chapter 11: pH and Alkalinity\", EPA 2006 EPA-842-B-06-003)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Ambient Salinity Default Needed?**",null,null,"*","Average Ocean Salinity from NASA : https://salinity.oceansciences.org/overview.htm"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"** Note, if no ambient pH or salinity data was found in AWQMS, try here: https://oceanobservatories.org"]
[null,"Conditions at the Mixing Zone Boundary"]
[null,"Temperature (deg C)",null,null,null,null,null,"1. Enter Raw Background and Effluent Data"]
[null,"Salinity (psu)"]
[null,"Density (kg/m^3)",null,null,null,null,"Ambient Raw Data",null,null,null,null,null,null,null,null,null,null,null,null,null,"Effluent Raw Data"]
[null,"Alkalinity (mmol/kg-SW)",null,null,null,null,"pH",null,null,"Temperature",null,null,"Salinity",null,null,"Alkalinity",null,null,null,null,"Temperature",null,"Salinty",null,"Alkalinity"]
[null,"Total Inorganic Carbon (mmol/kg-SW)",null,null,null,null,"Location ","Date","SU","Location ","Date","Deg C","Location ","Date","PSU","Location","Date","mg/L (CaCO3)",null,null,"Date","Deg C","Date","PSU","Date","mg/L (CaCO3)"]
[null,"pH"]
[]
[null,"Click the Calculate button(s) to determine pH at edge of MZ"]
[]
[null,null,"Lower pH","Upper pH"]
[null,"Water Quality Criterion (Basin Standards or TBELs)","enter value","enter value"]
[null,"Is there Reasonable Potential?",{"$f":"IF(ISNUMBER($C$30),IF(ROUND($C$30,1)>=ROUND(C35,1),\"No\",\"Yes\"),\"--\")","v":"--"},{"$f":"IF(ISNUMBER($D$30),IF(ROUND($D$30,1)<=ROUND(D35,1),\"No\",\"Yes\"),\"--\")","v":"--"}]
[]
["3. This Section is Used to Iteratively Calculate pH Effluent Limits if there is RP"]
[]
[null,"Discharge Area Characteristics","Characterization Values"]
[null,"Dilution at Mixing Zone boundary",{"$f":"IF(C10=\"enter value\",\"--\",C10)","v":"--"},{"$f":"IF(D10=\"enter value\",\"--\",D10)","v":"--"}]
[null,"Depth of Discharge (m)",{"$f":"IF(C11=\"enter value\",\"--\",C11)","v":"--"},{"$f":"IF(D11=\"enter value\",\"--\",D11)","v":"--"}]
[null,"Background Area Characteristics (@critical condition)"]
[null,"Temperature (deg C):",{"$f":"$C$13","v":"*"},{"$f":"$D$13","v":"*"}]
[null,"pH:",{"$f":"$C$14","v":"*"},{"$f":"$D$14","v":"*"}]
[null,"Salinity (psu)",{"$f":"$C$15","v":"*"},{"$f":"$D$15","v":"*"}]
[null,"Total Alkalinity (mg/l)",{"$f":"$C$16","v":"*"},{"$f":"$D$16","v":"*"}]
[null,"Effluent Characteristics"]
[null,"Temperature (deg C):",{"$f":"$C$18","v":"*"},{"$f":"$D$18","v":"*"}]
[null,"pH: (Iteratively enter pH and hit calculate until no RP)",{"$f":"IF(C36=\"yes\",\"enter value\",IF(C36=\"--\",C36,\"no RP\"))","v":"--"},{"$f":"IF(D36=\"yes\",\"enter value\",IF(D36=\"--\",D36,\"no RP\"))","v":"--"}]
[null,"Salinity (psu)",{"$f":"$C$20","v":"*"},{"$f":"$D$20","v":"*"}]
[null,"Total Alkalinity (mg/l)",{"$f":"$C$21","v":"*"},{"$f":"$D$21","v":"*"}]
[]
[]
[null,"Click the Calculate button to determine pH at edge of MZ"]
[]
[null,"Conditions at the Mixing Zone Boundary"]
[null,"Temperature (deg C)"]
[null,"Salinity (psu)"]
[null,"Density (kg/m^3)"]
[null,"Alkalinity (mmol/kg-SW)"]
[null,"Total Inorganic Carbon (mmol/kg-SW)"]
[null,"pH"]
[]
[null,null,"Lower pH","Upper pH"]
[null,"Water Quality Criterion (Basin Standards)",{"$f":"IF(C35=\"enter value\",\"--\",C35)","v":"--"},{"$f":"IF(D35=\"enter value\",\"--\",D35)","v":"--"}]
[null,"Is there Reasonable Potential?",{"$f":"IF(ISNUMBER($C$63),IF(OR(ROUND($C$63,1)>=ROUND(C66,1),C50=C66),\"No\",\"Yes\"),\"--\")","v":"--"},{"$f":"IF(ISNUMBER($D$63),IF(OR(ROUND($D$63,1)<=ROUND(D66,1),D50=D66),\"No\",\"Yes\"),\"--\")","v":"--"}]
[]
["4. Copy and Paste This Table Into the Fact Sheet"]
[]
[]
[null,"pH Analysis"]
[null,"INPUT","Lower pH","Upper pH"]
[null,null,"Criteria","Criteria"]
[null,"1.  DILUTION AT MZ BOUNDARY",{"$f":"IF(C10=\"enter value\",\"--\",C10)","v":"--"},{"$f":"IF(D10=\"enter value\",\"--\",D10)","v":"--"}]
[null,"2.  UPSTREAM CHARACTERISTICS"]
[null,"      Temperature (deg C):",{"$f":"C13","v":"*"},{"$f":"D13","v":"*"}]
[null,"      pH:",{"$f":"C14","v":"*"},{"$f":"D14","v":"*"}]
[null,"      Alkalinity (mg CaCO3/L):",{"$f":"C16","v":"*"},{"$f":"D16","v":"*"}]
[null,"3.  EFFLUENT CHARACTERISTICS"]
[null,"      Temperature (deg C):",{"$f":"C18","v":"*"},{"$f":"D18","v":"*"}]
[null,"      pH (S.U.):",{"$f":"IF(C19=\"enter value\",\"--\",C19)","v":"--"},{"$f":"IF(D19=\"enter value\",\"--\",D19)","v":"--"}]
[null,"      Alkalinity (mg CaCO3/L):",{"$f":"C21","v":"*"},{"$f":"D21","v":"*"}]
[null,"4.  APPLICABLE PH CRITERIA",{"$f":"IF(C35=\"enter value\",\"--\",C35)","v":"--"},{"$f":"IF(D35=\"enter value\",\"--\",D35)","v":"--"}]
[null,"      pH at Mixing Zone Boundary:",{"$f":"C30","v":0},{"$f":"D30","v":0}]
[null,"      Is there Reasonable Potential?",{"$f":"C36","v":"--"},{"$f":"D36","v":"--"}]
[null,"      Proposed Effluent Limits",{"$f":"IF(C19=\"enter value\",\"--\",IF(C36=\"no\",C19,C50))","v":"--"},{"$f":"IF(D19=\"enter value\",\"--\",IF(D36=\"no\",D19,D50))","v":"--"}]
[null,"Effluent Data Source:"]
[null,{"$f":"IF(N20=\"\",\"\",N20)","v":""}]
[]
[null,"Ambient Data Source:"]
[null,{"$f":"IF(H20=\"\",\"\",H20)","v":""}]

{"sheet":"Version notes","index":2,"n_rows":15,"n_cols":11,"rows_kept":15,"cut":false,"hidden":false,"merged":[[5,1,6,1],[9,5,9,6],[7,7,7,8],[7,9,7,10],[8,1,10,1],[8,2,10,2],[8,3,10,3]]}
["Version","Comments","Modifier","Date","Notes"]
["V 1.0","New stand alone version - modified to remove effluent pH data, added summary statistic table, added more room for comments/notes","Schnurbusch"]
["V1.1","Added pH limit macro button calculator to the freshwater pH RPA, added video tutorial","Schnurbusch","2020-06-04","Used goal seek macro to calculate effluent limits in cases where there is RP"]
["v1.2","Fixed freshwater macro so it referred to the pH criterion in cell C64 and D64.  Previously it referred to a fixed value of 6.5 and 8.5","Schnurbusch","2020-11-10"]
[null,"Added summary table in the marine RPA for copying and pasting into the fact sheet"]
["v1.3","Fixed the marine pH RPA so that the macros work. I created a lower pH macro and an upper pH macro. These correspond to the RPAs against the lower pH criterion (e.g. 6.5) and the upper pH criterion (e.g. 8.5) in columns C and D respectively.","Schnurbusch","2021-06-15"]
[]
["v1.4","Updated marine pH RPA because the upgrade to office 365 caused macro errors","Schnurbusch","2022-03-17",null,null,null,"effluent pH 6",null,"effluent pH 9"]
["v1.5","Updated Freshwater and Marine pH RPA to use defaults and ICIS data when needed. Updated Marine pH RPA to use 10th and 90th percentile temperature. Updated Freshwater pH RPA to use 90th percentile temperature for lower pH and 10th percentile temperature for upper pH (as temperature increases, pH decreases) to ensure conservative estimate. Effluent alkalinity changed to 90th percentile for both lower and upper pH, ambient alkalinity changes to 10th percentile for both lower and upper pH in freshwater and marine RPA, as this better accounts for the impact of alkalinity on pH and is the more conservative assumption","Britson","2022-12-21",null,null,null,"Ambient pH>effluent pH, Ambient Alk>Effluent Alk","Ambient pH>effluent pH, Ambient Alk<effluent Alk","Ambient pH<effluent pH, Ambient alk>effluent alk","Ambient pH<effluent pH, Ambient Alk<effluent alk"]
[null,null,null,null,null,"Effect on pH at MZ Boundary",null,"Increases pH","Decreases pH","Decreases pH","Increases pH"]
[null,null,null,null,null,null,null,null,"More conservative estimate on lower end of pH scale when effluent alk is greater than ambient alk. Use 90th %ile Effluent alk and 10th %ile ambient alk",null,"More conservative estimate on higher end of pH scale when effluent alk is greater than ambient alk. Use 90th %ile Effluent alk and 10th %ile ambient alk"]
[null,"Clarified that alkalinity defaults were for effluent and MZ dilution to use is the 7Q10 ","Britson","2023-01-20"]
["v 1.6","Fixed Marine summary statistic cell reference","Britson","2023-03-20"]
["v1.7","Fixed Marine temperature cell references for calcualting pH limit so that they match the references used in calculating RP","Britson","2023-05-23"]
["v1.8","Removed hidden comments in cells. Made directions explicit if instructions were hiding in comment.","Britson","2024-09-04"]