{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Introduction","Glossary","AdminMenu","GenericPNECs","ThresholdValues","Screening Tool","TestDataPb","VersionControl","ChangeLog"],"not_rendered":{"charts":0,"images":12,"macros":1,"external_links":0,"defined_names":18,"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":"Introduction","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[[5,4,5,10]]}

{"sheet":"Glossary","index":1,"n_rows":8,"n_cols":7,"rows_kept":8,"cut":false,"hidden":true,"merged":[[6,4,6,5],[6,6,6,13],[7,6,7,13],[2,6,2,13],[3,6,3,13],[4,6,4,13],[5,6,5,13]]}
[]
[]
[null,null,null,null,"AA",null,"Annual average. Environmental quality standard for the assessment of chronic exposures. "]
[null,null,null,null,"BioF",null,"The bioavailability factor. The BioF is based on a comparison between the expected availability at the reference site and that relating to site-specific conditions. Through the use of a BioF, differences in availability are accounted for by adjustments to the monitoring data but the EQS remains the same. It is calculated by dividing the Generic or Reference EQS by the calculated site specific EQS. "]
[null,null,null,null,"DOC",null,"Dissolved organic carbon. The input to the screening tool for DOC should be site-specific median concentrations from at least eight sampling occasions. This is because metal EQS are expressed as AA concentrations, and the short-term EQS or Maximum Allowable Concentrations are not relevant here. "]
[null,null,null,null,"EQSavailable",null,"The EQS (Environmental Quality Standard) for available Pb, sometimes also termed the reference or generic EQS. This is representative of conditions of high bioavailability and is expressed as ‘available’ metal concentration. "]
[null,null,null,null,"Site-Specific \nPNEC",null,"The Site-specific PNEC (Predicted No Effect Concentration) is the calculated dissolved concentration of metal that is equivalent the EQSavailable at the local water conditions at the site. "]
[null,null,null,null,"RCR",null,"Risk Characterisation Ratio, also sometimes called the risk quotient. This is calculated by dividing the PEC by the PNEC. Values equal to or greater than 1 present a potential risk. "]

{"sheet":"AdminMenu","index":2,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"GenericPNECs","index":3,"n_rows":4,"n_cols":5,"rows_kept":4,"cut":false,"hidden":true,"merged":[[3,4,3,7],[2,2,2,3],[2,4,2,7],[3,2,3,3]]}
[]
[]
[null,null,"Metal",null,"Generic EQSavailable Concentration \n(µg l-1)"]
[null,null,"Lead (Pb)",null,{"$f":"PbEQSReference","v":1.2}]

{"sheet":"ThresholdValues","index":4,"n_rows":7,"n_cols":5,"rows_kept":7,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,null,"Name","Value","Unit"]
[null,null,"EQS Reference",1.2,"µg/l"]
[null,null,"DOC Reference",1,"mg/l"]
[null,null,"DOC Upper Limit",20,"mg/l"]
[null,null,"Constant",1.2]

{"sheet":"Screening Tool","index":5,"n_rows":5,"n_cols":11,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"INPUT DATA",null,null,null,null,null,"RESULTS (Pb)"]
[null,"ID","Location","Waterbody","Date","Measured Pb Concentration (dissolved) (µg l-1)","DOC","Site Specific PNEC Dissolved Pb (µg l-1)","BioF","Available Pb\n(µg l-1)","Risk Characterisation Ratio"]
[null,1,"Meece","River Sow",null,null,6,7.2,0.16666666666666666,0]

{"sheet":"TestDataPb","index":6,"n_rows":14,"n_cols":16,"rows_kept":14,"cut":false,"hidden":true,"merged":[]}
["EXPECTED RESULTS",null,null,null,null,null,null,null,"Actual Results",null,null,null,null,"COMPARISON"]
["Location","WB","Date","Pb Meas","DOC","PNEC Displayed","BioFDisplayed","RCR","Measured Pb Concentration (dissolved) (µg l-1)","DOC","PNEC (µg l-1)","BioF","RCR","PNEC","BioF","RCR"]
[null,null,null,null,0.1,{"$f":"IF(E3<PbDOCReference,PbEQSReference,PbEQSReference+(Constant*(E3-PbDOCReference)))","v":1.2},{"$f":"1/F3","v":0.83333333333333337},{"$f":"(D3*G3)/1","v":0},null,0.1,1.2,0.83333333333333337,null,{"$f":"IF(K3<>F3,\"mismatch\",\"\")","v":""},{"$f":"IF(L3<>G3,\"mismatch\",\"\")","v":""},{"$f":"IF(M3<>H3,\"mismatch\",\"\")","v":""}]
[null,null,null,null,1,1.2,{"$f":"1/F4","v":0.83333333333333337},{"$f":"(D4*G4)/1","v":0},null,1,1.2,0.83333333333333337,null,{"$f":"IF(K4<>F4,\"mismatch\",\"\")","v":""},{"$f":"IF(L4<>G4,\"mismatch\",\"\")","v":""},{"$f":"IF(M4<>H4,\"mismatch\",\"\")","v":""}]
[null,null,null,null,3,3.5999999999999996,{"$f":"1/F5","v":0.27777777777777779},{"$f":"(D5*G5)/1","v":0},null,3,3.6,0.27777777777777779,null,"","",""]
[null,null,null,null,5,6,{"$f":"1/F6","v":0.16666666666666666},{"$f":"(D6*G6)/1","v":0},null,5,6,0.16666666666666666,null,"","",""]
[null,null,null,null,10,11.999999999999998,8.3333333333333343E-2,0,null,10,12,8.3333333333333343E-2,null,"","",""]
[null,null,null,null,15,18,5.5555555555555552E-2,0,null,15,18,5.5555555555555552E-2,null,"","",""]
[null,null,null,15,0.1,1.2,0.83333333333333337,12.5,15,0.1,1.2,0.83333333333333337,12.5,"","",""]
[null,null,null,15,1,1.2,0.83333333333333337,12.5,15,1,1.2,0.83333333333333337,12.5,"","",""]
[null,null,null,15,3,3.5999999999999996,0.27777777777777779,4.166666666666667,15,3,3.6,0.27777777777777779,4.166666666666667,"","",""]
[null,null,null,15,5,6,0.16666666666666666,2.5,15,5,6,0.16666666666666666,2.5,"","",""]
[null,null,null,15,10,11.999999999999998,8.3333333333333343E-2,1.2500000000000002,15,10,12,8.3333333333333343E-2,1.25,"","",""]
[null,null,null,15,15,18,5.5555555555555552E-2,0.83333333333333326,15,15,18,5.5555555555555552E-2,0.83333333333333326,"","",""]

{"sheet":"VersionControl","index":7,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":true,"merged":[]}
["CURRENT"]
["ApplicationTitle","Pb Screening tool"]
["VersionNumber",1]
["ReleaseNumber","0"]
["ReleaseDate","5 March 2015"]
["Changes","See ChangeLog"]
["VersionTitle",{"$f":"CONCATENATE(VersionNumber, \" (\", ReleaseNumber, \") \", ReleaseDate)","v":"1 (0) 5 March 2015"}]
["FullTitle",{"$f":"B2&\" \"&\"[\"&VersionTitle&\"]\"","v":"Pb Screening tool [1 (0) 5 March 2015]"}]
["FooterTitle",{"$f":"CONCATENATE(\"Version \",VersionNumber,\".\",ReleaseNumber,\", created \",ReleaseDate)","v":"Version 1.0, created 5 March 2015"}]

{"sheet":"ChangeLog","index":8,"n_rows":65,"n_cols":3,"rows_kept":65,"cut":false,"hidden":true,"merged":[]}
["Version","Based on ","Description of Changes"]
["Cu PNEC Estimator 1",null,"BLM turned into automated tool for Cu only, using VBA"]
["Cu PNEC Estimator 2","Cu PNEC Estimator 1","BioF column added to outputs"]
[null,null,"pH domains altered (minimum = 5, max = 8.5)"]
[null,null,"Threshold level set for Cu PNECs (user-configurable).  Calculated PNECs are trimmed to this level.  Instructions amended accordingly."]
[null,null,"Buttons added to clear the sheet and run the programme (and instructions amended accordingly)"]
[null,null,"All numeric input and output columns: format set to 2 decimal places"]
[null,null,"Calculator sheet layout altered (order of columns)"]
[null,null,"Comments column hidden (comments now all shown as float-over boxes on highlighted cells)"]
[null,null,"Calculations written into VBA code as custom functions, rather than formulae in cells"]
[null,null,"Code amended to ignore empty rows, and to include rudimentary validation sequence to encourage users to input adequate, valid data."]
["BLMTool 3.0","Cu PNEC Estimator 2","Design draft for addition of Zn, and new interface (for users and admin)"]
["BLMTool 4.0","BLMTool 3.0","Working draft with Cu and Zn in place"]
["BLMTool5.0","BLMTool 4.0","Added ID column, fixed errors, added units, cosmetic changes to main menu"]
["BLMTool6.0","BLMTool5.0","RCR calculation corrected (was PNEC/BioavailablePEC due to error in code.  Should be BioavailablePEC/PNECGeneric)"]
[null,null,"Addition of boundary conditions for Zn model (PNEC to be trimmed to Generic value if pH <6 or >9)"]
[null,null,"Addition of soft-water threshold for Zn (of 3.1 ug/l) if Ca <7"]
[null,null,"Correction of code so it now picks up Zn measured values from correct column (was picking them up from Cu meas column)"]
[null,null,"Correction of test datasets, for validation of tool"]
["BLMTool7.0","BLMTool6.0","Correction of code, so that Zn PNEC is set to soft water threshold in all instances where Ca <7 (i.e. no PNEC calculation performed, and BioF = 1).  Test data also revised accordingly."]
["BLMTool8.0","BLMTool7.0","Spelling errors on front page corrected"]
["BLMTool8.1","BLMTool8.0","Code restructured to ensure consistency, to take account of new validation schedule and new parameter bands, and to allow easier addition of new metals in future."]
["BLMTool8.2","BLMTool8.1","Addition of Extended BioF calculation"]
[null,null,"Code tested for errors and de-bugged. "]
[null,null,"Instructions re-worded"]
[null,null,"Front-page layout altered and tidied"]
["BLMTool9.0","BLMTool8.2","Passwords re-set"]
["BLMTool10.0","BLMTool9.0","Deleted header (with version number) and replaced with footer (intro page only)"]
[null,null,"Altered colour scheme to fit Agency branding"]
[null,null,"Altered default font and size"]
[null,null,"Renamed \"Main Menu\" as \"Introduction\""]
[null,null,"Altered text on introduction page"]
[null,null,"Renamed \"PNECCalculator\" sheet as \"Screening Tool\""]
[null,null,"Added Help button and Help sheet (currently contains no Help text)"]
[null,null,"Added Glossary button and Glossary sheet (currently contains no definitions)"]
[null,null,"Removed WCA logo (awaiting EA logo)"]
[null,null,"Altered text on message box that shows when calculation completes"]
[null,null,"Altered colour of copper results to pale orange "]
[null,null,"Altered pH range for the BioF Extender tool (>=4 to <5.5 for Cu; >=4 to <6 for Zn)"]
[null,null,"Removed \"sensitive conditions\" phrase from flags outside pH range"]
[null,null,"Corrected code to show BioF Flag for whenpH>=9 for Zn (i.e. 100% availability assumed)"]
[null,null,"Altered case (Zinc to zinc) in Ca input flag"]
[null,null,"Altered code to highlight RCR, BioM, PNEC and BioF in red, where RCR >1 (and to add flag to RCR and BioM)"]
["BLMTool11.0","BLMTool10.0","Updated glossary"]
["BLMTool12.0","BLMTool11.0","Added manganese mockup (identical to zinc)"]
["BLMTool13.0","BLMTool12.0","Added equations and constants for manganese, and amended code for Mn validation sequence and warning messages"]
["BLMTool14.0","BLMTool13.0","Introduction of second Zn threshold value of 10.9, and adjustment of PNECs using ratio between the two threshold values"]
[null,null,"Introduction of fish and algal reference values to Mn model, to generate BioFs from EC10s. BioF limited to 1. PNEC = EQS/BioF."]
["BLMTool15.0","BLMTool14.0","Mn bug fixed, and BioF flag text altered."]
["BLMTool16.0","BLMTool15.0","Textual change to glossary"]
[null,null,"Bug correction to Mn model"]
["BLMTool17.0","BLMTool16.0","Mn model altered so EC10 minimum is set to the reference value (for fish or algae, as appropriate)"]
["BLMTool18.0","BLMTool17.0","Errors corrected in the Zn BioF extender"]
[null,null,"Removal of Zn PNEC-adjustment (i.e. removal ZnThreshold ratio as multiplier on final PNEC)"]
[null,null,"Zn BioFs calculated using old threshold of 7.8"]
[null,null,"Zn PNECs calculated Generic PNEC of 10.9"]
["BLMTool19.0","BLMTool18.0","Zn, Mn and Cu RCR calculation altered to = (Bioavailable Metal Concentration / Threshold value)"]
[null,null,"Test-data sheets updated for all three metals, to match current calculation structure"]
["BLMTool20.0","BLMTool19.0","Change to colour of flag for Cu where RCR >1"]
[null,null,"Textual edits"]
["MnBLM2.0","BLMTool20.0","All sheets, data and calculations relating to Cu and Zn removed. Code adjusted to work as a standalone Mn BLM."]
["PbBLM1.0","MnBLM2.0","All sheets, data and calculations relating to Mn adapted to work as a standalone Pb BLM."]
["PbBLM2.0","PbBLM1.0","Edits to Screening Tool table reformatting procedure, and to front-end text"]
["PbBLM3.0","PbBLM2.0","Made all parts of the PNEC formula configurable by user, and edited flag text for DOC column so it picks up the current value for the DOC Reference."]
["Pb Screening Tool","Pb Screening Tool 1.0","Addition of Available Pb tool; lay-out changed to match m-BAT"]