{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["About","Sampling template","Binomial calculator"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"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":"About","index":0,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[null,"This is a basic proof-of-concept for a sampling template, created for audit."]
[null,"It was created by David Lyford-Smith of ICAEW and is © ICAEW 2020."]
[]
[null,"The following cell styles are used in this workbook.  To change them, amend the styles from the Home => Cell Styles menu."]
[null,null,"Inputs"]
[null,null,"Calculations"]
[null,null,"Outputs"]
[]
[null,"This workbooks uses VBA script (aka macros).  Some devices or organisations do not allow unknown script to run for security reasons."]
[null,"This workbook should be usable in any version of Excel from Excel 2007 onwards."]

{"sheet":"Sampling template","index":1,"n_rows":110,"n_cols":10,"rows_kept":110,"cut":false,"hidden":false,"merged":[[7,2,7,3]]}
[]
[null,null,"Number of items:",{"$f":"ROWS(Sampling[Item number])","v":100},null,null,"Sample:","Number","Identifier","Value"]
[null,null,"Total value:",{"$f":"SUM(Sampling[Value])","v":535329.15},null,null,null,{"$f":"ROW()-ROW(Picked[[#Headers],[Number]])","v":1}]
[null,null,"Total value sampled:",{"$f":"SUM(Picked[Value])","v":0},{"$f":"D4/D3","v":0}]
[]
[]
[]
[null,null,"v Paste your data in these columns v"]
[]
["Item number","Cumulative value","Item identifier","Value"]
[{"$f":"ROW()-10","v":1},{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":0},"001",7848.96]
[{"$f":"ROW()-10","v":2},{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":7848.96},"002",2013.38]
[3,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":9862.34},"003",6056.52]
[4,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":15918.86},"004",8894.32]
[5,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":24813.18},"005",115.96]
[6,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":24929.14},"006",1962.02]
[7,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":26891.16},"007",3338.81]
[8,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":30229.97},"008",7176.33]
[9,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":37406.300000000003},"009",3987.78]
[10,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":41394.080000000002},"010",9691.8700000000008]
[11,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":51085.950000000004},"011",4298.97]
[12,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":55384.920000000006},"012",4588.6400000000003]
[13,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":59973.560000000012},"013",5581.39]
[14,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":65554.950000000012},"014",6628.3]
[15,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":72183.250000000015},"015",9845.74]
[16,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":82028.99000000002},"016",7575.34]
[17,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":89604.330000000016},"017",7849.17]
[18,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":97453.500000000015},"018",2777.11]
[19,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":100230.61000000002},"019",9627.58]
[20,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":109858.19000000002},"020",9527.2800000000007]
[21,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":119385.47000000002},"021",4517.13]
[22,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":123902.60000000002},"022",6204.04]
[23,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":130106.64000000001},"023",8833.41]
[24,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":138940.05000000002},"024",9839.58]
[25,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":148779.63},"025",3147.61]
[26,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":151927.24},"026",1673.84]
[27,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":153601.07999999999},"027",1012.69]
[28,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":154613.76999999999},"028",2411.9299999999998]
[29,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":157025.69999999998},"029",5693.05]
[30,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":162718.74999999997},"030",8779.68]
[31,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":171498.42999999996},"031",9483.4500000000007]
[32,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":180981.87999999998},"032",8071.38]
[33,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":189053.25999999998},"033",1180.1099999999999]
[34,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":190233.36999999997},"034",5797.99]
[35,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":196031.35999999996},"035",7384.59]
[36,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":203415.94999999995},"036",9522.8700000000008]
[37,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":212938.81999999995},"037",3053.32]
[38,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":215992.13999999996},"038",8077.71]
[39,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":224069.84999999995},"039",6922.07]
[40,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":230991.91999999995},"040",3744.19]
[41,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":234736.10999999996},"041",5793.8]
[42,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":240529.90999999995},"042",4771.17]
[43,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":245301.07999999996},"043",1748.06]
[44,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":247049.13999999996},"044",5747.52]
[45,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":252796.65999999995},"045",9465.0400000000009]
[46,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":262261.69999999995},"046",2603.04]
[47,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":264864.73999999993},"047",6720.76]
[48,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":271585.49999999994},"048",9245.65]
[49,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":280831.14999999997},"049",6542.45]
[50,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":287373.59999999998},"050",5337.82]
[51,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":292711.42},"051",5902.66]
[52,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":298614.07999999996},"052",7518.14]
[53,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":306132.21999999997},"053",691.68]
[54,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":306823.89999999997},"054",7031.24]
[55,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":313855.13999999996},"055",6628.26]
[56,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":320483.39999999997},"056",440.4]
[57,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":320923.8},"057",2216.36]
[58,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":323140.15999999997},"058",2316.31]
[59,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":325456.46999999997},"059",3191.04]
[60,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":328647.50999999995},"060",4690.26]
[61,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":333337.76999999996},"061",1868.52]
[62,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":335206.28999999998},"062",7173.51]
[63,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":342379.8},"063",8898.5400000000009]
[64,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":351278.33999999997},"064",7550.74]
[65,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":358829.07999999996},"065",9637.7900000000009]
[{"$f":"ROW()-10","v":66},{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":368466.86999999994},"066",5111.8999999999996]
[67,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":373578.76999999996},"067",3194.32]
[68,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":376773.08999999997},"068",8843.69]
[69,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":385616.77999999997},"069",1404.63]
[70,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":387021.41},"070",9398.11]
[71,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":396419.51999999996},"071",7146.29]
[72,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":403565.80999999994},"072",1175.1500000000001]
[73,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":404740.95999999996},"073",6780.46]
[74,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":411521.42},"074",474.06]
[75,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":411995.48},"075",1216.68]
[76,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":413212.15999999997},"076",3514.68]
[77,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":416726.83999999997},"077",7822.04]
[78,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":424548.87999999995},"078",6260.82]
[79,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":430809.69999999995},"079",6242.3]
[80,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":437051.99999999994},"080",8789.2999999999993]
[81,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":445841.29999999993},"081",2178.9499999999998]
[82,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":448020.24999999994},"082",4758.16]
[83,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":452778.40999999992},"083",937.7]
[84,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":453716.10999999993},"084",225.47]
[85,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":453941.5799999999},"085",5463.23]
[86,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":459404.80999999988},"086",4876.96]
[87,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":464281.7699999999},"087",9790.61]
[88,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":474072.37999999989},"088",9489.02]
[89,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":483561.39999999991},"089",2825.96]
[90,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":486387.35999999993},"090",4519.6099999999997]
[91,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":490906.96999999991},"091",9945.01]
[92,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":500851.97999999992},"092",906.45]
[93,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":501758.42999999993},"093",7222.76]
[94,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":508981.18999999994},"094",5474.24]
[95,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":514455.42999999993},"095",4087.76]
[96,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":518543.18999999994},"096",3307.98]
[97,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":521851.16999999993},"097",481.05]
[98,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":522332.21999999991},"098",1190.2]
[99,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":523522.42},"099",6428.55]
[100,{"$f":"SUM(INDEX(Sampling[Value],1):Sampling[[#This Row],[Value]])-Sampling[[#This Row],[Value]]","v":529950.97},"100",5378.18]

{"sheet":"Binomial calculator","index":2,"n_rows":21,"n_cols":5,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[]
[null,"This sheet demonstrates how to compute simple error rate statistics using the binomial distribution."]
[null,"The user can enter their presumed (or target) error rate for the items being sampled, along with the number of errors found in the sample examined."]
[null,"The statistics then compute how likely the observed facts would be for the presumed error rate."]
[null,"A low probability means that the actual error rate is probably higher."]
[null,"If further evidence is required to show that the error rate really is at the presumed level, then the sample must be extended."]
[]
[null,"Presumed error rate:",0.05]
[null,"Sample size:",{"$f":"ROWS(Picked[])","v":1}]
[null,"Actual number of errors:",2]
[null,"Probability of observed number of errors:",{"$f":"_xlfn.BINOM.DIST(C10,C9,C8,FALSE)","v":{"$error":"#NUM!"}}]
[null,"Probability of this number of errors or fewer:",{"$f":"_xlfn.BINOM.DIST(C10,C9,C8,TRUE)","v":{"$error":"#NUM!"}}]
[]
[null,"Full population size:",{"$f":"'Sampling template'!D2","v":100}]
[]
[null,"Probability of numbers of errors in the total population:","Number","Exactly this many","This many or fewer"]
[null,"(based on presumed error rate)",{"$f":"C18-1","v":3},{"$f":"_xlfn.BINOM.DIST(C17,$C$14,$C$8,FALSE)","v":0.13957567793089545},{"$f":"_xlfn.BINOM.DIST(C17,$C$14,$C$8,TRUE)","v":0.25783865911601633}]
[null,null,{"$f":"C19-1","v":4},{"$f":"_xlfn.BINOM.DIST(C18,$C$14,$C$8,FALSE)","v":0.17814264156969564},{"$f":"_xlfn.BINOM.DIST(C18,$C$14,$C$8,TRUE)","v":0.4359813006857115}]
[null,null,{"$f":"ROUND(C14*C8,0)","v":5},0.1800178272704292,0.61599912795614087]
[null,null,{"$f":"C19+1","v":6},0.150014856058691,0.76601398401483178]
[null,null,{"$f":"C20+1","v":7},0.10602553736478912,0.87203952137962093]