{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["INTRODUCTION","INPUT","SAMPLING PLANS","CALCULATIONS","REVISION","RANDOM SELECTOR TOOL"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":15,"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":"INTRODUCTION","index":0,"n_rows":41,"n_cols":2,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
[null,"A Template for You to Develop Sampling Plans for the NTD Sampling Method"]
[]
[null,"What can I do with this template?"]
[]
[null,"You can use it to develop template sampling plans for obtaining annual data on unlinked passenger trips (UPT) and passenger miles traveled (PMT) for a particular mode and type of service.  When you follow the steps for this template and the guidance in the NTD Sampling Manual, the resulting sampling plans meet FTA's 95% confidence and 10% precision levels.         "]
[]
[null,"What do I need to know before using this template?"]
[]
[null,"You should go over Subsection 54 of the NTD Sampling Manual, which is available at https://www.transit.dot.gov/ntd/ntd-sampling-manual ."]
[]
[null,"What is in the content of this template?"]
[]
[null,"This template consists of six worksheets:"]
[]
[null,"a) INTRODUCTION: You should go over the general directions in this worksheet to using this template."]
[]
[null,"b) INPUT: You must use this worksheet to enter your data."]
[]
[null,"c) SAMPLING PLANS: You must go there to see the sampling plans you have developed after you have entered your data into INPUT."]
[]
[null,"d) CALCULATIONS: You do not need to go there for using this template.  If you are interested, it does provide a number of characteristics of your service as reflected in the information you enter into INPUT.   "]
[]
[null,"e) REVISION: You do not need to use the information in this worksheet for developing this set of template sampling plans.  You will need to use it in the future to determine whether you need to revise this set of template sampling plans. "]
[]
[null,"f) RANDOM SELECTOR TOOL: You can use this to select a random sample given your complete list of trips."]
[]
[null,"What information do I need to use this template?"]
[]
[null,"You must have a set of sample data collected from the mode and type of service for which you would like to have a new sampling plan that reflects its conditions.  "]
[]
[null,"How do I enter the information?"]
[]
[null,"You must follow the steps in INPUT and enter the information accordingly."]
[]
[null,"How do I use the resulting sampling plans?"]
[]
[null,"You should follow the NTD Sampling Manual in using the template sampling plans.  SAMPLING PLANS also describes the options."]
[]
[null,"Is there an example of using this template that I can take a look at before I use it myself?"]
[]
[null,"\"The NTD Sampling Template with Sample Data.xls\" at https://www.transit.dot.gov/ntd/ntd-sampling-template-data-downloadable-excel-file illustrates the use of this template with real sample data."]

{"sheet":"INPUT","index":1,"n_rows":70,"n_cols":26,"rows_kept":70,"cut":false,"hidden":false,"merged":[[47,1,47,12],[68,9,68,14],[2,1,2,12],[21,1,22,12],[30,1,30,12],[35,1,35,12],[42,1,42,12]]}
[null,"Input"]
[]
[null,"Before you start, gather your data from your prior year sample. You must at least have UPT and PMT. Date, ID, PPMT, and Group are all optional."]
[]
[null,"A. Common Data"]
[]
[null,"1. To use this template, enter your prior year sample data for both UPT and PMT in section E below."]
[]
[null,"a) Enter UPT data in column D"]
[null,"b) Enter PMT data in column E"]
[null,"c) Start in row 71."]
[]
[null,"It is critical that you enter the data in the right columns!"]
[]
[null,"2. You may choose to enter some identification information for your sample:"]
[]
[null,"a) The dates on which your sample data were collected in column B, \"Date\"."]
[null,"b) Some other identification, e.g., trip number, trip time, etc., in column C, \"ID\"."]
[]
[null,"B. Group Size"]
[]
[null,"1. Most agencies do not use route grouping. If you do not use route grouping, skip this section. The NTD Sampling Manual discusses route grouping in the section \"Route Grouping Option\". If you choose to use route grouping, you must also enter three additional pieces of information:"]
[]
[null,"a) The name of the group to which each vehicle trip in the sample belongs:"]
[]
[null,"i) Enter the group names in column G starting in row 71."]
[null,"ii) You may have up to 10 distinct groups. However, using more than 3 groups will usually add complexity without making the sample more efficient."]
[null,"iii) The names can be numeric or text."]
[null,"iv) The names automatically appear in ranges D33:M33 and D38:M38 in this sheet, in range E6:N6 in CALCULATIONS, and in range F7:O7 in SAMPLING PLANS."]
[]
[null,"                b) For each group, enter the number of vehicle trips in the group during the year (or other period) you collected the sample data you are entering below. This is the number for the entire period, not just in the sample itself! After you enter the sample data below, come back and enter the group size (number of vehicle trips) in cells D34:M34.",null,null,null,null,null,null,null,null,null,null,null,null,null,"The tables in P33:Y34 and P38:Y39 are only used for plan revision. If you are revising your plan, follow the instructions in the REVISION tab. Otherwise leave these tables blank."]
[null,null,null,{"$f":"uniqueitems(Group,FALSE)","v":0},0,0,0,0,0,0,0,0,0,null,null,{"$f":"uniqueitems(Group_orig,FALSE)","v":0},0,0,0,0,0,0,0,0,0]
[null,"i) Group Names",null,{"$f":"IF(ISERROR(D32),\"\",IF(D32=0,\"\",D32))","v":""},"","","","","","","","","","ALL",null,{"$f":"IF(ISERROR(P32),\"\",IF(P32=0,\"\",P32))","v":""},"","","","","","","","","","ALL"]
[null,"ii) Group Size",null,null,null,null,null,null,null,null,null,null,null,{"$f":"+IF(SUM(D34:M34)=0,\" \",SUM(D34:M34))","v":" "},null,null,null,null,null,null,null,null,null,null,null,{"$f":"+IF(SUM(P34:Y34)=0,\" \",SUM(P34:Y34))","v":" "}]
[]
[null,"              c) For each group, enter the number of vehicle trips you expect to operate during the coming sample year. This is the number for the entire year, not just in the sample itself! After you enter the sample data below, come back and enter the group size (number of vehicle trips) in cells D39:M39."]
[]
[null,"i) Group Names",null,{"$f":"+D33","v":""},{"$f":"+E33","v":""},"","","","","","","","","ALL",null,{"$f":"+P33","v":""},{"$f":"+Q33","v":""},"","","","","","","",""]
[null,"ii) Group Size",null,null,null,null,null,null,null,null,null,null,null,{"$f":"+IF(SUM(D39:M39)=0,\" \",SUM(D39:M39))","v":" "}]
[]
[null,"2. It is critical that:"]
[]
[null,"                  a) You enter group size in the same service units as you have chosen for your template sampling plans.  For example, if you want your sample size in one-way bus trips, group size must be in one-way bus trips as well. For non-scheduled modes, use vehicle days instead of vehicle trips."]
[null,"b) You enter group size data in the right cells to match group names."]
[]
[null,"C. PPMT Data (uncommon)"]
[]
[null,"1. If you choose to consider the PPMT option, you must also enter your sample data for PPMT in column F starting in row 71."]
[]
[null,"D. Is 100% Count of UPT Available by Service Group?"]
[]
[null,"1. If 100% counts of UPT are available by service group, enter 1 in cell M52. If not, enter 0. If you are not using grouping, leave M52 blank."]
[]
[null,"E. Sample Data"]
[]
[null,"1. We suggest that you start by organizing your prior year sample data for Date, ID, UPT, PMT, PPMT, and Group side by side in a separate spreadsheet."]
[]
[null,"2. Copy the full range of the sample data from this separate spreadsheet."]
[]
[null,"3. Paste the sample data as follows:"]
[]
[null,"a) Click cell B71 if you include the two identification columns and cell D71 otherwise"]
[null,"b) Right Click "]
[null,"c) Locate Paste Options"]
[null,"d) Select Paste Values"]
[null,"e) Click OK."]
[]
[null,"4. Make sure that all data on UPT, PMT, PPMT, and Group from other applications are cleared at the bottom."]
[null,"Once you have entered all required data on this tab, proceed to the SAMPLING PLANS tab.",null,null,null,null,null,null,null,"ORIGINAL SAMPLING YEAR DATA: only use this table when you are revising your sampling plan as described in \"Revision\" in the manual. Otherwise leave it blank."]
[null,"Date","ID","UPT","PMT","PPMT","Group",null,null,"Date","ID","UPT","PMT","PPMT","Group"]

{"sheet":"SAMPLING PLANS","index":2,"n_rows":60,"n_cols":17,"rows_kept":60,"cut":false,"hidden":false,"merged":[[1,1,1,15],[45,1,45,15],[59,1,59,15],[41,1,41,15],[33,1,33,15],[55,1,55,15],[57,1,57,15],[5,4,6,4],[5,3,6,3],[4,3,4,4],[4,5,4,15],[5,5,5,14],[5,15,6,15]]}
[1.25,"Sampling Plans"]
[null,"Once you've completed the INPUT tab, your options for a Sampling Plan will appear here. You can choose to select samples annually, quarterly, monthly, or weekly (sections 1, 2, 3, 4). Within these sections, look at row a) if 100% count of UPT is not available, and row b) if 100% count of UPT is available. Look at row c) if you use the uncommon PPMT Option. If you do not use route grouping, your required sample in each period will be in column D, Sample Size by Period. If you use route grouping, your required sample in each period will be in columns F-O, Sample Size by Period and by Group (depending on how many groups you use)."]
[null,"A. Sample Size by Options"]
[]
[null,null,null,"Without Grouping",null,"With Grouping"]
[null,null,null,"Sample Size by Period","Annual Sample Size","Sample Size by Period and by Group",null,null,null,null,null,null,null,null,null,"Annual Sample Size"]
[null," ",null,null,null,{"$f":"+IF(CALCULATIONS!E8=\" \",\" \",INPUT!D33)","v":" "},{"$f":"+IF(CALCULATIONS!F8=\" \",\" \",INPUT!E33)","v":" "},{"$f":"+IF(CALCULATIONS!G8=\" \",\" \",INPUT!F33)","v":" "},{"$f":"+IF(CALCULATIONS!H8=\" \",\" \",INPUT!G33)","v":" "},{"$f":"+IF(CALCULATIONS!I8=\" \",\" \",INPUT!H33)","v":" "},{"$f":"+IF(CALCULATIONS!J8=\" \",\" \",INPUT!I33)","v":" "},{"$f":"+IF(CALCULATIONS!K8=\" \",\" \",INPUT!J33)","v":" "},{"$f":"+IF(CALCULATIONS!L8=\" \",\" \",INPUT!K33)","v":" "},{"$f":"+IF(CALCULATIONS!M8=\" \",\" \",INPUT!L33)","v":" "},{"$f":"+IF(CALCULATIONS!N8=\" \",\" \",INPUT!M33)","v":" "},null,"All"]
[]
[null,"1. Annual Frequency",1]
[null,"    a) 100% UPT Not Available",null,{"$f":"+IF(CALCULATIONS!D19=\" \",\" \",ROUNDUP(A1*(CALCULATIONS!D19*1.96/0.1)^2,0))","v":" "},{"$f":"+IF(D10=\" \",\" \",D10)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!E$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!D$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!F$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!E$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!G$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!F$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!H$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!G$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!I$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!H$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!J$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!I$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!K$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!J$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!L$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!K$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!M$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!L$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!N$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!M$39/INPUT!$N$39*$Q10,0)))","v":" "},{"$f":"+IF(SUM(F10:O10)=0,\" \",SUM(F10:O10))","v":" "},{"$f":"+IF(CALCULATIONS!O19=\" \",\" \",ROUNDUP(A1*(CALCULATIONS!O19*1.96/0.1)^2,0))","v":" "}]
[null,"    b) 100% UPT Available",null,{"$f":"+IF(CALCULATIONS!D21=\" \",\" \",ROUNDUP(A1*(CALCULATIONS!D21*1.96/0.1)^2,0))","v":" "},{"$f":"+IF(D11=\" \",\" \",D11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!E$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!D$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!D$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!F$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!E$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!E$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!G$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!F$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!F$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!H$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!G$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!G$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!I$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!H$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!H$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!J$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!I$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!I$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!K$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!J$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!J$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!L$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!K$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!K$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!M$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!L$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!L$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!N$8=\" \"),\" \",IF(INPUT!$M52=0,ROUNDUP(INPUT!M$39/INPUT!$N$39*$Q11,0),MAX(10,ROUNDUP(INPUT!M$39/INPUT!$N$39*$Q11,0))))","v":" "},{"$f":"+IF(SUM(F11:O11)=0,\" \",SUM(F11:O11))","v":" "},{"$f":"+IF(AND(CALCULATIONS!O21=\" \",CALCULATIONS!O22=\" \"),\" \",IF(INPUT!M52=1,ROUNDUP(A1*(CALCULATIONS!O21*1.96/0.1)^2,0),ROUNDUP(A1*(CALCULATIONS!O22*1.96/0.1)^2,0)))","v":" "}]
[null,"    c) PPMT Option",null,{"$f":"+IF(CALCULATIONS!D23=\" \",\" \",ROUNDUP(A1*(CALCULATIONS!D23*1.96/0.1)^2,0))","v":" "},{"$f":"+IF(D12=\" \",\" \",D12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!E$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!D$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!F$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!E$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!G$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!F$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!H$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!G$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!I$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!H$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!J$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!I$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!K$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!J$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!L$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!K$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!M$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!L$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,SUM(SizeActual)=0,SUM(SizeExpected)=0,CALCULATIONS!N$8=\" \"),\" \",MAX(10,ROUNDUP(INPUT!M$39/INPUT!$N$39*$Q12,0)))","v":" "},{"$f":"+IF(SUM(F12:O12)=0,\" \",SUM(F12:O12))","v":" "},{"$f":"+IF(CALCULATIONS!O23=\" \",\" \",ROUNDUP(A1*(CALCULATIONS!O23*1.96/0.1)^2,0))","v":" "}]
[]
[null,"2. Quarterly Frequency",4]
[null,"    a) 100% UPT Not Available",null,{"$f":"+IF(D$10=\" \",\" \",ROUNDUP(D$10/$C14,0))","v":" "},{"$f":"+IF(D15=\" \",\" \",D15*C14)","v":" "}," "," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q15=\" \",\" \",SUM(F15:O15)*C14)","v":" "},{"$f":"+IF(SUM(F15:O15)=0,\" \",SUM(F15:O15))","v":" "}]
[null,"    b) 100% UPT Available",null,{"$f":"+IF(D$11=\" \",\" \",ROUNDUP(D$11/$C14,0))","v":" "},{"$f":"+IF(D16=\" \",\" \",D16*C14)","v":" "},{"$f":"+IF(F$11=\" \",\" \",ROUNDUP(F$11/$C14,0))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q16=\" \",\" \",SUM(F16:O16)*C14)","v":" "},{"$f":"+IF(SUM(F16:O16)=0,\" \",SUM(F16:O16))","v":" "}]
[null,"    c) PPMT Option",null,{"$f":"+IF(D$12=\" \",\" \",ROUNDUP(D$12/$C14,0))","v":" "},{"$f":"+IF(D17=\" \",\" \",D17*C14)","v":" "},{"$f":"+IF(F$12=\" \",\" \",ROUNDUP(F$12/$C14,0))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q17=\" \",\" \",SUM(F17:O17)*C14)","v":" "},{"$f":"+IF(SUM(F17:O17)=0,\" \",SUM(F17:O17))","v":" "}]
[]
[null,"3. Monthly Frquency",12]
[null,"    a) 100% UPT Not Available",null,{"$f":"+IF(D$10=\" \",\" \",ROUNDUP(D$10/$C19,0))","v":" "},{"$f":"+IF(D20=\" \",\" \",D20*C19)","v":" "}," "," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q20=\" \",\" \",SUM(F20:O20)*C19)","v":" "},{"$f":"+IF(SUM(F20:O20)=0,\" \",SUM(F20:O20))","v":" "}]
[null,"    b) 100% UPT Available",null,{"$f":"+IF(D$11=\" \",\" \",ROUNDUP(D$11/$C19,0))","v":" "},{"$f":"+IF(D21=\" \",\" \",D21*C19)","v":" "},{"$f":"+IF(F$11=\" \",\" \",ROUNDUP(F$11/$C19,0))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q21=\" \",\" \",SUM(F21:O21)*C19)","v":" "},{"$f":"+IF(SUM(F21:O21)=0,\" \",SUM(F21:O21))","v":" "}]
[null,"    c) PPMT Option",null,{"$f":"+IF(D$12=\" \",\" \",ROUNDUP(D$12/$C19,0))","v":" "},{"$f":"+IF(D22=\" \",\" \",D22*C19)","v":" "},{"$f":"+IF(F$12=\" \",\" \",ROUNDUP(F$12/$C19,0))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q22=\" \",\" \",SUM(F22:O22)*C19)","v":" "},{"$f":"+IF(SUM(F22:O22)=0,\" \",SUM(F22:O22))","v":" "}]
[]
[null,"4. Weekly Frequency",52]
[null,"    a) 100% UPT Not Available",null,{"$f":"+IF(D$10=\" \",\" \",ROUNDUP(D$10/$C24,0))","v":" "},{"$f":"+IF(D25=\" \",\" \",D25*C24)","v":" "}," "," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q25=\" \",\" \",SUM(F25:O25)*C24)","v":" "},{"$f":"+IF(SUM(F25:O25)=0,\" \",SUM(F25:O25))","v":" "}]
[null,"    b) 100% UPT Available",null,{"$f":"+IF(D$11=\" \",\" \",ROUNDUP(D$11/$C24,0))","v":" "},{"$f":"+IF(D26=\" \",\" \",D26*C24)","v":" "},{"$f":"+IF(F$11=\" \",\" \",ROUNDUP(F$11/$C24,0))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(Q26=\" \",\" \",SUM(F26:O26)*C24)","v":" "},{"$f":"+IF(SUM(F26:O26)=0,\" \",SUM(F26:O26))","v":" "}]
[null,"    c) PPMT Option",null,{"$f":"+IF(D$12=\" \",\" \",ROUNDUP(D$12/$C24,0))","v":" "},{"$f":"+IF(D27=\" \",\" \",D27*C24)","v":" "},{"$f":"+IF(F$12=\" \",\" \",ROUNDUP(F$12/$C24,0))","v":" "}," "," "," "," "," "," "," "," ",null,{"$f":"+IF(Q27=\" \",\" \",Q27*C24)","v":" "},{"$f":"+IF(SUM(F27:O27)=0,\" \",SUM(F27:O27))","v":" "}]
[]
[]
[null,"B. Description of Options"]
[]
[null,"1. Sampling Frequency"]
[]
[null,"a) Annual = sample size during an entire year that is determined from analyzing the sample data you have entered.  A margin of safety has been built into this sample size to guard against fluctuations of service and usage patterns in your system.  It is logistically difficult to select a random sample for an entire year at the same time, since you may not know at the start of the year what vehicle trips you will operate for the entire year.  Most agencies choose to select a sample quarterly, monthly, or weekly."]
[]
[null,"b) Quarterly = sample size during an entire quarter if you choose to select a sample once every three months."]
[]
[null,"c) Monthly = sample size during an entire month if you choose to select a sample once every month."]
[]
[null,"d) Weekly = sample size during a week if you choose to select a sample once every week."]
[]
[null,"e) Choose the sampling frequency that works best for you. Shorter selection periods are usually easier to manage because you have a better idea what service you will operate, and the number of trips you must select per period is smaller. But due to rounding when the annual sample is allocated to each of these periods, the annual sample size is higher if you use shorter periods.  "]
[]
[null,"2. Grouping for Improving Sampling Efficiency"]
[]
[null,"        Grouping is one method for you to improve your sampling efficiency. For in-depth discussion of grouping, please refer to the NTD Sampling Manual sections on \"Route Grouping\"."]
[]
[null,"a) Without Grouping= you sample and estimate service consumed without dividing your overall operation into distinct groups."]
[]
[null,"b) With Grouping = you sample and estimate service consumed data by dividing your overall operation into two or more distinct groups."]
[]
[null,"c) Cells F7:O7 = group names if you use grouping and empty if you do not."]
[]
[null,"3. Other Options for Improving Sampling Efficiency"]
[]
[null,"a) If you will not be reporting a 100% count of UPT for a future NTD reporting year, you must use the Base option.  "]
[]
[null,"b) If you will be reporting a 100% count of UPT for a future NTD reporting year, you may use either the APTL option or the PPMT option.  You have both options for you to consider if you have entered your sample data on UPT, PMT, and PPMT into INPUT.  You only have the APTL option if you have entered your sample data on UPT and PMT but not on PPMT."]
[null," "]
[null,"c) When you do have both the APTL and PPMT options, you should choose the APTL option if it gives you a smaller annual sample size.  Otherwise you need to decide whether the extra effort required in estimating annual data for the NTD is more trouble than the smaller sample size that the PPMT option gives you.    "]

{"sheet":"CALCULATIONS","index":3,"n_rows":43,"n_cols":30,"rows_kept":43,"cut":false,"hidden":false,"merged":[[4,4,4,14],[4,19,4,29]]}
[null,"Calculations"]
[]
[null,"A. Sample Data Analysis",null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null," "]
[]
[null,null,null,"Without","With Grouping",null,null,null,null,null,null,null,null,null,null,null,null,null,"Without","With Grouping"]
[null," ",null,"Grouping",{"$f":"+IF(CALCULATIONS!E8=0,\" \",INPUT!D33)","v":""},{"$f":"+IF(CALCULATIONS!F8=0,\" \",INPUT!E33)","v":""},{"$f":"+IF(CALCULATIONS!G8=0,\" \",INPUT!F33)","v":""},{"$f":"+IF(CALCULATIONS!H8=0,\" \",INPUT!G33)","v":""},{"$f":"+IF(CALCULATIONS!I8=0,\" \",INPUT!H33)","v":""},{"$f":"+IF(CALCULATIONS!J8=0,\" \",INPUT!I33)","v":""},{"$f":"+IF(CALCULATIONS!K8=0,\" \",INPUT!J33)","v":""},{"$f":"+IF(CALCULATIONS!L8=0,\" \",INPUT!K33)","v":""},{"$f":"+IF(CALCULATIONS!M8=0,\" \",INPUT!L33)","v":""},{"$f":"+IF(CALCULATIONS!N8=0,\" \",INPUT!M33)","v":""},"All",null," ",null,"Grouping",{"$f":"+IF(CALCULATIONS!T8=0,\" \",INPUT!P33)","v":""},{"$f":"+IF(CALCULATIONS!U8=0,\" \",INPUT!Q33)","v":""},{"$f":"+IF(CALCULATIONS!V8=0,\" \",INPUT!R33)","v":""},{"$f":"+IF(CALCULATIONS!W8=0,\" \",INPUT!S33)","v":""},{"$f":"+IF(CALCULATIONS!X8=0,\" \",INPUT!T33)","v":""},{"$f":"+IF(CALCULATIONS!Y8=0,\" \",INPUT!U33)","v":""},{"$f":"+IF(CALCULATIONS!Z8=0,\" \",INPUT!V33)","v":""},{"$f":"+IF(CALCULATIONS!AA8=0,\" \",INPUT!W33)","v":""},{"$f":"+IF(CALCULATIONS!AB8=0,\" \",INPUT!X33)","v":""},{"$f":"+IF(CALCULATIONS!AC8=0,\" \",INPUT!Y33)","v":""},"All"]
[]
[null,"Group Size in the Sample",null,{"$f":"+IF(COUNT(PMT)=0,\" \",COUNT(PMT))","v":" "},{"$f":"+IF(INPUT!D33=\"\",\" \",COUNT(IF(Group=INPUT!D$33,PMT)))","v":" "},{"$f":"+IF(INPUT!E33=\"\",\" \",COUNT(IF(Group=INPUT!E$33,PMT)))","v":" "},{"$f":"+IF(INPUT!F33=\"\",\" \",COUNT(IF(Group=INPUT!F$33,PMT)))","v":" "},{"$f":"+IF(INPUT!G33=\"\",\" \",COUNT(IF(Group=INPUT!G$33,PMT)))","v":" "},{"$f":"+IF(INPUT!H33=\"\",\" \",COUNT(IF(Group=INPUT!H$33,PMT)))","v":" "},{"$f":"+IF(INPUT!I33=\"\",\" \",COUNT(IF(Group=INPUT!I$33,PMT)))","v":" "},{"$f":"+IF(INPUT!J33=\"\",\" \",COUNT(IF(Group=INPUT!J$33,PMT)))","v":" "},{"$f":"+IF(INPUT!K33=\"\",\" \",COUNT(IF(Group=INPUT!K$33,PMT)))","v":" "},{"$f":"+IF(INPUT!L33=\"\",\" \",COUNT(IF(Group=INPUT!L$33,PMT)))","v":" "},{"$f":"+IF(INPUT!M33=\"\",\" \",COUNT(IF(Group=INPUT!M$33,PMT)))","v":" "},{"$f":"+IF(SUM(E8:N8)=0,\" \",SUM(E8:N8))","v":" "},null,"Group Size in the Sample",null,{"$f":"+IF(COUNT(PMT_orig)=0,\" \",COUNT(PMT_orig))","v":" "},{"$f":"+IF(INPUT!P33=\"\",\" \",COUNT(IF(Group_orig=INPUT!P$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!Q33=\"\",\" \",COUNT(IF(Group_orig=INPUT!Q$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!R33=\"\",\" \",COUNT(IF(Group_orig=INPUT!R$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!S33=\"\",\" \",COUNT(IF(Group_orig=INPUT!S$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!T33=\"\",\" \",COUNT(IF(Group_orig=INPUT!T$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!U33=\"\",\" \",COUNT(IF(Group_orig=INPUT!U$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!V33=\"\",\" \",COUNT(IF(Group_orig=INPUT!V$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!W33=\"\",\" \",COUNT(IF(Group_orig=INPUT!W$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!X33=\"\",\" \",COUNT(IF(Group_orig=INPUT!X$33,PMT_orig)))","v":" "},{"$f":"+IF(INPUT!Y33=\"\",\" \",COUNT(IF(Group_orig=INPUT!Y$33,PMT_orig)))","v":" "},{"$f":"+IF(SUM(T8:AC8)=0,\" \",SUM(T8:AC8))","v":" "}]
[]
[null,"Average",null," ",null,null,null,null,null,null,null,null,null,null,null,null,"Average",null," "]
[null,"     UPT",null,{"$f":"+IF(SUM(UPT)=0,\" \",AVERAGE(UPT))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,E8=\" \"),\" \",AVERAGE(IF(Group=E6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,F8=\" \"),\" \",AVERAGE(IF(Group=F6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,G8=\" \"),\" \",AVERAGE(IF(Group=G6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,H8=\" \"),\" \",AVERAGE(IF(Group=H6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,I8=\" \"),\" \",AVERAGE(IF(Group=I6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,J8=\" \"),\" \",AVERAGE(IF(Group=J6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,K8=\" \"),\" \",AVERAGE(IF(Group=K6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,L8=\" \"),\" \",AVERAGE(IF(Group=L6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,M8=\" \"),\" \",AVERAGE(IF(Group=M6,UPT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,N8=\" \"),\" \",AVERAGE(IF(Group=N6,UPT)))","v":" "},{"$f":"+IF(SUM(SizeActual)=0,\" \",IF(SUM(E11:N11)=0,\" \",IF(E$8=\" \",0,E11*(INPUT!D$34/INPUT!N$34))+IF(F$8=\" \",0,F11*(INPUT!E$34/INPUT!N$34))+IF(G$8=\" \",0,G11*(INPUT!F$34/INPUT!N$34))+IF(H$8=\" \",0,H11*(INPUT!G$34/INPUT!N$34))+IF(I$8=\" \",0,I11*(INPUT!H$34/INPUT!N$34))+IF(J$8=\" \",0,J11*(INPUT!I$34/INPUT!N$34))+IF(K$8=\" \",0,K11*(INPUT!J$34/INPUT!N$34))+IF(L$8=\" \",0,L11*(INPUT!K$34/INPUT!N$34))+IF(M$8=\" \",0,M11*(INPUT!L$34/INPUT!N$34))+IF(N$8=\" \",0,N11*(INPUT!M$34/INPUT!N$34))))","v":" "},null,"     UPT",null,{"$f":"+IF(SUM(UPT_orig)=0,\" \",AVERAGE(UPT_orig))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,T8=\" \"),\" \",AVERAGE(IF(Group_orig=T6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,U8=\" \"),\" \",AVERAGE(IF(Group_orig=U6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,V8=\" \"),\" \",AVERAGE(IF(Group_orig=V6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,W8=\" \"),\" \",AVERAGE(IF(Group_orig=W6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,X8=\" \"),\" \",AVERAGE(IF(Group_orig=X6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,Y8=\" \"),\" \",AVERAGE(IF(Group_orig=Y6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,Z8=\" \"),\" \",AVERAGE(IF(Group_orig=Z6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,AA8=\" \"),\" \",AVERAGE(IF(Group_orig=AA6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,AB8=\" \"),\" \",AVERAGE(IF(Group_orig=AB6,UPT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,AC8=\" \"),\" \",AVERAGE(IF(Group_orig=AC6,UPT_orig)))","v":" "},{"$f":"+IF(SUM(SizeActual_orig)=0,\" \",IF(SUM(T11:AC11)=0,\" \",IF(T$8=\" \",0,T11*(INPUT!P$34/INPUT!Z$34))+IF(U$8=\" \",0,U11*(INPUT!Q$34/INPUT!Z$34))+IF(V$8=\" \",0,V11*(INPUT!R$34/INPUT!Z$34))+IF(W$8=\" \",0,W11*(INPUT!S$34/INPUT!Z$34))+IF(X$8=\" \",0,X11*(INPUT!T$34/INPUT!Z$34))+IF(Y$8=\" \",0,Y11*(INPUT!U$34/INPUT!Z$34))+IF(Z$8=\" \",0,Z11*(INPUT!V$34/INPUT!Z$34))+IF(AA$8=\" \",0,AA11*(INPUT!W$34/INPUT!Z$34))+IF(AB$8=\" \",0,AB11*(INPUT!X$34/INPUT!Z$34))+IF(AC$8=\" \",0,AC11*(INPUT!Y$34/INPUT!Z$34))))","v":" "}]
[null,"     PMT",null,{"$f":"+IF(SUM(PMT)=0,\" \",AVERAGE(PMT))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,E8=\" \"),\" \",AVERAGE(IF(Group=E6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,F8=\" \"),\" \",AVERAGE(IF(Group=F6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,G8=\" \"),\" \",AVERAGE(IF(Group=G6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,H8=\" \"),\" \",AVERAGE(IF(Group=H6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,I8=\" \"),\" \",AVERAGE(IF(Group=I6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,J8=\" \"),\" \",AVERAGE(IF(Group=J6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,K8=\" \"),\" \",AVERAGE(IF(Group=K6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,L8=\" \"),\" \",AVERAGE(IF(Group=L6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,M8=\" \"),\" \",AVERAGE(IF(Group=M6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,N8=\" \"),\" \",AVERAGE(IF(Group=N6,PMT)))","v":" "},{"$f":"+IF(SUM(INPUT!D34:M34)=0,\" \",IF(SUM(E12:N12)=0,\" \",IF(E$8=\" \",0,E12*(INPUT!D$34/INPUT!N$34))+IF(F$8=\" \",0,F12*(INPUT!E$34/INPUT!N$34))+IF(G$8=\" \",0,G12*(INPUT!F$34/INPUT!N$34))+IF(H$8=\" \",0,H12*(INPUT!G$34/INPUT!N$34))+IF(I$8=\" \",0,I12*(INPUT!H$34/INPUT!N$34))+IF(J$8=\" \",0,J12*(INPUT!I$34/INPUT!N$34))+IF(K$8=\" \",0,K12*(INPUT!J$34/INPUT!N$34))+IF(L$8=\" \",0,L12*(INPUT!K$34/INPUT!N$34))+IF(M$8=\" \",0,M12*(INPUT!L$34/INPUT!N$34))+IF(N$8=\" \",0,N12*(INPUT!M$34/INPUT!N$34))))","v":" "},null,"     PMT",null,{"$f":"+IF(SUM(PMT_orig)=0,\" \",AVERAGE(PMT_orig))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,T8=\" \"),\" \",AVERAGE(IF(Group_orig=T6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,U8=\" \"),\" \",AVERAGE(IF(Group_orig=U6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,V8=\" \"),\" \",AVERAGE(IF(Group_orig=V6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,W8=\" \"),\" \",AVERAGE(IF(Group_orig=W6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,X8=\" \"),\" \",AVERAGE(IF(Group_orig=X6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,Y8=\" \"),\" \",AVERAGE(IF(Group_orig=Y6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,Z8=\" \"),\" \",AVERAGE(IF(Group_orig=Z6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,AA8=\" \"),\" \",AVERAGE(IF(Group_orig=AA6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,AB8=\" \"),\" \",AVERAGE(IF(Group_orig=AB6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,AC8=\" \"),\" \",AVERAGE(IF(Group_orig=AC6,PMT_orig)))","v":" "},{"$f":"+IF(SUM(SizeActual_orig)=0,\" \",IF(SUM(T12:AC12)=0,\" \",IF(T$8=\" \",0,T12*(INPUT!P$34/INPUT!Z$34))+IF(U$8=\" \",0,U12*(INPUT!Q$34/INPUT!Z$34))+IF(V$8=\" \",0,V12*(INPUT!R$34/INPUT!Z$34))+IF(W$8=\" \",0,W12*(INPUT!S$34/INPUT!Z$34))+IF(X$8=\" \",0,X12*(INPUT!T$34/INPUT!Z$34))+IF(Y$8=\" \",0,Y12*(INPUT!U$34/INPUT!Z$34))+IF(Z$8=\" \",0,Z12*(INPUT!V$34/INPUT!Z$34))+IF(AA$8=\" \",0,AA12*(INPUT!W$34/INPUT!Z$34))+IF(AB$8=\" \",0,AB12*(INPUT!X$34/INPUT!Z$34))+IF(AC$8=\" \",0,AC12*(INPUT!Y$34/INPUT!Z$34))))","v":" "}]
[null,"     PPMT",null,{"$f":"+IF(SUM(PPMT)=0,\" \",AVERAGE(PPMT))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,E8=\" \"),\" \",AVERAGE(IF(Group=E6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,F8=\" \"),\" \",AVERAGE(IF(Group=F6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,G8=\" \"),\" \",AVERAGE(IF(Group=G6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,H8=\" \"),\" \",AVERAGE(IF(Group=H6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,I8=\" \"),\" \",AVERAGE(IF(Group=I6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,J8=\" \"),\" \",AVERAGE(IF(Group=J6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,K8=\" \"),\" \",AVERAGE(IF(Group=K6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,L8=\" \"),\" \",AVERAGE(IF(Group=L6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,M8=\" \"),\" \",AVERAGE(IF(Group=M6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,N8=\" \"),\" \",AVERAGE(IF(Group=N6,PPMT)))","v":" "},{"$f":"+IF(SUM(INPUT!D34:M34)=0,\" \",IF(SUM(E13:N13)=0,\" \",IF(E$8=\" \",0,E13*(INPUT!D$34/INPUT!N$34)+IF(F$8=\" \",0,F13*(INPUT!E$34/INPUT!N$34))+IF(G$8=\" \",0,G13*(INPUT!F$34/INPUT!N$34))+IF(H$8=\" \",0,H13*(INPUT!G$34/INPUT!N$34))+IF(I$8=\" \",0,I13*(INPUT!H$34/INPUT!N$34))+IF(J$8=\" \",0,J13*(INPUT!I$34/INPUT!N$34))+IF(K$8=\" \",0,K13*(INPUT!J$34/INPUT!N$34))+IF(L$8=\" \",0,L13*(INPUT!K$34/INPUT!N$34))+IF(M$8=\" \",0,M13*(INPUT!L$34/INPUT!N$34))+IF(N$8=\" \",0,N13*(INPUT!M$34/INPUT!N$34)))))","v":" "},null,"     PPMT",null,{"$f":"+IF(SUM(PPMT_orig)=0,\" \",AVERAGE(PPMT_orig))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,T8=\" \"),\" \",AVERAGE(IF(Group_orig=T6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,U8=\" \"),\" \",AVERAGE(IF(Group_orig=U6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,V8=\" \"),\" \",AVERAGE(IF(Group_orig=V6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,W8=\" \"),\" \",AVERAGE(IF(Group_orig=W6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,X8=\" \"),\" \",AVERAGE(IF(Group_orig=X6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,Y8=\" \"),\" \",AVERAGE(IF(Group_orig=Y6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,Z8=\" \"),\" \",AVERAGE(IF(Group_orig=Z6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,AA8=\" \"),\" \",AVERAGE(IF(Group_orig=AA6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,AB8=\" \"),\" \",AVERAGE(IF(Group_orig=AB6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,AC8=\" \"),\" \",AVERAGE(IF(Group_orig=AC6,PPMT_orig)))","v":" "},{"$f":"+IF(SUM(SizeActual_orig)=0,\" \",IF(SUM(T13:AC13)=0,\" \",IF(T$8=\" \",0,T13*(INPUT!P$34/INPUT!Z$34))+IF(U$8=\" \",0,U13*(INPUT!Q$34/INPUT!Z$34))+IF(V$8=\" \",0,V13*(INPUT!R$34/INPUT!Z$34))+IF(W$8=\" \",0,W13*(INPUT!S$34/INPUT!Z$34))+IF(X$8=\" \",0,X13*(INPUT!T$34/INPUT!Z$34))+IF(Y$8=\" \",0,Y13*(INPUT!U$34/INPUT!Z$34))+IF(Z$8=\" \",0,Z13*(INPUT!V$34/INPUT!Z$34))+IF(AA$8=\" \",0,AA13*(INPUT!W$34/INPUT!Z$34))+IF(AB$8=\" \",0,AB13*(INPUT!X$34/INPUT!Z$34))+IF(AC$8=\" \",0,AC13*(INPUT!Y$34/INPUT!Z$34))))","v":" "}]
[null,"     APTL",null,{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0),\" \",D12/D11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,E8=\" \"),\" \",E12/E11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,F8=\" \"),\" \",F12/F11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,G8=\" \"),\" \",G12/G11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,H8=\" \"),\" \",H12/H11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,I8=\" \"),\" \",I12/I11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,J8=\" \"),\" \",J12/J11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,K8=\" \"),\" \",K12/K11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,L8=\" \"),\" \",L12/L11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,M8=\" \"),\" \",M12/M11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,N8=\" \"),\" \",N12/N11)","v":" "},{"$f":"+IF(O12=\" \",\" \",IF(SUM(E14:N14)=0,\" \",O12/O11))","v":" "},null,"     APTL",null,{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0),\" \",S12/S11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,T8=\" \"),\" \",T12/T11)","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(AD12=\" \",\" \",IF(SUM(T14:AC14)=0,\" \",AD12/AD11))","v":" "}]
[null,"     PMT/PPMT",null,{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0),\" \",D12/D13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,E8=\" \"),\" \",E12/E13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,F8=\" \"),\" \",F12/F13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,G8=\" \"),\" \",G12/G13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,H8=\" \"),\" \",H12/H13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,I8=\" \"),\" \",I12/I13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,J8=\" \"),\" \",J12/J13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,K8=\" \"),\" \",K12/K13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,L8=\" \"),\" \",L12/L13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,M8=\" \"),\" \",M12/M13)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,N8=\" \"),\" \",N12/N13)","v":" "},{"$f":"+IF(O12=\" \",\" \",IF(SUM(E15:N15)=0,\" \",O12/O13))","v":" "},null,"     PMT/PPMT",null,{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0),\" \",S12/S13)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,T8=\" \"),\" \",T12/T13)","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(AD12=\" \",\" \",IF(SUM(T15:AC15)=0,\" \",AD12/AD13))","v":" "}]
[]
[null,"Unit Coefficient of Variation",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Unit Coefficient of Variation"]
[null,"     UPT",null,{"$f":"+IF(SUM(UPT)=0,\" \",SQRT(VAR(UPT))/D11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,E8=\" \"),\" \",SQRT(VAR(IF(Group=E6,UPT)))/E11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,F8=\" \"),\" \",SQRT(VAR(IF(Group=F6,UPT)))/F11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,G8=\" \"),\" \",SQRT(VAR(IF(Group=G6,UPT)))/G11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,H8=\" \"),\" \",SQRT(VAR(IF(Group=H6,UPT)))/H11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,I8=\" \"),\" \",SQRT(VAR(IF(Group=I6,UPT)))/I11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,J8=\" \"),\" \",SQRT(VAR(IF(Group=J6,UPT)))/J11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,K8=\" \"),\" \",SQRT(VAR(IF(Group=K6,UPT)))/K11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,L8=\" \"),\" \",SQRT(VAR(IF(Group=L6,UPT)))/L11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,M8=\" \"),\" \",SQRT(VAR(IF(Group=M6,UPT)))/M11)","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,N8=\" \"),\" \",SQRT(VAR(IF(Group=N6,UPT)))/N11)","v":" "},{"$f":"+IF(SUM(INPUT!D34:M34)=0,\" \",IF(SUM(E18:N18)=0,\" \",SQRT(IF(E$8=\" \",0,(INPUT!D$34/INPUT!N$34)*($E18*$E11)^2)+IF(F$8=\" \",0,(INPUT!E$34/INPUT!N$34)*($F18*$F11)^2)+IF(G$8=\" \",0,(INPUT!F$34/INPUT!N$34)*($G18*$G11)^2)+IF(H$8=\" \",0,(INPUT!G$34/INPUT!N$34)*($H18*$H11)^2)+IF(I$8=\" \",0,(INPUT!H$34/INPUT!N$34)*($I18*$I11)^2)+IF(J$8=\" \",0,(INPUT!I$34/INPUT!N$34)*($J18*$J11)^2)+IF(K$8=\" \",0,(INPUT!J$34/INPUT!N$34)*($K18*$K11)^2)+IF(L$8=\" \",0,(INPUT!K$34/INPUT!N$34)*($L18*$L11)^2)+IF(M$8=\" \",0,(INPUT!L$34/INPUT!N$34)*($M18*$M11)^2)+IF(N$8=\" \",0,(INPUT!M$34/INPUT!N$34)*($N18*$N11)^2))/O11))","v":" "},null,"     UPT",null,{"$f":"+IF(SUM(UPT_orig)=0,\" \",SQRT(VAR(UPT_orig))/S11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,T8=\" \"),\" \",SQRT(VAR(IF(Group_orig=T6,UPT_orig)))/T11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,U8=\" \"),\" \",SQRT(VAR(IF(Group_orig=U6,UPT_orig)))/U11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,V8=\" \"),\" \",SQRT(VAR(IF(Group_orig=V6,UPT_orig)))/V11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,W8=\" \"),\" \",SQRT(VAR(IF(Group_orig=W6,UPT_orig)))/W11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,X8=\" \"),\" \",SQRT(VAR(IF(Group_orig=X6,UPT_orig)))/X11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,Y8=\" \"),\" \",SQRT(VAR(IF(Group_orig=Y6,UPT_orig)))/Y11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,Z8=\" \"),\" \",SQRT(VAR(IF(Group_orig=Z6,UPT_orig)))/Z11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,AA8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AA6,UPT_orig)))/AA11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,AB8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AB6,UPT_orig)))/AB11)","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,AC8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AC6,UPT_orig)))/AC11)","v":" "},{"$f":"+IF(SUM(INPUT!P$34:Y$34)=0,\" \",IF(SUM(T18:AC18)=0,\" \",SQRT(IF(T$8=\" \",0,(INPUT!P$34/INPUT!Z$34)*($T18*$T11)^2)+IF(U$8=\" \",0,(INPUT!Q$34/INPUT!Z$34)*($U18*$U11)^2)+IF(V$8=\" \",0,(INPUT!R$34/INPUT!Z$34)*($V18*$V11)^2)+IF(W$8=\" \",0,(INPUT!S$34/INPUT!Z$34)*($W18*$W11)^2)+IF(X$8=\" \",0,(INPUT!T$34/INPUT!Z$34)*($X18*$X11)^2)+IF(Y$8=\" \",0,(INPUT!U$34/INPUT!Z$34)*($Y18*$Y11)^2)+IF(Z$8=\" \",0,(INPUT!V$34/INPUT!Z$34)*($Z18*$Z11)^2)+IF(AA$8=\" \",0,(INPUT!W$34/INPUT!Z$34)*($AA18*$AA11)^2)+IF(AB$8=\" \",0,(INPUT!X$34/INPUT!Z$34)*($AB18*$AB11)^2)+IF(AC$8=\" \",0,(INPUT!Y$34/INPUT!Z$34)*($AC18*$AC11)^2))/AD11))","v":" "}]
[null,"     PMT",null,{"$f":"+IF(SUM(PMT)=0,\" \",SQRT(VAR(PMT))/D12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,E8=\" \"),\" \",SQRT(VAR(IF(Group=E6,PMT)))/E12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,F8=\" \"),\" \",SQRT(VAR(IF(Group=F6,PMT)))/F12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,G8=\" \"),\" \",SQRT(VAR(IF(Group=G6,PMT)))/G12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,H8=\" \"),\" \",SQRT(VAR(IF(Group=H6,PMT)))/H12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,I8=\" \"),\" \",SQRT(VAR(IF(Group=I6,PMT)))/I12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,J8=\" \"),\" \",SQRT(VAR(IF(Group=J6,PMT)))/J12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,K8=\" \"),\" \",SQRT(VAR(IF(Group=K6,PMT)))/K12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,L8=\" \"),\" \",SQRT(VAR(IF(Group=L6,PMT)))/L12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,M8=\" \"),\" \",SQRT(VAR(IF(Group=M6,PMT)))/M12)","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,N8=\" \"),\" \",SQRT(VAR(IF(Group=N6,PMT)))/N12)","v":" "},{"$f":"+IF(SUM(INPUT!D34:M34)=0,\" \",IF(SUM(E19:N19)=0,\" \",SQRT(IF(E$8=\" \",0,(INPUT!D$34/INPUT!N$34)*($E19*$E$12)^2)+IF(F$8=\" \",0,(INPUT!E$34/INPUT!N$34)*($F19*$F$12)^2)+IF(G$8=\" \",0,(INPUT!F$34/INPUT!N$34)*($G19*$G$12)^2)+IF(H$8=\" \",0,(INPUT!G$34/INPUT!N$34)*($H19*$H$12)^2)+IF(I$8=\" \",0,(INPUT!H$34/INPUT!N$34)*($I19*$I$12)^2)+IF(J$8=\" \",0,(INPUT!I$34/INPUT!N$34)*($J19*$J$12)^2)+IF(K$8=\" \",0,(INPUT!J$34/INPUT!N$34)*($K19*$K$12)^2)+IF(L$8=\" \",0,(INPUT!K$34/INPUT!N$34)*($L19*$L$12)^2)+IF(M$8=\" \",0,(INPUT!L$34/INPUT!N$34)*($M19*$M$12)^2)+IF(N$8=\" \",0,(INPUT!M$34/INPUT!N$34)*($N19*$N$12)^2))/O$12))","v":" "},null,"     PMT",null,{"$f":"+IF(SUM(PMT_orig)=0,\" \",SQRT(VAR(PMT_orig))/S12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,T8=\" \"),\" \",SQRT(VAR(IF(Group_orig=T6,PMT_orig)))/T12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,U8=\" \"),\" \",SQRT(VAR(IF(Group_orig=U6,PMT_orig)))/U12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,V8=\" \"),\" \",SQRT(VAR(IF(Group_orig=V6,PMT_orig)))/V12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,W8=\" \"),\" \",SQRT(VAR(IF(Group_orig=W6,PMT_orig)))/W12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,X8=\" \"),\" \",SQRT(VAR(IF(Group_orig=X6,PMT_orig)))/X12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,Y8=\" \"),\" \",SQRT(VAR(IF(Group_orig=Y6,PMT_orig)))/Y12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,Z8=\" \"),\" \",SQRT(VAR(IF(Group_orig=Z6,PMT_orig)))/Z12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,AA8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AA6,PMT_orig)))/AA12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,AB8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AB6,PMT_orig)))/AB12)","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,AC8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AC6,PMT_orig)))/AC12)","v":" "},{"$f":"+IF(SUM(INPUT!P$34:Y$34)=0,\" \",IF(SUM(T19:AC19)=0,\" \",SQRT(IF(T$8=\" \",0,(INPUT!P$34/INPUT!Z$34)*($T19*$T12)^2)+IF(U$8=\" \",0,(INPUT!Q$34/INPUT!Z$34)*($U19*$U12)^2)+IF(V$8=\" \",0,(INPUT!R$34/INPUT!Z$34)*($V19*$V12)^2)+IF(W$8=\" \",0,(INPUT!S$34/INPUT!Z$34)*($W19*$W12)^2)+IF(X$8=\" \",0,(INPUT!T$34/INPUT!Z$34)*($X19*$X12)^2)+IF(Y$8=\" \",0,(INPUT!U$34/INPUT!Z$34)*($Y19*$Y12)^2)+IF(Z$8=\" \",0,(INPUT!V$34/INPUT!Z$34)*($Z19*$Z12)^2)+IF(AA$8=\" \",0,(INPUT!W$34/INPUT!Z$34)*($AA19*$AA12)^2)+IF(AB$8=\" \",0,(INPUT!X$34/INPUT!Z$34)*($AB19*$AB12)^2)+IF(AC$8=\" \",0,(INPUT!Y$34/INPUT!Z$34)*($AC19*$AC12)^2))/AD12))","v":" "}]
[null,"     PPMT",null,{"$f":"+IF(SUM(PPMT)=0,\" \",SQRT(VAR(PPMT))/D13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,E8=\" \"),\" \",SQRT(VAR(IF(Group=E6,PPMT)))/E13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,F8=\" \"),\" \",SQRT(VAR(IF(Group=F6,PPMT)))/F13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,G8=\" \"),\" \",SQRT(VAR(IF(Group=G6,PPMT)))/G13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,H8=\" \"),\" \",SQRT(VAR(IF(Group=H6,PPMT)))/H13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,I8=\" \"),\" \",SQRT(VAR(IF(Group=I6,PPMT)))/I13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,J8=\" \"),\" \",SQRT(VAR(IF(Group=J6,PPMT)))/J13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,K8=\" \"),\" \",SQRT(VAR(IF(Group=K6,PPMT)))/K13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,L8=\" \"),\" \",SQRT(VAR(IF(Group=L6,PPMT)))/L13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,M8=\" \"),\" \",SQRT(VAR(IF(Group=M6,PPMT)))/M13)","v":" "},{"$f":"+IF(OR(SUM(PPMT)=0,N8=\" \"),\" \",SQRT(VAR(IF(Group=N6,PPMT)))/N13)","v":" "},{"$f":"+IF(SUM(INPUT!D34:M34)=0,\" \",IF(SUM(E20:N20)=0,\" \",SQRT(IF(E$8=\" \",0,(INPUT!D$34/INPUT!N$34)*($E20*$E13)^2)+IF(F$8=\" \",0,(INPUT!E$34/INPUT!N$34)*($F20*$F13)^2)+IF(G$8=\" \",0,(INPUT!F$34/INPUT!N$34)*($G20*$G13)^2)+IF(H$8=\" \",0,(INPUT!G$34/INPUT!N$34)*($H20*$H13)^2)+IF(I$8=\" \",0,(INPUT!H$34/INPUT!N$34)*($I20*$I13)^2)+IF(J$8=\" \",0,(INPUT!I$34/INPUT!N$34)*($J20*$J13)^2)+IF(K$8=\" \",0,(INPUT!J$34/INPUT!N$34)*($K20*$K13)^2)+IF(L$8=\" \",0,(INPUT!K$34/INPUT!N$34)*($L20*$L13)^2)+IF(M$8=\" \",0,(INPUT!L$34/INPUT!N$34)*($M20*$M13)^2)+IF(N$8=\" \",0,(INPUT!M$34/INPUT!N$34)*($N20*$N13)^2))/O13))","v":" "},null,"     PPMT",null,{"$f":"+IF(SUM(PPMT_orig)=0,\" \",SQRT(VAR(PPMT_orig))/S13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,T8=\" \"),\" \",SQRT(VAR(IF(Group_orig=T6,PPMT_orig)))/T13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,U8=\" \"),\" \",SQRT(VAR(IF(Group_orig=U6,PPMT_orig)))/U13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,V8=\" \"),\" \",SQRT(VAR(IF(Group_orig=V6,PPMT_orig)))/V13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,W8=\" \"),\" \",SQRT(VAR(IF(Group_orig=W6,PPMT_orig)))/W13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,X8=\" \"),\" \",SQRT(VAR(IF(Group_orig=X6,PPMT_orig)))/X13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,Y8=\" \"),\" \",SQRT(VAR(IF(Group_orig=Y6,PPMT_orig)))/Y13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,Z8=\" \"),\" \",SQRT(VAR(IF(Group_orig=Z6,PPMT_orig)))/Z13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,AA8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AA6,PPMT_orig)))/AA13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,AB8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AB6,PPMT_orig)))/AB13)","v":" "},{"$f":"+IF(OR(SUM(PPMT_orig)=0,AC8=\" \"),\" \",SQRT(VAR(IF(Group_orig=AC6,PPMT_orig)))/AC13)","v":" "},{"$f":"+IF(SUM(INPUT!P$34:Y$34)=0,\" \",IF(SUM(T20:AC20)=0,\" \",SQRT(IF(T$8=\" \",0,(INPUT!P$34/INPUT!Z$34)*($T20*$T13)^2)+IF(U$8=\" \",0,(INPUT!Q$34/INPUT!Z$34)*($U20*$U13)^2)+IF(V$8=\" \",0,(INPUT!R$34/INPUT!Z$34)*($V20*$V13)^2)+IF(W$8=\" \",0,(INPUT!S$34/INPUT!Z$34)*($W20*$W13)^2)+IF(X$8=\" \",0,(INPUT!T$34/INPUT!Z$34)*($X20*$X13)^2)+IF(Y$8=\" \",0,(INPUT!U$34/INPUT!Z$34)*($Y20*$Y13)^2)+IF(Z$8=\" \",0,(INPUT!V$34/INPUT!Z$34)*($Z20*$Z13)^2)+IF(AA$8=\" \",0,(INPUT!W$34/INPUT!Z$34)*($AA20*$AA13)^2)+IF(AB$8=\" \",0,(INPUT!X$34/INPUT!Z$34)*($AB20*$AB13)^2)+IF(AC$8=\" \",0,(INPUT!Y$34/INPUT!Z$34)*($AC20*$AC13)^2))/AD13))","v":" "}]
[null,"     APTL-100% UPT by Group",null,{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0),\" \",SQRT(D18^2+D19^2-2*D26*(D18*D19)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,E$8=\" \"),\" \",SQRT(E$19^2+E$18^2-2*E$26*(E$19*E$18)))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(SUM(INPUT!D$34:M$34)=0,\" \",IF(SUM(E21:N21)=0,\" \",SQRT(IF(E$8=\" \",0,(INPUT!D$34/INPUT!N$34)*($E21*$E$12)^2)+IF(F$8=\" \",0,(INPUT!E$34/INPUT!N$34)*($F21*$F$12)^2)+IF(G$8=\" \",0,(INPUT!F$34/INPUT!N$34)*($G21*$G$12)^2)+IF(H$8=\" \",0,(INPUT!G$34/INPUT!N$34)*($H21*$H$12)^2)+IF(I$8=\" \",0,(INPUT!H$34/INPUT!N$34)*($I21*$I$12)^2)+IF(J$8=\" \",0,(INPUT!I$34/INPUT!N$34)*($J21*$J$12)^2)+IF(K$8=\" \",0,(INPUT!J$34/INPUT!N$34)*($K21*$K$12)^2)+IF(L$8=\" \",0,(INPUT!K$34/INPUT!N$34)*($L21*$L$12)^2)+IF(M$8=\" \",0,(INPUT!L$34/INPUT!N$34)*($M21*$M$12)^2)+IF(N$8=\" \",0,(INPUT!M$34/INPUT!N$34)*($N21*$N$12)^2))/O$12))","v":" "},null,"     APTL-100% UPT by Group",null,{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0),\" \",SQRT(S18^2+S19^2-2*S26*(S18*S19)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,T$8=\" \"),\" \",SQRT(T$19^2+T$18^2-2*T$26*(T$19*T$18)))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(SUM(INPUT!P$34:Y$34)=0,\" \",IF(SUM(T21:AC21)=0,\" \",SQRT(IF(T$8=\" \",0,(INPUT!P$34/INPUT!Z$34)*($T21*$T12)^2)+IF(U$8=\" \",0,(INPUT!Q$34/INPUT!Z$34)*($U21*$U12)^2)+IF(V$8=\" \",0,(INPUT!R$34/INPUT!Z$34)*($V21*$V12)^2)+IF(W$8=\" \",0,(INPUT!S$34/INPUT!Z$34)*($W21*$W12)^2)+IF(X$8=\" \",0,(INPUT!T$34/INPUT!Z$34)*($X21*$X12)^2)+IF(Y$8=\" \",0,(INPUT!U$34/INPUT!Z$34)*($Y21*$Y12)^2)+IF(Z$8=\" \",0,(INPUT!V$34/INPUT!Z$34)*($Z21*$Z12)^2)+IF(AA$8=\" \",0,(INPUT!W$34/INPUT!Z$34)*($AA21*$AA12)^2)+IF(AB$8=\" \",0,(INPUT!X$34/INPUT!Z$34)*($AB21*$AB12)^2)+IF(AC$8=\" \",0,(INPUT!Y$34/INPUT!Z$34)*($AC21*$AC12)^2))/AD12))","v":" "}]
[null,"     APTL-100% UPT Total",null,"N/A",{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,E$8=\" \"),\" \",SQRT(E$19^2+($O14/E14)^2*E$18^2-2*E$26*($O14/E14)*(E$19*E$18)))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(SUM(INPUT!D$34:M$34)=0,\" \",IF(SUM(E22:N22)=0,\" \",SQRT(IF(E$8=\" \",0,(INPUT!D$34/INPUT!N$34)*($E22*$E$12)^2)+IF(F$8=\" \",0,(INPUT!E$34/INPUT!N$34)*($F22*$F$12)^2)+IF(G$8=\" \",0,(INPUT!F$34/INPUT!N$34)*($G22*$G$12)^2)+IF(H$8=\" \",0,(INPUT!G$34/INPUT!N$34)*($H22*$H$12)^2)+IF(I$8=\" \",0,(INPUT!H$34/INPUT!N$34)*($I22*$I$12)^2)+IF(J$8=\" \",0,(INPUT!I$34/INPUT!N$34)*($J22*$J$12)^2)+IF(K$8=\" \",0,(INPUT!J$34/INPUT!N$34)*($K22*$K$12)^2)+IF(L$8=\" \",0,(INPUT!K$34/INPUT!N$34)*($L22*$L$12)^2)+IF(M$8=\" \",0,(INPUT!L$34/INPUT!N$34)*($M22*$M$12)^2)+IF(N$8=\" \",0,(INPUT!M$34/INPUT!N$34)*($N22*$N$12)^2))/O$12))","v":" "},null,"     APTL-100% UPT Total",null,"N/A",{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,T$8=\" \"),\" \",SQRT(T$19^2+($O14/T14)^2*T$18^2-2*T$26*($O14/T14)*(T$19*T$18)))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(SUM(INPUT!P$34:Y$34)=0,\" \",IF(SUM(T22:AC22)=0,\" \",SQRT(IF(T$8=\" \",0,(INPUT!P$34/INPUT!Z$34)*($T22*$T12)^2)+IF(U$8=\" \",0,(INPUT!Q$34/INPUT!Z$34)*($U22*$U12)^2)+IF(V$8=\" \",0,(INPUT!R$34/INPUT!Z$34)*($V22*$V12)^2)+IF(W$8=\" \",0,(INPUT!S$34/INPUT!Z$34)*($W22*$W12)^2)+IF(X$8=\" \",0,(INPUT!T$34/INPUT!Z$34)*($X22*$X12)^2)+IF(Y$8=\" \",0,(INPUT!U$34/INPUT!Z$34)*($Y22*$Y12)^2)+IF(Z$8=\" \",0,(INPUT!V$34/INPUT!Z$34)*($Z22*$Z12)^2)+IF(AA$8=\" \",0,(INPUT!W$34/INPUT!Z$34)*($AA22*$AA12)^2)+IF(AB$8=\" \",0,(INPUT!X$34/INPUT!Z$34)*($AB22*$AB12)^2)+IF(AC$8=\" \",0,(INPUT!Y$34/INPUT!Z$34)*($AC22*$AC12)^2))/AD12))","v":" "}]
[null,"     PMT/PPMT",null,{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0),\" \",SQRT(D19^2+D20^2-2*D27*(D19*D20)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,E$8=\" \"),\" \",SQRT(E$19^2+E$20^2-2*E$27*(E$20*E$19)))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(SUM(INPUT!D$34:M$34)=0,\" \",IF(SUM(E23:N23)=0,\" \",SQRT(IF(E$8=\" \",0,(INPUT!D$34/INPUT!N$34)*($E23*$E$12)^2)+IF(F$8=\" \",0,(INPUT!E$34/INPUT!N$34)*($F23*$F$12)^2)+IF(G$8=\" \",0,(INPUT!F$34/INPUT!N$34)*($G23*$G$12)^2)+IF(H$8=\" \",0,(INPUT!G$34/INPUT!N$34)*($H23*$H$12)^2)+IF(I$8=\" \",0,(INPUT!H$34/INPUT!N$34)*($I23*$I$12)^2)+IF(J$8=\" \",0,(INPUT!I$34/INPUT!N$34)*($J23*$J$12)^2)+IF(K$8=\" \",0,(INPUT!J$34/INPUT!N$34)*($K23*$K$12)^2)+IF(L$8=\" \",0,(INPUT!K$34/INPUT!N$34)*($L23*$L$12)^2)+IF(M$8=\" \",0,(INPUT!L$34/INPUT!N$34)*($M23*$M$12)^2)+IF(N$8=\" \",0,(INPUT!M$34/INPUT!N$34)*($N23*$N$12)^2))/O$12))","v":" "},null,"     PMT/PPMT",null,{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0),\" \",SQRT(S19^2+S20^2-2*S27*(S19*S20)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,T$8=\" \"),\" \",SQRT(T$19^2+T$20^2-2*T$27*(T$20*T$19)))","v":" "}," "," "," "," "," "," "," "," "," ",{"$f":"+IF(SUM(INPUT!P$34:Y$34)=0,\" \",IF(SUM(T23:AC23)=0,\" \",SQRT(IF(T$8=\" \",0,(INPUT!P$34/INPUT!Z$34)*($T23*$T12)^2)+IF(U$8=\" \",0,(INPUT!Q$34/INPUT!Z$34)*($U23*$U12)^2)+IF(V$8=\" \",0,(INPUT!R$34/INPUT!Z$34)*($V23*$V12)^2)+IF(W$8=\" \",0,(INPUT!S$34/INPUT!Z$34)*($W23*$W12)^2)+IF(X$8=\" \",0,(INPUT!T$34/INPUT!Z$34)*($X23*$X12)^2)+IF(Y$8=\" \",0,(INPUT!U$34/INPUT!Z$34)*($Y23*$Y12)^2)+IF(Z$8=\" \",0,(INPUT!V$34/INPUT!Z$34)*($Z23*$Z12)^2)+IF(AA$8=\" \",0,(INPUT!W$34/INPUT!Z$34)*($AA23*$AA12)^2)+IF(AB$8=\" \",0,(INPUT!X$34/INPUT!Z$34)*($AB23*$AB12)^2)+IF(AC$8=\" \",0,(INPUT!Y$34/INPUT!Z$34)*($AC23*$AC12)^2))/AD12))","v":" "}]
[]
[null,"Correlation Coefficient",null,null,null,null,null,null,null,null,null,null,null,null," ",null,"Correlation Coefficient",null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,"     UPT & PMT",null,{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0),\" \",CORREL(UPT,PMT))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,E8=\" \"),\" \",CORREL(IF(Group=E6,UPT),IF(Group=E6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,F8=\" \"),\" \",CORREL(IF(Group=F6,UPT),IF(Group=F6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,G8=\" \"),\" \",CORREL(IF(Group=G6,UPT),IF(Group=G6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,H8=\" \"),\" \",CORREL(IF(Group=H6,UPT),IF(Group=H6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,I8=\" \"),\" \",CORREL(IF(Group=I6,UPT),IF(Group=I6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,J8=\" \"),\" \",CORREL(IF(Group=J6,UPT),IF(Group=J6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,K8=\" \"),\" \",CORREL(IF(Group=K6,UPT),IF(Group=K6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,L8=\" \"),\" \",CORREL(IF(Group=L6,UPT),IF(Group=L6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,M8=\" \"),\" \",CORREL(IF(Group=M6,UPT),IF(Group=M6,PMT)))","v":" "},{"$f":"+IF(OR(SUM(UPT)=0,SUM(PMT)=0,N8=\" \"),\" \",CORREL(IF(Group=N6,UPT),IF(Group=N6,PMT)))","v":" "},"N/A",null,"     UPT & PMT",null,{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0),\" \",CORREL(UPT_orig,PMT_orig))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,T8=\" \"),\" \",CORREL(IF(Group_orig=T6,UPT_orig),IF(Group_orig=T6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,U8=\" \"),\" \",CORREL(IF(Group_orig=U6,UPT_orig),IF(Group_orig=U6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,V8=\" \"),\" \",CORREL(IF(Group_orig=V6,UPT_orig),IF(Group_orig=V6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,W8=\" \"),\" \",CORREL(IF(Group_orig=W6,UPT_orig),IF(Group_orig=W6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,X8=\" \"),\" \",CORREL(IF(Group_orig=X6,UPT_orig),IF(Group_orig=X6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,Y8=\" \"),\" \",CORREL(IF(Group_orig=Y6,UPT_orig),IF(Group_orig=Y6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,Z8=\" \"),\" \",CORREL(IF(Group_orig=Z6,UPT_orig),IF(Group_orig=Z6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,AA8=\" \"),\" \",CORREL(IF(Group_orig=AA6,UPT_orig),IF(Group_orig=AA6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,AB8=\" \"),\" \",CORREL(IF(Group_orig=AB6,UPT_orig),IF(Group_orig=AB6,PMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(UPT_orig)=0,SUM(PMT_orig)=0,AC8=\" \"),\" \",CORREL(IF(Group_orig=AC6,UPT_orig),IF(Group_orig=AC6,PMT_orig)))","v":" "},"N/A"]
[null,"     PMT & PPMT",null,{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0),\" \",CORREL(PMT,PPMT))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,E8=\" \"),\" \",CORREL(IF(Group=E6,PMT),IF(Group=E6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,F8=\" \"),\" \",CORREL(IF(Group=F6,PMT),IF(Group=F6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,G8=\" \"),\" \",CORREL(IF(Group=G6,PMT),IF(Group=G6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,H8=\" \"),\" \",CORREL(IF(Group=H6,PMT),IF(Group=H6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,I8=\" \"),\" \",CORREL(IF(Group=I6,PMT),IF(Group=I6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,J8=\" \"),\" \",CORREL(IF(Group=J6,PMT),IF(Group=J6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,K8=\" \"),\" \",CORREL(IF(Group=K6,PMT),IF(Group=K6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,L8=\" \"),\" \",CORREL(IF(Group=L6,PMT),IF(Group=L6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,M8=\" \"),\" \",CORREL(IF(Group=M6,PMT),IF(Group=M6,PPMT)))","v":" "},{"$f":"+IF(OR(SUM(PMT)=0,SUM(PPMT)=0,N8=\" \"),\" \",CORREL(IF(Group=N6,PMT),IF(Group=N6,PPMT)))","v":" "},"N/A",null,"     PMT & PPMT",null,{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0),\" \",CORREL(PMT_orig,PPMT_orig))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,T8=\" \"),\" \",CORREL(IF(Group_orig=T6,PMT_orig),IF(Group_orig=T6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,U8=\" \"),\" \",CORREL(IF(Group_orig=U6,PMT_orig),IF(Group_orig=U6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,V8=\" \"),\" \",CORREL(IF(Group_orig=V6,PMT_orig),IF(Group_orig=V6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,W8=\" \"),\" \",CORREL(IF(Group_orig=W6,PMT_orig),IF(Group_orig=W6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,X8=\" \"),\" \",CORREL(IF(Group_orig=X6,PMT_orig),IF(Group_orig=X6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,Y8=\" \"),\" \",CORREL(IF(Group_orig=Y6,PMT_orig),IF(Group_orig=Y6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,Z8=\" \"),\" \",CORREL(IF(Group_orig=Z6,PMT_orig),IF(Group_orig=Z6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,AA8=\" \"),\" \",CORREL(IF(Group_orig=AA6,PMT_orig),IF(Group_orig=AA6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,AB8=\" \"),\" \",CORREL(IF(Group_orig=AB6,PMT_orig),IF(Group_orig=AB6,PPMT_orig)))","v":" "},{"$f":"+IF(OR(SUM(PMT_orig)=0,SUM(PPMT_orig)=0,AC8=\" \"),\" \",CORREL(IF(Group_orig=AC6,PMT_orig),IF(Group_orig=AC6,PPMT_orig)))","v":" "},"N/A"]
[]
[]
[null,"B. Description"]
[]
[null,"Measures"]
[null,"    Group Size in the Sample = the number of service units contained in the sample data that have been entered into INPUT.  "]
[null,"    Average = sample mean"]
[null,"    Unit Coefficient of Variation = a statistical measure of the degree of variation in a particular variable.  A lower value is more desirable."]
[null,"    Correlation Coefficient = a statistical measure of how closely two variables relate to each other.  It ranges between 0 and 1.  A higher value is better."]
[]
[null,"Variables"]
[null,"    UPT = unlinked passenger trips"]
[null,"    PMT = passenger miles traveled"]
[null,"    PPMT = UPT * average route length = potential passenger miles traveled"]
[null,"    APTL = PMT/UPT = average passegner trip length"]
[null,"    PMT/PPMT = ratio of PMT to PPMT = fraction of a route's length traveled by the average passenger"]

{"sheet":"REVISION","index":4,"n_rows":40,"n_cols":28,"rows_kept":40,"cut":false,"hidden":false,"merged":[[4,2,4,3],[6,1,6,3],[29,1,35,10]]}
[null,"Information for Considering Plan Revision"]
[null,"You must revise your sampling plan periodically to ensure that it continues to meet FTA's statistical standards. Please refer to the NTD Sampling Manual sections on \"Revision\" to learn when you must revise your plan. If this is your mandatory revision year as described in the manual, use this tab to revise your plan."]
[null,"A. Sample Size"]
[null,null,55,null,null,37,null,"crit val","groups?","100%?","PPMT?","Option"]
[null,"Sample Size",{"$f":"CALCULATIONS!D8","v":" "},null,{"$f":"COUNTIFS($P$27:$AB$27,\"<=\"&C4)","v":6},{"$f":"CALCULATIONS!S8","v":" "},{"$f":"COUNTIFS($P$27:$AB$27,\"<=\"&F4)","v":3},{"$f":"INDEX(P28:AB40,G5,E5)","v":1.72},null,{"$f":"INPUT!M52","v":0},null,{"$f":"INDEX($B$10:$B$13,IF($J$5=0,1,IF($K$5=1,4,2)))&\" - \"&IF($I$5=1,D9,C9)","v":"Base Option - No Grouping"}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"INDEX($K$10:$L$13,IF($J$5=0,1,IF($K$5=1,4,IF(I5=1,3,2))),IF(I5=1,2,1))","v":""}]
[null,"B. Statistical Variation by Efficiency Options"]
[]
[null,"Efficiency Options","No Grouping","Grouping",null,"Efficiency Options","No Grouping","Grouping",null,"Efficiency Options","No Grouping","Grouping"]
[null,"Base Option",{"$f":"+IF(OR(CALCULATIONS!D19=\" \",CALCULATIONS!D12=\" \"),\" \",(CALCULATIONS!D19*CALCULATIONS!D12)^2)","v":" "},{"$f":"+IF(OR(CALCULATIONS!O19=\" \",CALCULATIONS!O12=\" \"),\" \",(CALCULATIONS!O19*CALCULATIONS!O12)^2)","v":" "},null,"Base Option",{"$f":"+IF(OR(CALCULATIONS!S19=\" \",CALCULATIONS!S12=\" \"),\" \",(CALCULATIONS!S19*CALCULATIONS!S12)^2)","v":" "},{"$f":"+IF(OR(CALCULATIONS!AD19=\" \",CALCULATIONS!AD12=\" \"),\" \",(CALCULATIONS!AD19*CALCULATIONS!AD12)^2)","v":" "},null,"Base Option",{"$f":"IFERROR(C10/G10,\"\")","v":""},{"$f":"IFERROR(D10/H10,\"\")","v":""}]
[null,"APTL Option - 100% UPT Total",{"$f":"+IF(OR(CALCULATIONS!D21=\" \",CALCULATIONS!D12=\" \"),\" \",(CALCULATIONS!D21*CALCULATIONS!D12)^2)","v":" "},{"$f":"+IF(OR(CALCULATIONS!O$22=\" \",CALCULATIONS!O$12=\" \"),\" \",(CALCULATIONS!O$22*CALCULATIONS!O$12)^2)","v":" "},null,"APTL Option - 100% UPT Total",{"$f":"+IF(OR(CALCULATIONS!S21=\" \",CALCULATIONS!S12=\" \"),\" \",(CALCULATIONS!S21*CALCULATIONS!S12)^2)","v":" "},{"$f":"+IF(OR(CALCULATIONS!AD$22=\" \",CALCULATIONS!AD$12=\" \"),\" \",(CALCULATIONS!AD$22*CALCULATIONS!AD$12)^2)","v":" "},null,"APTL Option - 100% UPT Total",{"$f":"IFERROR(C11/G11,\"\")","v":""},{"$f":"IFERROR(D11/H11,\"\")","v":""}]
[null,"APTL Option - 100% UPT by Group","N/A",{"$f":"+IF(INPUT!M52=0,\"\",IF(OR(CALCULATIONS!O$21=\" \",CALCULATIONS!O$12=\" \"),\" \",(CALCULATIONS!O$21*CALCULATIONS!O$12)^2))","v":""},null,"APTL Option - 100% UPT by Group","N/A",{"$f":"+IF(INPUT!M52=0,\"\",IF(OR(CALCULATIONS!AD$21=\" \",CALCULATIONS!AD$12=\" \"),\" \",(CALCULATIONS!AD$21*CALCULATIONS!AD$12)^2))","v":""},null,"APTL Option - 100% UPT by Group","N/A",{"$f":"IFERROR(D12/H12,\"\")","v":""}]
[null,"PPMT Option",{"$f":"+IF(OR(CALCULATIONS!D23=\" \",CALCULATIONS!D12=\" \"),\" \",(CALCULATIONS!D23*CALCULATIONS!D12)^2)","v":" "},{"$f":"+IF(OR(CALCULATIONS!O23=\" \",CALCULATIONS!O12=\" \"),\" \",(CALCULATIONS!O23*CALCULATIONS!O12)^2)","v":" "},null,"PPMT Option",{"$f":"+IF(OR(CALCULATIONS!S23=\" \",CALCULATIONS!S12=\" \"),\" \",(CALCULATIONS!S23*CALCULATIONS!S12)^2)","v":" "},{"$f":"+IF(OR(CALCULATIONS!AD23=\" \",CALCULATIONS!AD12=\" \"),\" \",(CALCULATIONS!AD23*CALCULATIONS!AD12)^2)","v":" "},null,"PPMT Option",{"$f":"IFERROR(C13/G13,\"\")","v":""},{"$f":"IFERROR(D13/H13,\"\")","v":""}]
[]
[null,"Notes:"]
[null,"1. APTL Option - 100% UPT Total applies if cell M52 = 0 in the Input Worksheet."]
[null,"2. APTL Option - 100% UPT by Group applies otherwise."]
[null,"Follow these steps to determine whether you need to revise your sampling plan:"]
[null,"1. Enter your most recent prior year sampling data in the INPUT tab, in the table starting in cell B71."]
[null,"2. Enter the sampling data you used to develop your current sampling plan in the INPUT tab, in the table starting in cell J71. For example, if you used a Ready-to-Use Sampling Plan in 2023, then used the NTD Sampling Template in 2026, used the same plan in 2029, and are now revising your sampling plan for 2032, enter your 2029 sampling data in the table starting at B71, and your 2023 sampling data in the table starting at J71."]
[null,"3. Make sure to enter a value in INPUT cell M52: If 100% counts of UPT are available by service group, enter 1 in cell M52. If not, enter 0."]
[null,"4. If you use route grouping, enter the group sizes for the most recent prior year sample in the table starting in INPUT cell D34. For the original (older) sampling data, enter the group sizes starting in INPUT cell P34. Enter \"1\" in REVISION cell I5."]
[null,"5. If you use the PPMT option, enter \"1\" in REVISION cell K5."]
[null,"6. Confirm that REVISION cell L5 correctly describes your sampling method."]
[null,"7. See the results below."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Size of Base Sample","Size of Current Sample"]
[null,{"$f":"IF(L6<=H5,\"You do not need to revise your sampling plan. Continue to use the same sampling plan as in your most recent prior year sample.\",\"You must revise your sampling plan. Refer to the SAMPLING PLANS tab and select one of the plans according to the instructions on that tab.\")","v":"You must revise your sampling plan. Refer to the SAMPLING PLANS tab and select one of the plans according to the instructions on that tab."},null,null,null,null,null,null,null,null,null,null,null,null,null,25,30,35,40,45,50,75,100,150,200,300,400,600]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,25,1.98,1.95,1.92,1.9,1.88,1.86,1.82,1.8,1.78,1.77,1.76,1.75,1.74]
[null,"Q: How did the template come up with this answer?",null,null,null,null,null,null,null,null,null,null,null,null,30,1.9,1.86,1.83,1.81,1.79,1.78,1.73,1.71,1.69,1.67,1.66,1.66,1.65]
[null,"A: The template compares the statistical characteristics of your most recent sample to the original sample you used to develop your sampling plan. If your service has been fairly consistent since the original sample, the template will usually determine that your original plan is still valid. If you've had big changes in your service, you may need to change your sample size to ensure you're still getting data that meet FTA's requirements.",null,null,null,null,null,null,null,null,null,null,null,null,35,1.84,1.8,1.77,1.75,1.73,1.72,1.67,1.65,1.62,1.61,1.6,1.59,1.58]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,40,1.8,1.76,1.73,1.7,1.69,1.67,1.62,1.6,1.57,1.56,1.55,1.54,1.53]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,45,1.77,1.73,1.69,1.67,1.65,1.64,1.59,1.56,1.53,1.52,1.51,1.5,1.49]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,50,1.74,1.7,1.67,1.64,1.62,1.61,1.56,1.53,1.5,1.49,1.47,1.47,1.46]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,75,1.67,1.62,1.59,1.56,1.54,1.52,1.47,1.44,1.41,1.39,1.38,1.37,1.36]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,100,1.63,1.58,1.55,1.52,1.5,1.48,1.42,1.39,1.36,1.34,1.33,1.32,1.31]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,150,1.59,1.54,1.51,1.48,1.46,1.44,1.38,1.35,1.31,1.29,1.27,1.26,1.25]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,200,1.57,1.52,1.49,1.46,1.44,1.42,1.36,1.32,1.28,1.26,1.24,1.23,1.22]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,300,1.55,1.51,1.47,1.44,1.42,1.4,1.33,1.3,1.26,1.23,1.21,1.2,1.18]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,400,1.54,1.5,1.46,1.43,1.41,1.39,1.32,1.28,1.24,1.22,1.19,1.18,1.1599999999999999]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,600,1.54,1.49,1.45,1.42,1.4,1.38,1.31,1.27,1.23,1.2,1.18,1.1599999999999999,1.1399999999999999]

{"sheet":"RANDOM SELECTOR TOOL","index":5,"n_rows":6,"n_cols":9,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
["Trip IDs",null,null,null,null,null,null,null,"Trips Selected for Sample"]
[null,"<--Paste your complete list of Trip IDs for the selection period in column A."]
[null,null,"<--Enter your sample size for the selection period in cell B3."]
[null,null,"<--Click this button to check the data for errors."]
[null,null,"<--Click this button to select your random sample."]
[null,null,"Your sample will appear in column I. -->"]