{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Notes","Life Table","Batch Calculator"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":2,"comments":2,"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":"Notes","index":0,"n_rows":22,"n_cols":5,"rows_kept":22,"cut":false,"hidden":false,"merged":[[20,2,20,4],[21,2,21,4],[17,2,17,4],[18,2,18,4],[19,2,19,4],[13,1,13,4],[14,1,14,4],[15,1,15,4],[16,2,16,4],[7,1,7,4],[8,1,8,4],[9,1,9,4],[11,1,11,4],[12,1,12,4],[10,1,10,4],[0,4,0,10],[3,1,3,4],[4,1,4,4],[5,1,5,4],[6,1,6,4]]}
[null,null,null,null,"LIFE EXPECTANCY TEMPLATE\nTECHNICAL NOTES"]
[]
[]
[null,"Life Expectancy and Life Tables"]
[null,"Life expectancy is one of the key measures of the health status of a population and can be defined as: ‘The average number of years a baby born in a particular area or population can be expected to live if it experiences the current age-specific mortality rates of that particular area or population throughout its life’. "]
[null,"The calculation of life expectancy requires the construction of a current life table – a table of information that breaks down the mortality experience of a population by age.  These age-specfic rates are applied to a hypothetical cohort of newborns to calculate their average expected life span, or life expectancy.  Life expectancy at other ages can be calculated in a similar way, eg life expectancy at age 65 can be calculated by applying the current age specific mortality rates over the age of 65 to a hypothetical cohort of 65 year olds.  The current life table is the most effective way of summarising the mortality experience of a population and can also be used to make statistical inferences and comparison between the mortality experience of different populations. "]
[null,"The life table may be complete ('unabridged') or abridged.  In complete life tables the mortality experience is broken down by individual years of life and occasionally even further, particularly for mortality under 1 year of age.  In abridged life tables the mortality experience is broken down by larger age intervals, usually of 5 years, though with the general exceptional of the first years of life, ie ages under 1, and 1 to 4 years."]
[null,"Methodology used In this template"]
[null,"This template is for an abridged life table using 5 year age intervals with a final age interval of 90+.  The table has been completed using the methods described by Chiang.1,2  This age structure and methodology is used by The Office for National Statistics to produce life expectancy at national and local authority level.3"]
[null,"This template includes an adjustment to the method for calculating the variance of the life expectancy estimate to include a term for the variance associated with the final age interval.  In the Chiang method the variance of the life expectancy is the weighted sum of the variance of the probability of survival across all the age intervals.  For the final age interval the probability of survival is, by definition, zero and has zero variance.  However, Silcocks et al argue4 that in the case of the final age interval the life expectancy is dependent not on the probability of survival but on the mean length of survival (1/Mω).  Therefore the variance associated with the final age interval depends on the age-specific mortality rate Mω."]
[null,"The template was originally developed with a final age interval of 85+.  The availability of mid-year estimates of population for the 85-89 and 90+ age groups enabled the possibility of separating these two age groups in the life table, something that became more important as the numbers of deaths above age 85 increased.  Following analysis of the impact of the change, PHE and ONS agreed to change the method to use 90+ as the final age interval.  The methodology for estimating the variance associated with the final age interval was modified in this spreadsheet to match the Silcocks method used by ONS.  It had previously used a method based on a binomial assumption for the final age band, for consistency with the Chiang method.  However, the difference between the two approaches was negligible and for consistency with ONS outputs the PHE calculator was changed in February 2019."]
[null,"Issues that can arise with small numbers"]
[null,"Life expectancy cannot be calculated if the person-years in any given age interval is zero.  It will also not be calculated if the total person-years is less than 5,000 as this is considered to be the minimum size for robust calculation of life expectancy.5  Zero death counts are not a problem, except for the final age interval - there must be at least one death in the 90+ interval for the calculations to be possible."]
[null,"Further information on methodology"]
[null,"The methodology used in this spreadsheet, along with discussion of alternative options for life expectancy calculation for small areas, were described in full in a paper which accompanied the original version of this tool.6"]
[null,"References:"]
[null,"1\n","Chiang CL. The Life Table and its Construction. In: Introduction to Stochastic Processes in Biostatistics. New York, John Wiley & Sons, 1968:189-214."]
[null,2,"Newell C. Methods and Models in Demography. Chichester, John Wiley & Sons, 1994:63-81"]
[null,"3\n","Office for National Statistics Report. Life expectancy at birth by health and local authorities in the United Kingdom, 1998 to 2000 (3-year aggregate figures.) Health Statistics Quarterly 2002;13:83-90"]
[null,"4\n","Silcocks PBS, Jenner DA, Reza R.  Life expectancy as a summary of mortality in a population: statistical considerations and suitability for use by health authorities.  J Epidemiol Community Health 2001;55:38-43"]
[null,"5\n","Toson B, Baker A. Life expectancy at birth: methodological options for small populations. National Statistics  Methodological Series No 33. HMSO 2003."]
[null,"6\n","Eayres DP, Williams ES. Evaluation of methodologies for small area life expectancy estimation.\nJ Epidemiol Community Health 2004;58:243-249"]

{"sheet":"Life Table","index":1,"n_rows":30,"n_cols":21,"rows_kept":30,"cut":false,"hidden":false,"merged":[[0,4,0,9],[7,19,7,20],[2,15,2,16]]}
[null,null,null,null,"LIFE EXPECTANCY TEMPLATE\nLIFE TABLE"]
[]
[null,null,null,null,"This sheet calculates life expectancy for a single area and shows every stage of the calculations. For multiple areas use the Batch Calculator.",null,null,null,null,null,null,null,null,null,null,"the Batch Calculator."]
[null,"Technical Notes",null,null,"Paste the numbers of deaths and the population years at risk for your area into columns H and G respectively."]
[]
[null,"Abridged Life Table (0, 1 – 4, then 5-year age intervals to 90 +)"]
[]
[null,"Age Interval ID","Age at Start of Interval","Age Interval","Interval Width","Fraction of Last Age Interval\nSurvived","Population Years at Risk","Number of Deaths in Interval","Death Rate in Interval","Probability of Dying in Interval","Number Alive at Start of Interval","Number Dying in Interval","Person-Years Lived in Interval","Person-Years Lived Beyond Start of Interval","Observed Life Expectancy at Start of Interval","Sample Vari-ance of Propor-tion Surviving\nin Interval","Weighted Vari-ance of Propor-tion Surviving\nin Interval","Sample Variance\nof Person-Years Lived Beyond\nStart of Interval","Sample Variance\nof Observed Life Expectancy at\nStart of Interval","95% Confidence Interval for Observed Life Expectancy at Start of Interval"]
[null," i ","xi","xi≤x<xi+1","ni","ai","Pi","Di","Mi","qi","li","di","Li","Ti","êi","Spi2","Weighted Spi2","STi2","Sêi2","Lower","Upper"]
[]
[null,1,0,0,1,0.1,50698,206,{"$f":"MIN(1,H11/G11)","v":4.0632766578563261E-3},{"$f":"IF(H11>G11/E11/F11,1,E11*I11/(1+E11*(1-F11)*I11))","v":4.0484716037057283E-3},100000,{"$f":"K11-K12","v":404.84716037056933},{"$f":"E11*(K12+F11*L11)","v":99635.637555666486},{"$f":"N12+M11","v":7832451.0678724507},{"$f":"IF(K11=0,0,N11/K11)","v":78.324510678724508},{"$f":"IF(H11=0,0,J11^2*(1-J11)/H11)","v":7.9241589229094719E-8},{"$f":"K11^2*((1-F11)*E11+O12)^2*P11","v":4888352.1287823506},{"$f":"R12+Q11","v":39590992.394603267},{"$f":"R11/K11^2","v":3.9590992394603263E-3},{"$f":"O11-1.96*SQRT(S11)","v":78.201184786721612},{"$f":"O11+1.96*SQRT(S11)","v":78.447836570727404}]
[null,2,1,1,4,0.5,215400,37,1.7177344475394614E-4,{"$f":"IF(H12>G12/E12/F12,1,E12*I12/(1+E12*(1-F12)*I12))","v":6.8685781115122933E-4},{"$f":"(1-J11)*K11","v":99595.152839629431},{"$f":"K12-K13","v":68.407708680693759},398243.79594115634,7732815.4303167844,{"$f":"IF(K12=0,0,N12/K12)","v":77.642487709902454},1.274188138705347E-8,724166.40192047344,34702640.265820913,{"$f":"R12/K12^2","v":3.4985341172364022E-3},{"$f":"O12-1.96*SQRT(S12)","v":77.52655683107902},{"$f":"O12+1.96*SQRT(S12)","v":77.758418588725888}]
[null,3,5,5,5,0.5,280458,23,8.2008714317295287E-5,4.0995952095338763E-4,99526.745130948737,40.80193675593182,497531.72081285389,7334571.6343756281,73.694479054101777,7.304256879647779E-9,367032.42524658708,33978473.863900438,3.4302380969161161E-3,73.579685314407001,73.809272793796552]
[null,4,10,"10 – 14",5,0.5,258105,23,8.9111020708626336E-5,4.4545586597588728E-4,99485.943194192805,44.316596977994777,497318.92447851901,6837039.9135627747,68.723677878965589,8.6235885352705176E-9,374649.42234979401,33611441.438653849,3.3959688215961965E-3,68.609458993377245,68.837896764553932]
[null,5,15,"15 – 19",5,0.5,282062,105,3.7225858144663231E-4,1.8595623121620689E-3,99441.62659721481,184.91790108027635,496745.83823337336,6339720.9890842559,63.753190751425429,3.2871825508754205E-8,1224149.3156143788,33236792.016304057,3.3611094915492511E-3,63.639559601546814,63.866821901304043]
[null,6,20,"20 – 24",5,0.5,329060,162,4.9231143256548955E-4,2.4585312552167912E-3,99256.708696134534,244.02572061939281,495673.47917912417,5842975.1508508828,58.86730708287562,3.7219232381040688E-8,1170792.1687396688,32012642.700689677,3.2493896079230488E-3,58.755580384554236,58.979033781197003]
[null,7,25,"25 – 29",5,0.5,306097,268,8.7553945317987432E-4,4.3681360772182144E-3,99012.682975515141,432.50087260751752,493982.16269605688,5347301.6716717584,54.006229414004402,7.0885321179088986E-8,1859772.8812245692,30841850.531950008,3.1460003773116406E-3,53.89629454669889,54.116164281309914]
[null,8,30,"30 – 34",5,0.5,274544,314,1.1437146686869865E-3,5.7022689219079726E-3,98580.182102907624,562.13070872143726,491495.58374273451,4853319.5089757014,49.232202715037921,1.029632376366132E-7,2210349.9063964216,28982077.650725439,2.9822928242996911E-3,49.125166384225295,49.339239045850547]
[null,9,35,"35 – 39",5,0.5,260415,413,1.5859301499529597E-3,7.8983352298262564E-3,98018.051394186186,774.17942848562961,488154.80839971686,4361823.9252329664,44.500210555008877,1.4985707513790612E-7,2580357.9697950385,26771727.744329017,2.7865335148491068E-3,44.396746807006764,44.60367430301099]
[null,10,40,"40 – 44",5,0.5,267450,584,2.1835857169564404E-3,1.0858651593469935E-2,97243.871965700557,1055.9373252755322,483579.51651531394,3873669.1168332496,39.834583285613654,1.9970884829574151E-7,2690467.1627602531,24191369.77453398,2.5582087154346149E-3,39.735448959564529,39.933717611662779]
[null,11,45,"45 – 49",5,0.5,311314,954,3.0644301252112016E-3,1.5205658927825719E-2,96187.934640425025,1462.6009271142975,477283.17088433937,3390089.6003179355,35.244436976331315,2.3867540005540909E-7,2441365.6200520187,21500902.611773726,2.3238896025181309E-3,35.149951769568382,35.338922183094247]
[null,12,50,"50 – 54",5,0.5,324311,1359,4.1904221565102632E-3,2.073489091677512E-2,94725.333713310727,1964.1194616006251,468716.36991255207,2912806.4294335959,30.750025523787524,3.0980208348951206E-7,2313416.0757619916,19059536.991721708,2.124124940864028E-3,30.659692593065536,30.840358454509513]
[null,13,55,"55 – 59",5,0.5,296825,1912,6.4415059378421626E-3,3.169708724987981E-2,92761.214251710102,2940.2603015412606,456455.42050469737,2444090.0595210437,26.348189588042025,5.0881752473853739E-7,2655726.0003712485,16746120.915959716,1.9461726849131704E-3,26.261723311457668,26.434655864626382]
[null,14,60,"60 – 64",5,0.5,271339,2824,1.040764504918202E-2,5.0718572983379971E-2,89820.953950168841,4555.5906083584559,437715.79322994803,1987634.6390163461,22.128852473767452,8.6469777797171571E-7,2982768.3324390259,14090394.915588468,1.7464969798312488E-3,22.046941895709917,22.210763051824987]
[null,15,65,"65 – 69",5,0.5,284608,4507,1.5835816280638633E-2,7.6163791865159508E-2,85265.363341810385,6494.1333868728543,410091.48324186978,1549918.845786398,18.177590349003836,1.1890621000028758E-6,2489536.488725761,11107626.583149442,1.5278333196144598E-3,18.100978832371993,18.254201865635679]
[null,16,70,"70 – 74",5,0.5,228062,5851,2.5655304259368066E-2,0.12054497619386087,78771.229954937531,9495.4760396790807,370117.45967548992,1139827.3625445282,14.470097308326739,2.1841465104234499E-6,2510643.8749592816,8618090.0944236815,1.3889153463043479E-3,14.397051725336645,14.543142891316833]
[null,17,75,"75 – 79",5,0.5,162785,7117,4.3720244494271587E-2,0.19706220320914844,69275.75391525845,13651.632695515626,312249.68783750321,769709.90286903828,11.110812360275224,4.3811851220529892E-6,2418124.0305281235,6107446.2194643989,1.2726152692225342E-3,11.040891842412115,11.180732878138333]
[null,18,80,"80 – 84",5,0.5,111263,8192,7.3627351410621683E-2,0.31090835945742851,55624.121219742825,17294.004274691375,234885.59541198571,457460.21503153513,8.2241337930417053,8.1311474414928669E-6,1735976.1380117079,3689322.1889362754,1.1923953074085959E-3,8.1564528813092174,8.2918147047741932]
[null,19,85,"85 – 89",5,0.5,58987,7745,0.13130011697492669,0.49425969533947256,38330.11694505145,18945.031923587485,144288.00491628854,222574.61961954943,5.8067816474085809,1.5952051315094822E-5,1001965.4173174859,1953346.0509245677,1.3295323233376885E-3,5.7353146524155489,5.8782486424016129]
[null,20,90,"90 +",{"$f":"1/F30/I30","v":8.0769947221359821},0.5,26016,6442,{"$f":"MIN(1,H30/G30)","v":0.24761685116851168},1,19385.085021463965,{"$f":"K30","v":19385.085021463965},{"$f":"K30/I30","v":78286.614703260871},{"$f":"M30","v":78286.614703260871},4.0384973610679911,{"$f":"4/H30/I30^2","v":1.0126954942783688E-2},{"$f":"(K30/2)^2*P30","v":951380.63360708184},{"$f":"Q30","v":951380.63360708184},2.5317387356959221E-3,3.9398772438147174,4.1371174783212652]

{"sheet":"Batch Calculator","index":2,"n_rows":10,"n_cols":46,"rows_kept":10,"cut":false,"hidden":false,"merged":[[0,3,0,9],[7,44,8,45],[7,43,8,43],[2,11,2,12],[7,1,8,1],[7,2,8,2],[7,3,7,22],[7,23,7,42]]}
[null,null,null,"LIFE EXPECTANCY TEMPLATE\nBATCH CALCULATOR"]
[]
[null,null,null,null,null,"This sheet automates the calculations set out in the",null,null,null,null,null,"Life Table sheet,",null,"allowing multiple areas to be calculated at once."]
[null,"Technical Notes",null,null,null,"Copy row 10 and paste it below as many times as required."]
[null,null,null,null,null,"Paste the area identifiers, numbers of deaths and the population years at risk for your areas into columns B to AQ."]
[null,"Calculate Life Expectancy at age:",null,0,null,"Life expectancy at age specified in cell D6 is calculated by the function in cells AR to AT."]
[null,null,null,null,null,"If additional metadata fields are required to identify rows, eg for males and females, insert columns between columns B & C."]
[null,"Area code","Area name","Deaths",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Population",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Life Expectancy","95% CI"]
[null,null,null,"0","1 – 4","5 – 9","10 – 14","15 – 19","20 – 24","25 – 29","30 – 34","35 – 39","40 – 44","45 – 49","50 – 54","55 – 59","60 – 64","65 – 69","70 – 74","75 – 79","80 – 84","85 – 89","90 +","0","1 – 4","5 – 9","10 – 14","15 – 19","20 – 24","25 – 29","30 – 34","35 – 39","40 – 44","45 – 49","50 – 54","55 – 59","60 – 64","65 – 69","70 – 74","75 – 79","80 – 84","85 – 89","90 +"]
[null,"Eg1","Example Area",206,37,23,23,105,162,268,314,413,584,954,1359,1912,2824,4507,5851,7117,8192,7745,6442,50698,215400,280458,258105,282062,329060,306097,274544,260415,267450,311314,324311,296825,271339,284608,228062,162785,111263,58987,26016,{"$f":"le(D10:W10,X10:AQ10,$D$6)","v":78.324510678724508},78.201184786721612,78.447836570727404]