{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Instructions","Summary","Data"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":1,"defined_names":4,"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":"Instructions","index":0,"n_rows":24,"n_cols":7,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,{"$f":"Summary!PayItemFile","v":"N:\\CFL-DPIT\\Excel\\payitems24.csv"}]
[null,"Instructions"]
[null,1,"On the Summary Tab, click the \"Browse for Pay Item\" button, hit the \"Browse for Pay Item File\" in the dialog box and browse to N:\\CFL-DPIT\\Excel\\PayItems24.csv and select \"Open\" or if you downloaded the file off the network browse to the location to where it was saved."]
[null,null,"Click the \"Return to Excel\" button."]
[]
[null,2,"On the Summary Tab, click the \"Add/Remove Pay Items\" button."]
[null,null,"Pay items are broken out by pay item categories.  Choose the desired pay item category."]
[null,null,"Select the desired pay item and select \"Add Item\".  Repeat for all required pay items."]
[null,null,"To remove pay items click the \"Delete a pay item already placed in sheet\" button and select the desired pay item and click the \"delete item\" button.  Click the \"Close\" button to close the dialog box."]
[null,null,"When all desired pay items have been added click the \"Close\" button."]
[]
[null,3,"Add station ranges and quantities.  Add and remove schedules to tables using the buttons at the top of the Summary tab.  Hide and unhide rows to tables using the buttons at the top of the Summary tab.  Merge and center title over the entire table width."]
[]
[null,4,"Using the Engineer's Estimate Manual (https://highways.dot.gov/federal-lands/estimates/cfl-estimate-manual), determine the correct rounding % and round up values for each item."]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"The Bid schedule and allowance quantities will be automatically calculated at the bottom but should not be included in tabulation sheets."]
[null,5,"DO NOT MANUALLY ADD ANY COLUMNS TO THE SPREADSHEET."]

{"sheet":"Summary","index":1,"n_rows":117,"n_cols":12,"rows_kept":117,"cut":false,"hidden":false,"merged":[[96,3,96,4],[97,3,97,4],[98,3,98,4],[99,3,99,4],[100,3,100,4],[91,3,91,4],[92,3,92,4],[93,3,93,4],[94,3,94,4],[95,3,95,4],[86,3,86,4],[87,3,87,4],[88,3,88,4],[89,3,89,4],[90,3,90,4],[81,3,81,4],[82,3,82,4],[83,3,83,4],[84,3,84,4],[85,3,85,4],[76,3,76,4],[77,3,77,4],[78,3,78,4],[79,3,79,4],[80,3,80,4],[71,3,71,4],[72,3,72,4],[73,3,73,4],[74,3,74,4],[75,3,75,4],[67,3,67,4],[68,3,68,4],[69,3,69,4],[60,3,60,4],[61,3,61,4],[62,3,62,4],[63,3,63,4],[64,3,64,4],[55,3,55,4],[56,3,56,4],[57,3,57,4],[58,3,58,4],[59,3,59,4],[44,3,44,4],[34,3,34,4],[35,3,35,4],[36,3,36,4],[37,3,37,4],[38,3,38,4],[6,7,7,7],[65,3,65,4],[66,3,66,4],[50,3,50,4],[51,3,51,4],[52,3,52,4],[53,3,53,4],[54,3,54,4],[45,3,45,4],[46,3,46,4],[47,3,47,4],[2,11,2,14],[39,2,39,6],[4,3,4,6],[19,3,19,4],[20,3,20,4],[21,3,21,4],[22,3,22,4],[23,3,23,4],[14,3,14,4],[15,3,15,4],[16,3,16,4],[17,3,17,4],[18,3,18,4],[29,3,29,4],[30,3,30,4],[31,3,31,4],[32,3,32,4],[33,3,33,4],[24,3,24,4],[25,3,25,4],[26,3,26,4],[27,3,27,4],[28,3,28,4],[113,0,113,2],[5,3,5,6],[6,2,8,2],[6,6,8,6],[108,0,108,2],[70,2,70,6],[101,2,101,6],[103,0,103,2],[7,11,7,15],[6,8,8,8],[5,11,6,15],[6,3,8,4],[6,5,8,5],[9,3,9,4],[13,3,13,4],[12,3,12,4],[11,3,11,4],[10,3,10,4],[48,3,48,4],[49,3,49,4],[40,3,40,4],[41,3,41,4],[42,3,42,4],[43,3,43,4]]}
[null,null,null,null,null,null,"N:\\CFL-DPIT\\Excel\\payitems24.csv"]
[null,null,null,null,null,null,null,"Leave this column HIDDEN!!! Do not delete"]
["Check Column - do not delete",null,null,null,null,null,null,{"$f":"IF(ISERROR(VLOOKUP(H2,Data!$A:$C,2,FALSE)),\"\",VLOOKUP(H2,Data!$A:$C,2,FALSE))","v":""},null,null,null,"Do not manually add columns to the spreadsheet.  The macro will become inoperable if columns are manually added to the left of the remarks column."]
[]
[null,null,null,"DRAINAGE SUMMARY",null,null,null,null,null,null,null,"<---------------- Merge and Center the Title over the entire table"]
[null,null,null,"Item Number",null,null,null,{"$f":"H2","v":"Leave this column HIDDEN!!! Do not delete"},null,null,null,"**If a supplemental description is needed add the following at the end of the formula in Row 7\n\n&\"                       \"&\"(SUPPLEMENTAL DESCRIPTION)\"\n\nExample:  "]
[null,null,"Schedule","Station",null,"Side","Skew (deg)",{"$f":"IF(ISBLANK(H2),\"\",LOOKUP(H2,'[1]FP24 Pay Items'!$B$1:$B$65536,'[1]FP24 Pay Items'!$E$1:$E$65536))","v":"FUNDING RECLASSIFICATION"},"Remarks"]
[]
[null,null,null,null,null,null,null,{"$f":"IF(ISBLANK(H2),\"\",LOOKUP(H2,'[1]FP24 Pay Items'!$B$1:$B$65536,'[1]FP24 Pay Items'!$F$1:$F$65536))","v":"LPSM"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,{"$f":"IF(Data!$D$2>1,IF(Data!$G$2>1,\"SCHEDULE A TOTALS\",\"TOTALS\"),IF(Data!$G$2>1,\"SCHEDULE A TOTAL\",\"TOTAL\"))","v":"TOTAL"},null,null,null,null,{"$f":"IF(H$9=\"LPSM\",\"ALL\",SUM(H$10:H$39))","v":"ALL"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,{"$f":"IF(Data!D2>1,IF(Data!G2>1,\"SCHEDULE B TOTALS\",\"TOTALS\"),IF(Data!G2>1,\"SCHEDULE B TOTAL\",\"TOTAL\"))","v":"TOTAL"},null,null,null,null,{"$f":"IF(H$9=\"LPSM\",\"ALL\",SUM(H$41:H$70)+H$40)","v":"ALL"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,{"$f":"IF(Data!$D$2>1,IF(Data!$G$2>1,\"SCHEDULE C TOTALS\",\"TOTALS\"),IF(Data!$G$2>1,\"SCHEDULE C TOTAL\",\"TOTAL\"))","v":"TOTAL"},null,null,null,null,{"$f":"IF(H$9=\"LPSM\",\"ALL\",SUM(H$72:H$101)+H$71)","v":"ALL"}]
[]
[{"$f":"IF(Data!$G$2=1,\" \",IF(Data!$G$2=2,\"Schedule A\",IF(Data!$G$2=3,\"Schedule A\")))","v":" "},null,null,"Rounding %"]
[null,null,null,"Round up"]
[null,null,null,"Bid Schedule",null,null,null,{"$f":"IF(H$9=\"each\",H$40,IF(H$9=\"LPSM\",\"LPSM\",IF(H$105=0,ROUND((H$40*(1+H$104)),0),CEILING(H$40*(1+H$104),H$105))))","v":"LPSM"}]
[null,null,null,"Allowance",null,null,null,{"$f":"IF(H$9=\"LPSM\",\"--\",H$106-H$40)","v":"--"}]
[]
[{"$f":"IF(Data!$G$2=1,\" \",IF(Data!$G$2=2,\"Schedule B\",IF(Data!$G$2=3,\"Schedule B\")))","v":" "},null,null,{"$f":"IF(Data!$G$2=1, \"\", \"Rounding %\")","v":""}]
[null,null,null,{"$f":"IF(Data!$G$2=1, \" \", \"Round up\")","v":" "}]
[null,null,null,{"$f":"IF(Data!$G$2=1, \" \", \"Bid Schedule\")","v":" "},null,null,null,{"$f":"IF(Data!$G$2=1,\"\",IF(H$9=\"each\",H$71,IF(H$9=\"LPSM\",\"LPSM\",IF(H$110=0,ROUND((H$71*(1+H$109)),0),CEILING(H$71*(1+H$109),H$110)))))","v":""}]
[null,null,null,{"$f":"IF(Data!$G$2=1, \" \", \"Allowance\")","v":" "},null,null,null,{"$f":"IF(Data!$G$2=1,\"\",IF(H$9=\"LPSM\",\"--\",H$111-H$71))","v":""}]
[]
[{"$f":"IF(Data!$G$2=1,\" \",IF(Data!$G$2=2,\" \",IF(Data!$G$2=3,\"Schedule C\")))","v":" "},null,null,{"$f":"IF(Data!$G$2=1, \"\", IF(Data!$G$2=2, \"\", \"Rounding %\"))","v":""}]
[null,null,null,{"$f":"IF(Data!$G$2=1, \" \", IF(Data!$G$2=2, \"\", \"Round up\"))","v":" "}]
[null,null,null,{"$f":"IF(Data!$G$2=1, \" \", IF(Data!$G$2=2, \"\", \"Bid Schedule\"))","v":" "},null,null,null,{"$f":"IF(Data!$G$2<3,\"\",IF(H$9=\"each\",H$102,IF(H$9=\"LPSM\",\"LPSM\",IF(H$115=0,ROUND((H$102*(1+H$114)),0),CEILING(H$102*(1+H$114),H$115)))))","v":""}]
[null,null,null,{"$f":"IF(Data!$G$2=1, \" \", IF(Data!$G$2=2, \"\", \"Allowance\"))","v":" "},null,null,null,{"$f":"IF(Data!$G$2<3,\"\",IF(H$9=\"LPSM\",\"--\",H$116-H$102))","v":""}]

{"sheet":"Data","index":2,"n_rows":4,"n_cols":7,"rows_kept":4,"cut":false,"hidden":false,"merged":[]}
["Pay Item Number","Description","Unit","Num Items",null,"Sides","Schedules"]
[null,null,null,{"$f":"COUNTA(A:A)-1","v":0},null,"LT",1]
[null,null,null,null,null,"RT"]
[null,null,null,null,null,"LT/RT"]