{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Project Info","Damage Info","Post Resil Damages","Analysis Results","Values","Status","Freq Calculations","RI Estimator","Calculations"],"not_rendered":{"charts":1,"images":13,"macros":1,"external_links":0,"defined_names":77,"comments":7,"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":"Project Info","index":0,"n_rows":81,"n_cols":162,"rows_kept":81,"cut":false,"hidden":false,"merged":[[76,7,76,8],[77,7,77,8],[75,5,75,6],[76,5,76,6],[77,5,77,6],[79,3,79,4],[79,1,79,2],[75,3,75,4],[70,1,70,2],[72,3,72,4],[76,1,76,2],[77,1,77,2],[72,1,72,2],[73,1,73,2],[76,3,76,4],[77,3,77,4],[19,10,20,16],[21,10,23,16],[24,10,25,16],[14,10,17,16],[8,10,11,16],[12,10,13,16],[6,10,6,16],[7,10,7,16],[6,1,6,8],[11,1,11,8],[8,1,9,8],[80,1,80,5],[80,6,80,8],[38,2,38,8],[39,2,39,8],[78,1,78,8],[74,5,74,8],[74,1,74,4],[62,1,62,8],[57,2,57,8],[42,2,42,8],[45,2,45,4],[46,2,46,4],[47,2,47,4],[68,5,70,8],[43,6,43,8],[79,5,79,8],[1,1,1,8],[3,1,3,8],[4,1,4,8],[71,1,71,8],[26,1,26,8],[47,6,47,8],[16,1,20,8],[22,1,24,8],[34,1,34,8],[36,1,36,8],[37,2,37,8],[43,3,43,4],[40,2,40,4],[51,1,51,8],[41,1,41,8],[13,1,14,8],[52,3,56,8],[44,6,44,8],[31,1,31,8],[75,7,75,8],[75,1,75,2],[67,1,67,8],[44,2,44,4],[45,5,45,8],[58,3,61,8],[48,2,48,8],[46,6,46,8],[50,3,50,4],[50,5,50,8],[49,2,49,8],[72,5,73,8],[68,3,68,4],[69,3,69,4],[73,3,73,4],[70,3,70,4],[63,7,63,8],[63,5,63,6],[63,1,63,4],[64,1,64,4],[65,1,65,4],[66,1,66,4],[64,6,66,8],[68,1,68,2],[69,1,69,2]]}
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2017"]
[null,"U.S. Department of Transportation\nFederal Transit Administration"]
[]
[null,"Hazard Mitigation Cost Effectiveness Tool"]
[null,"3/10/2017 – version 2.2"]
[]
[null,"Disclaimer:",null,null,null,null,null,null,null,null,"Tool Information"]
[]
[null,"The results produced by this tool are not conclusive evidence that a project proposal is or is not cost-effective.  The analysis conducted is dependent upon the quality of the information input into the tool.  It is intended for use as a planning tool only.",null,null,null,null,null,null,null,null,"Project and related information may be inputted only into the white cells on the tabs of this tool . All other cells are protected in order to prevent accidental erasure or overwriting. Attempts to modify a protected cell will display an error.  Cells marked with a red asterisk (*) represent required inputs."]
[]
[]
[null,"Description:"]
[null,null,null,null,null,null,null,null,null,null,"Hover your cursor over cells with red triangles in the top-right corner to show guidance notes for that cell. Guidance notes provide additional information about values that should be entered into cells."]
[null,"Many transit agencies are considering including resilience elements in capital improvement projects during the planning and design phases.  This tool may be used for benefit-cost analysis (BCA) of resilience projects being considered for funding as part of a transit agency's asset management program to enable them to make informed decisions about implementing resilience projects. "]
[null,null,null,null,null,null,null,null,null,null,"Click the \"Save As New Project\" button to start a new project file. You will be prompted to enter a file name."]
[]
[null,"Resilience projects are those natural hazard mitigation projects designed and built to address vulnerabilities to a public transportation facility or system due to future occurrence of emergencies or major disasters that are likely to occur in the geographic area in which the public transportation system is located; or projected changes in development patterns, demographics, or extreme weather or other climate patterns. Cost-effectiveness evaluations for proposed projects that include a benefit-cost ratio (BCR) can provide important information to decision-makers regarding the project's ability to reduce an asset’s and the public transportation system’s vulnerabilities to future disasters in a cost-effective way.  In general, a BCR of one or greater indicates a project is cost-effective. However, additional  considerations may result in some projects with a BCR of less than one being considered cost-effective."]
[]
[]
[null,null,null,null,null,null,null,null,null,null,"Click the \"Save Current Project\" button to save the current project file."]
[]
[null,null,null,null,null,null,null,null,null,null,"Click the \"Print This Tab\" button to print the current tab. You will be prompted to choose a printer."]
[null,"The tool provides a framework for transit agencies to input quantitative information about a project and its cost-effectiveness.  Quantitative information to be input into the tool includes the estimated damages and losses from specifically identified hazards (i.e., recorded historical or expected theoretical events) and the reduction in the anticipated damages and losses after such an event as a result of implementing the proposed project.       "]
[]
[null,null,null,null,null,null,null,null,null,null,"Click the \"Print All\" button to print all the sheets. You will be prompted to choose a printer."]
[]
[null,"Instructions:"]
[]
[null,"Step 1: Fill out the Project Information section on this tab. "]
[null,"Step 2: Add expected or historic damage information on the next tab."]
[null,"Step 3: View your BCA results in the Analysis Results tab."]
[null,"Note: For each damage event in the ‘Damage Info’ tab, fill in all cells requiring a number."]
[null,"Note: For all sheets, cells marked with a red asterisk (*) represent required inputs."]
[]
[null,"Project Information"]
[]
[null,"Section I - Agency Information"]
[null,"Agency*:"]
[null,"Address:"]
[null,"Address Line 2:"]
[null,"City:",null,null,null,"State:",null,"Zip:"]
[null,"Section II - Project Information",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
[null,"Project Name*:"]
[null,"Analysis Year*:",null,null,null,"Analysis Date*:"]
[null,"Analyst*:",null,null,null,"Analyst Phone:"]
[null,"Transit Mode(s)\n Protected by Project*:"]
[null,"Primary Hazard\n Protected by Project*:",null,null,null,"If Primary Hazard is\n\"Other,\" please specify:"]
[null,"Secondary Hazard(s) Protected by Project:",null,null,null,"If Secondary Hazard is \"Other,\" please specify:"]
[null,"Brief Project \nDescription*:"]
[null,"Select Basis to Evaluate Post-Resilence Damage*:"]
[null,"Proposed Project Effectiveness (Years)*:",null,"Source/Documentation of Project Effectiveness:"]
[null,"Section III - Cost Information"]
[null,"Total Project Initial Cost*:"]
[null,"Source/Documentation of Total Cost Estimate:"]
[null,"Project Useful \nLife (Years)*:"]
[null,"Remaining Useful \nLife of Assets to be Protected (Years):"]
[null,"Annual Project \nOperation & Maintenance (O&M) Cost*:"]
[null,"Source/Documentation of Annual O&M Cost:"]
[null,"Discount Rate (%):",7]
[null,"Present Value\n Coefficient:",{"$f":"IF(AND(ISNUMBER(Project_Useful_Life),ISNUMBER(Discount_Rate)),PV(Discount_Rate/100,Project_Useful_Life,-1),\"\")","v":""}]
[null,"Present Value of \nAnnual O&M Costs:",{"$f":"IF(AND(ISNUMBER(Present_Value_Coefficient),ISNUMBER(Annual_O_M_Cost)),Present_Value_Coefficient*Annual_O_M_Cost,\"\")","v":""}]
[null,"Total Project Cost including O&M:",{"$f":"IF(ISNUMBER(Total_Project_Initial_Cost+PV_Annual_O_M_Costs),Total_Project_Initial_Cost+PV_Annual_O_M_Costs,\"\")","v":""}]
[null,"Section IV - Cost Associated with Interruption in Transit \nServices during Project Construction/Implementation"]
[null,"Cost of Loss of Services for Transit \nPassengers ($/Passenger/Hour)*:",null,null,null,"Default",null,{"$f":"VLOOKUP(F64,CostLoss, 2, FALSE)","v":17.079999999999998}]
[null,"Current Federal Mileage Rate ($/Mile)*:",null,null,null,0.53500000000000003]
[null,"Will there be a losses or delays in transit \nservice during project construction?*",null,null,null,"No"]
[null,"Duration of Loss or Reduction of Services (Days)*:"]
[null,"Impacts Due to Delay and/or Extra Travel Time for Transit Mode Under Project Construction"]
[null,"Delay or Extra Travel Time (Hours):"]
[null,"Average Daily Number of Passengers:"]
[null,"Loss of Transit Services Cost:",null,{"$f":"IF(AND(ISNUMBER(F67),ISNUMBER(D69),ISNUMBER(D70)),F67*D69*D70*$H$64,\" \")","v":" "}]
[null,"Impacts Due to Delay and/or Extra Travel Time and Miles for Alternate Transit Modes"]
[null,"Additional Time per One-way Trip (Hours):"]
[null,"Additional Travel Miles:"]
[null,"Alternate Transit Mode (Rail, Ferry, Buses)",null,null,null,"Other Alternate Transit Mode (Vehicles)"]
[null,"Number of One-way Traffic Trips \nper Day (Rail/Ferry/Buses):",null,null,null,"Number of One-way Traffic \nTrips per Day (Vehicles):"]
[null,"Average Number of Passengers per Trip:",null,null,null,"Average Number of \nPassengers per Vehicle:",null,1.67]
[null,"Loss of Services Cost (Rail/Ferry/Buses):",null,{"$f":"IF(AND(ISNUMBER(D76),ISNUMBER(D77)),F67*$H$64*D73*D76*D77+F67*$F$65*D74*D76,\" \")","v":" "},null,"Loss of Services Cost (Vehicles):",null,{"$f":"IF(AND(ISNUMBER(D73),ISNUMBER(D74),ISNUMBER(H76)),F67*D73*H76*($H$64*H77)+D74*H76*$F$65,\" \")","v":" "}]
[null,"Totals"]
[null,"Total Cost due to Interruption of Services:",null,{"$f":"IF(F66=\"Yes\",IF(OR(ISNUMBER(D71),ISNUMBER(H78),ISNUMBER(D78)),SUM(D71,H78,D78),\"\"),0)","v":0}]
[null,"Total BCA Project Costs: Total Project Cost including O&M and Interruption of Services Loss:",null,null,null,null,{"$f":"IF(OR(ISNUMBER(Total_Project_Cost_including_O_M),ISNUMBER(Interruption_of_Services_Cost)),SUM(Total_Project_Cost_including_O_M,Interruption_of_Services_Cost),\"\")","v":0}]

{"sheet":"Damage Info","index":1,"n_rows":172,"n_cols":21,"rows_kept":172,"cut":false,"hidden":false,"merged":[[7,1,7,2],[7,3,7,4],[3,1,3,4],[167,3,171,12],[69,3,75,12],[64,3,68,12],[128,3,132,12],[133,3,139,12],[45,1,45,12],[62,1,62,12],[77,1,77,12],[78,1,78,3],[142,3,143,4],[141,3,141,4],[85,4,86,12],[81,5,81,6],[156,6,157,12],[141,1,143,2],[140,1,140,12],[149,1,149,2],[150,7,150,12],[150,3,150,6],[158,1,158,12],[154,1,154,12],[1,1,1,12],[3,11,3,12],[4,1,4,2],[4,3,4,4],[5,1,5,2],[5,3,5,4],[6,1,6,2],[6,3,6,4],[4,11,6,12],[29,1,29,12],[91,4,91,12],[92,1,92,12],[93,1,95,1],[46,1,46,2],[30,1,31,1],[26,3,26,12],[27,3,27,12],[93,2,93,3],[93,6,95,8],[94,4,95,4],[94,2,95,3],[25,1,27,2],[89,4,90,12],[78,5,80,12],[82,5,82,12],[160,3,166,12],[10,1,10,12],[63,4,63,12],[87,1,88,3],[89,1,90,3],[127,4,127,12],[155,7,155,12],[109,1,109,12],[83,1,83,12],[84,1,84,5],[84,7,84,11],[81,1,81,3],[82,1,82,3],[85,1,86,3],[91,1,91,3],[126,1,126,12],[112,2,112,5],[30,6,31,6],[48,2,48,5],[47,1,47,2],[46,4,47,12],[48,6,48,11],[11,1,11,12],[81,7,81,12],[141,10,146,12],[141,7,143,9],[142,5,143,5],[30,7,37,12],[30,2,30,3],[31,2,31,3],[159,4,159,12],[110,1,110,2],[110,4,111,12],[111,1,111,2],[93,9,101,12],[112,6,112,11],[148,4,149,12],[147,1,147,12],[148,1,148,2],[87,4,88,12],[79,1,79,3],[80,1,80,3],[142,6,143,6],[94,5,95,5],[20,1,21,2],[28,1,28,2],[28,3,28,12],[12,1,12,2],[13,1,13,2],[14,1,14,2],[15,1,15,2],[14,5,15,12],[20,3,21,12],[22,3,24,12],[22,1,24,2],[19,1,19,5],[17,1,17,12],[18,1,18,12],[19,7,19,11],[12,3,12,12],[13,3,13,12],[14,3,14,4],[15,3,15,4],[25,3,25,12]]}
[]
[null,"Hazard Mitigation Cost Effectiveness Tool"]
[]
[null,"BCR Summary",null,null,null,null,null,null,null,null,null,"Tools"]
[null,"Proposed Project \nEffectiveness (Years):",null,{"$f":"'Project Info'!C51","v":0}]
[null,"Total Project Benefits:",null,{"$f":"IF(ISNUMBER(PV_Annual_Damages_Before_Mitigation-PV_Annual_Damages_After_Mitigation),PV_Annual_Damages_Before_Mitigation-PV_Annual_Damages_After_Mitigation,\"\")","v":""}]
[null,"Total BCA Project Costs:",null,{"$f":"IF(ISNUMBER(Total_Project_Cost),Total_Project_Cost,\"\")","v":0}]
[null,"Benefit-Cost Ratio (BCR):",null,{"$f":"BCR","v":""}]
[]
[]
[null,"Damage Information"]
[null,"Section I - General Information"]
[null,"Agency:",null,{"$f":"IF(ISBLANK('Project Info'!$C$38),\"\",'Project Info'!$C$38)","v":""}]
[null,"Project Name:",null,{"$f":"IF(ISBLANK('Project Info'!$C$43),\"\",'Project Info'!$C$43)","v":""}]
[null,"Analysis Year:",null,{"$f":"IF(ISNUMBER(Analysis_Year),Analysis_Year,\"\")","v":""}]
[null,"Select Damage                                    Analysis Type*:",null,"Select Damage Type"]
[]
[null,"Section II - Expected Damages"]
[null,"Expected Damages Questionnaire"]
[null,"How many documented expected damage events do you have?",null,null,null,null,null,"For how many of these expected damage events \ndo you know the Recurrence Intervals (RIs)?"]
[null,"Errors:\n(must be corrected to \nproceed with analysis)",null,{"$f":"IF($M20<1,\"Insufficient number of expected damage events of known RIs to conduct a valid analysis!\",\" \")","v":"Insufficient number of expected damage events of known RIs to conduct a valid analysis!"}]
[]
[null,"Warnings:\n(must be addressed to\n conduct a valid analysis)",null,{"$f":"IF(AND($G20=1,$M20=1),\"With only one damage event of known RI, the analysis will be of limited accuracy and is not recommended!\",\" \")","v":" "}]
[]
[]
[null,"Conclusions:\n(provide directions on \nanalysis approach based on completed questionnaire)",null,{"$f":"IF(AND($G20>=1,$M20=1),\"A valid analysis may be conducted using one damage event of known RI, but other expected damage events should be added if possible to improve the accuracy of the analysis.\", \" \")","v":" "}]
[null,null,null,{"$f":"IF(AND($G20>=2,$M20>=2),\"A valid analysis may be conducted using two or more damage events of known RIs.\", \" \")","v":" "}]
[null,null,null,{"$f":"IF(AND($M20<1),\"A valid analysis cannot be conducted without at least one or more damage events of known RIs.\", \" \")","v":"A valid analysis cannot be conducted without at least one or more damage events of known RIs."}]
[null,"Source/Documentation of Expected Damages:"]
[null,"Expected Damages Part A"]
[null,null,"Physical Damages Costs",null,"Response and Recovery Costs","Other Damage/Loss (describe below)"]
[null,null,"(includes permanent repairs to damaged fixed structures and rolling stock)",null,"(includes costs of emergency protective measures and temporary repairs or measures that can be avoided by the proposed project)"]
[null,"Recurrence Interval\n(Years)","Physical Damage Costs for Fixed Structures ($)","Physical Damage Costs for Rolling Stock ($)","Response and Recovery Costs \n($)","Other Damage Costs ($)","Total Part A Damages "]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C34),ISNUMBER($D34),ISNUMBER($E34),ISNUMBER($F34)),SUM($C34,$D34,$E34,$F34),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C35),ISNUMBER($D35),ISNUMBER($E35),ISNUMBER($F35)),SUM($C35,$D35,$E35,$F35),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C36),ISNUMBER($D36),ISNUMBER($E36),ISNUMBER($F36)),SUM($C36,$D36,$E36,$F36),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C37),ISNUMBER($D37),ISNUMBER($E37),ISNUMBER($F37)),SUM($C37,$D37,$E37,$F37),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C38),ISNUMBER($D38),ISNUMBER($E38),ISNUMBER($F38)),SUM($C38,$D38,$E38,$F38),\"\")","v":""}]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B39:E39)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E39,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C39+D39),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G39), ISNUMBER(H39)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H39,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G39,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J39), ISNUMBER(K39)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K39,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J39,\"\")","v":""},{"$f":"IF(AND(ISNUMBER($F39),ISNUMBER($I39),ISNUMBER($L39)),SUM($F39,$I39,$L39),\"\")","v":""}]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B40:E40)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E40,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C40+D40),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G40), ISNUMBER(H40)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H40,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G40,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J40), ISNUMBER(K40)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K40,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J40,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B41:E41)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E41,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C41+D41),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G41), ISNUMBER(H41)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H41,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G41,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J41), ISNUMBER(K41)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K41,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J41,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B42:E42)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E42,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C42+D42),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G42), ISNUMBER(H42)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H42,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G42,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J42), ISNUMBER(K42)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K42,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J42,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B43:E43)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E43,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C43+D43),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G43), ISNUMBER(H43)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H43,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G43,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J43), ISNUMBER(K43)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K43,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J43,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B44:E44)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E44,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C44+D44),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G44), ISNUMBER(H44)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H44,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G44,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J44), ISNUMBER(K44)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K44,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J44,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B45:E45)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E45,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C45+D45),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G45), ISNUMBER(H45)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H45,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G45,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J45), ISNUMBER(K45)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K45,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J45,\"\")","v":""},""]
[null,"Expected Damages Part B"]
[null,"Cost of Loss of Transit\n Services for Passengers ($/Passenger/Hour):",null,{"$f":"+Service_Loss_Coeff","v":17.079999999999998}]
[null,"Current Federal \nMileage Rate ($/Mile):",null,{"$f":"Federal_Mileage_Coeff","v":0.53500000000000003}]
[null,null,"Damages Due to Delay and/or Extra Travel \nTime for Passengers in Rail or Ferry Services  ",null,null,null,"Damages Due to Delay and/or Extra Travel \nTime and Miles for Passengers in Buses"]
[null,"Recurrence Interval\n(Years)","Delay or Extra Travel Time (Hours)","Average Daily Number of Passengers","Duration of Loss or Reduction of Rail or Ferry Services (Days)","Loss of Services Damages\n(Rail or Ferry)","Additional Time per One-way Trip (Hours)","Additional Travel Miles","Duration of Loss or Reduction of Services (Days)","Number of One-way Traffic Trips Per Day (Buses)","Average Number of Passengers per Bus","Loss of Services Damages (Buses)","Total Part B Damages"]
[null,{"$f":"IF(ISNUMBER(B34),B34,\"\")","v":""},null,null,null,{"$f":"IF(COUNTBLANK(B51:E51)=0,$D$47*C51*D51*E51,\"\")","v":""},null,null,null,null,null,{"$f":"IF(COUNTBLANK(G51:K51)=0,$D$47*G51*J51*K51*I51+$D$48*H51*J51*I51,\" \")","v":" "},{"$f":"IF(AND(ISNUMBER($F51),ISNUMBER($L51)),SUM($F51,$L51),\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER(B35),B35,\"\")","v":""},null,null,null,{"$f":"IF(COUNTBLANK(B52:E52)=0,$D$47*C52*D52*E52,\"\")","v":""},null,null,null,null,null,{"$f":"IF(COUNTBLANK(G52:K52)=0,$D$47*G52*J52*K52*I52+$D$48*H52*J52*I52,\" \")","v":" "},{"$f":"IF(AND(ISNUMBER($F52),ISNUMBER($L52)),SUM($F52,$L52),\"\")","v":""}]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"Overall Expected Damages to be Used in BCR Calculation"]
[null,"Recurrence Interval\n(Years)","Total Expected Damages","Error Check:",{"$f":"IF(COUNT(C65:C76)<2,\"\",IF(AND(T65:T76,U65:U76),\"\",\"Total damages need to increase with increasing RI!\"))","v":""},{"$f":"IF(COUNT(E64:E75)<2,\"\",IF(AND(V64:V75,W64:W75),\"\",\"Total damages need to increase with increasing RI!\"))","v":""},"","","","","","",""]
[null,{"$f":"IF(ISNUMBER(B34),B34,\"\")","v":""},{"$f":"IF(OR(ISNUMBER(G34),ISNUMBER(M51)),SUM(G34,M51),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(ISNUMBER(B65),RANK(B65,$B$65:$B$76),\"\")","v":""},{"$f":"IF(ISNUMBER(C65),RANK(C65,$C$65:$C$76),\"\")","v":""},{"$f":"IF(ISNUMBER(R66),R65>R66,\"\")","v":""},{"$f":"IF(C65=0,TRUE,IF(ISNUMBER(S66),S65>S66,\"\"))","v":""}]
[null,"",{"$f":"IF(OR(ISNUMBER(G35),ISNUMBER(M52)),SUM(G35,M52),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(G36),ISNUMBER(M53)),SUM(G36,M53),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(G37),ISNUMBER(M54)),SUM(G37,M54),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(G38),ISNUMBER(M55)),SUM(G38,M55),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(M39),ISNUMBER(M56)),SUM(M39,M56),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","",{"$f":"IF(ISNUMBER(R78),R76>R78,\"\")","v":""},{"$f":"IF(C76=0,TRUE,IF(ISNUMBER(T78),S76>T78,\"\"))","v":""}]
[]
[null,"Section II - Historic Damages"]
[null,"Analysis Year :",null,null,{"$f":"IF(ISNUMBER(Analysis_Year),Analysis_Year,\"\")","v":""}]
[null,"Year Built  (4-digit Year)*:"]
[null,"Analysis Duration (Years):",null,null,{"$f":"IF(AND(ISNUMBER(Analysis_Year),ISNUMBER(Year_Built)),MAX(10,Analysis_Year-MAX(Year_Built,1908)+1),\"\")","v":""}]
[null,"User Input Analysis Duration (Years):",null,null,null,"Documentation/Justification:"]
[null,"Selected Analysis Duration (Years):",null,null,{"$f":"IF(ISNUMBER(User_Input_Analysis_Duration),MIN(Analysis_Duration,User_Input_Analysis_Duration),Analysis_Duration)","v":""}]
[null,"Historic Damages Questionnaire"]
[null,"How many documented historic damage events do you have?*",null,null,null,null,null,"For how many of these historic damage events \ndo you know the Recurrence Intervals (RIs)?*"]
[null,"Errors: (must be corrected \nto proceed with analysis)",null,null,{"$f":"IF(Values!$U4=\"None\",\" \",Values!$U4)","v":"Insufficient number of historic damage events of known RIs to conduct a valid analysis!"}]
[]
[null,"Warnings: (must be addressed \nto conduct a valid analysis)",null,null,{"$f":"IF(Values!$U5=\"None\",\" \",Values!$U5)","v":" "}]
[]
[null,"Conclusions: (provide directions \non analysis approach based \non completed questionnaire)",null,null,{"$f":"IF(Values!$U6=\"None\",\" \",Values!$U6)","v":"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. "}]
[]
[null,"Source/Documentation of Historic Damages:"]
[null,"Historic Damages Part A (Unknown Recurrence Interval)"]
[null,null,"Physical Damages Costs",null,"Response and Recovery Costs","Other Damage/Loss (describe below)"]
[null,null,"(includes permanent repairs to damaged fixed structures and rolling stock)",null,"(includes costs of emergency\nprotective measures and temporary repairs or measures that can be avoided by the proposed project)"]
[]
[null,"Event Year*\n(4-digit Year)","Physical Damage Costs for Fixed Structures*\n($)","Physical Damage Costs for Rolling \nStock* ($)","Response and \nRecovery Costs* ($)","Other Damage \nor Loss ($)","Sum of Part A Damages ($) ","Are Damages \nin Current Dollars?* ","Total Part A Damages (Inflated to Analysis Year) ($) "]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C98),ISNUMBER($D98),ISNUMBER($E98),ISNUMBER($F98)),SUM($C98,$D98,$E98,$F98),\"\")","v":""},false,{"$f":"IF(ISNUMBER($G98), IF($H98,$G98, (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$164,2,FALSE)/VLOOKUP($B98,'Freq Calculations'!$E$54:$F$164,2,FALSE))*$G98),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C99),ISNUMBER($D99),ISNUMBER($E99),ISNUMBER($F99)),SUM($C99,$D99,$E99,$F99),\"\")","v":""},false,{"$f":"IF(ISNUMBER($G99), IF($H99,$G99, (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$164,2,FALSE)/VLOOKUP($B99,'Freq Calculations'!$E$54:$F$164,2,FALSE))*$G99),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C100),ISNUMBER($D100),ISNUMBER($E100),ISNUMBER($F100)),SUM($C100,$D100,$E100,$F100),\"\")","v":""},false,{"$f":"IF(ISNUMBER($G100), IF($H100,$G100, (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$164,2,FALSE)/VLOOKUP($B100,'Freq Calculations'!$E$54:$F$164,2,FALSE))*$G100),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C101),ISNUMBER($D101),ISNUMBER($E101),ISNUMBER($F101)),SUM($C101,$D101,$E101,$F101),\"\")","v":""},false,{"$f":"IF(ISNUMBER($G101), IF($H101,$G101, (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$164,2,FALSE)/VLOOKUP($B101,'Freq Calculations'!$E$54:$F$164,2,FALSE))*$G101),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C102),ISNUMBER($D102),ISNUMBER($E102),ISNUMBER($F102)),SUM($C102,$D102,$E102,$F102),\"\")","v":""},false,{"$f":"IF(ISNUMBER($G102), IF($H102,$G102, (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$164,2,FALSE)/VLOOKUP($B102,'Freq Calculations'!$E$54:$F$164,2,FALSE))*$G102),\"\")","v":""}]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B103:E103)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E103,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C103+D103),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G103), ISNUMBER(H103)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H103,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G103,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J103), ISNUMBER(K103)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K103,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J103,\"\")","v":""},{"$f":"IF(AND(ISNUMBER($F103),ISNUMBER($I103),ISNUMBER($L103)),SUM($F103,$I103,$L103),\"\")","v":""}]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B104:E104)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E104,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C104+D104),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G104), ISNUMBER(H104)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H104,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G104,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J104), ISNUMBER(K104)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K104,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J104,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B105:E105)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E105,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C105+D105),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G105), ISNUMBER(H105)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H105,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G105,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J105), ISNUMBER(K105)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K105,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J105,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B106:E106)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E106,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C106+D106),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G106), ISNUMBER(H106)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H106,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G106,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J106), ISNUMBER(K106)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K106,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J106,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B107:E107)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E107,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C107+D107),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G107), ISNUMBER(H107)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H107,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G107,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J107), ISNUMBER(K107)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K107,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J107,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B108:E108)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E108,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C108+D108),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G108), ISNUMBER(H108)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H108,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G108,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J108), ISNUMBER(K108)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K108,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J108,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B109:E109)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E109,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C109+D109),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G109), ISNUMBER(H109)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H109,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G109,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J109), ISNUMBER(K109)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K109,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J109,\"\")","v":""},""]
[null,"Historic Damages Part B (Unknown Recurrence Interval)"]
[null,"Cost of Loss of Transit\n Services for Passengers ($/Passenger/Hour):",null,{"$f":"+Service_Loss_Coeff","v":17.079999999999998}]
[null,"Current Federal \nMileage Rate ($/Mile):",null,{"$f":"+Federal_Mileage_Coeff","v":0.53500000000000003}]
[null,null,"Damages Due to Delay and/or Extra Travel \nTime for Passengers in Rail or Ferry Services ",null,null,null,"Damages Due to Delay and/or Extra Travel \nTime and Miles for Passengers in Buses"]
[null,"Event Year\n(4-digit Year)","Delay or Extra Travel Time (Hours)*","Average Daily Number of Passengers*","Duration of Loss or Reduction of Rail or Ferry Services (Days)*","Loss of Services Damages\n(Rail or Ferry)","Additional Time \nper One-way \nTrip (Hours)*","Additional \nTravel Miles*","Duration of Loss or Reduction of Services (Days)*","Number of One-way Traffic Trips Per Day (Buses)*","Average Number of Passengers per Bus*","Loss of Services \nDamages (Buses)","Total Part B Damages"]
[null,{"$f":"IF(ISNUMBER(B98),B98,\"\")","v":""},null,null,null,{"$f":"IF(COUNTBLANK(B115:E115)=0,$D$111*C115*D115*E115,\"\")","v":""},null,null,null,null,null,{"$f":"IF(COUNTBLANK(G115:K115)=0,$D$111*G115*J115*K115*I115+$D$112*H115*J115*I115,\" \")","v":" "},{"$f":"IF(AND(ISNUMBER($F115),ISNUMBER($L115)),SUM($F115,$L115),\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER(B99),B99,\"\")","v":""},null,null,null,{"$f":"IF(COUNTBLANK(B116:E116)=0,$D$111*C116*D116*E116,\"\")","v":""},null,null,null,null,null,{"$f":"IF(COUNTBLANK(G116:K116)=0,$D$111*G116*J116*K116*I116+$D$112*H116*J116*I116,\" \")","v":" "},{"$f":"IF(AND(ISNUMBER($F116),ISNUMBER($L116)),SUM($F116,$L116),\"\")","v":""}]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"Overall Historic Damages to be Used in BCR Calculation (Not Including Catastrophic Events to be Entered in Parts C and D)"]
[null,"Event Year\n(4-digit Year)","Total Historic Damages","Error Check:",{"$f":"IF(COUNT(C129:C140)<2,\"\",'Freq Calculations'!$B$24)","v":""}]
[null,{"$f":"IF(ISNUMBER(B98),B98,\"\")","v":""},{"$f":"IF(OR(ISNUMBER(I98),ISNUMBER(M115)),SUM(I98,M115),\"\")","v":""}]
[null,"",{"$f":"IF(OR(ISNUMBER(I99),ISNUMBER(M116)),SUM(I99,M116),\"\")","v":""}]
[null,"",{"$f":"IF(OR(ISNUMBER(I100),ISNUMBER(M117)),SUM(I100,M117),\"\")","v":""}]
[null,"",{"$f":"IF(OR(ISNUMBER(I101),ISNUMBER(M118)),SUM(I101,M118),\"\")","v":""}]
[null,"",{"$f":"IF(OR(ISNUMBER(I102),ISNUMBER(M119)),SUM(I102,M119),\"\")","v":""}]
[null,"",{"$f":"IF(OR(ISNUMBER(M103),ISNUMBER(M120)),SUM(M103,M120),\"\")","v":""}]
[null,"",""]
[null,"",""]
[null,"",""]
[null,"",""]
[null,"",""]
[null,"",""]
[null,"Historic Damages Part C (Known Recurrence Interval)"]
[null,null,null,"Physical Damages Costs",null,"Response and Recovery Costs","Other Damage/Loss (describe below)"]
[null,null,null,"(includes permanent repairs \nto damaged fixed structures \nand rolling stock)",null,"(includes costs of emergency protective measures and temporary repairs or measures that can be avoided by the proposed project)"]
[]
[null,"Damage \nYear\n(4-digit \nYear)*","Known Recurrence Interval (Years)*","Physical Damage Costs for Fixed Structures ($)*","Physical Damage Costs for Rolling\nStock ($)*","Response and recovery Costs that can be Avoided by proposed Measures ($)*","Other Damage \nor Loss ($)","Sum of Part C Damages ($) ","Are Damages \nin Current Dollars?*","Total Part C Damages\n(Inflated to Analysis Year) ($)"]
[null,null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C146),ISNUMBER($D146),ISNUMBER($E146),ISNUMBER($F146),ISNUMBER($G146)),SUM($D146,$E146,$F146,$G146),\"\")","v":""},false,{"$f":"IF(ISNUMBER($H146), IF($I146,$H146, (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$164,2,FALSE)/VLOOKUP($B146,'Freq Calculations'!$E$54:$F$164,2,FALSE))*$H146),\"\")","v":""}]
[null,null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C147),ISNUMBER($D147),ISNUMBER($E147),ISNUMBER($F147),ISNUMBER($G147)),SUM($D147,$E147,$F147,$G147),\"\")","v":""},false,{"$f":"IF(ISNUMBER($H147), IF($I147,$H147, (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$164,2,FALSE)/VLOOKUP($B147,'Freq Calculations'!$E$54:$F$164,2,FALSE))*$H147),\"\")","v":""}]
[null,"Historic Damages Part D (Known Recurrence Interval)"]
[null,"Cost of Loss of Transit\n Services for Passengers ($/Passenger/Hour):",null,{"$f":"+Service_Loss_Coeff","v":17.079999999999998}]
[null,"Current Federal \nMileage Rate ($/Mile):",null,{"$f":"+Federal_Mileage_Coeff","v":0.53500000000000003}]
[null,null,null,"Damages Due to Delay and/or Extra Travel \nTime for Passengers in Rail or Ferry Services ",null,null,null,"Damages Due to Delay and/or Extra Travel \nTime and Miles for Passengers in Buses"]
[null,"Damage \nYear\n(4-digit \nYear)","Known Recurrence Interval (Years)","Delay or Extra Travel Time (Hours)*","Average Daily Number of Passengers*","Duration of Loss \nor Reduction of \nRail or Ferry \nServices (Days)*","Loss of Services Damages\n(Rail or Ferry)","Additional Time per One-way\nTrip (Hours)*","Additional Travel Miles*","Duration of Loss or Reduction of Services (Days)*","Number of One-way Traffic Trips Per Day (Buses)*","Average Number of Passengers per Bus*","Loss of Services Damages (Buses)"]
[null,{"$f":"IF(ISNUMBER(B146),B146,\"\")","v":""},{"$f":"IF(ISNUMBER(C146),C146,\"\")","v":""},null,null,null,{"$f":"IF(AND(ISNUMBER(B146),ISNUMBER(C153),ISNUMBER(D153),ISNUMBER(E153),ISNUMBER(F153)),$D$149*D153*E153*F153,\" \")","v":" "},null,null,null,null,null,{"$f":"IF(COUNTBLANK(H153:L153)=0,$D$149*H153*K153*L153*J153+$D$150*I153*K153*J153,\" \")","v":" "}]
[null,{"$f":"IF(ISNUMBER(B147),B147,\"\")","v":""},{"$f":"IF(ISNUMBER(C147),C147,\"\")","v":""},null,null,null,{"$f":"IF(AND(ISNUMBER(B147),ISNUMBER(C154),ISNUMBER(D154),ISNUMBER(E154),ISNUMBER(F154)),$D$149*D154*E154*F154,\" \")","v":" "},null,null,null,null,null,{"$f":"IF(COUNTBLANK(H154:L154)=0,$D$149*H154*K154*L154*J154+$D$150*I154*K154*J154,\" \")","v":" "}]
[null,"Historic Damages Totals (Known Recurrence Interval)"]
[null,"Damage \nYear\n(4-digit \nYear)","Known Recurrence Interval (Years)","Total Part C Damages","Total Part D Damages","Total Historic Damages (Inflated to Analysis Year)","Error Check:",{"$f":"'Freq Calculations'!$B$25","v":""}]
[null,{"$f":"IF(ISNUMBER(B146),B146,\"\")","v":""},{"$f":"IF(ISNUMBER(C146),C146,\"\")","v":""},{"$f":"IF(ISNUMBER(J146),J146,\"\")","v":""},{"$f":"IF(AND(ISNUMBER($G153),ISNUMBER($M153)),SUM($G153,$M153),\"\")","v":""},{"$f":"IF(OR(ISNUMBER(D157),ISNUMBER(E157)),SUM(D157,E157),\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER(B147),B147,\"\")","v":""},{"$f":"IF(ISNUMBER(C147),C147,\"\")","v":""},{"$f":"IF(ISNUMBER(J147),J147,\"\")","v":""},{"$f":"IF(AND(ISNUMBER($G154),ISNUMBER($M154)),SUM($G154,$M154),\"\")","v":""},{"$f":"IF(OR(ISNUMBER(D158),ISNUMBER(E158)),SUM(D158,E158),\"\")","v":""}]
[null,"Overall Historic Damages to be Used in BCR Calculation"]
[null,"Recurrence Interval\n(Years)","Total Historic Damages","Error Check:",{"$f":"IF(COUNT(C161:C172)<2,\"\",IF(AND(Values!Q1:Q12,Values!R1:R12),\"\",\"Total damages need to increase with increasing RI!\"))","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H10),'Freq Calculations'!H10,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I10),'Freq Calculations'!I10,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H11),'Freq Calculations'!H11,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I11),'Freq Calculations'!I11,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H12),'Freq Calculations'!H12,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I12),'Freq Calculations'!I12,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H13),'Freq Calculations'!H13,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I13),'Freq Calculations'!I13,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H14),'Freq Calculations'!H14,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I14),'Freq Calculations'!I14,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H15),'Freq Calculations'!H15,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I15),'Freq Calculations'!I15,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H16),'Freq Calculations'!H16,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I16),'Freq Calculations'!I16,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H17),'Freq Calculations'!H17,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I17),'Freq Calculations'!I17,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H18),'Freq Calculations'!H18,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I18),'Freq Calculations'!I18,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H19),'Freq Calculations'!H19,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I19),'Freq Calculations'!I19,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H20),'Freq Calculations'!H20,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I20),'Freq Calculations'!I20,\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER('Freq Calculations'!H21),'Freq Calculations'!H21,\"\")","v":""},{"$f":"IF(ISNUMBER('Freq Calculations'!I21),'Freq Calculations'!I21,\"\")","v":""}]

{"sheet":"Post Resil Damages","index":2,"n_rows":64,"n_cols":21,"rows_kept":64,"cut":false,"hidden":true,"merged":[[50,1,50,12],[51,4,51,12],[52,3,56,12],[57,3,63,12],[33,1,33,12],[34,1,34,2],[34,4,35,12],[35,1,35,2],[36,2,36,5],[36,6,36,11],[16,1,16,2],[16,3,16,12],[17,1,17,12],[18,1,19,1],[18,2,18,3],[18,6,19,6],[18,7,25,12],[19,2,19,3],[15,1,15,12],[12,1,12,2],[12,3,12,12],[13,1,13,2],[13,3,13,12],[14,1,14,2],[14,3,14,4],[3,1,3,4],[11,1,11,12],[1,1,1,12],[3,11,3,12],[4,1,4,2],[4,3,4,4],[4,11,6,12],[5,1,5,2],[5,3,5,4],[6,1,6,2],[6,3,6,4],[7,1,7,2],[7,3,7,4],[10,1,10,12]]}
[]
[null,"Hazard Mitigation Cost Effectiveness Tool"]
[]
[null,"BCR Summary",null,null,null,null,null,null,null,null,null,"Tools"]
[null,"Proposed Project \nEffectiveness (Years):",null,{"$f":"'Project Info'!C51","v":0}]
[null,"Total Project Benefits:",null,{"$f":"IF(ISNUMBER(PV_Annual_Damages_Before_Mitigation-PV_Annual_Damages_After_Mitigation),PV_Annual_Damages_Before_Mitigation-PV_Annual_Damages_After_Mitigation,\"\")","v":""}]
[null,"Total BCA Project Costs:",null,{"$f":"IF(ISNUMBER(Total_Project_Cost),Total_Project_Cost,\"\")","v":0}]
[null,"Benefit-Cost Ratio (BCR):",null,{"$f":"BCR","v":""}]
[]
[]
[null,"Post-Resilience Damage Information"]
[null,"Section I - General Information"]
[null,"Agency:",null,{"$f":"IF(ISBLANK('Project Info'!$C$38),\"\",'Project Info'!$C$38)","v":""}]
[null,"Project Name:",null,{"$f":"IF(ISBLANK('Project Info'!$C$43),\"\",'Project Info'!$C$43)","v":""}]
[null,"Analysis Year:",null,{"$f":"IF(ISNUMBER(Analysis_Year),Analysis_Year,\"\")","v":""}]
[null,"Section II - Expected Damages"]
[null,"Source/Documentation of Expected Damages:"]
[null,"Expected Damages Part A"]
[null,null,"Physical Damages Costs",null,"Response and Recovery Costs","Other Damage/Loss (describe below)"]
[null,null,"(includes permanent repairs to damaged fixed structures and rolling stock)",null,"(includes costs of emergency protective measures and temporary repairs or measures that can be avoided by the proposed project)"]
[null,"Recurrence Interval\n(Years)*","Physical Damage Costs for Fixed Structures ($)*","Physical Damage Costs for Rolling Stock ($)*","Response and Recovery Costs \n($)*","Other Damage \nor Loss ($)","Total Part A Damages "]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C22),ISNUMBER($D22),ISNUMBER($E22),ISNUMBER($F22)),SUM($C22,$D22,$E22,$F22),\"\")","v":""}]
[null,null,null,null,null,null,{"$f":"IF(AND(ISNUMBER($C23),ISNUMBER($D23),ISNUMBER($E23),ISNUMBER($F23)),SUM($C23,$D23,$E23,$F23),\"\")","v":""}]
[null,null,null,null,null,null,""]
[null,null,null,null,null,null,""]
[null,null,null,null,null,null,""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B27:E27)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E27,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C27+D27),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G27), ISNUMBER(H27)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H27,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G27,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J27), ISNUMBER(K27)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K27,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J27,\"\")","v":""},{"$f":"IF(AND(ISNUMBER($F27),ISNUMBER($I27),ISNUMBER($L27)),SUM($F27,$I27,$L27),\"\")","v":""}]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B28:E28)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E28,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C28+D28),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G28), ISNUMBER(H28)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H28,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G28,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J28), ISNUMBER(K28)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K28,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J28,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B29:E29)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E29,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C29+D29),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G29), ISNUMBER(H29)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H29,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G29,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J29), ISNUMBER(K29)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K29,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J29,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B30:E30)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E30,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C30+D30),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G30), ISNUMBER(H30)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H30,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G30,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J30), ISNUMBER(K30)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K30,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J30,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B31:E31)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E31,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C31+D31),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G31), ISNUMBER(H31)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H31,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G31,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J31), ISNUMBER(K31)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K31,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J31,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B32:E32)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E32,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C32+D32),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G32), ISNUMBER(H32)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H32,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G32,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J32), ISNUMBER(K32)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K32,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J32,\"\")","v":""},""]
[null,null,null,null,null,{"$f":"IF(COUNTBLANK(B33:E33)=0,(VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(E33,'Freq Calculations'!$E$54:$F$162,2,FALSE))*(C33+D33),\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(G33), ISNUMBER(H33)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(H33,'Freq Calculations'!$E$54:$F$162,2,FALSE))*G33,\"\")","v":""},null,null,{"$f":"IF(AND(ISNUMBER(J33), ISNUMBER(K33)), (VLOOKUP(Analysis_Year,'Freq Calculations'!$E$54:$F$162,2,FALSE)/VLOOKUP(K33,'Freq Calculations'!$E$54:$F$162,2,FALSE))*J33,\"\")","v":""},""]
[null,"Expected Damages Part B"]
[null,"Cost of Loss of Transit\n Services for Passengers ($/Passenger/Hour):",null,{"$f":"+Service_Loss_Coeff","v":17.079999999999998}]
[null,"Current Federal \nMileage Rate ($/Mile):",null,{"$f":"Federal_Mileage_Coeff","v":0.53500000000000003}]
[null,null,"Damages Due to Delay and/or Extra Travel \nTime for Passengers in Rail or Ferry Services  ",null,null,null,"Damages Due to Delay and/or Extra Travel \nTime and Miles for Passengers in Buses"]
[null,"Recurrence Interval\n(Years)","Delay or Extra Travel Time (Hours)*","Average Daily Number of Passengers*","Duration of Loss or Reduction of Rail or Ferry Services (Days)*","Loss of Services Damages\n(Rail or Ferry)","Additional Time per One-way Trip (Hours)*","Additional Travel Miles*","Duration of Loss or Reduction of Services (Days)*","Number of One-way Traffic Trips Per Day (Buses)*","Average Number of Passengers per Bus*","Loss of Services Damages (Buses)","Total Part B Damages"]
[null,{"$f":"IF(ISNUMBER(B22),B22,\"\")","v":""},null,null,null,{"$f":"IF(COUNTBLANK(B39:E39)=0,$D$35*C39*D39*E39,\"\")","v":""},null,null,null,null,null,{"$f":"IF(COUNTBLANK(G39:K39)=0,$D$35*G39*J39*K39*I39+$D$36*H39*J39*I39,\" \")","v":" "},{"$f":"IF(AND(ISNUMBER($F39),ISNUMBER($L39)),SUM($F39,$L39),\"\")","v":""}]
[null,{"$f":"IF(ISNUMBER(B23),B23,\"\")","v":""},null,null,null,{"$f":"IF(COUNTBLANK(B40:E40)=0,$D$35*C40*D40*E40,\"\")","v":""},null,null,null,null,null,{"$f":"IF(COUNTBLANK(G40:K40)=0,$D$35*G40*J40*K40*I40+$D$36*H40*J40*I40,\" \")","v":" "},{"$f":"IF(AND(ISNUMBER($F40),ISNUMBER($L40)),SUM($F40,$L40),\"\")","v":""}]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"",null,null,null,"",null,null,null,null,null," ",""]
[null,"Overall Expected Damages to be Used in BCR Calculation"]
[null,"Recurrence Interval\n(Years)","Total Expected Damages","Error Check:",{"$f":"IF(COUNT(C53:C64)<2,\"\",IF(AND(T53:T64,U53:U64),\"\",\"Total damages need to increase with increasing RI!\"))","v":""},{"$f":"IF(COUNT(E52:E63)<2,\"\",IF(AND(V52:V63,W52:W63),\"\",\"Total damages need to increase with increasing RI!\"))","v":""},"","","","","","",""]
[null,{"$f":"IF(ISNUMBER(B22),B22,\"\")","v":""},{"$f":"IF(OR(ISNUMBER(G22),ISNUMBER(M39)),SUM(G22,M39),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(ISNUMBER(B53),RANK(B53,$B$53:$B$64),\"\")","v":""},{"$f":"IF(ISNUMBER(C53),RANK(C53,$C$53:$C$64),\"\")","v":""},{"$f":"IF(ISNUMBER(R54),R53>R54,\"\")","v":""},{"$f":"IF(C53=0,TRUE,IF(ISNUMBER(S54),S53>S54,\"\"))","v":""}]
[null,"",{"$f":"IF(OR(ISNUMBER(G23),ISNUMBER(M40)),SUM(G23,M40),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(G24),ISNUMBER(M41)),SUM(G24,M41),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(G25),ISNUMBER(M42)),SUM(G25,M42),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(G26),ISNUMBER(M43)),SUM(G26,M43),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"",{"$f":"IF(OR(ISNUMBER(M27),ISNUMBER(M44)),SUM(M27,M44),\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",""]
[null,"","",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","",{"$f":"IF(ISNUMBER(#REF!),R64>#REF!,\"\")","v":""},{"$f":"IF(C64=0,TRUE,IF(ISNUMBER(#REF!),S64>#REF!,\"\"))","v":""}]

{"sheet":"Analysis Results","index":3,"n_rows":42,"n_cols":11,"rows_kept":42,"cut":false,"hidden":false,"merged":[[20,7,20,8],[21,7,21,8],[22,7,22,8],[23,7,23,8],[29,3,29,4],[29,7,29,8],[24,7,24,8],[25,7,25,8],[26,7,26,8],[27,7,27,8],[28,7,28,8],[20,3,20,4],[21,3,21,4],[22,3,22,4],[23,3,23,4],[25,3,25,4],[3,1,3,8],[17,3,17,4],[18,3,18,4],[19,3,19,4],[17,7,17,8],[18,7,18,8],[19,7,19,8],[16,3,16,4],[16,7,16,8],[30,5,30,6],[30,3,30,4],[30,7,30,8],[32,1,32,8],[33,1,33,2],[33,3,33,4],[33,5,36,8],[34,1,34,2],[34,3,34,4],[30,1,30,2],[40,3,40,8],[41,2,41,8],[5,1,5,8],[6,2,6,8],[7,2,7,8],[8,2,8,8],[9,2,9,4],[11,2,11,8],[12,3,13,8],[15,1,15,4],[15,5,15,8],[10,1,10,8],[14,1,14,8],[24,3,24,4],[35,1,35,2],[35,3,35,4],[6,10,11,12],[5,10,5,12],[1,1,1,8],[38,1,38,8],[39,2,39,8],[36,1,36,2],[36,3,36,4],[26,3,26,4],[27,3,27,4],[28,3,28,4],[37,1,37,2],[37,3,37,4],[37,5,37,8],[29,1,29,2],[29,5,29,6],[31,1,31,8]]}
[]
[null,"Hazard Mitigation Cost Effectiveness Tool"]
[]
[null,"BCA Results"]
[]
[null,"Section I - Agency Information",null,null,null,null,null,null,null,null,"Tools"]
[null,"Agency:",{"$f":"IF(ISBLANK('Project Info'!$C$38),\"\",'Project Info'!$C$38)","v":""}]
[null,"Address:",{"$f":"IF(ISBLANK('Project Info'!$C$39),\"\",'Project Info'!$C$39)","v":""}]
[null,"Address Line 2:",{"$f":"IF(ISBLANK('Project Info'!$C$40),\"\",'Project Info'!$C$40)","v":""}]
[null,"City:",{"$f":"IF(ISBLANK('Project Info'!$C$41),\"\",'Project Info'!$C$41)","v":""},null,null,"State:",{"$f":"IF('Project Info'!$G$41=\"\",\"\",'Project Info'!$G$41)","v":""},"Zip:",{"$f":"IF(ISBLANK('Project Info'!$I$41),\"\",'Project Info'!$I$41)","v":""}]
[null,"Section II - Project Information"]
[null,"Project Name:",{"$f":"IF(ISBLANK('Project Info'!$C$43),\"\",'Project Info'!$C$43)","v":""}]
[null,"Application Date:",{"$f":"IF(ISBLANK('Project Info'!$G$44),\"\",'Project Info'!$G$44)","v":""}]
[null,"Analysis Year:",{"$f":"IF(ISNUMBER(Analysis_Year),Analysis_Year,\"\")","v":""}]
[null,"Section III - Resilience Damages"]
[null,"Pre-Resilience Damages*",null,null,null,"Post-Resilience Damages"]
[null,"Recurrence Interval\n(Years)","Total Damages","Annualized Damages ",null,"Recurrence Interval\n(Years)","Total Damages","Annualized Damages "]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B65,IF(Values!$J$3=2,'Damage Info'!B161,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C65,IF(Values!$J$3=2,'Damage Info'!C161,\"\"))","v":""},{"$f":"IF(C18=0,0,IF(Values!L3=0,\"\",IF(Values!L4=0,Values!L3*C18,EXP(0.5*(LN(C18)+LN(C19)))*(Values!L3-Values!L4))))","v":""},null,{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(B18),B18,\"\"),IF(ISNUMBER('Post Resil Damages'!B53),'Post Resil Damages'!B53,\"\"))","v":""},{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(C18),IF($B18>Project_Effectiveness,C18,0),\"\"),IF(ISNUMBER('Post Resil Damages'!C53),'Post Resil Damages'!C53,\"\"))","v":""},{"$f":"IF(G18=0,0,IF(Values!M3=0,\"\",IF(Values!M4=0,Values!M3*G18,EXP(0.5*(LN(G18)+LN(G19)))*(Values!M3-Values!M4))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B66,IF(Values!$J$3=2,'Damage Info'!B162,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C66,IF(Values!$J$3=2,'Damage Info'!C162,\"\"))","v":""},{"$f":"IF(C19=0,0,IF(Values!L4=0,\"\",IF(Values!L5=0,Values!L4*C19,EXP(0.5*(LN(C19)+LN(C20)))*(Values!L4-Values!L5))))","v":""},null,{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(B19),B19,\"\"),IF(ISNUMBER('Post Resil Damages'!B54),'Post Resil Damages'!B54,\"\"))","v":""},{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(C19),IF($B19>Project_Effectiveness,C19,0),\"\"),IF(ISNUMBER('Post Resil Damages'!C54),'Post Resil Damages'!C54,\"\"))","v":""},{"$f":"IF(G19=0,0,IF(Values!M4=0,\"\",IF(Values!M5=0,Values!M4*G19,EXP(0.5*(LN(G19)+LN(G20)))*(Values!M4-Values!M5))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B67,IF(Values!$J$3=2,'Damage Info'!B163,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C67,IF(Values!$J$3=2,'Damage Info'!C163,\"\"))","v":""},{"$f":"IF(C20=0,0,IF(Values!L5=0,\"\",IF(Values!L6=0,Values!L5*C20,EXP(0.5*(LN(C20)+LN(C21)))*(Values!L5-Values!L6))))","v":""},null,{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(B20),B20,\"\"),IF(ISNUMBER('Post Resil Damages'!B55),'Post Resil Damages'!B55,\"\"))","v":""},{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(C20),IF($B20>Project_Effectiveness,C20,0),\"\"),IF(ISNUMBER('Post Resil Damages'!C55),'Post Resil Damages'!C55,\"\"))","v":""},{"$f":"IF(G20=0,0,IF(Values!M5=0,\"\",IF(Values!M6=0,Values!M5*G20,EXP(0.5*(LN(G20)+LN(G21)))*(Values!M5-Values!M6))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B68,IF(Values!$J$3=2,'Damage Info'!B164,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C68,IF(Values!$J$3=2,'Damage Info'!C164,\"\"))","v":""},{"$f":"IF(C21=0,0,IF(Values!L6=0,\"\",IF(Values!L7=0,Values!L6*C21,EXP(0.5*(LN(C21)+LN(C22)))*(Values!L6-Values!L7))))","v":""},null,{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(B21),B21,\"\"),IF(ISNUMBER('Post Resil Damages'!B56),'Post Resil Damages'!B56,\"\"))","v":""},{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(C21),IF($B21>Project_Effectiveness,C21,0),\"\"),IF(ISNUMBER('Post Resil Damages'!C56),'Post Resil Damages'!C56,\"\"))","v":""},{"$f":"IF(G21=0,0,IF(Values!M6=0,\"\",IF(Values!M7=0,Values!M6*G21,EXP(0.5*(LN(G21)+LN(G22)))*(Values!M6-Values!M7))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B69,IF(Values!$J$3=2,'Damage Info'!B165,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C69,IF(Values!$J$3=2,'Damage Info'!C165,\"\"))","v":""},{"$f":"IF(C22=0,0,IF(Values!L7=0,\"\",IF(Values!L8=0,Values!L7*C22,EXP(0.5*(LN(C22)+LN(C23)))*(Values!L7-Values!L8))))","v":""},null,{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(B22),B22,\"\"),IF(ISNUMBER('Post Resil Damages'!B57),'Post Resil Damages'!B57,\"\"))","v":""},{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(C22),IF($B22>Project_Effectiveness,C22,0),\"\"),IF(ISNUMBER('Post Resil Damages'!C57),'Post Resil Damages'!C57,\"\"))","v":""},{"$f":"IF(G22=0,0,IF(Values!M7=0,\"\",IF(Values!M8=0,Values!M7*G22,EXP(0.5*(LN(G22)+LN(G23)))*(Values!M7-Values!M8))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B70,IF(Values!$J$3=2,'Damage Info'!B166,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C70,IF(Values!$J$3=2,'Damage Info'!C166,\"\"))","v":""},{"$f":"IF(C23=0,0,IF(Values!L8=0,\"\",IF(Values!L9=0,Values!L8*C23,EXP(0.5*(LN(C23)+LN(C24)))*(Values!L8-Values!L9))))","v":""},null,{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(B23),B23,\"\"),IF(ISNUMBER('Post Resil Damages'!B58),'Post Resil Damages'!B58,\"\"))","v":""},{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(C23),IF($B23>Project_Effectiveness,C23,0),\"\"),IF(ISNUMBER('Post Resil Damages'!C58),'Post Resil Damages'!C58,\"\"))","v":""},{"$f":"IF(G23=0,0,IF(Values!M8=0,\"\",IF(Values!M9=0,Values!M8*G23,EXP(0.5*(LN(G23)+LN(G24)))*(Values!M8-Values!M9))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B71,IF(Values!$J$3=2,'Damage Info'!B167,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C71,IF(Values!$J$3=2,'Damage Info'!C167,\"\"))","v":""},{"$f":"IF(C24=0,0,IF(Values!L9=0,\"\",IF(Values!L10=0,Values!L9*C24,EXP(0.5*(LN(C24)+LN(C25)))*(Values!L9-Values!L10))))","v":""},null,{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(B24),B24,\"\"),IF(ISNUMBER('Post Resil Damages'!B59),'Post Resil Damages'!B59,\"\"))","v":""},{"$f":"IF(Values!$J$9=1,IF(ISNUMBER(C24),IF($B24>Project_Effectiveness,C24,0),\"\"),IF(ISNUMBER('Post Resil Damages'!C59),'Post Resil Damages'!C59,\"\"))","v":""},{"$f":"IF(G24=0,0,IF(Values!M9=0,\"\",IF(Values!M10=0,Values!M9*G24,EXP(0.5*(LN(G24)+LN(G25)))*(Values!M9-Values!M10))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B72,IF(Values!$J$3=2,'Damage Info'!B168,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C72,IF(Values!$J$3=2,'Damage Info'!C168,\"\"))","v":""},{"$f":"IF(C25=0,0,IF(Values!L10=0,\"\",IF(Values!L11=0,Values!L10*C25,EXP(0.5*(LN(C25)+LN(C26)))*(Values!L10-Values!L11))))","v":""},null,{"$f":"IF(ISNUMBER(B25),B25,\"\")","v":""},{"$f":"IF(ISNUMBER(C25),IF($B25>Project_Effectiveness,C25,0),\"\")","v":""},{"$f":"IF(G25=0,0,IF(Values!M10=0,\"\",IF(Values!M11=0,Values!M10*G25,EXP(0.5*(LN(G25)+LN(G26)))*(Values!M10-Values!M11))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B73,IF(Values!$J$3=2,'Damage Info'!B169,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C73,IF(Values!$J$3=2,'Damage Info'!C169,\"\"))","v":""},{"$f":"IF(C26=0,0,IF(Values!L11=0,\"\",IF(Values!L12=0,Values!L11*C26,EXP(0.5*(LN(C26)+LN(C27)))*(Values!L11-Values!L12))))","v":""},null,"","",{"$f":"IF(G26=0,0,IF(Values!M11=0,\"\",IF(Values!M12=0,Values!M11*G26,EXP(0.5*(LN(G26)+LN(G27)))*(Values!M11-Values!M12))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B74,IF(Values!$J$3=2,'Damage Info'!B170,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C74,IF(Values!$J$3=2,'Damage Info'!C170,\"\"))","v":""},{"$f":"IF(C27=0,0,IF(Values!L12=0,\"\",IF(Values!L13=0,Values!L12*C27,EXP(0.5*(LN(C27)+LN(C28)))*(Values!L12-Values!L13))))","v":""},null,"","",{"$f":"IF(G27=0,0,IF(Values!M12=0,\"\",IF(Values!M13=0,Values!M12*G27,EXP(0.5*(LN(G27)+LN(G28)))*(Values!M12-Values!M13))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B75,IF(Values!$J$3=2,'Damage Info'!B171,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C75,IF(Values!$J$3=2,'Damage Info'!C171,\"\"))","v":""},{"$f":"IF(C28=0,0,IF(Values!L13=0,\"\",IF(Values!L14=0,Values!L13*C28,EXP(0.5*(LN(C28)+LN(C29)))*(Values!L13-Values!L14))))","v":""},null,"","",{"$f":"IF(G28=0,0,IF(Values!M13=0,\"\",IF(Values!M14=0,Values!M13*G28,EXP(0.5*(LN(G28)+LN(G29)))*(Values!M13-Values!M14))))","v":""}]
[null,{"$f":"IF(Values!$J$3=1,'Damage Info'!B76,IF(Values!$J$3=2,'Damage Info'!B172,\"\"))","v":""},{"$f":"IF(Values!$J$3=1,'Damage Info'!C76,IF(Values!$J$3=2,'Damage Info'!C172,\"\"))","v":""},{"$f":"IF(C29=0,0,IF(Values!L14=0,\"\",IF(Values!L15=0,Values!L14*C29,EXP(0.5*(LN(C29)+LN(C30)))*(Values!L14-Values!L15))))","v":""},null,"","",{"$f":"IF(G29=0,0,IF(Values!M14=0,\"\",IF(Values!M15=0,Values!M14*G29,EXP(0.5*(LN(G29)+LN(G30)))*(Values!M14-Values!M15))))","v":""}]
[null,"Sum of Annualized Damages:",null,{"$f":"IF(COUNTBLANK(D18:D29)=12,\"\",SUM(D18:D29))","v":""},null,"Sum of Annualized Damages:",null,{"$f":"IF(COUNTBLANK(H18:H29)=12,\"\",SUM(H18:H29))","v":""}]
[null,"Present Value of Annual Damages:",null,{"$f":"IF(ISNUMBER(Present_Value_Coefficient),Present_Value_Coefficient*D30,\"\")","v":""},null,"Present Value of Annual Damages:",null,{"$f":"IF(ISNUMBER(Present_Value_Coefficient),Present_Value_Coefficient*H30,\"\")","v":""}]
[null,{"$f":"IF(Values!$J$3=1,\"*Pre-Resilience Damages are based on Expected Damages.\",IF(Values!J3=2,\"*Pre-Resilience Damages are based on Historic Damages.\",\"\"))","v":""}]
[null,"Section IV - Final Results of BCA"]
[null,"Reduction in Annual Damages:",null,{"$f":"IF(ISNUMBER(D30-H30),D30-H30,\"\")","v":""}]
[null,"Total Project Benefits:",null,{"$f":"IF(ISNUMBER(PV_Annual_Damages_Before_Mitigation-PV_Annual_Damages_After_Mitigation),PV_Annual_Damages_Before_Mitigation-PV_Annual_Damages_After_Mitigation,\"\")","v":""}]
[null,"Total BCA Project Costs:",null,{"$f":"IF(ISNUMBER(Total_Project_Cost),Total_Project_Cost,\"\")","v":0}]
[null,"Benefits Minus Costs:",null,{"$f":"IF(ISNUMBER(Total_Project_Benefits-Total_Project_Cost),Total_Project_Benefits-Total_Project_Cost,\"\")","v":""}]
[null,"Benefit-Cost Ratio (BCR):",null,{"$f":"IF(ISNUMBER(Total_Project_Benefits/Total_Project_Cost),Total_Project_Benefits/Total_Project_Cost,\"\")","v":""},null,{"$f":"IF(ISNUMBER(BCR),IF(BCR<0,\"BCR is negative. A negative BCR indicates that the proposed project is expected to increase damages rather than decrease damages and is not recommended.\", IF(BCR>1,\"BCR is greater than 1.0; the project is cost-effective based on the quantitative information provided.\",\"BCR is NOT greater than 1.0; although the project is not considered cost-effective based on the quantitative information provided, it may be cost-effective based on a review of the qualitative information provided.\")), \"BCR is not evaluated.\")","v":"BCR is not evaluated."}]
[null,"Section V - Qualitative Benefits"]
[null,"Description of Qualitative\n Benefits of the Proposed Project:"]
[null,"Average Loss of Transit Revenue ($/day):"]
[null,"Source/ Documentation of Qualitative \nBenefits:"]

{"sheet":"Values","index":4,"n_rows":58,"n_cols":64,"rows_kept":58,"cut":false,"hidden":true,"merged":[]}
["Select State","Select Year","Select Year","Losses or Delays?","Select Damage Type","Source","Location","Cost","Damage Type","Damage Type Values",null,"Before Mitig","After Mitig",null,{"$f":"IF(ISNUMBER('Damage Info'!B161),RANK('Damage Info'!B161,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C161),RANK('Damage Info'!C161,'Damage Info'!$C$161:$C$172),\"\")","v":""},{"$f":"IF(ISNUMBER(O2),O1>O2,\"\")","v":""},{"$f":"IF('Damage Info'!C161=0,TRUE,IF(ISNUMBER(P2),P1>P2,\"\"))","v":""},null,null,null,null,"Historic Damages, Questionnaire"]
[null,null,2014,"No","Expected Damages",null,"Default",17.079999999999998,"Select Damage Type","0 = No Selection, 1 = Expected Damages, 2 = Historic Damages",null,"Average Annual \nNumber of Events","Average Annual \nNumber of Events",null,{"$f":"IF(ISNUMBER('Damage Info'!B162),RANK('Damage Info'!B162,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C162),RANK('Damage Info'!C162,'Damage Info'!$C$161:$C$172),\"\")","v":""},"",{"$f":"IF('Damage Info'!C162=0,TRUE,IF(ISNUMBER(P3),P2>P3,\"\"))","v":""},null,"Events",{"$f":"'Damage Info'!G85","v":0},null,"ERROR MESSAGES",null,null,null,null,null,null,null,null,null,null,null,null,null,"WARNING MESSAGES",null,null,null,null,null,null,null,null,null,null,null,null,null,"CONCLUSIONS"]
["Alabama",2017,2015,"Yes","Historic Damages","Local historic cost data","Boston-Worcester-Manchester (MA-NH) ",21.580152264940995,"Expected Damages",{"$f":"IF('Damage Info'!D16=\"Expected Damages\",1,IF('Damage Info'!D16=\"Historic Damages\",2,0))","v":0},null,{"$f":"IF(ISNUMBER('Analysis Results'!B18),1/'Analysis Results'!B18,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F18),1/'Analysis Results'!F18,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B163),RANK('Damage Info'!B163,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C163),RANK('Damage Info'!C163,'Damage Info'!$C$161:$C$172),\"\")","v":""},"",{"$f":"IF('Damage Info'!C163=0,TRUE,IF(ISNUMBER(P4),P3>P4,\"\"))","v":""},null,"Known Event R.I>s",{"$f":"'Damage Info'!M85","v":0},null,null,"Events (Q1)",null,null,null,null,null,null,null,null,null,null,null,null,"Events (Q1)",null,null,null,null,null,null,null,null,null,null,null,null,null,"Events (Q1)"]
["Alaska",2018,null,null,null,"Current contractor bids","New York-Newark-Bridgeport (NY-NJ-CT-PA) ",22.082947863582906,"Historic Damages",null,null,{"$f":"IF(ISNUMBER('Analysis Results'!B19),1/'Analysis Results'!B19,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F19),1/'Analysis Results'!F19,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B164),RANK('Damage Info'!B164,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C164),RANK('Damage Info'!C164,'Damage Info'!$C$161:$C$172),\"\")","v":""},"",{"$f":"IF('Damage Info'!C164=0,TRUE,IF(ISNUMBER(P5),P4>P5,\"\"))","v":""},null,"Errors",{"$f":"HLOOKUP(U2,X4:AJ18,15)","v":"Insufficient number of historic damage events of known RIs to conduct a valid analysis!"},null,null,0,1,2,3,4,5,6,7,8,9,10,11,12,null,0,1,2,3,4,5,6,7,8,9,10,11,12,null,0,1,2,3,4,5,6,7,8,9,10,11,12]
["American Samoa",null,null,null,null,"Published unit costs","Philadelphia-Camden-Vineyard (PA-NJ-DE-MD) ",17.77638913234005,null,"Basis for Post Resilence Damages",null,{"$f":"IF(ISNUMBER('Analysis Results'!B20),1/'Analysis Results'!B20,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F20),1/'Analysis Results'!F20,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B165),RANK('Damage Info'!B165,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C165),RANK('Damage Info'!C165,'Damage Info'!$C$161:$C$172),\"\")","v":""},"",{"$f":"IF('Damage Info'!C165=0,TRUE,IF(ISNUMBER(P6),P5>P6,\"\"))","v":""},null,"Warnings",{"$f":"HLOOKUP(U2,AL4:AX18,15)","v":"None"},"Known Event R.I.s (Q2)",0,"Insufficient number of historic damage events of known RIs to conduct a valid analysis!","Insufficient number of historic damage events of known RIs to conduct a valid analysis!","Insufficient number of historic damage events of known RIs to conduct a valid analysis!","None","None","None","None","None","None","None","None","None","None",0,"None","None","None","None","None","None","None","None","None","None","None","None","None",0,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Three historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. ","Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "]
["Arizona",null,null,null,null,null,"Atlanta-Sandy Springs-Gainesville (GA-AL) ",17.386231847771658,null,"\"Project Effectiveness\" as entered above",null,{"$f":"IF(ISNUMBER('Analysis Results'!B21),1/'Analysis Results'!B21,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F21),1/'Analysis Results'!F21,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B166),RANK('Damage Info'!B166,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C166),RANK('Damage Info'!C166,'Damage Info'!$C$161:$C$172),\"\")","v":""},"",{"$f":"IF('Damage Info'!C166=0,TRUE,IF(ISNUMBER(P7),P6>P7,\"\"))","v":""},null,"Conclusions",{"$f":"HLOOKUP(U2,AZ4:BL18,15)","v":"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. "},null,1,"Number of RIs exceeds number of damage events!","None","None","None","None","None","None","None","None","None","None","None","None",1,"None","With only one damage event of known RI, the analysis will be of limited accuracy and is not recommended!","With two historic damage events, both RIs must be determined or the unknown RI event must be eliminated!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!","The known RI event must have the highest Total Historic Damages to conduct a valid analysis!",1,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A valid analysis may be conducted using one damage event of known RI, but other expected damage events should be added if possible to improve the accuracy of the analysis.","A valid analysis may be conducted using one damage event of known RI, but other expected damage events should be added if possible to improve the accuracy of the analysis.","Two historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis.","Three or more historic events of unknown RIs and one historic event of known RI (see WARNING) is sufficient to conduct a valid analysis."]
["Arkansas",null,null,null,null,null,"Dallas-Fort Worth (TX) ",15.258880668257758,null,"\"User-entered Data\" (new Tab opens up)",null,{"$f":"IF(ISNUMBER('Analysis Results'!B22),1/'Analysis Results'!B22,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F22),1/'Analysis Results'!F22,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B167),RANK('Damage Info'!B167,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C167),RANK('Damage Info'!C167,'Damage Info'!$C$161:$C$172),\"\")","v":""},{"$f":"IF(ISNUMBER(Values!O8),O7>Values!O8,\"\")","v":""},{"$f":"IF('Damage Info'!C167=0,TRUE,IF(ISNUMBER(Values!P8),P7>Values!P8,\"\"))","v":""},null,null,null,null,2,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","None","None","None","None","None","None","None","None","None","None","None",2,"None","None","None","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!","The known RI events must have the highest Total Historic Damages to conduct a valid analysis!",2,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["California",null,null,null,null,null,"Houston-Baytown-Huntsville (TX) ",17.699504447268104,null,"cell link",null,{"$f":"IF(ISNUMBER('Analysis Results'!B23),1/'Analysis Results'!B23,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F23),1/'Analysis Results'!F23,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B168),RANK('Damage Info'!B168,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C168),RANK('Damage Info'!C168,'Damage Info'!$C$161:$C$172),\"\")","v":""},{"$f":"IF(ISNUMBER(O9),O8>O9,\"\")","v":""},{"$f":"IF('Damage Info'!C168=0,TRUE,IF(ISNUMBER(P9),P8>P9,\"\"))","v":""},null,null,null,null,3,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all three historic damage event RIs are known, then input these events under Expected Damages!","None","None","None","None","None","None","None","None","None",3,"None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",3,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Colorado",null,null,null,null,null,"Miami-Fort Lauderdale-Pompano Beach (FL) ",15.033067552602438,null,1,null,{"$f":"IF(ISNUMBER('Analysis Results'!B24),1/'Analysis Results'!B24,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F24),1/'Analysis Results'!F24,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B169),RANK('Damage Info'!B169,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C169),RANK('Damage Info'!C169,'Damage Info'!$C$161:$C$172),\"\")","v":""},{"$f":"IF(ISNUMBER(O10),O9>O10,\"\")","v":""},{"$f":"IF('Damage Info'!C169=0,TRUE,IF(ISNUMBER(P10),P9>P10,\"\"))","v":""},null,null,null,null,4,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all four historic damage event RIs are known, then input these events under Expected Damages!","None","None","None","None","None","None","None","None",4,"None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",4,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Connecticut",null,null,null,null,null,"Washington-Baltimore-NOVA (DC-MD-VA-WV) ",19.523828306264502,null,null,null,{"$f":"IF(ISNUMBER('Analysis Results'!B25),1/'Analysis Results'!B25,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F25),1/'Analysis Results'!F25,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B170),RANK('Damage Info'!B170,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C170),RANK('Damage Info'!C170,'Damage Info'!$C$161:$C$172),\"\")","v":""},{"$f":"IF(ISNUMBER(O11),O10>O11,\"\")","v":""},{"$f":"IF('Damage Info'!C170=0,TRUE,IF(ISNUMBER(P11),P10>P11,\"\"))","v":""},null,null,null,null,5,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all five historic damage event RIs are known, then input these events under Expected Damages!","None","None","None","None","None","None","None",5,"None","None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",5,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Delaware",null,null,null,null,null,"Chicago-Naperville-Michigan City (IL-IN-WI) ",18.238689189189191,null,null,null,{"$f":"IF(ISNUMBER('Analysis Results'!B26),1/'Analysis Results'!B26,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F26),1/'Analysis Results'!F26,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B171),RANK('Damage Info'!B171,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C171),RANK('Damage Info'!C171,'Damage Info'!$C$161:$C$172),\"\")","v":""},{"$f":"IF(ISNUMBER(O12),O11>O12,\"\")","v":""},{"$f":"IF('Damage Info'!C171=0,TRUE,IF(ISNUMBER(P12),P11>P12,\"\"))","v":""},null,null,null,null,6,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all six historic damage event RIs are known, then input these events under Expected Damages!","None","None","None","None","None","None",6,"None","None","None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",6,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["District of Columbia",null,null,null,null,null,"Detroit-Warren-Flint (MI) ",17.229087127845883,null,null,null,{"$f":"IF(ISNUMBER('Analysis Results'!B27),1/'Analysis Results'!B27,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F27),1/'Analysis Results'!F27,0)","v":0},null,{"$f":"IF(ISNUMBER('Damage Info'!B172),RANK('Damage Info'!B172,'Damage Info'!$B$161:$B$172),\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!C172),RANK('Damage Info'!C172,'Damage Info'!$C$161:$C$172),\"\")","v":""},{"$f":"IF(ISNUMBER(#REF!),O12>#REF!,\"\")","v":""},{"$f":"IF('Damage Info'!C172=0,TRUE,IF(ISNUMBER(#REF!),P12>#REF!,\"\"))","v":""},null,null,null,null,7,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all seven historic damage event RIs are known, then input these events under Expected Damages!","None","None","None","None","None",7,"None","None","None","None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",7,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Florida",null,null,null,null,null,"Minneapolis-St. Paul-St. Cloud (MN-WI) ",18.462342023862131,null,null,null,{"$f":"IF(ISNUMBER('Analysis Results'!B28),1/'Analysis Results'!B28,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F28),1/'Analysis Results'!F28,0)","v":0},null,null,null,null,null,null,null,null,null,8,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all eight historic damage event RIs are known, then input these events under Expected Damages!","None","None","None","None",8,"None","None","None","None","None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",8,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Georgia",null,null,null,null,null,"Los Angeles-Long Beach-Riverside (CA) ",18.482710926694327,null,null,null,{"$f":"IF(ISNUMBER('Analysis Results'!B29),1/'Analysis Results'!B29,0)","v":0},{"$f":"IF(ISNUMBER('Analysis Results'!F29),1/'Analysis Results'!F29,0)","v":0},null,null,null,null,null,null,null,null,null,9,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all nine historic damage event RIs are known, then input these events under Expected Damages!","None","None","None",9,"None","None","None","None","None","None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",9,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Guam",null,null,null,null,null,"Phoenix-Mesa-Scottsdale (AZ) ",15.308565834557024,null,null,null,null,null,null,null,null,null,null,null,null,null,null,10,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all ten historic damage event RIs are known, then input these events under Expected Damages!","None","None",10,"None","None","None","None","None","None","None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",10,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Hawaii",null,null,null,null,null,"San Jose-San Francisco-Oakland (CA) ",26.031686046511627,null,null,null,null,null,null,null,null,null,null,null,null,null,null,11,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all eleven historic damage event RIs are known, then input these events under Expected Damages!","None",11,"None","None","None","None","None","None","None","None","None","None","None","None","The number of historic damage events with known RIs is limited to the two events with the highest Total Historic Damages!",11,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Idaho",null,null,null,null,null,"Seattle-Tacoma-Olympia (WA) ",19.391130434782607,null,null,null,null,null,null,null,null,null,null,null,null,null,null,12,"Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","Number of RIs exceeds number of damage events!","If all twelve historic damage event RIs are known, then input these events under Expected Damages!",12,"None","None","None","None","None","None","None","None","None","None","None","None","None",12,"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. ","Two historic events of known RIs is sufficient to conduct a valid analysis.","One historic event of unknown RI and two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis.","Two or more historic events of unknown RIs and up to two historic events of known RIs (see WARNING) is sufficient to conduct a valid analysis."]
["Illinois",null,null,null,null,null,"User-Entered",0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,2)","v":"Insufficient number of historic damage events of known RIs to conduct a valid analysis!"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,3)","v":"Insufficient number of historic damage events of known RIs to conduct a valid analysis!"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,4)","v":"Insufficient number of historic damage events of known RIs to conduct a valid analysis!"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,5)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,6)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,7)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,8)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,9)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,10)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,11)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,12)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,13)","v":"None"},{"$f":"VLOOKUP($U$3,$W$5:$AJ$17,14)","v":"None"},null,{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,2)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,3)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,4)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,5)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,6)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,7)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,8)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,9)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,10)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,11)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,12)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,13)","v":"None"},{"$f":"VLOOKUP($U$3,$AK$5:$AX$17,14)","v":"None"},null,{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,2)","v":"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,3)","v":"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,4)","v":"A minimum of one historic event of known RI or three historic events of unknown RI are needed to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,5)","v":"Three historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,6)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,7)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,8)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,9)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,10)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,11)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,12)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,13)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "},{"$f":"VLOOKUP($U$3,$AY$5:$BL$17,14)","v":"Three or more historic events of unknown RIs is sufficient to conduct a valid analysis. "}]
["Indiana"]
["Iowa"]
["Kansas"]
["Kentucky"]
["Louisiana"]
["Maine"]
["Maryland"]
["Massachusetts"]
["Michigan"]
["Minnesota"]
["Mississippi"]
["Missouri"]
["Montana"]
["Nebraska"]
["Nevada"]
["New Hampshire"]
["New Jersey"]
["New Mexico"]
["New York"]
["North Carolina"]
["North Dakota"]
["Northern Mariana Islands"]
["Ohio"]
["Oklahoma"]
["Oregon"]
["Pennsylvania"]
["Puerto Rico"]
["Rhode Island"]
["South Carolina"]
["South Dakota"]
["Tennessee"]
["Texas"]
["US Virgin Islands"]
["Utah"]
["Vermont"]
["Virginia"]
["Washington"]
["West Virginia"]
["Wisconsin"]
["Wyoming"]

{"sheet":"Status","index":5,"n_rows":3,"n_cols":4,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
["Current Status of the Project:"]
[{"$f":"+'Project Info'!B51","v":"Proposed Project Effectiveness (Years)*:"},null,null,{"$f":"IF(ISNUMBER(Project_Effectiveness),Project_Effectiveness,\"N/A\")","v":"N/A"}]
[{"$f":"+'Analysis Results'!B38:C38","v":{"$error":"#VALUE!"}},null,null,{"$f":"IF(ISNUMBER(BCR),BCR,\"N/A\")","v":"N/A"}]

{"sheet":"Freq Calculations","index":6,"n_rows":164,"n_cols":66,"rows_kept":164,"cut":false,"hidden":true,"merged":[[0,0,0,10],[4,0,4,3],[4,7,4,8],[7,0,7,6],[8,0,8,2],[8,3,8,6]]}
["HISTORICAL DAMAGES FREQUENCY CALCULATOR"]
["This damage Frequency Calculator will assign Recurrence Intervals to historical damages. Both events with known and unknown recurrence interval may be used.  "]
["All damage values are inflated to the Year of Analysis. Adjustments for high outliers and known frequency events are applied if needed."]
["Project \nInformation:"]
["INPUT",null,null,null,null,null,null,"Results"]
["Analysis Year\n(4 digit year)","Year Built \n(4 digit year)","Analysis \nDuration\n(years)","Selected\nAnalysis Duartion\n(years)",null,null,null,"Upper Limit\nfor Inflated \nDamage \nOutlier","Adjustment of Tr for outlier applied?",null,null,null,null,null,null,null,null,null,"Log Normal fit to data then adjusted to Total Probability",null,null,null,null,null,null,null,"for graph",null,"for LN graph"]
[{"$f":"Analysis_Year","v":0},{"$f":"Year_Built","v":0},{"$f":"Analysis_Duration","v":""},{"$f":"Selected_Analysis_Duration","v":""},null,null,null,{"$f":"IF(ISNUMBER($BA$32),$BA$32,\"\")","v":""},{"$f":"AT35","v":""},null,null,null,null,null,null,null,null,null,"mean ln","st dev ln","if only processed","total P ",null,null,null,null,"If more than 9 percent of the annual peaks equal zero, then the 1.1-year flood equals zero;"]
["Historical Damage Data",null,null,null,null,null,null,"Recurrence\nInterval (Tr)","Damages",null,null,null,null,null,null,null,null,null,{"$f":"AVERAGE(S10:S23)","v":{"$error":"#DIV/0!"}},{"$f":"STDEV(S10:S23)","v":{"$error":"#DIV/0!"}},"damage data is used","Adjustment",null,null,null,null,"if more than 1/3 of the peaks equal zero, then the 1.5-year flood equals zero.)"]
["Damages with Unknown Recurrence Interval",null,null,"Damages with Known Recurrence Interval",null,null,null,"(years)","(dollars)",null,null,null,null,null,null,null,null,null,"ln damage","LNormal ","exceedance P","Tr (yrs)",null,null,null,null,1,1,1,1]
["Damage Year",null,"Damages inflated to analysis Year","Damage \nYear",null,"Known Recurrence Interval","Damages inflated to analysis Year",{"$f":"IF(ISNUMBER(BM35),ROUND(BM35,1),\"\")","v":""},{"$f":"BN35","v":""},null,null,null,null,null,null,null,null,null,{"$f":"+IF(ISNUMBER(J35),LN(J35),\"\")","v":""},{"$f":"IF(ISNUMBER(J35),LOGNORMDIST(J35,$S$8,$T$8),\"\")","v":""},{"$f":"IF(ISNUMBER(J35),(1-(($D$31/$D$29)+$D$32*T10))*($D$29/($D$29+1)),\"\")","v":""},{"$f":"IF(ISNUMBER(U10),1/U10,\"\")","v":""},null,null,null,null,{"$f":"+IF(ISNUMBER(H10),H10,AA9)","v":1},1,{"$f":"+IF(ISNUMBER(J35),J35,AC9)","v":1},{"$f":"+IF(ISNUMBER(V10),V10,AC9)","v":1}]
["(4 digit year)",null,"(dollars)","(4 digit year)",null,"(years)","(dollars)","",{"$f":"BN36","v":""},null,null,null,null,null,null,null,null,null,{"$f":"+IF(ISNUMBER(J36),LN(J36),\"\")","v":""},{"$f":"IF(ISNUMBER(J36),LOGNORMDIST(J36,$S$8,$T$8),\"\")","v":""},{"$f":"IF(ISNUMBER(J36),(1-(($D$31/$D$29)+$D$32*T11))*($D$29/($D$29+1)),\"\")","v":""},{"$f":"IF(ISNUMBER(U11),1/U11,\"\")","v":""},null,null,null,null,1,1,{"$f":"+IF(ISNUMBER(J36),J36,AC10)","v":1},{"$f":"+IF(ISNUMBER(V11),V11,AD10)","v":1}]
[{"$f":"IF(ISNUMBER('Damage Info'!B129),'Damage Info'!B129,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C129),'Damage Info'!C129,\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!B146),'Damage Info'!B146,\"\")","v":""},null,{"$f":"IF(AND(ISNUMBER('Damage Info'!B146),ISNUMBER('Damage Info'!C146)),'Damage Info'!C146,\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!F157),'Damage Info'!F157,\"\")","v":""},"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B130),'Damage Info'!B130,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C130),'Damage Info'!C130,\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!B147),'Damage Info'!B147,\"\")","v":""},null,{"$f":"IF(AND(ISNUMBER('Damage Info'!B147),ISNUMBER('Damage Info'!C147)),'Damage Info'!C147,\"\")","v":""},{"$f":"IF(ISNUMBER('Damage Info'!F158),'Damage Info'!F158,\"\")","v":""},"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B131),'Damage Info'!B131,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C131),'Damage Info'!C131,\"\")","v":""},"User Notes:",null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B132),'Damage Info'!B132,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C132),'Damage Info'!C132,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B133),'Damage Info'!B133,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C133),'Damage Info'!C133,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B134),'Damage Info'!B134,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C134),'Damage Info'!C134,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B135),'Damage Info'!B135,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C135),'Damage Info'!C135,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B136),'Damage Info'!B136,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C136),'Damage Info'!C136,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B137),'Damage Info'!B137,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C137),'Damage Info'!C137,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","","",null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B138),'Damage Info'!B138,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C138),'Damage Info'!C138,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","",{"$f":"IF(ISNUMBER(U21),1/U21,\"\")","v":""},null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B139),'Damage Info'!B139,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C139),'Damage Info'!C139,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","",{"$f":"IF(ISNUMBER(U22),1/U22,\"\")","v":""},null,null,null,null,1,1,1,1]
[{"$f":"IF(ISNUMBER('Damage Info'!B140),'Damage Info'!B140,\"\")","v":""},null,{"$f":"IF(ISNUMBER('Damage Info'!C140),'Damage Info'!C140,\"\")","v":""},null,null,null,null,"","",null,null,null,null,null,null,null,null,null,"","","",null,null,null,null,null,1,1,1,1]
["Warning/Error",{"$f":"IF(ISNUMBER(MODE($A$12:$A$23,$D$12:$D$13)),\"You have repeated a damage year.  Enter a given calendar year only once with the total damages for that year!\",\"\")","v":""}]
["Messages:",{"$f":"IF(ISNUMBER(D12),IF(MIN($G$12:$G$13)<MAX($C$12:$C$23),\"Inflated damage(s) with known RI must be larger than all inflated damages with unknown RI!\",\"\"),\"\")","v":""}]
[]
[]
["Calcs"]
["Total No of analysis years ",null,null,{"$f":"IF(ISNUMBER(D7),D7,C7)","v":""},null,"min RI criteria for historic events"]
["Total No of years with damage",null,null,{"$f":"DCOUNT(C10:C23,C10,J31:J32)","v":0},null,"(years)",null,null,null,"criteria","Year Validation",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Adjusted Tr changed from FEMA's version"]
["Total No of years without damage",null,null,{"$f":"D29-D30","v":{"$error":"#VALUE!"}},null,{"$f":"+D29+1","v":{"$error":"#VALUE!"}},null,null,null,"Damages inflated to analysis Year",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Mean=",{"$f":"AVERAGE(C35:C46)","v":{"$error":"#DIV/0!"}},"z(1-p): ",{"$f":"-NORMSINV(1-0.9)","v":1.2815515655446006},"a: ",{"$f":"1-AW32^2/(2*($D$30-1))","v":1},"k1: ",{"$f":"(AW31+SQRT(AW31^2-AY31*AY32))/AY31","v":{"$error":"#DIV/0!"}},null,"H, largest Tr=",{"$f":"MAX($BE$35:$BE$36)","v":0}]
["Annual P having a damage ",null,null,{"$f":"D30/D29","v":{"$error":"#VALUE!"}},null,null,null,null,null,">0","\">B3",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Probability Theorem adjusted for n+1",null,null,null,"St. Dev=",{"$f":"STDEV(C35:C46)","v":{"$error":"#DIV/0!"}},"z(1-g): ",{"$f":"-NORMSINV(1-0.5)","v":0},"b: ",{"$f":"AW31^2-AW32^2/$D$30","v":{"$error":"#DIV/0!"}},"H-outlier limit=",{"$f":"+AU31+BA31*AU32","v":{"$error":"#DIV/0!"}},null,"Z, count=",{"$f":"COUNT($BF$35:$BF$36)","v":0},"Weight=",{"$f":"(BD31-$BD$32)/($D$30+$D$31)","v":{"$error":"#VALUE!"}},"changed from FEMA's version"]
["Damage Inflation and Initial Ranking of Inflated Damages",null,null,null,null,null,null,null,"Initial sortin in Ascending Order",null,null,null,null,null,"First Adjustment for grouping",null,null,null,null,null,"Second Adjustment for grouping",null,null,null,null,null,null,"Third Adjustment for grouping",null,null,null,null,null,null,"Fourth Adjustment for grouping",null,null,null,null,null,null,"Adjustment to Conditional Probability",null,null,null,"Adjustment for outlier (only the largest inflated damage)",null,null,null,null,"Sorting in Ascenting Tr order",null,null,null,"Adjustment to Damages with Known Recurrence Interval",null,null,null,null,null,null,null,null,"Sorting in Ascenting Tr order"]
["order \nentered","Year","Inflated\nTotal\ndamage","Rank in\nlist",null,null,null,"Total\ndamage","Rank","Ranked\nDamage","P\nTotal period","Tr\nTotal period","P\ndamage only","Tr\ndamage only","Ratio of\nsequential\ndamages (%)","log-Avg. rank\nif diff<10%","log-Avg. damage\nif diff<10%","Rank in\nlist",null,null,"Rank after First Grouping","Ratio of\nsequential\ndamages (%)","log-Avg. rank\nif diff<10%","log-Avg. damage\nif diff<10%","Rank in\nlist",null,null,"Rank after Second\nGrouping","Ratio of\nsequential\ndamages (%)","log-Avg. rank\nif diff<10%","log-Avg. damage\nif diff<10%","Rank in\nlist",null,null,"Rank after Third Grouping","Ratio of\nsequential\ndamages (%)","log-Avg. rank\nif diff<10%","log-Avg. damage\nif diff<10%","Rank in\nlist",null,null,"P\ndamage only","Tr\ndamage only","P\nAdjusted to\nconditional P","Tr adjusted to C.P.\n& grouping","Is max \ndamage\nan outlier?","Exp Damage by LN","FTEST",null,"Adjusted Tr","Rank of\nordered\nTrs","Desired\nAscenting\nTr order","ascending\nTrs\n","ascending\ndamages\n","Damage Year\n","Total\ndamage","Assigned \nRecurrence Interval","Inflated damage","new rank","Adjusted Tr","Rank in group","Desired\nAscenting\nTr order","All damages\nnot sorted","Rank in group","ascending\nTrs\n","ascending\ndamages\n"]
[1,{"$f":"IF(A12>0,A12,\"\")","v":""},{"$f":"IF(ISNUMBER(C12),C12,\"\")","v":""},{"$f":"IF(ISNUMBER(C35),RANK(C35,$C$35:$C$46,0),\"\")","v":""},null,null,null,{"$f":"C35","v":""},1,{"$f":"IF(ISNUMBER(C35),INDEX($C$35:$C$46,MATCH(I35,$D$35:$D$46,0),1),\"\")","v":""},{"$f":"IF(ISNUMBER(J35),I35/($D$29+1),\"\")","v":""},{"$f":"IF(ISNUMBER(K35),1/K35,\"\")","v":""},{"$f":"IF(ISNUMBER(J35),I35/($D$30+1),\"\")","v":""},{"$f":"IF(ISNUMBER(M35),1/M35,\"\")","v":""},{"$f":"IF(ISNUMBER(J36),100*(J35-J36)/J35,\"\")","v":""},{"$f":"IF(O35<10,EXP(0.5*(LN(I35)+LN(I36))),I35)","v":1},{"$f":"IF(ISNUMBER(P35),IF(P35>I35,EXP(0.5*(LN(J35)+LN(J36))),J35),\"\")","v":""},{"$f":"IF(ISNUMBER(Q35),RANK(Q35,$Q$35:$Q$46,0),\"\")","v":""},{"$f":"+Q35","v":""},{"$f":"+INDEX($Q$35:$Q$46,MATCH(I35,$R$35:$R$46,0),1)","v":{"$error":"#N/A"}},{"$f":"+INDEX($P$35:$P$46,MATCH(I35,$R$35:$R$46,0),1)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(T36),100*(T35-T36)/T35,\"\")","v":""},{"$f":"IF(V35<10,EXP(0.5*(LN(U35)+LN(U36))),U35)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(W35),IF(W35>U35,EXP(0.5*(LN(T35)+LN(T36))),T35),\"\")","v":""},{"$f":"IF(ISNUMBER(X35),RANK(X35,$X$35:$X$46,0),\"\")","v":""},{"$f":"+X35","v":""},{"$f":"+INDEX($X$35:$X$46,MATCH(I35,$Y$35:$Y$46,0),1)","v":{"$error":"#N/A"}},{"$f":"+INDEX($W$35:$W$46,MATCH(I35,$Y$35:$Y$46,0),1)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AA36),100*(AA35-AA36)/AA35,\"\")","v":""},{"$f":"IF(AC35<10,EXP(0.5*(LN(AB35)+LN(AB36))),AB35)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AD35),IF(AD35>AB35,EXP(0.5*(LN(AA35)+LN(AA36))),AA35),\"\")","v":""},{"$f":"IF(ISNUMBER(AE35),RANK(AE35,$AE$35:$AE$46,0),\"\")","v":""},{"$f":"+AE35","v":""},{"$f":"+INDEX($AE$35:$AE$46,MATCH(I35,$AF$35:$AF$46,0),1)","v":{"$error":"#N/A"}},{"$f":"+INDEX($AD$35:$AD$46,MATCH(I35,$AF$35:$AF$46,0),1)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AH36),100*(AH35-AH36)/AH35,\"\")","v":""},{"$f":"IF(AJ35<10,EXP(0.5*(LN(AI35)+LN(AI36))),AI35)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AK35),IF(AK35>AI35,EXP(0.5*(LN(AH35)+LN(AH36))),AH35),\"\")","v":""},{"$f":"IF(ISNUMBER(AL35),RANK(AL35,$AL$35:$AL$46,0),\"\")","v":""},{"$f":"+AL35","v":""},{"$f":"+INDEX($AL$35:$AL$46,MATCH(I35,$AM$35:$AM$46,0),1)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AL35),AK35/($D$30),\"\")","v":""},{"$f":"IF(ISNUMBER(AP35),1/AP35,\"\")","v":""},{"$f":"IF(ISNUMBER(AL35),(1-(($D$31/$D$29)+$D$32*(1-AP35)))*($D$29/($D$29+1)),\"\")","v":""},{"$f":"IF(ISNUMBER(AR35),1/AR35,\"\")","v":""},{"$f":"IF(ISNUMBER($AO$35),IF($AO$35>$BA$32,\"Yes\",\"No\"),\"\")","v":""},{"$f":"IF(ISNUMBER(J35),LOGINV(1-M35,$S$8,$T$8),\"\")","v":""},{"$f":"FTEST($J$35:$J$46,$AU$35:$AU$46)","v":{"$error":"#DIV/0!"}},null,{"$f":"IF(COUNT(C35:C46)=1,AS35,IF($AO$35>$BA$32,MAX(AS35,(1-$AV$35)*AS35+$AV$35*MAX($V$10:$V$23)),AS35))","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AX35),RANK(AX35,$AX$35:$AX$46,1),\"\")","v":""},1,{"$f":"IF(AZ35<=MAX($AY$35:$AY$46),INDEX($AX$35:$AY$46,MATCH(AZ35,$AY$35:$AY$46,0),1),\"\")","v":""},{"$f":"IF(AZ35<=MAX($AY$35:$AY$46),INDEX($AN$35:$AY$46,MATCH(BA35,$AX$35:$AX$46,0),1),\"\")","v":""},{"$f":"+IF(ISNUMBER(D12),D12,\"\")","v":""},"","","",{"$f":"IF(ISNUMBER(BF35),RANK(BF35,$BF$35:$BF$36),\"\")","v":""},{"$f":"IF(BG35=1,MAX($BE$35:$BE$36),IF(ISNUMBER(BF35),MIN($BE$35:$BE$36),\"\"))","v":""},{"$f":"IF(ISNUMBER(BG35),RANK(BG35,$BG$35:$BG$48),\"\")","v":""},1,{"$f":"+BF35","v":""},{"$f":"IF(ISNUMBER(BG35),RANK(BG35,$BG$35:$BG$48),\"\")","v":""},{"$f":"IF(BJ35<=MAX($BI$35:$BI$48),INDEX($BH$35:$BI$48,MATCH(BJ35,$BI$35:$BI$48,0),1),\"\")","v":""},{"$f":"IF(BJ35<=MAX($BL$35:$BL$48),INDEX($BK$35:$BL$48,MATCH(BJ35,$BL$35:$BL$48,0),1),\"\")","v":""}]
[2,"",{"$f":"IF(ISNUMBER(C13),C13,\"\")","v":""},"",null,null,null,{"$f":"C36","v":""},2,"","",{"$f":"IF(ISNUMBER(K36),1/K36,\"\")","v":""},"","",{"$f":"IF(ISNUMBER(J37),100*(J36-J37)/J36,\"\")","v":""},{"$f":"IF(O36<10,EXP(0.5*(LN(I36)+LN(I37))),IF(O35<10,\"\",I36))","v":2},{"$f":"IF(ISNUMBER(P36),IF(P36>I36,EXP(0.5*(LN(J36)+LN(J37))),J36),\"\")","v":""},"",{"$f":"+Q36","v":""},{"$error":"#N/A"},{"$error":"#N/A"},{"$f":"IF(ISNUMBER(T37),100*(T36-T37)/T36,\"\")","v":""},{"$f":"IF(V36<10,EXP(0.5*(LN(U36)+LN(U37))),IF(V35<10,\"\",U36))","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(W36),IF(W36>U36,EXP(0.5*(LN(T36)+LN(T37))),T36),\"\")","v":""},"",{"$f":"+X36","v":""},{"$error":"#N/A"},{"$error":"#N/A"},{"$f":"IF(ISNUMBER(AA37),100*(AA36-AA37)/AA36,\"\")","v":""},{"$f":"IF(AC36<10,EXP(0.5*(LN(AB36)+LN(AB37))),IF(AC35<10,\"\",AB36))","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AD36),IF(AD36>AB36,EXP(0.5*(LN(AA36)+LN(AA37))),AA36),\"\")","v":""},"",{"$f":"+AE36","v":""},{"$error":"#N/A"},{"$f":"+INDEX($AD$35:$AD$46,MATCH(I36,$AF$35:$AF$46,0),1)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AH37),100*(AH36-AH37)/AH36,\"\")","v":""},{"$f":"IF(AJ36<10,EXP(0.5*(LN(AI36)+LN(AI37))),IF(AJ35<10,\"\",AI36))","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AK36),IF(AK36>AI36,EXP(0.5*(LN(AH36)+LN(AH37))),AH36),\"\")","v":""},{"$f":"IF(ISNUMBER(AL36),RANK(AL36,$AL$35:$AL$46,0),\"\")","v":""},{"$f":"+AL36","v":""},{"$f":"+INDEX($AL$35:$AL$46,MATCH(I36,$AM$35:$AM$46,0),1)","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AL36),AK36/($D$30),\"\")","v":""},"",{"$f":"IF(ISNUMBER(AL36),(1-(($D$31/$D$29)+$D$32*(1-AP36)))*($D$29/($D$29+1)),\"\")","v":""},{"$f":"IF(ISNUMBER(AR36),1/AR36,\"\")","v":""},null,"",null,null,{"$f":"+AS36","v":""},{"$f":"IF(ISNUMBER(AX36),RANK(AX36,$AX$35:$AX$46,1),\"\")","v":""},2,{"$f":"IF(AZ36<=MAX($AY$35:$AY$46),INDEX($AX$35:$AY$46,MATCH(AZ36,$AY$35:$AY$46,0),1),\"\")","v":""},{"$f":"IF(AZ36<=MAX($AY$35:$AY$46),INDEX($AN$35:$AY$46,MATCH(BA36,$AX$35:$AX$46,0),1),\"\")","v":""},"","","","",{"$f":"IF(ISNUMBER(BF36),RANK(BF36,$BF$35:$BF$36),\"\")","v":""},{"$f":"IF(BG36=1,MAX($BE$35:$BE$36),IF(ISNUMBER(BF36),MIN($BE$35:$BE$36),\"\"))","v":""},"",2,{"$f":"+BF36","v":""},{"$f":"IF(ISNUMBER(BG36),RANK(BG36,$BG$35:$BG$48),\"\")","v":""},{"$f":"IF(BJ36<=MAX($BI$35:$BI$48),INDEX($BH$35:$BI$48,MATCH(BJ36,$BI$35:$BI$48,0),1),\"\")","v":""},{"$f":"IF(BJ36<=MAX($BL$35:$BL$48),INDEX($BK$35:$BL$48,MATCH(BJ36,$BL$35:$BL$48,0),1),\"\")","v":""}]
[3,"","","",null,null,null,"",3,"","","","","","",{"$f":"IF(O37<10,EXP(0.5*(LN(I37)+LN(I38))),IF(O36<10,\"\",I37))","v":3},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$f":"IF(V37<10,EXP(0.5*(LN(U37)+LN(U38))),IF(V36<10,\"\",U37))","v":{"$error":"#N/A"}},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$f":"IF(AC37<10,EXP(0.5*(LN(AB37)+LN(AB38))),IF(AC36<10,\"\",AB37))","v":{"$error":"#N/A"}},"","","",{"$error":"#N/A"},{"$f":"+INDEX($AD$35:$AD$46,MATCH(I37,$AF$35:$AF$46,0),1)","v":{"$error":"#N/A"}},"",{"$f":"IF(AJ37<10,EXP(0.5*(LN(AI37)+LN(AI38))),IF(AJ36<10,\"\",AI37))","v":{"$error":"#N/A"}},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,{"$f":"+AS37","v":""},"",3,"","",null,null,"next",{"$f":"IF(ISNUMBER(BA35),IF(RANK(BA35,$BA$35:$BA$46,0)=1,IF($AX$35>$AS$35,$AS$35/$AX$35,RANK(BA35,$BA$35:$BA$46,0)),RANK(BA35,$BA$35:$BA$46,0)),\"\")","v":""},{"$f":"IF(ISNUMBER(BA35),IF(ISNUMBER($BF$35),IF(ISNUMBER(RANK(BA35,$BA$35:$BA$46,0)),$BF$32*(BF37+$BD$32)-($BF$32-1)*($BD$32+0.5),\"\"),RANK(BA35,$BA$35:$BA$46,0)),\"\")","v":""},{"$f":"IF(ISNUMBER($BF$35),IF(ISNUMBER(BG37),($BD$31+1)/BG37,\"\"),BA35)","v":""},"",3,{"$f":"+BB35","v":""},"","",""]
[4,"","","",null,null,null,"",4,"","","","","","",4,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",4,"","",null,null,"column",{"$f":"IF(ISNUMBER(BA36),IF(RANK(BA36,$BA$35:$BA$46,0)=1,IF($AX$35>$AS$35,$AS$35/$AX$35,RANK(BA36,$BA$35:$BA$46,0)),RANK(BA36,$BA$35:$BA$46,0)),\"\")","v":""},{"$f":"IF(ISNUMBER(BA36),IF(ISNUMBER($BF$35),IF(ISNUMBER(RANK(BA36,$BA$35:$BA$46,0)),$BF$32*(BF38+$BD$32)-($BF$32-1)*($BD$32+0.5),\"\"),RANK(BA36,$BA$35:$BA$46,0)),\"\")","v":""},{"$f":"IF(ISNUMBER($BF$35),IF(ISNUMBER(BG38),($BD$31+1)/BG38,\"\"),BA36)","v":""},"",4,{"$f":"+BB36","v":""},"","",""]
[5,"","","",null,null,null,"",5,"","","","","","",5,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",5,"","",null,null,"is revised","","","","",5,"","","",""]
[6,"","","",null,null,null,"",6,"","","","","","",6,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",6,"","",null,null,"ranks if","","","","",6,"","","",""]
[7,"","","",null,null,null,"",7,"","","","","","",7,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",7,"","",null,null,"outlier","","","","",7,"","","",""]
[8,"","","",null,null,null,"",8,"","","","","","",8,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",8,"","",null,null,"adjustment","","","","",8,"","","",""]
[9,"","","",null,null,null,"",9,"","","","","","",9,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",9,"","",null,null,"has ","","","","",9,"","","",""]
[10,"","","",null,null,null,"",10,"","","","","","",10,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",10,"","",null,null,"happened","","","","",10,"","","",""]
[11,"","","",null,null,null,"",11,"","","","","","",11,"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},{"$error":"#N/A"},"",{"$error":"#N/A"},"","","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",11,"","",null,null,null,"","","","",11,"","","",""]
[12,"","","",null,null,null,"",12,"","","","","",{"$f":"IF(ISNUMBER(#REF!),100*(J46-#REF!)/J46,\"\")","v":""},{"$f":"IF(O46<10,EXP(0.5*(LN(I46)+LN(#REF!))),IF(O45<10,\"\",I46))","v":12},{"$f":"IF(ISNUMBER(P46),IF(P46>I46,EXP(0.5*(LN(J46)+LN(#REF!))),J46),\"\")","v":""},"","",{"$error":"#N/A"},{"$error":"#N/A"},{"$f":"IF(ISNUMBER(#REF!),100*(T46-#REF!)/T46,\"\")","v":""},{"$f":"IF(V46<10,EXP(0.5*(LN(U46)+LN(#REF!))),IF(V45<10,\"\",U46))","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(W46),IF(W46>U46,EXP(0.5*(LN(T46)+LN(#REF!))),T46),\"\")","v":""},"","",{"$error":"#N/A"},{"$error":"#N/A"},{"$f":"IF(ISNUMBER(#REF!),100*(AA46-#REF!)/AA46,\"\")","v":""},{"$f":"IF(AC46<10,EXP(0.5*(LN(AB46)+LN(#REF!))),IF(AC45<10,\"\",AB46))","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AD46),IF(AD46>AB46,EXP(0.5*(LN(AA46)+LN(#REF!))),AA46),\"\")","v":""},"","",{"$error":"#N/A"},{"$error":"#N/A"},{"$f":"IF(ISNUMBER(#REF!),100*(AH46-#REF!)/AH46,\"\")","v":""},{"$f":"IF(AJ46<10,EXP(0.5*(LN(AI46)+LN(#REF!))),IF(AJ45<10,\"\",AI46))","v":{"$error":"#N/A"}},{"$f":"IF(ISNUMBER(AK46),IF(AK46>AI46,EXP(0.5*(LN(AH46)+LN(#REF!))),AH46),\"\")","v":""},"","",{"$error":"#N/A"},"","","","",null,"",null,null,"","",12,"","",null,null,null,"","","","",12,"","","",""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","","",13,"","","",""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","","",14,"","","",""]
[]
[]
[null,null,null,null,null,"ENR used for years after 1908"]
[]
[null,null,null,null,"Year","Construction Cost (ENR)"]
[null,null,null,null,1908,1.1990926453590789E-2]
[null,null,null,null,1909,1.1249219662647029E-2]
[null,null,null,null,1910,1.1867308655100162E-2]
[null,null,null,null,1911,1.1496455259628282E-2]
[null,null,null,null,1912,1.1249219662647029E-2]
[null,null,null,null,1913,1.2361779849062669E-2]
[null,null,null,null,1914,1.1001984065665774E-2]
[null,null,null,null,1915,1.1496455259628282E-2]
[null,null,null,null,1916,1.6070313803781468E-2]
[null,null,null,null,1917,2.237482152680343E-2]
[null,null,null,null,1918,2.3363763914728441E-2]
[null,null,null,null,1919,2.4476324101144083E-2]
[null,null,null,null,1920,3.1028067421147297E-2]
[null,null,null,null,1921,2.4970795295106589E-2]
[null,null,null,null,1922,2.1509496937369043E-2]
[null,null,null,null,1923,2.6454208876994109E-2]
[null,null,null,null,1924,2.6577826675484736E-2]
[null,null,null,null,1925,2.5588884287559722E-2]
[null,null,null,null,1926,2.5712502086050349E-2]
[null,null,null,null,1927,2.5465266489069098E-2]
[null,null,null,null,1928,2.5588884287559722E-2]
[null,null,null,null,1929,2.5588884287559722E-2]
[null,null,null,null,1930,2.5094413093597216E-2]
[null,null,null,null,1931,2.237482152680343E-2]
[null,null,null,null,1932,1.940799436302839E-2]
[null,null,null,null,1933,2.1015025743406537E-2]
[null,null,null,null,1934,2.4476324101144083E-2]
[null,null,null,null,1935,2.4229088504162829E-2]
[null,null,null,null,1936,2.5465266489069098E-2]
[null,null,null,null,1937,2.9050182645297271E-2]
[null,null,null,null,1938,2.9173800443787899E-2]
[null,null,null,null,1939,2.9173800443787899E-2]
[null,null,null,null,1940,2.9915507234731659E-2]
[null,null,null,null,1941,3.1893392010581681E-2]
[null,null,null,null,1942,3.4118512383412965E-2]
[null,null,null,null,1943,3.5849161562281739E-2]
[null,null,null,null,1944,3.6961721748697378E-2]
[null,null,null,null,1945,3.8074281935113016E-2]
[null,null,null,null,1946,4.2771758277756831E-2]
[null,null,null,null,1947,5.1054150776628823E-2]
[null,null,null,null,1948,5.6987805104178904E-2]
[null,null,null,null,1949,5.8965689880028926E-2]
[null,null,null,null,1950,6.3045077230219612E-2]
[null,null,null,null,1951,6.7124464580410284E-2]
[null,null,null,null,1952,7.0338527341166579E-2]
[null,null,null,null,1953,7.417067909437601E-2]
[null,null,null,null,1954,7.763197745211356E-2]
[null,null,null,null,1955,8.1587747003813604E-2]
[null,null,null,null,1956,8.5543516555513663E-2]
[null,null,null,null,1957,8.9499286107213721E-2]
[null,null,null,null,1958,9.3825909054385648E-2]
[null,null,null,null,1959,9.852338539702947E-2]
[null,null,null,null,1960,0.10186106595627639]
[null,null,null,null,1961,0.10470427532156081]
[null,null,null,null,1962,0.10779472028382647]
[null,null,null,null,1963,0.11137963644005464]
[null,null,null,null,1964,0.11570625938722658]
[null,null,null,null,1965,0.12003288233439852]
[null,null,null,null,1966,0.1259665366619486]
[null,null,null,null,1967,0.13276551557893307]
[null,null,null,null,1968,0.14277855725667382]
[null,null,null,null,1969,0.15687098628460527]
[null,null,null,null,1970,0.17071617971555544]
[null,null,null,null,1971,0.19543973941368079]
[null,null,null,null,1972,0.21670200075406856]
[null,null,null,null,1973,0.23425572813973755]
[null,null,null,null,1974,0.2497079529510659]
[null,null,null,null,1975,0.27344257026126623]
[null,null,null,null,1976,0.29680633417599467]
[null,null,null,null,1977,0.31843944891185433]
[null,null,null,null,1978,0.34316300860997967]
[null,null,null,null,1979,0.37122424886735195]
[null,null,null,null,1980,0.40015081371415856]
[null,null,null,null,1981,0.43698891766436532]
[null,null,null,null,1982,0.47283807922664706]
[null,null,null,null,1983,0.50262996866288812]
[null,null,null,null,1984,0.51251939254213819]
[null,null,null,null,1985,0.51857666466817898]
[null,null,null,null,1986,0.53093844451724159]
[null,null,null,null,1987,0.54466002014970116]
[null,null,null,null,1988,0.55862883137914199]
[null,null,null,null,1989,0.57049614003424209]
[null,null,null,null,1990,0.58495942245764543]
[null,null,null,null,1991,0.59769205570217998]
[null,null,null,null,1992,0.61623472547577407]
[null,null,null,null,1993,0.64404873013616504]
[null,null,null,null,1994,0.66852505423730912]
[null,null,null,null,1995,0.67631297554221859]
[null,null,null,null,1996,0.69473202751732199]
[null,null,null,null,1997,0.7201972940063911]
[null,null,null,null,1998,0.73181736706450995]
[null,null,null,null,1999,0.74900024105470708]
[null,null,null,null,2000,0.76902632441018859]
[null,null,null,null,2001,0.78299513563962941]
[null,null,null,null,2002,0.80821316653171726]
[null,null,null,null,2003,0.82757665848728901]
[null,null,null,null,2004,0.87952703830297496]
[null,null,null,null,2005,0.92045565520523642]
[null,null,null,null,2006,0.97505021973063688]
[null,null,null,null,2007,1]
[null,null,null,null,2008,1.0570557948933488]
[null,null,null,null,2009,1.0681813967575051]
[null,null,null,null,2010,1.1066265320880901]
[null,null,null,null,2011,1.1338224477560279]
[null,null,null,null,2012,1.163521623843401]
[null,null,null,null,2013,1.1951368760000001]
[null,null,null,null,2014,1.2282664458028667]
[null,null,null,null,2015,1.2549678902768422]
[null,null,null,null,2016,1.301695418106299,"2008 to 2016 value from ENR website"]
[null,null,null,null,2017,{"$f":"+F162+(F162-F161)","v":1.3484229459357557},"Value assumed same as 2016"]
[null,null,null,null,2018,{"$f":"+F163+(F163-F162)","v":1.3951504737652125},"Value assumed same as 2016"]

{"sheet":"RI Estimator","index":7,"n_rows":36,"n_cols":9,"rows_kept":36,"cut":false,"hidden":true,"merged":[[29,1,29,3],[29,4,29,6],[35,4,35,6],[19,1,19,6],[21,1,21,6],[22,2,22,3],[22,4,22,6],[23,4,27,6],[28,1,28,6],[7,8,7,10],[18,2,18,6],[1,1,1,6],[3,1,3,6],[4,1,4,6],[6,1,6,6],[7,1,7,6],[8,2,8,6],[9,2,9,6],[10,2,10,6],[11,1,11,6],[13,6,17,6],[12,3,12,5]]}
[]
[null,"Hazard Mitigation Cost Effectiveness Tool"]
[]
[null,"Coastal Flooding Recurrence Interval (R.I.) Estimator \nfor Hazard Mitigation Cost Effectiveness Tool"]
[]
[]
[null,"Note: Hover your cursor over cells with red triangles in the top-right corner to show guidance notes for that cell. Guidance notes provide additional information about values that should be entered into cells. For general information about this tool and references please hover over the title box above."]
[null,"Section I - Agency and Project Information",null,null,null,null,null,null,"Tools"]
[null,"Agency:",{"$f":"IF(ISBLANK('Project Info'!$C$38),\"\",'Project Info'!$C$38)","v":""}]
[null,"Project Name:",{"$f":"IF(ISBLANK('Project Info'!$C$43),\"\",'Project Info'!$C$43)","v":""}]
[null,"Analysis Date:",{"$f":"IF(ISBLANK('Project Info'!$G$44),\"\",'Project Info'!$G$44)","v":""}]
[null,"Section II - Coastal Flood Recurrence Interval-Elevations Input"]
[null,"BFE, (ft):"]
[null,"Recurrence Interval (years)","Stillwater Elevation (ft)","Default Flood Elevation Including Waveheight (ft)","User-Entered Flood Elevation Including Waveheight (ft)","Flood Elevation Including Waveheight (ft)"]
[null,10,null,{"$f":"IF(ISNUMBER(C15),IF(ISBLANK($C$13),ROUND(C15*1.4,1),ROUND(C15*($C$13/$C$17),2)),\"\")","v":""},null,{"$f":"IF(ISBLANK(E15),IF(ISBLANK(D15),\"\",D15),ROUND(E15,2))","v":""}]
[null,50,null,{"$f":"IF(ISNUMBER(C16),IF(ISBLANK($C$13),ROUND(C16*1.4,1),ROUND(C16*($C$13/$C$17),2)),\"\")","v":""},null,{"$f":"IF(ISBLANK(E16),IF(ISBLANK(D16),\"\",D16),ROUND(E16,2))","v":""}]
[null,100,null,{"$f":"IF(ISNUMBER(C17),IF(ISBLANK($C$13),ROUND(C17*1.4,1),ROUND(C17*($C$13/$C$17),2)),\"\")","v":""},null,{"$f":"IF(ISBLANK(E17),IF(ISBLANK(D17),\"\",D17),ROUND(E17,2))","v":""}]
[null,500,null,{"$f":"IF(ISNUMBER(C18),IF(ISBLANK($C$13),ROUND(C18*1.4,1),ROUND(C18*($C$13/$C$17),2)),\"\")","v":""},null,{"$f":"IF(ISBLANK(E18),IF(ISBLANK(D18),\"\",D18),ROUND(E18,2))","v":""}]
[null,"Source/\nDocumentation \nof Input Data:"]
[null,"Section III (Optional) – Adjustment of Flood Elevations for Sea Level Rise (SLR)"]
[null,"Project Useful Life (Years):",{"$f":"IF(ISBLANK('Project Info'!Project_Useful_Life),\"\",'Project Info'!Project_Useful_Life)","v":""},"Analysis Year:",{"$f":"IF(ISNUMBER(Analysis_Year),Analysis_Year,\"\")","v":""},"Last SLR Year:",{"$f":"IF(ISBLANK('Project Info'!Project_Useful_Life),\"\",E21+C21)","v":""}]
[null,"Automatic Elevation of SLR (Select nearby NOAA Gauge)"]
[null,"Click to Select nearby\nNOAA Gauge:","This Option Not Used",null,{"$f":"IF(Calculations!$H$8=1,\"Sea Level Rise is not considered for this analysis\", IF(Calculations!$I$12<0,\"Sea Level Rise for the selected station is negative and will not be used in calculations of recurrence interval\",CONCATENATE(\"Linear SLR projected for the next \",Project_Useful__Life__Years,\" years is \",Calculations!$I$11,\" ft, but the rise by equiv. year in \",Calculations!$I$15,\" years after project starts is \",Calculations!$I$16,\" ft and the equivalent rise in 100-year flood elev. is \",Calculations!$I$17,\" ft.\")))","v":"Sea Level Rise is not considered for this analysis"}]
[null,"Recurrence Interval (years)","Equivalent SLR \nto Add (ft)","Equivalent Flood Elevation Including \nWave Height \n& SLR (ft)"]
[null,10,{"$f":"IF(ISNUMBER(D25),D25-F15,\"\")","v":""},{"$f":"IF(Calculations!$H$8=1,\"\",IF(Calculations!$I$12<0,F15,Calculations!F137* (Calculations!D137-LN( LN(B25/(B25-1)) ) * Calculations!E137)))","v":""}]
[null,50,{"$f":"IF(ISNUMBER(D26),D26-F16,\"\")","v":""},{"$f":"IF(Calculations!$H$8=1,\"\",IF(Calculations!$I$12<0,F16,Calculations!F138* (Calculations!D138-LN( LN(B26/(B26-1)) ) * Calculations!E138)))","v":""}]
[null,100,{"$f":"IF(ISNUMBER(D27),D27-F17,\"\")","v":""},{"$f":"IF(Calculations!$H$8=1,\"\",IF(Calculations!$I$12<0,F17,Calculations!F139* (Calculations!D139-LN( LN(B27/(B27-1)) ) * Calculations!E139)))","v":""}]
[null,500,{"$f":"IF(ISNUMBER(D28),D28-F18,\"\")","v":""},{"$f":"IF(Calculations!$H$8=1,\"\",IF(Calculations!$I$12<0,F18,Calculations!F140* (Calculations!D140-LN( LN(B28/(B28-1)) ) * Calculations!E140)))","v":""}]
[null,"Section IV - Recorded Flood Elevations and Estimated RIs without and with SLR"]
[null,"Input Recorded Elevation(s) to see R.I.(s)",null,null,"R.I.s of Current Flood Elevations Considering SLR"]
[null,"Recorded Flood Elevation (ft)*","Estimated R.I.\nWithout SLR\n(Year)","Estimated Equivalent R.I. With SLR\n(Year)","Flood Elevation \nIncluding Wave \nHeight (ft)","RI Without SLR\n(Year)","Estimated Equivalent R.I. With SLR\n(Year)"]
[null,null,{"$f":"IF(ISNUMBER(B32*$F$15),Calculations!D144,\"\")","v":""},{"$f":"IF(COUNTBLANK($D$25:$D$28)=0,IF(ISNUMBER(B32), IF(Calculations!$I$12<0,C32,1/(1-1/(EXP(EXP( (Calculations!B144-(B32/Calculations!E144)) /Calculations!C144))))),\"\"),\"\")","v":""},{"$f":"IF(ISNUMBER(F15),F15,\"\")","v":""},{"$f":"+B15","v":10},{"$f":"IF(ISNUMBER(D25), IF(Calculations!$I$12<0,F32,1/(1-1/(EXP(EXP( (Calculations!D137-(E32/Calculations!F137)) /Calculations!E137))))),\"\")","v":""}]
[null,null,{"$f":"IF(ISNUMBER(B33*$F$15),Calculations!D145,\"\")","v":""},{"$f":"IF(COUNTBLANK($D$25:$D$28)=0,IF(ISNUMBER(B33), IF(Calculations!$I$12<0,C33,1/(1-1/(EXP(EXP( (Calculations!B145-(B33/Calculations!E145)) /Calculations!C145))))),\"\"),\"\")","v":""},{"$f":"IF(ISNUMBER(F16),F16,\"\")","v":""},{"$f":"+B16","v":50},{"$f":"IF(ISNUMBER(D26), IF(Calculations!$I$12<0,F33,1/(1-1/(EXP(EXP( (Calculations!D138-(E33/Calculations!F138)) /Calculations!E138))))),\"\")","v":""}]
[null,null,{"$f":"IF(ISNUMBER(B34*$F$15),Calculations!D146,\"\")","v":""},{"$f":"IF(COUNTBLANK($D$25:$D$28)=0,IF(ISNUMBER(B34), IF(Calculations!$I$12<0,C34,1/(1-1/(EXP(EXP( (Calculations!B146-(B34/Calculations!E146)) /Calculations!C146))))),\"\"),\"\")","v":""},{"$f":"IF(ISNUMBER(F17),F17,\"\")","v":""},{"$f":"+B17","v":100},{"$f":"IF(ISNUMBER(D27), IF(Calculations!$I$12<0,F34,1/(1-1/(EXP(EXP( (Calculations!D139-(E34/Calculations!F139)) /Calculations!E139))))),\"\")","v":""}]
[null,null,{"$f":"IF(ISNUMBER(B35*$F$15),Calculations!D147,\"\")","v":""},{"$f":"IF(COUNTBLANK($D$25:$D$28)=0,IF(ISNUMBER(B35), IF(Calculations!$I$12<0,C35,1/(1-1/(EXP(EXP( (Calculations!B147-(B35/Calculations!E147)) /Calculations!C147))))),\"\"),\"\")","v":""},{"$f":"IF(ISNUMBER(F18),F18,\"\")","v":""},{"$f":"+B18","v":500},{"$f":"IF(ISNUMBER(D28), IF(Calculations!$I$12<0,F35,1/(1-1/(EXP(EXP( (Calculations!D140-(E35/Calculations!F140)) /Calculations!E140))))),\"\")","v":""}]
[null,null,{"$f":"IF(ISNUMBER(B36*$F$15),Calculations!D148,\"\")","v":""},{"$f":"IF(COUNTBLANK($D$25:$D$28)=0,IF(ISNUMBER(B36), IF(Calculations!$I$12<0,C36,1/(1-1/(EXP(EXP( (Calculations!B148-(B36/Calculations!E148)) /Calculations!C148))))),\"\"),\"\")","v":""}]

{"sheet":"Calculations","index":8,"n_rows":157,"n_cols":11,"rows_kept":157,"cut":false,"hidden":true,"merged":[[6,7,6,8]]}
["Station","\"M\"*",null,"Year","Low","Int",null,"E","Base","M ","b"]
["This Option Not Used","don't delete this",null,{"$f":"+$I$3","v":1992},{"$f":"IF(D2<>\"\",(($H$3/1000+$J$3/1000)*(D2-$I$3))*3.28083333333,\"\")","v":0},{"$f":"IF(D2<>\"\",(($H$3/1000+$J$3/1000)*(D2-$I$3)+($K$4*(D2-$I$3)^2))*3.28083333333,\"\")","v":0},null,"mm/yr","Year","mm/yr","coefficients"]
["Nawiliwili, HI",1.5,null,{"$f":"Calculations!$I$3-((Calculations!$I$3/5-INT(Calculations!$I$3/5))*5)+5","v":1995},{"$f":"IF(D3<>\"\",(($H$3/1000+$J$3/1000)*(D3-$I$3))*3.28083333333,\"\")","v":1.4763749999985001E-2},{"$f":"IF(D3<>\"\",(($H$3/1000+$J$3/1000)*(D3-$I$3)+($K$4*(D3-$I$3)^2))*3.28083333333,\"\")","v":1.5563945249984189E-2},null,1.7,1992,{"$f":"INDEX(mm_yr,H8-1,1)-H3","v":-0.19999999999999996},0]
["Honolulu, HI",1.52,null,{"$f":"D3+5","v":2000},3.9369999999959999E-2,4.5060277333287553E-2,null,null,null,null,2.7100000000000001E-5]
["Mokuoloe, HI",1.67,null,2005,6.3976249999934995E-2,7.900213858325307E-2,null,null,null,null,1.13E-4]
["Kahului, HI",2.21,null,2010,8.8582499999909997E-2,0.11738952899988074]
["Hilo, HI",3.17,null,2015,0.11318874999988501,0.16022244858317056,null,"dropdown"]
["Johnston Atoll",0.75,null,2020,0.13779499999986,0.20750089733312255,null,{"$f":"MATCH('RI Estimator'!$C$23,stations,0)","v":1},{"$f":"+INDEX(justStations,H8-1,1)","v":"Johnston Atoll"}]
["Midway Atoll",0.7,null,2025,0.16240124999983502,0.25922487524973659]
["Guam, Marianas Islands",-1.05,null,2030,0.18700749999981001,0.31539438233301292,null,2,"Linear (Low)"]
["Pago Pago, American Samoa",2.0699999999999998,null,2035,0.21161374999978499,0.37600941858295134,null,"SLR=",{"$f":"+ROUND(Project_Useful__Life__Years*$I$12,2)","v":{"$error":"#VALUE!"}}]
["Kwajalein, Marshall Islands",1.43,null,2040,0.23621999999976001,0.44106998399955188,null,"r (in ft) =",{"$f":"(H3+J3)/(25.4*12)","v":4.9212598425196858E-3}]
["Chuuk, Caroline Islands",0.6,null,2045,0.26082624999973497,0.51057607858281462]
["Wake Island",1.91,null,2050,0.28543249999971004,0.58452770233273943,null,null,"This choice is fixed"]
["Bermuda",2.04,null,2055,0.310038749999685,0.66292485524932654,null,"Eqv. Year=",{"$f":"+ROUND((0.57*Project_Useful__Life__Years-1.18),0)","v":{"$error":"#VALUE!"}}]
["Eastport, ME",2.0499999999999998,null,2060,0.33464499999966002,0.74576753733257573,null,"SLR by Eq Y=",{"$f":"+ROUND(I15*$I$12,2)","v":{"$error":"#VALUE!"}}]
["Bar Harbor, ME",2.4500000000000002,null,2065,0.35925124999963498,0.83305574858248699,null,"BFE rise=",{"$f":"ROUND('RI Estimator'!D27-'RI Estimator'!F17,2)","v":{"$error":"#VALUE!"}}]
["Portland, ME",1.8599999999999999,null,2070,0.38385749999961,0.92478948899906044]
["Seavey Island, ME",1.49,null,2075,0.40846374999958501,1.020968758582296]
["Boston, MA",2.54,null,2080,0.43306999999956003,1.1215935573321938]
["Woods Hole, MA",2.67,null,2085,0.45767624999953505,1.2266638852487539]
["Nantucket Island, MA",2.86,null,2090,0.48228249999950995,1.3361797423319757]
["Newport, RI",2.58,null,2095,0.50688874999948497,1.45014112858186]
["Providence, RI",2,null,2100,0.53149499999946004,1.5685480439984065]
["New London, CT",2.37,null,2105,0.556101249999435,1.691400488581615]
["Bridgeport, CT",2.46,null,2110,0.58070749999940996,1.8186984623314857]
["Montauk, NY",2.9299999999999997,null,2115,0.60531374999938503,1.9504419652480185]
["Port Jefferson, NY",2.44,null,2120,0.62991999999935999,2.0866309973312136]
["Kings Point, NY",2.37,null,2125,0.65452624999933506,2.2272655585810708]
["The Battery, NY",2.92,null,2130,0.67913249999931002,2.37234564899759]
["Sandy Hook, NJ",3.9699999999999998,null,2135,0.70373874999928498,2.5218712685807714]
["Atlantic City, NJ",3.87,null,2140,0.72834499999925995,2.6758424173306148]
["Cape May, NJ",3.8,null,2145,0.75295124999923502,2.8342590952471203]
["Philadelphia, PA",2.76,null,2150,0.77755749999921009,2.9971213023302883]
["Reedy Point, DE",3.41,null,2155,0.80216374999918494,3.1644290385801184]
["Lewes, DE",3.36,null,2160,0.82676999999916001,3.336182303996611]
["Ocean City, MD",4.43,null,2165,0.85137624999913497,3.5123810985797652]
["Cambridge, MD",3.5999999999999996]
["Chesapeake City, MD",3.0300000000000002]
["Baltimore, MD",3.0300000000000002]
["Annapolis, MD",3.3200000000000003]
["Solomons Island, MD",3.5300000000000002]
["Washington, DC",3.04]
["Kiptopeke, VA",3.5999999999999996]
["Colonial Beach, VA",4.7699999999999996]
["Lewisetta, VA",4.12]
["Gloucester Point, VA",4.12]
["Sewells Point, VA",4.3099999999999996]
["Portsmouth, VA",3.76]
["Chesapeake Bay Bridge Tunnel, VA",5.04]
["Oregon Inlet Marina, NC",2.34]
["Beaufort, NC",2.4900000000000002]
["Wilmington, NC",2.13]
["Southport, NC",2.2199999999999998]
["Springmaid Pier, SC",4.04]
["Charleston, SC",2.94]
["Fort Pulaski, GA",3.06]
["Fernandina Beach, FL",2.2999999999999998]
["Mayport, FL",2.29]
["Daytona Beach Shores, FL",2.3199999999999998]
["Miami Beach, FL",2.39]
["Vaca Key, FL",2.9]
["Key West, FL",2.2000000000000002]
["Naples, FL",1.97]
["Fort Myers, FL",2.3199999999999998]
["St. Petersburg, FL",2.62]
["Clearwater Beach, FL",2.56]
["Cedar Key, FL",1.81]
["Apalachicola, FL",1.46]
["Panama City, FL",1.1000000000000001]
["Pensacola, FL",2.0299999999999998]
["Dauphin Island, AL",2.92]
["Grand Isle, LA",9.2999999999999989]
["Eugene Island, LA",9.65]
["Sabine Pass, TX",5.55]
["Galveston Pier 21, TX",6.42]
["Galveston Pleasure Pier, TX",6.6400000000000006]
["Freeport, TX",5.35]
["Rockport, TX",5.35]
["Port Mansfield, TX",1.93]
["Padre Island, TX",3.48]
["Port Isabel, TX",3.8600000000000003]
["San Diego, CA",2.0699999999999998]
["La Jolla, CA",2.0699999999999998]
["Newport Beach, CA",2.2200000000000002]
["Los Angeles, CA",0.78999999999999992]
["Santa Monica, CA",1.42]
["Rincon Island, CA",3.22]
["Santa Barbara, CA",-0.32000000000000006]
["Port San Luis, CA",0.76999999999999991]
["Monterey, CA",1.3699999999999999]
["San Francisco, CA",2.06]
["Redwood City, CA",1.5999999999999999]
["Alameda, CA",0.91999999999999993]
["Point Reyes, CA",2.21]
["Port Chicago, CA",2.79]
["North Spit, CA",5.13]
["Crescent City, CA",-0.67999999999999994]
["Port Orford, OR",0.30000000000000004]
["Charleston, OR",1.1299999999999999]
["South Beach, OR",2.8200000000000003]
["Garibaldi, OR",1.69]
["Astoria, OR",-0.40000000000000013]
["Toke Point, WA",1.48]
["Neah Bay, WA",-1.64]
["Port Angeles, WA",0.12999999999999989]
["Port Townsend, WA",1.94]
["Seattle, WA",2.2400000000000002]
["Cherry Point, WA",0.53]
["Friday Harbor, WA",1.1200000000000001]
["Ketchikan, AK",-0.17999999999999994]
["Sitka, AK",-2.0099999999999998]
["Juneau, AK",-12.92]
["Skagway, AK",-17.260000000000002]
["Yakutat, AK",-10.61]
["Cordova, AK",5.15]
["Valdez, AK",-2.1799999999999997]
["Seward, AK",-2.12]
["Seldovia, AK",-9.7200000000000006]
["Nikiski, AK",-10.130000000000001]
["Anchorage, AK",0.30000000000000004]
["Kodiak Island, AK",-9.89]
["Sand Point, AK",1.1200000000000001]
["Adak Island, AK",-2.63]
["Unalaska, AK",-5.55]
["Guantanamo Bay, Cuba",1.64]
["Lime Tree Bay, Virgin Islands",1.8499999999999999]
["Charlotte Amalie, Virgin Islands",1.18]
["San Juan, Puerto Rico",1.68]
["Magueyes Island, Puerto Rico",1.21]
[]
[]
[]
["Piecewise cumulative fit to Gumbel Distribution"]
["This approach used because the purpose of tool is to produce accuaret Tr for a range of known elevations and not overall SLR design elevation change"]
["Tr\n(Year)","Flood Elev(including waveheight)(ft)","Tr of El used\nfor this range","Loc","Scale","(1+r*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/Location)=","P","Gumbel fit \nby equation","flood elev (ft)","Theor. Flood El."]
[{"$f":"+'RI Estimator'!B15","v":10},{"$f":"+'RI Estimator'!F15","v":""},"10,50",{"$f":"B137+LN(LN((10/9)))*E137","v":{"$error":"#VALUE!"}},{"$f":"+(B138-B137)/(LN(LN(10/9))-LN(LN((50/49))))","v":{"$error":"#VALUE!"}},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/D137)","v":{"$error":"#VALUE!"}},{"$f":"1/A137","v":0.1},{"$f":"1-EXP(-EXP(-(B137-D137)/E137))","v":{"$error":"#VALUE!"}},{"$f":"+B137","v":""},{"$f":"D137-E137*(LN(LN((1/G137)/((1/G137)-1))))","v":{"$error":"#VALUE!"}}]
[{"$f":"+'RI Estimator'!B16","v":50},{"$f":"+'RI Estimator'!F16","v":""},10100,{"$f":"B138+LN(LN((50/49)))*E138","v":{"$error":"#VALUE!"}},{"$f":"+(B139-B137)/(LN(LN(10/9))-LN(LN((100/99))))","v":{"$error":"#VALUE!"}},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/D138)","v":{"$error":"#VALUE!"}},{"$f":"1/A138","v":0.02},{"$f":"1-EXP(-EXP(-(B138-D138)/E138))","v":{"$error":"#VALUE!"}},{"$f":"+B138","v":""},{"$f":"D138-E138*(LN(LN((1/G138)/((1/G138)-1))))","v":{"$error":"#VALUE!"}}]
[{"$f":"+'RI Estimator'!B17","v":100},{"$f":"+'RI Estimator'!F17","v":""},10500,{"$f":"B139+LN(LN((100/99)))*E139","v":{"$error":"#VALUE!"}},{"$f":"+(B140-B137)/(LN(LN(10/9))-LN(LN((500/499))))","v":{"$error":"#VALUE!"}},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/D139)","v":{"$error":"#VALUE!"}},{"$f":"1/A139","v":0.01},{"$f":"1-EXP(-EXP(-(B139-D139)/E139))","v":{"$error":"#VALUE!"}},{"$f":"+B139","v":""},{"$f":"D139-E139*(LN(LN((1/G139)/((1/G139)-1))))","v":{"$error":"#VALUE!"}}]
[{"$f":"+'RI Estimator'!B18","v":500},{"$f":"+'RI Estimator'!F18","v":""},10500,{"$f":"B140+LN(LN((500/499)))*E140","v":{"$error":"#VALUE!"}},{"$f":"+E139","v":{"$error":"#VALUE!"}},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/D140)","v":{"$error":"#VALUE!"}},{"$f":"1/A140","v":2E-3},{"$f":"1-EXP(-EXP(-(B140-D140)/E140))","v":{"$error":"#VALUE!"}},{"$f":"+B140","v":""},{"$f":"D140-E140*(LN(LN((1/G140)/((1/G140)-1))))","v":{"$error":"#VALUE!"}}]
[]
["Estimation of Tr for recorded Flood Elevations"]
["Recorded Flood Elevation (ft)","Loc","Scale","Estimated R.I.\nwithout SLR\n(Year)","(1+r*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/Location)="]
[{"$f":"+'RI Estimator'!B32","v":0},{"$f":"IF(A144<=$B$137,$D$137,VLOOKUP($A144,$B$137:$E$140,3))","v":{"$error":"#VALUE!"}},{"$f":"IF(A144<=$B$137,$E$137,VLOOKUP($A144,$B$137:$E$140,4))","v":{"$error":"#VALUE!"}},{"$f":"IF(ISNUMBER('RI Estimator'!B32),1/(1-EXP(-EXP(-(A144-B144)/C144))),\"\")","v":""},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/B144)","v":{"$error":"#VALUE!"}}]
[{"$f":"+'RI Estimator'!B33","v":0},{"$f":"IF(A145<=$B$137,$D$137,VLOOKUP($A145,$B$137:$E$140,3))","v":{"$error":"#VALUE!"}},{"$f":"IF(A145<=$B$137,$E$137,VLOOKUP($A145,$B$137:$E$140,4))","v":{"$error":"#VALUE!"}},{"$f":"IF(ISNUMBER('RI Estimator'!B33),1/(1-EXP(-EXP(-(A145-B145)/C145))),\"\")","v":""},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/B145)","v":{"$error":"#VALUE!"}}]
[{"$f":"+'RI Estimator'!B34","v":0},{"$f":"IF(A146<=$B$137,$D$137,VLOOKUP($A146,$B$137:$E$140,3))","v":{"$error":"#VALUE!"}},{"$f":"IF(A146<=$B$137,$E$137,VLOOKUP($A146,$B$137:$E$140,4))","v":{"$error":"#VALUE!"}},{"$f":"IF(ISNUMBER('RI Estimator'!B34),1/(1-EXP(-EXP(-(A146-B146)/C146))),\"\")","v":""},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/B146)","v":{"$error":"#VALUE!"}}]
[{"$f":"+'RI Estimator'!B35","v":0},{"$f":"IF(A147<=$B$137,$D$137,VLOOKUP($A147,$B$137:$E$140,3))","v":{"$error":"#VALUE!"}},{"$f":"IF(A147<=$B$137,$E$137,VLOOKUP($A147,$B$137:$E$140,4))","v":{"$error":"#VALUE!"}},{"$f":"IF(ISNUMBER('RI Estimator'!B35),1/(1-EXP(-EXP(-(A147-B147)/C147))),\"\")","v":""},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/B147)","v":{"$error":"#VALUE!"}}]
[{"$f":"+'RI Estimator'!B36","v":0},{"$f":"IF(A148<=$B$137,$D$137,VLOOKUP($A148,$B$137:$E$140,3))","v":{"$error":"#VALUE!"}},{"$f":"IF(A148<=$B$137,$E$137,VLOOKUP($A148,$B$137:$E$140,4))","v":{"$error":"#VALUE!"}},{"$f":"IF(ISNUMBER('RI Estimator'!B36),1/(1-EXP(-EXP(-(A148-B148)/C148))),\"\")","v":""},{"$f":"+(1+Calculations!$I$12*ROUND((0.57*Project_Useful__Life__Years-1.18),0)/B148)","v":{"$error":"#VALUE!"}}]
[]
["Solving 2 eq 2 unknows (ex: 10-yr annd 50-yr)"]
["El10= loc-ln(ln(10/9))scale --> Loc= El10+ln(ln(10/9))scale"]
["El50= loc-ln(ln(50/49))scale--> Loc= El50+ln(ln(50/49))scale"]
["setting Loc from above 2 equation equal and solving for scale:"]
["Scale= (El50-El10)/ (ln(ln(10/9))-ln(ln(50/49))"]
["and then:"]
["Loc= Wl10+ln(ln(10/9)) * sclae … where scale is already known from above"]
["So pairs of equations were solved in cumulative fashion"]