{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Cover page","Barrier Strike Costs","All Workings"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":8,"comments":0,"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":"Cover page","index":0,"n_rows":42,"n_cols":1,"rows_kept":42,"cut":false,"hidden":false,"merged":[[2,0,3,0]]}
[]
[]
["Quantifying the likelihood of barrier strike maintenance"]
[]
["February 2016"]
[]
[]
["Barrier strike cost prediction model (see Appendix C in the main report)"]
[]
[]
[]
[]
[]
[]
[]
["Dave Smith, Abley Transportation Consultants, Christchurch"]
["Robyn Hyde, Abley Transportation Consultants, Christchurch"]
["Carl O'Neil, Abley Transportation Consultants, Christchurch"]
["Bruce Corben, Corben Consulting, Victoria, Australia"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["NZ Transport Agency research report 580"]
["Contracted research organisation – Abley Transportation Consultants"]

{"sheet":"Barrier Strike Costs","index":1,"n_rows":35,"n_cols":19,"rows_kept":35,"cut":false,"hidden":false,"merged":[[19,14,19,19],[10,1,18,1],[3,4,3,9],[5,4,5,6],[19,1,26,1],[19,2,19,7],[19,8,19,13]]}
[]
[null,"Roadside Barrier Strike Prediction Model"]
[]
[null,null,null,"Site/Location Information: "]
[]
[null,null,null,"Barrier Type:","LHS Wire Rope"]
[]
[]
[]
[]
[null,"Inputs"]
[null,null,null,"2-way AADT (vehicles/day): ",10000,null,{"$f":"'All Workings'!G27","v":""},null,null,"Horizontal alignment variable: ","1 - Straight",null,{"$f":"IF(E6=\"LHS Wire Rope\",'All Workings'!G30,IF(E6=\"Median Wire Rope\",'All Workings'!G34,IF(E6=\"LHS W-Beam > 40m\",'All Workings'!G39,IF(E6=\"LHS W-Beam ≤  40m\",'All Workings'!G43,IF(E6=\"Median W-Beam ≤  40m\",'All Workings'!G47,\"\")))))","v":""},null,null,"Posted speed < 100kph? ","No",null,{"$f":"IF(E6=\"Median Wire Rope\",'All Workings'!G35,\"\")","v":""}]
[]
[null,null,null,"Barrier length (m): ",1000,null,{"$f":"IF(OR(E6=\"LHS Wire Rope\",E6=\"Median Wire Rope\"),'All Workings'!G28,IF(AND(RIGHT(E6,5)=\"≤ 40m\",'All Workings'!F28>40),\"ERROR*****\",IF(AND(RIGHT(E6,5)=\"> 40m\",'All Workings'!F28<=40),\"ERROR*****\",'All Workings'!G28)))","v":""},null,null,"Audio tactile road markings: ","No",null,{"$f":"IF(E6=\"LHS Wire Rope\",'All Workings'!G31,IF(E6=\"Median Wire Rope\",'All Workings'!G36,\"\"))","v":""},null,null,"Terrain Code:","1 - Level",null,{"$f":"IF(E6=\"LHS W-Beam > 40m\",'All Workings'!G38,IF(E6=\"Median W-Beam > 40m\",'All Workings'!G41,IF(E6=\"LHS W-Beam ≤ 40m\",'All Workings'!G44,\"\")))","v":""}]
[]
[null,null,null,"Average strike cost (Wire Rope)*: ",2700,null,{"$f":"IF(E6=\"LHS Wire Rope\",'All Workings'!G32,IF(E6=\"Median Wire Rope\",'All Workings'!G37,\"\"))","v":""},null,null,"Centreline-LHS barrier offset (m): ",5,null,{"$f":"IF(E6=\"LHS Wire Rope\",'All Workings'!G29,\"\")","v":""},null,null,"% Heavy Vehicles traversing section",10,null,{"$f":"IF(E6=\"LHS W-Beam ≤ 40m\",'All Workings'!G45,\"\")","v":""}]
[]
[null,null,null,"Average strike cost (W-Beam)**: ",2000,null,{"$f":"IF(E6=\"Median W-Beam > 40m\",'All Workings'!G42,IF(E6=\"LHS W-Beam > 40m\",'All Workings'!G40,IF(E6=\"Median W-Beam ≤ 40m\",'All Workings'!G48,IF(E6=\"LHS W-Beam ≤ 40m\",'All Workings'!G46,\"\"))))","v":""},null,null,"Median width (m): ",2,null,{"$f":"IF(E6=\"Median Wire Rope\",'All Workings'!G33,\"\")","v":""}]
[]
[null,"Outputs","Vehicle Kilometres Travelled",null,null,null,null,null,"Estimated Total Strikes",null,null,null,null,null,"Estimated Nuisance Strikes***"]
[]
[null,null,null,null,null,null,null,null,null,"Strike rate: ",{"$f":"IF('All Workings'!N37>=0,'All Workings'!N37,\"0****\")","v":0.17680000000000001}," per million VKT",null,null,null,"Strike rate: ",{"$f":"IF('All Workings'!N40>=0,'All Workings'!N40,\"0****\")","v":0.15559999999999999}," per million VKT"]
[]
[null,null,null,"Annual VKT: ",{"$f":"IF('All Workings'!P2>=0,'All Workings'!P2,\"0****\")","v":3650000}," per annum",null,null,null,"Strike rate: ",{"$f":"IF('All Workings'!N38>=0,'All Workings'!N38,\"0****\")","v":0.64532}," per annum",null,null,null,"Strike rate: ",{"$f":"IF('All Workings'!N41>=0,'All Workings'!N41,\"0****\")","v":0.56794}," per annum"]
[]
[null,null,null,null,null,null,null,null,null,"Maintenance cost: ",{"$f":"IF('All Workings'!N39>=0,'All Workings'!N39,\"0****\")","v":1742.364}," per annum",null,null,null,"Maintenance cost: ",{"$f":"IF('All Workings'!N42>=0,'All Workings'!N42,\"0****\")","v":1533.4380000000001}," per annum"]
[]
[]
[]
[null,null,null,"*   Default value: $2,700 /Strike"]
[null,null,null,"**  Default value: $2,000 /Strike"]
[null,null,null,"*** Nuisance strikes occur during drive-away events (where the driver is unlikely to be found liable for the cost of repair)"]
[null,null,null,"*** Nusiance strikes can only be calculated for barriers of type: LHS Wire Rope, Median Wire Rope"]
[null,null,null,{"$f":"IF(OR(E24=\"0****\",K22=\"0****\",K24=\"0****\",K26=\"0****\"),\"****  Value rounded to zero\",\"\")","v":""}]
[null,null,null,{"$f":"IF(OR(G12=\"ERROR*****\",G14=\"ERROR*****\",G16=\"ERROR*****\",G18=\"ERROR*****\",M12=\"ERROR*****\",M14=\"ERROR*****\",M16=\"ERROR*****\",M18=\"ERROR*****\",Q12=\"ERROR*****\",Q14=\"ERROR*****\",Q16=\"ERROR*****\"),\"***** Consult user guide for more information, including valid input ranges\",\"\")","v":""}]

{"sheet":"All Workings","index":2,"n_rows":46,"n_cols":16,"rows_kept":46,"cut":false,"hidden":true,"merged":[[32,1,36,1],[37,1,39,1],[40,1,41,1],[42,1,45,1],[26,7,27,7],[28,7,31,7],[32,7,36,7],[37,7,39,7],[40,7,41,7],[42,7,45,7],[0,11,0,13],[0,14,0,15],[25,1,25,2],[28,1,31,1],[26,1,27,1]]}
["Selection cells",null,null,null,null,null,null,null,null,null,null,"Inputs reformulated",null,null,"Outputs"]
[null,"Barrier type:","LHS Wire Rope",null,null,null,null,null,null,null,null,"All site",null,null,"VKT",{"$f":"IF(H27,365*F27*(F28/1000),\"--\")","v":3650000}]
[null,null,"Median Wire Rope"]
[null,null,"LHS W-Beam > 40m",null,null,null,null,null,null,null,null,"LHS Wire Rope - S/millVKT","Horiz",{"$f":"$F$30","v":1},"S/millVKT",{"$f":"IF(AND(H29,H27),0.1768*N4-0.4132*N5+2.0042*N6,\"--\")","v":0.17680000000000001}]
[null,null,"Median W-Beam > 40m",null,null,null,null,null,null,null,null,"LHS Wire Rope - S/annum","ATP",{"$f":"$F$31-1","v":0},"S/annum",{"$f":"IF(AND(H27,H29),P4*(P2/1000000),\"--\")","v":0.64532}]
[null,null,"LHS W-Beam ≤ 40m",null,null,null,null,null,null,null,null,"LHS Wire Rope - $/annum","Offset Test",{"$f":"IF($F$29>=5,0,5-$F$29)","v":0},"$/annum",{"$f":"IF(AND(H27,H29),P5*F32,\"--\")","v":1742.364}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Median Wire Rope - S/millVKT","Horiz",{"$f":"$F$34","v":1},"S/millVKT",{"$f":"IF(AND(H33,H27),0.122*N8+0.00401*EXP(N9)-0.423*N10-0.132*N11,\"--\")","v":0.71713676800133208}]
[null,"Horizontal alignment variable:","1 - Straight",null,null,null,null,null,null,null,null,"Median Wire Rope - S/annum","Med",{"$f":"MAX(0,7-$F$33)","v":5},"S/annum",{"$f":"IF(AND(H27,H33),P8*(P2/1000000),\"--\")","v":2.6175492032048622}]
[null,null,"2 - Easy curves",null,null,null,null,null,null,null,null,"Median Wire Rope - $/annum","PSL",{"$f":"$F$35-1","v":0},"$/annum",{"$f":"IF(AND(H27,H33),P9*F37,\"--\")","v":7067.3828486531274}]
[null,null,"3 - Easy-moderate curves",null,null,null,null,null,null,null,null,null,"ATP",{"$f":"$F$36-1","v":0}]
[null,null,"4 - Moderate curves"]
[null,null,"5 - Tight curves",null,null,null,null,null,null,null,null,"LHS W-Beam > 40m - S/annum","Ter",{"$f":"F38","v":1},"S/annum",{"$f":"IF(AND(H38,H27),0.0842*N13-0.118*N15+0.00000683*N16+0.0104*N14,\"--\")","v":0.16289999999999999}]
[null,null,"6 - Very tight curves",null,null,null,null,null,null,null,null,"LHS W-Beam > 40m - S/millVKT","Hor",{"$f":"F39","v":1},"S/millVKT",{"$f":"IF(AND(H27,H38),P13/(P2/1000000),\"--\")","v":4.4630136986301368E-2}]
[null,null,null,null,null,null,null,null,null,null,null,"LHS W-Beam > 40m - $/annum","Length",{"$f":"IF(F28<400,1,0)","v":0},"$/annum",{"$f":"IF(AND(H27,H38),P13*F40,\"--\")","v":325.79999999999995}]
[null,"Audio tactile road markings: ","No",null,null,null,null,null,null,null,null,null,"AADT",{"$f":"F27","v":10000}]
[null,null,"Yes"]
[null,null,null,null,null,null,null,null,null,null,null,"Median W-Beam > 40m - S/annum","Ter",{"$f":"F41","v":1},"S/annum",{"$f":"IF(AND(H41,H27),0.236*N18-0.324*N19+0.00000529*N20,\"--\")","v":0.28889999999999999}]
[null,"Posted speed < 100kph?","No",null,null,null,null,null,null,null,null,"Median W-Beam > 40m - S/millVKT","Length",{"$f":"IF(F28<400,1,0)","v":0},"S/millVKT",{"$f":"IF(AND(H27,H41),P18/(P2/1000000),\"--\")","v":7.9150684931506843E-2}]
[null,null,"Yes",null,null,null,null,null,null,null,null,"Median W-Beam > 40m - $/annum","AADT",{"$f":"F27","v":10000},"$/annum",{"$f":"IF(AND(H27,H41),P18*F42,\"--\")","v":577.79999999999995}]
[]
[null,"Terrain Code:","1 - Level",null,null,null,null,null,null,null,null,"LHS W-Beam ≤ 40m - S/annum","Hor",{"$f":"F43","v":1},"S/annum",{"$f":"IF(AND(H43,H27),0.00963*N22+0.0205*N23-0.188/N24+ 0.000000672*N25,\"--\")","v":1.805E-2}]
[null,null,"2 - Rolling",null,null,null,null,null,null,null,null,"LHS W-Beam ≤ 40m - S/millVKT","Ter",{"$f":"F44","v":1},"S/millVKT",{"$f":"IF(AND(H27,H43),P22/(P2/1000000),\"--\")","v":4.9452054794520547E-3}]
[null,null,"3 - Mountainous",null,null,null,null,null,null,null,null,"LHS W-Beam ≤ 40m - $/annum","%Heavy",{"$f":"F45","v":10},"$/annum",{"$f":"IF(AND(H27,H43),P22*F46,\"--\")","v":36.1}]
[null,null,null,null,null,null,null,null,null,null,null,null,"AADT",{"$f":"F27","v":10000}]
[null,"Inputs ranges",null,"min (<=)","max (>=)","Value","VALID","Group VALID"]
[null,"All site","2-way AADT: ",1,100000,{"$f":"'Barrier Strike Costs'!E12","v":10000},{"$f":"IF(OR(F27<D27,F27>E27),\"ERROR*****\", \"\")","v":""},{"$f":"LEN(G27)+LEN(G28)=0","v":true},null,null,null,"LHS Wire Rope - S/millVKT - Nuisance Only","Horiz",{"$f":"$F$30","v":1},"S/millVKT",{"$f":"IF(AND(H27,H29),0.1556*N27-0.5906*N28+2.074*N29,\"--\")","v":0.15559999999999999}]
[null,null,"Barrier length: ",1,10000,{"$f":"'Barrier Strike Costs'!E14","v":1000},"",null,null,null,null,"LHS Wire Rope - S/annum - Nuisance Only","ATP",{"$f":"$F$31-1","v":0},"S/annum",{"$f":"IF(AND(H27,H29),P27*P2/1000000,\"--\")","v":0.56794}]
[null,"LHS Wire Rope","Centreline LHS barrier offset: ",3.5,11,{"$f":"'Barrier Strike Costs'!K16","v":5},"",{"$f":"LEN(G30)+LEN(G31)+LEN(G29)+LEN(G32)=0","v":true},null,null,null,"LHS Wire Rope - $/annum - Nuisance Only","Offset Test",{"$f":"IF($F$29>=5,0,5-$F$29)","v":0},"$/annum",{"$f":"IF(AND(H27,H29),P28*F32,\"--\")","v":1533.4380000000001}]
[null,null,"Horizontal alignment variable: ",1,6,{"$f":"VALUE(LEFT('Barrier Strike Costs'!K12,1))","v":1},""]
[null,null,"Audio tactile road markings: ",1,2,{"$f":"IF('Barrier Strike Costs'!K14=\"No\",1,2)","v":1},"",null,null,null,null,"Median Wire Rope - S/millVKT - Nuisance Only","Horiz",{"$f":"$F$34","v":1},"S/millVKT",{"$f":"IF(AND(H27,H33),0.0792*N31+0.8056*N32-0.1432*N34-0.2694*N33,\"--\")","v":7.9200000000000007E-2}]
[null,null,"Strike cost - Wire Rope:",1,100000,{"$f":"'Barrier Strike Costs'!E16","v":2700},"",null,null,null,null,"Median Wire Rope - S/annum - Nuisance Only","Med",{"$f":"IF($F$33<2,1,0)","v":0},"S/annum",{"$f":"IF(AND(H27,H33),P31*P2/1000000,\"--\")","v":0.28908}]
[null,"Median Wire Rope","Median width: ",1.5,10,{"$f":"'Barrier Strike Costs'!K18","v":2},"",{"$f":"LEN(G34)+LEN(G36)+LEN(G33)+LEN(G35)+ LEN(G37)=0","v":true},null,null,null,"Median Wire Rope - $/annum - Nuisance Only","PSL",{"$f":"$F$35-1","v":0},"$/annum",{"$f":"IF(AND(H27,H33),P32*F37,\"--\")","v":780.51599999999996}]
[null,null,"Horizontal alignment variable: ",1,6,{"$f":"VALUE(LEFT('Barrier Strike Costs'!K12,1))","v":1},"",null,null,null,null,null,"ATP",{"$f":"$F$36-1","v":0}]
[null,null,"Posted speed < 100kph?",1,2,{"$f":"IF('Barrier Strike Costs'!Q12=\"No\",1,2)","v":1},""]
[null,null,"Audio tactile road markings: ",1,2,{"$f":"IF('Barrier Strike Costs'!K14=\"No\",1,2)","v":1},"",null,null,null,null,"Processed Outputs"]
[null,null,"Strike cost - Wire Rope:",1,100000,{"$f":"'Barrier Strike Costs'!E16","v":2700},"",null,null,null,null,{"$f":"CONCATENATE('Barrier Strike Costs'!$E$6,\" - S/millVKT\")","v":"LHS Wire Rope - S/millVKT"},"S/annum",{"$f":"INDEX($P$4:$P$29,MATCH(L37,$L$4:$L$29,0))","v":0.17680000000000001}]
[null,"LHS W-Beam > 40m","Terrain Code:",1,3,{"$f":"VALUE(LEFT('Barrier Strike Costs'!Q14,1))","v":1},"",{"$f":"LEN(G38)+LEN(G39)+LEN(G40)=0","v":true},null,null,null,{"$f":"CONCATENATE('Barrier Strike Costs'!$E$6,\" - S/annum\")","v":"LHS Wire Rope - S/annum"},"S/millVKT",{"$f":"INDEX($P$4:$P$29,MATCH(L38,$L$4:$L$29,0))","v":0.64532}]
[null,null,"Horizontal alignment variable: ",1,6,{"$f":"VALUE(LEFT('Barrier Strike Costs'!K12,1))","v":1},"",null,null,null,null,{"$f":"CONCATENATE('Barrier Strike Costs'!$E$6,\" - $/annum\")","v":"LHS Wire Rope - $/annum"},"$/annum",1742.364]
[null,null,"Strike cost - W-Beam:",1,100000,{"$f":"'Barrier Strike Costs'!E18","v":2000},"",null,null,null,null,{"$f":"CONCATENATE('Barrier Strike Costs'!$E$6,\" - S/millVKT - Nuisance Only\")","v":"LHS Wire Rope - S/millVKT - Nuisance Only"},"S/annum",{"$f":"IFERROR(INDEX($P$4:$P$33,MATCH(L40,$L$4:$L$33,0)),\"--\")","v":0.15559999999999999}]
[null,"Median W-Beam > 40m","Terrain Code:",1,3,{"$f":"VALUE(LEFT('Barrier Strike Costs'!Q14,1))","v":1},"",{"$f":"LEN(G41)+LEN(G42)=0","v":true},null,null,null,{"$f":"CONCATENATE('Barrier Strike Costs'!$E$6,\" - S/annum - Nuisance Only\")","v":"LHS Wire Rope - S/annum - Nuisance Only"},"S/millVKT",{"$f":"IFERROR(INDEX($P$4:$P$33,MATCH(L41,$L$4:$L$33,0)),\"--\")","v":0.56794}]
[null,null,"Strike cost - W-Beam:",1,100000,{"$f":"'Barrier Strike Costs'!E18","v":2000},"",null,null,null,null,{"$f":"CONCATENATE('Barrier Strike Costs'!$E$6,\" - $/annum - Nuisance Only\")","v":"LHS Wire Rope - $/annum - Nuisance Only"},"$/annum",{"$f":"IFERROR(INDEX($P$4:$P$33,MATCH(L42,$L$4:$L$33,0)),\"--\")","v":1533.4380000000001}]
[null,"LHS W-Beam ≤ 40m","Horizontal alignment variable: ",1,6,{"$f":"VALUE(LEFT('Barrier Strike Costs'!K12,1))","v":1},"",{"$f":"LEN(G43)+LEN(G44)+LEN(G45)+LEN(G46)=0","v":true}]
[null,null,"Terrain Code:",1,3,{"$f":"VALUE(LEFT('Barrier Strike Costs'!Q14,1))","v":1},""]
[null,null,"% Heavy Vehicles",1,30,{"$f":"'Barrier Strike Costs'!Q16","v":10},""]
[null,null,"Strike cost - W-Beam:",1,100000,{"$f":"'Barrier Strike Costs'!E18","v":2000},""]