{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["TOC","Rationale_Tetrachoric","Textbook_Examples","Two_situations","Data","Correlation_Tables","Copy_Function_Code","Rationale_Betti_Verma","B_V_weight_formulas","Betti_Verma_weights","Betti_Verma_scores","Indices_compared","Practicalities"],"not_rendered":{"charts":0,"images":6,"macros":1,"external_links":0,"defined_names":20,"comments":0,"pivot_tables":2},"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":"TOC","index":0,"n_rows":25,"n_cols":2,"rows_kept":25,"cut":false,"hidden":false,"merged":[[4,0,4,8]]}
["Aldo Benini"]
["Combining binary indicators with the Betti-Verma aggregation method -"]
["An Excel implementation step-by-step"]
[]
["Humanitarian analysts and poverty researchers may want to measure concepts like deprivation or protection risk by combining a number of indicators filled with survey data or expert judgments. The so-called \"Betti-Verma double weighting rule\" provides an aggregation method that penalizes redundant indicators and rewards informative ones.\n\nSo far Excel users have not had convenient access to the procedure. Mainly because there was no readily available function for one of the technical ingredients, so-called tetrachoric correlations. This workbook implements Betti-Verma for binary indicators. It includes a new user-defined function for the tetrachoric correlation. Step-by-step demonstrations lead the users from simulated data to the Betti-Verma indicator weights and hence to the scores and summary statistics such as of a deprivation measure."]
[]
["Table of Contents"]
["What are tetrachoric correlations, and why do we need them?"]
[null,"Rationale_Tetrachoric"]
[null,"Textbook_Examples"]
[null,"Two_situations"]
[null,"Data"]
[null,"Correlation_Tables"]
[null,"Copy_Function_Code"]
["How does Betti-Verma function? How do I calculate weights and scores?"]
[null,"Rationale_Betti_Verma"]
[null,"B_V_weight_formulas"]
[null,"Betti_Verma_weights"]
[null,"Betti_Verma_scores"]
[null,"Indices_compared"]
[null,"Practicalities"]
[]
["Version 1.0.; 7 April 2019"]
["aldobenini [at] gmail.com"]
["Deprecated 26 January 2020"]

{"sheet":"Rationale_Tetrachoric","index":1,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Why tetrachoric correlations? And what are they?"]
["Indicator correlation pattern:"]
[null,"When forming indices based on several indicators, it is necessary to inspect, besides the distribution of each indicator, their correlation pattern."]
[null,"Negative correlations may indicate one of several possibilities:"]
[null,"1. Random or systematic measurement error"]
[null,"2. The indicator was defined or coded in a manner opposite to the intention / substantive meaning of the index"]
[null,"3.Contrary to causal assumptions, the indicator in fact has a negative effect on the underlying construct."]
[null,"4. The causal model is too simple; the effect may be positive within some or all subgroups (defined by some other variable), but is negative overall. The interaction needs to be modeled appropriately."]
[null,"Strong positive correlations may strengthen causal assumptions if the indicators stand for clearly distinct objects or processes. If they denote the same or widely overlapping objects (e.g., \"three-stone stove\" and \"cooking fuel is wood\"), they are redundant, and one or the other should be eliminated from the index, in order to avoid double-counting."]
[]
["Tetrachoric correlation:"]
[null,"When the indicators are binary (coded 0/1), the \"classic\" correlation coefficient (Pearson-moment) (Excel function: CORREL) is not appropriate."]
[null,"The appropriate method is known as \"tetrachoric correlation\" (tetra-choric: \"tetra-\", prefix, derived from the Greek word for \"four\", alluding to the 2-by-2 table; \"choric\", from Greek \"choros\", a group of singers - thus an arrangement of four groups)."]
[null,"Further explanations in sheet \"Textbook_Examples\""]
[null,"There are no convenient implementations of the tetrachoir correlation coefficent in MS Excel."]
[null,"This workbook provides VBA code for calculating the coefficient, although without standard errors, and demonstrates the actual making of the correlation table for multiple binary indicators."]
[null,"The so-called \"Betti-Verma double-weighting method\" is an important aggregation method in the construction of composite measures, particularly poverty and deprivation indices. When such an index is based on binary indicators, tetrachoric correlations are part of its mechanics."]

{"sheet":"Textbook_Examples","index":2,"n_rows":51,"n_cols":3,"rows_kept":51,"cut":false,"hidden":false,"merged":[]}
["Source: ","STATA BASE REFERENCE MANUAL, Release 15, StataCorp LLC, College Station, Texas, 2017."]
["Pages 2810-11:",null,"tetrachoric — Tetrachoric correlations for binary variables"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Pages 2817-18:"]

{"sheet":"Two_situations","index":3,"n_rows":21,"n_cols":9,"rows_kept":21,"cut":false,"hidden":false,"merged":[[0,0,0,11]]}
["Different values, in spite of the same underlying data, between the \"classic\" (Pearson-moment) and the tetrachoric correlation coefficients. Two situations:"]
["Data are taken from the sheet \"Data\", the correlation coefficients form \"Correlation_Tables\":"]
[]
[null,"1. All four value combinations have positive frequencies, e.g. between var6 and var8:"]
[null,"Pivot table:",null,null,null,null,"Coefficients:"]
[null,"Count of id","var8"]
[null,"var6",0,1,"Grand Total",null,"Tetrachoric:",null,{"$f":"Correlation_Tables!I8","v":0.67613380386505506}]
[null,0,10,1,11]
[null,1,50,39,89,null,"Pearson-moment:",null,{"$f":"Correlation_Tables!I22","v":0.22181042312435456}]
[null,"Grand Total",60,40,100]
[null,"The tetrachoric variant expresses the association between var6 and var8 more strongly."]
[]
[null,"2. One of the frequency cells is empty, e.g. between var6 and var7:"]
[null,"Pivot table:",null,null,null,null,"Coefficients:"]
[null,"Count of id","var7"]
[null,"var6",0,1,"Grand Total",null,"Tetrachoric:",null,{"$f":"Correlation_Tables!H8","v":1}]
[null,0,11,0,11]
[null,1,40,49,89,null,"Pearson-moment:",null,{"$f":"Correlation_Tables!H22","v":0.34459922089976247}]
[null,"Grand Total",51,49,100]
[null,"The tetrachoric variant takes n01 = 0 to mean that the positive association betw. var6 and var7 is maximal."]
[null,"The Pearson-moment variant lowers it on account of n10 = 40 > 0. "]

{"sheet":"Data","index":4,"n_rows":101,"n_cols":13,"rows_kept":101,"cut":false,"hidden":false,"merged":[]}
["id","var1","var2","var3","var4","var5","var6","var7","var8","var9","var10"]
[1,1,0,0,1,0,1,1,1,1,1,null,"n = 100"]
[2,1,null,0,1,0,1,1,1,1,1]
[3,1,null,1,1,1,1,1,1,1,1]
[4,1,1,0,1,0,1,1,0,1,1]
[5,1,1,0,1,1,1,1,1,1,0]
[6,0,1,1,1,0,1,1,1,1,1]
[7,0,0,1,1,0,0,0,0,0,0]
[8,1,1,0,1,0,1,1,0,1,0]
[9,1,1,1,1,1,1,1,1,1,0]
[10,0,1,1,0,0,1,0,0,1,1]
[11,0,1,0,1,0,1,0,0,1,1]
[12,0,1,1,1,0,1,0,0,0,0]
[13,0,1,1,1,0,1,1,0,1,1]
[14,0,0,1,1,0,1,0,0,0,0]
[15,1,1,1,1,0,1,0,0,0,0]
[16,1,0,0,1,1,1,1,1,1,1]
[17,0,1,0,1,1,1,0,0,1,1]
[18,0,1,1,1,0,1,0,0,1,1]
[19,0,0,1,1,0,1,0,1,1,1]
[20,1,1,1,1,1,1,0,0,0,0]
[21,0,0,1,0,0,1,0,0,0,0]
[22,1,1,0,1,1,1,0,0,0,0]
[23,1,0,1,1,0,1,1,0,0,0]
[24,1,1,1,1,0,1,0,0,1,1]
[25,1,1,1,0,1,1,1,1,1,1]
[26,1,1,0,1,0,1,0,0,1,1]
[27,1,0,1,1,1,0,0,0,0,0]
[28,0,1,0,0,1,1,1,0,0,0]
[29,1,1,0,1,0,1,1,1,1,1]
[30,0,1,0,1,1,1,1,1,1,1]
[31,0,1,0,1,1,1,1,0,1,1]
[32,1,0,1,1,1,0,0,0,1,1]
[33,1,1,0,1,0,1,1,1,1,1]
[34,1,1,1,1,0,1,0,1,1,1]
[35,1,1,1,1,1,1,1,0,1,0]
[36,1,0,0,1,1,0,0,0,1,1]
[37,1,1,0,1,1,1,1,1,1,1]
[38,1,1,1,0,0,1,1,0,1,1]
[39,1,1,0,1,0,1,1,1,1,1]
[40,0,1,0,1,0,1,0,1,1,1]
[41,0,0,1,1,0,0,0,0,0,0]
[42,1,1,1,1,1,1,0,1,1,1]
[43,0,0,1,1,0,1,0,0,0,0]
[44,1,0,1,1,0,1,0,0,0,0]
[45,1,1,0,1,0,1,0,0,1,1]
[46,1,0,0,1,0,1,0,0,1,1]
[47,0,1,0,1,0,1,1,1,1,1]
[48,1,1,1,1,1,1,1,0,1,1]
[49,1,1,0,1,1,1,0,1,1,1]
[50,1,1,0,1,0,1,1,0,1,1]
[51,1,1,0,0,0,1,1,1,1,1]
[52,0,1,0,1,0,1,1,1,1,1]
[53,0,1,0,1,0,1,1,0,0,0]
[54,0,1,0,1,1,1,1,1,1,1]
[55,0,1,0,1,1,1,0,0,1,1]
[56,0,1,0,1,0,1,0,1,0,0]
[57,1,1,1,1,1,1,1,1,1,1]
[58,0,1,0,1,0,1,1,0,1,1]
[59,1,1,0,1,0,0,0,0,0,0]
[60,0,1,1,1,0,1,0,0,1,1]
[61,1,1,1,1,1,1,0,1,1,1]
[62,0,0,0,0,1,1,0,0,1,1]
[63,1,1,0,1,0,1,1,1,1,1]
[64,1,1,1,1,1,1,1,0,0,0]
[65,0,1,0,1,0,1,0,0,1,1]
[66,0,0,0,1,1,1,1,0,0,0]
[67,0,1,1,1,0,1,0,0,1,1]
[68,0,0,1,1,0,1,1,1,1,1]
[69,0,1,0,1,1,1,1,0,1,1]
[70,0,1,1,1,1,1,0,0,1,1]
[71,0,1,1,1,0,1,0,0,0,0]
[72,1,1,1,0,1,1,0,1,1,0]
[73,0,1,0,1,0,1,0,0,0,0]
[74,1,1,1,1,1,1,1,0,1,1]
[75,1,1,0,1,0,1,1,1,0,0]
[76,0,1,0,1,0,1,1,1,1,1]
[77,1,1,1,1,0,1,1,1,1,1]
[78,0,1,0,1,0,0,0,0,1,1]
[79,1,1,0,1,1,1,1,1,1,1]
[80,0,1,1,1,1,1,1,0,0,0]
[81,0,1,0,1,1,1,1,0,1,1]
[82,1,0,1,1,0,1,0,0,1,1]
[83,1,0,1,1,0,1,1,0,1,1]
[84,1,0,0,0,0,1,0,0,1,0]
[85,1,0,1,1,0,0,0,1,0,0]
[86,1,1,0,1,1,1,1,1,1,1]
[87,0,1,1,0,1,1,0,1,1,1]
[88,1,1,1,1,0,1,1,1,1,1]
[89,0,1,1,1,1,1,0,1,1,1]
[90,1,1,1,1,1,1,1,0,1,0]
[91,1,0,1,1,0,1,1,1,1,1]
[92,1,1,1,1,1,1,0,0,0,0]
[93,1,0,0,1,1,1,1,1,1,1]
[94,0,1,1,1,1,0,0,0,1,1]
[95,0,0,1,1,0,0,0,0,0,0]
[96,0,0,1,1,0,0,0,0,0,0]
[97,1,1,1,1,0,1,1,1,0,0]
[98,1,0,0,1,0,1,0,0,1,1]
[99,0,1,1,1,0,1,0,0,0,0]
[100,0,1,0,1,0,1,0,0,1,1]

{"sheet":"Correlation_Tables","index":5,"n_rows":26,"n_cols":13,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
["Tetrachoric correlation table (user-defined function: Tetra; function INDIRECT to refer to named data columns):"]
[null,"var1_","var2_","var3_","var4_","var5_","var6_","var7_","var8_","var9_","var10_"]
["var1_",{"$f":"Tetra(INDIRECT($A3), INDIRECT(B$2))","v":1},{"$f":"Tetra(INDIRECT($A3), INDIRECT(C$2))","v":-0.12665196551627309},2.5421336014303589E-2,8.7425724717069261E-2,0.19819622288520089,0.16772059169088199,0.37535607256475678,0.39160190101366799,0.2620034473715146,1.7423581612330593E-2,null,"Formulas use mixed-type cell references:"]
["var2_",{"$f":"Tetra(INDIRECT($A4), INDIRECT(B$2))","v":-0.12665196551627309},1,-0.26840886496651467,4.2472231504259557E-2,0.26625534204153223,0.71165675882615165,0.28878350244131795,0.18178916323186783,0.36329294424751241,0.26063823343938403,null,"=Tetra(INDIRECT(RC2), INDIRECT(R2C))"]
["var3_",2.5421336014303589E-2,-0.26840886496651467,1,-0.14091534378166445,1.3112360368256653E-2,-0.37642649143393098,-0.33811757954519173,-0.18228628343896341,-0.42207440538508656,-0.39527746008811421]
["var4_",8.7425724717069261E-2,4.2472231504259557E-2,-0.14091534378166445,1,-0.17582381435222985,-1,0.1580175478530608,1.0341155355510722E-13,-0.18859146345041808,9.3249882290340397E-2]
["var5_",0.19819622288520089,0.26625534204153223,1.3112360368256653E-2,-0.17582381435222985,1,6.7780992457785197E-2,0.22071281718652191,0.13507238371385374,0.26625534204153223,7.2277356030533119E-2]
["var6_",0.16772059169088199,0.71165675882615165,-0.37642649143393098,-1,6.7780992457785197E-2,1,1,0.67613380386505506,0.59836743515318391,0.48535914341098424]
["var7_",0.37535607256475678,0.28878350244131795,-0.33811757954519173,0.1580175478530608,0.22071281718652191,1,1,0.57917245776790316,0.44045923363978579,0.28423258004177554]
["var8_",0.39160190101366799,0.18178916323186783,-0.18228628343896341,1.0341155355510722E-13,0.13507238371385374,0.67613380386505506,0.57917245776790316,1,0.61316034905808436,0.50924942094835002]
["var9_",0.2620034473715146,0.36329294424751241,-0.42207440538508656,-0.18859146345041808,0.26625534204153223,0.59836743515318391,0.44045923363978579,0.61316034905808436,1,1]
["var10_",1.7423581612330593E-2,0.26063823343938403,-0.39527746008811421,9.3249882290340397E-2,7.2277356030533119E-2,0.48535914341098424,0.28423258004177554,0.50924942094835002,1,1]
[]
[]
["Compare to the Pearson-moment correlations (Excel function CORREL):"]
[null,"var1_","var2_","var3_","var4_","var5_","var6_","var7_","var8_","var9_","var10_"]
["var1_",{"$f":"CORREL(INDIRECT($A17), INDIRECT(B$16))","v":1.0000000000000002},{"$f":"CORREL(INDIRECT($A17), INDIRECT(C$16))","v":-4.3540229458354562E-2},1.6093484986175608E-2,3.3501260508640419E-2,0.12309149097933274,6.7454410545859267E-2,0.24326780972100287,0.24618298195866536,0.15221100431590523,1.0535659020683576E-2,null,"Formulas use mixed-type cell references:"]
["var2_",{"$f":"CORREL(INDIRECT($A18), INDIRECT(B$16))","v":-4.3540229458354562E-2},0.99999999999999989,-0.16050626095206708,2.6490164716906343E-2,0.15803839563136382,0.37206639955092002,0.20676983028530999,0.14617954213743869,0.23388213848187409,0.1791612832955235,null,"=CORREL(INDIRECT(RC2), INDIRECT(R16C))"]
["var3_",1.6093484986175608E-2,-0.16050626095206708,0.99999999999999989,-5.3376051268362437E-2,8.1715056307576622E-3,-0.14585496272007553,-0.21941945899403539,-0.11440107883060742,-0.24251094575618437,-0.24339687757091483]
["var4_",3.3501260508640419E-2,2.6490164716906343E-2,-5.3376051268362437E-2,1.0000000000000002,-6.8041381743977281E-2,-0.11718717488510871,6.0012003601200521E-2,-3.9659082035723481E-17,-5.9391387091649844E-2,3.4942827890730645E-2]
["var5_",0.12309149097933274,0.15803839563136382,8.1715056307576622E-3,-6.8041381743977281E-2,1,2.6095343896982891E-2,0.13883218797250726,8.3333333333333467E-2,0.14547859349066161,4.2796049251091303E-2]
["var6_",6.7454410545859267E-2,0.37206639955092002,-0.14585496272007553,-0.11718717488510871,2.6095343896982891E-2,1,0.34459922089976247,0.22181042312435456,0.27902907361893275,0.21107097067885536]
["var7_",0.24326780972100287,0.20676983028530999,-0.21941945899403539,6.0012003601200521E-2,0.13883218797250726,0.34459922089976247,0.99999999999999989,0.38383016674752002,0.25484002372574371,0.1740500963968164]
["var8_",0.24618298195866536,0.14617954213743869,-0.11440107883060742,-3.9659082035723481E-17,8.3333333333333467E-2,0.22181042312435456,0.38383016674752002,0.99999999999999989,0.32732683535398882,0.29957234475763933]
["var9_",0.15221100431590523,0.23388213848187409,-0.24251094575618437,-5.9391387091649844E-2,0.14547859349066161,0.27902907361893275,0.25484002372574371,0.32732683535398882,1,0.84983658559879893]
["var10_",1.0535659020683576E-2,0.1791612832955235,-0.24339687757091483,3.4942827890730645E-2,4.2796049251091303E-2,0.21107097067885536,0.1740500963968164,0.29957234475763933,0.84983658559879893,1]

{"sheet":"Copy_Function_Code","index":6,"n_rows":60,"n_cols":2,"rows_kept":60,"cut":false,"hidden":false,"merged":[]}
["Excel stores the VBA code for user-defined functions and macros in modules that user can open by pressing Alt+F11."]
["An inactive copy of the function \"Tetra\" is kept here for the event that the module inadvertently is lost."]
[]
[null,"Function Tetra(S As Range, T As Range)"]
[null,"'Tetrachoric correlations for binary variables"]
[]
[null,"'using the noniterative Edwards and Edwards estimator:"]
[null,"'Edwards, J. H., and A. W. F. Edwards. 1984.  Approximating the"]
[null,"'tetrachoric correlation coefficient.  Biometrics 40: 563."]
[]
[]
[null,"'Aldo Benini, 4 April 2019"]
[null,"'Adapted, with corrections, from:"]
[null,"'Lewis, N. D. C. (2004). Operational Risk with Excel and VBA:"]
[null,"'Applied Statistical Methods for Risk Management,John Wiley & Sons, p.128."]
[]
[null,"'Error checks:"]
[null,"If (S.Columns.Count > 1 Or T.Columns.Count > 1) Then"]
[null,"        Tetra = \"Ranges have to be only one column each\""]
[null,"    ElseIf (S.Rows.Count < 10 Or T.Rows.Count < 10) Then"]
[null,"        Tetra = \"At least 10 rows are needed\""]
[null,"    ElseIf (S.Rows.Count <> T.Rows.Count) Then"]
[null,"        Tetra = \"Equal number of rows required\""]
[null,"    "]
[null,"    Else 'Start calculating the cell frequencies of the 2x2-table:"]
[null,"        Dim a As Integer"]
[null,"        Dim b As Integer"]
[null,"        Dim c As Integer"]
[null,"        Dim d As Integer"]
[null,"        "]
[null,"        a = 0"]
[null,"        b = 0"]
[null,"        c = 0"]
[null,"        d = 0"]
[null,"        "]
[null,"        Dim pi As Double"]
[null,"        pi = 3.14159265359"]
[null,"        "]
[null,"        For i = 1 To S.Rows.Count"]
[null,"            If (S(i, 1) = 1 And (T(i, 1) = 1)) Then a = a + 1"]
[null,"            'Corresponds to n11 in the STATA reference text."]
[null,"            "]
[null,"            If (S(i, 1) = 1 And (T(i, 1) = 0)) Then b = b + 1 'n10"]
[null,"            If (S(i, 1) = 0 And (T(i, 1) = 1)) Then c = c + 1 'n01"]
[null,"            If (S(i, 1) = 0 And (T(i, 1) = 0)) Then d = d + 1 'n00"]
[null,"        Next i"]
[null,"        "]
[null,"        If a + b = 0 Or a + c = 0 Or b + d = 0 Or c + d = 0 Then"]
[null,"            Tetra = \"One of the variables is a constant\""]
[null,"        ElseIf b * c = 0 Then"]
[null,"            Tetra = 1"]
[null,"        ElseIf a * d = 0 Then"]
[null,"            Tetra = -1"]
[null,"        Else"]
[null,"            Tetra = -Cos(pi / (1 + (Sqr((b * c) / (a * d)))))"]
[null,"        End If"]
[null,"        "]
[null,"End If"]
[]
[null,"End Function"]

{"sheet":"Rationale_Betti_Verma","index":7,"n_rows":29,"n_cols":3,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
["Redundancy and discrimination among indicators"]
[null,"A survey designer wants to measure a fairly broad concept, e.g. deprivation:"]
[null,null,"The concept is \"multi-dimensional\", but the structure of dimensions is poorly understood; plausibly they overlap, i.e. reinforce each other and, in their observable manifestations, are significantly correlated."]
[null,null,"From the semantic universe of the concept, e.g. the (possibly infinite) set of things related to it, the designer takes a sample of those that can be measured (the \"indicators\")."]
[null,null,"The designer assumes that every indicator reflects some part of the concept, i.e. is positively correlated with the corresponding quantitative construct (which is not directly observable)."]
[null,null,"Therefore he/she expects that the indicators are positively correlated among themselves, although in varying degrees."]
[null,null,"However, some aspect of the concept may be \"oversampled\" (several indicators capture it), whereas an other aspect may be approximated by none, one or very few indicators."]
[null,null,"Oversampled aspects should result in highly positively correlated indicators (the reverse need not be the case), some of which are likely to be redundant."]
[null,null,"Indicators that have low positive correlations with most of the others may either capture some aspect uniquely or be foreign to the concept in point."]
[null,null,"Some low negative correlations are to be expected; strong negative ones signal validity problems and may require eliminating the concerned indicators."]
[null,null,"Moreover, some indicators discriminate better than others; i.e. have a wider relative spread (a larger coefficient of variation)."]
[null,null,"The aggregation of the indicators into the index should penalize redundancy (lower their weights) and reward discrimination (increase weights)."]
[null,null,"The Betti-Verma aggregation method achieves that, for binary, count as well as continuous indicators (but not for ordinal ones)."]
[null,"The STATA procedure \"mdepriv\" (https://econpapers.repec.org/software/bocbocode/s457806.htm ) implements the method for any such mixed-type set of indicators."]
[null,"For Excel users, this workbook demonstrates the calculation of Betti-Verma weights and scores for the case of binary indicators. We propose alternatives to the coefficient of variation."]
[]
["Betti-Verma for binary indicators"]
[null,"Not uncommonly, for the measurement of some concept, a number of binary indicators are combined in an index (e.g., household wealth via an asset index that counts the presence of durable household items from a defined list)"]
[null,"Classic model-based statistical methods such a Mokken scales or Item Response Theory scales may fail because they presume strong uni-dimensionality."]
[null,"Betti-Verma steps into such situations and produces a valid index. This construct is limited [0, 1] ratio level, i.e. permits a variety of operations in subsequent analysis."]
[null,"The validity is limited to the sample because the indicator weights themselves are data-driven. Inference to populations has to be conservative."]
[]
["Sources"]
[null,"Pi Alperin, M. N. and P. Van Kerm (2009, rev. 2014). mdepriv - Synthetic indicators of multiple deprivation. v2.0 (revised March 2014). Esch/Alzette, Luxembourg, CEPS/INSTEAD."]
[null,"[On the internet, only the 2009 version is available: http://medim.ceps.lu/stata/mdepriv_v1.pdf . I will make the 2014 one available.]"]
[null,"Betti, G. and V. Verma (1999). Measuring the degree of poverty in a dynamic and comparative context: a multi-dimensional approach using fuzzy set theory. Proceedings of the Sixth Islamic Countries Conference on Statistical Science ICCS-VI, Lahore (Pakistan), August 27-31, Lahore (Pakistan)."]
[null,"https://www.researchgate.net/profile/Gianni_Betti/publication/241227568_Measuring_the_degree_of_poverty_in_a_dynamic_and_comparative_context_A_multi-dimensional_approach_using_fuzzy_set_theory/links/00b49533307370c675000000/Measuring-the-degree-of-poverty-in-a-dynamic-and-comparative-context-A-multi-dimensional-approach-using-fuzzy-set-theory.pdf"]
[null,"[Going beyond the initial approach:] Betti, G., F. Gagliardi, A. Lemmi and V. Verma (2015). \"Comparative measures of multidimensional deprivation in the European Union.\" Empirical Economics 49(3): 1071-1100."]
[null,"https://www.researchgate.net/profile/Gianni_Betti/publication/270454990_Comparative_measures_of_multidimensional_deprivation_in_the_European_Union/links/5630b6c408aee0eba0196822/Comparative-measures-of-multidimensional-deprivation-in-the-European-Union.pdf"]

{"sheet":"B_V_weight_formulas","index":8,"n_rows":3,"n_cols":1,"rows_kept":3,"cut":false,"hidden":false,"merged":[]}
["Pi Alperin, M. N. and P. Van Kerm (2009, rev. 2014). mdepriv - Synthetic indicators of multiple deprivation. v2.0 (revised March 2014). Esch/Alzette, Luxembourg, CEPS/INSTEAD, page 2:"]
["Note: This workbook implements the second weight factor, wbj, only for the case rohH = 0. This should be sufficient for most situations."]
["For the first weight, waj, we provide three alternatives to  the coefficient of variation. The CoV is not suitable for binary indicators."]

{"sheet":"Betti_Verma_weights","index":9,"n_rows":52,"n_cols":13,"rows_kept":52,"cut":false,"hidden":false,"merged":[[31,0,32,0],[47,1,47,11],[49,1,49,11],[1,2,1,11],[3,0,15,0],[20,0,26,0],[28,0,29,0],[40,0,41,0]]}
["Work flow to obtain Betti-Verma weights (and then use them to obtain scores and statistics)"]
[null,null,"Binary indicators (1=undesirable state [lackings, hazards, etc.]; 0=preferred)"]
["1. Step:","Tetrachoric correlations: ","var1_","var2_","var3_","var4_","var5_","var6_","var7_","var8_","var9_","var10_"]
["Verify all indicators have the same substantive direction; else reverse the incorrectly coded ones [= replace x by 1 -x]\n\nCalculate the tetrachoric correlation table\n\nCalculate the item prevalences (proportions present in sample)","var1_",{"$f":"Tetra(INDIRECT($B4), INDIRECT(C$3))","v":1},{"$f":"Tetra(INDIRECT($B4), INDIRECT(D$3))","v":-0.12665196551627309},2.5421336014303589E-2,8.7425724717069261E-2,0.19819622288520089,0.16772059169088199,0.37535607256475678,0.39160190101366799,0.2620034473715146,1.7423581612330593E-2]
[null,"var2_",{"$f":"Tetra(INDIRECT($B5), INDIRECT(C$3))","v":-0.12665196551627309},1,-0.26840886496651467,4.2472231504259557E-2,0.26625534204153223,0.71165675882615165,0.28878350244131795,0.18178916323186783,0.36329294424751241,0.26063823343938403]
[null,"var3_",2.5421336014303589E-2,-0.26840886496651467,1,-0.14091534378166445,1.3112360368256653E-2,-0.37642649143393098,-0.33811757954519173,-0.18228628343896341,-0.42207440538508656,-0.39527746008811421]
[null,"var4_",8.7425724717069261E-2,4.2472231504259557E-2,-0.14091534378166445,1,-0.17582381435222985,-1,0.1580175478530608,1.0341155355510722E-13,-0.18859146345041808,9.3249882290340397E-2]
[null,"var5_",0.19819622288520089,0.26625534204153223,1.3112360368256653E-2,-0.17582381435222985,1,6.7780992457785197E-2,0.22071281718652191,0.13507238371385374,0.26625534204153223,7.2277356030533119E-2]
[null,"var6_",0.16772059169088199,0.71165675882615165,-0.37642649143393098,-1,6.7780992457785197E-2,1,1,0.67613380386505506,0.59836743515318391,0.48535914341098424]
[null,"var7_",0.37535607256475678,0.28878350244131795,-0.33811757954519173,0.1580175478530608,0.22071281718652191,1,1,0.57917245776790316,0.44045923363978579,0.28423258004177554]
[null,"var8_",0.39160190101366799,0.18178916323186783,-0.18228628343896341,1.0341155355510722E-13,0.13507238371385374,0.67613380386505506,0.57917245776790316,1,0.61316034905808436,0.50924942094835002]
[null,"var9_",0.2620034473715146,0.36329294424751241,-0.42207440538508656,-0.18859146345041808,0.26625534204153223,0.59836743515318391,0.44045923363978579,0.61316034905808436,1,1]
[null,"var10_",1.7423581612330593E-2,0.26063823343938403,-0.39527746008811421,9.3249882290340397E-2,7.2277356030533119E-2,0.48535914341098424,0.28423258004177554,0.50924942094835002,1,1]
[]
[null,"Prevalence of items:"]
[null,"Sample mean (e.g., proportion of households lacking the item)",{"$f":"AVERAGE(INDIRECT(C3))","v":0.55000000000000004},{"$f":"AVERAGE(INDIRECT(D3))","v":0.73469387755102045},0.52,0.9,0.4,0.89,0.49,0.4,0.72,0.65]
[]
[]
[null,"BETTI-VERMA FORMULA ELEMENTS, RAW (= not yet normalized)"]
["Step 2:","Betti-Verma first weight:"]
["Determine the values of the first Betti-Verma double-weighting components\n\n2.a.: Decide, on substantive grounds, on the analytic option: Equal, user-defined, social justice-led (Desai-Shah)\n\n2.b.: Set or calculate the value for each indicator. No normalization required at this point.","Three analyic choices:",null,null,null,null,null,null,null,null,null,null,"The STATA procedure mdepriv offers more choices, some of which are inappropriate for binary indicators."]
[null,"Equal",1,1,1,1,1,1,1,1,1,1,"Recommended for simplicity and for avoiding precarious assumption about the greater or lesser importance of some items"]
[]
[null,"User-defined",3,1,1,3,1,1,2,2,1,1,"Generally not recommended. Second-guesses the data without reference to the correlation pattern. May be permissible if there is a consensual and compelling hierarchy of indicators , but for such situations other approaches may be more suitable (e.g., lexicographic ranking)."]
[]
[null,"Desai-Shah weighting: = prop. of households who own the item",{"$f":" 1 - C$16","v":0.44999999999999996},{"$f":" 1 - D$16","v":0.26530612244897955},0.48,9.9999999999999978E-2,0.6,0.10999999999999999,0.51,0.6,0.28000000000000003,0.35,"Recommended for social justice considerations"]
[null,"Emphasis is on items that many or most households own, but the poorest may not have","Desai, M. & Shah, A. (1988), ‘An econometric approach to the measurement of poverty’, Oxford, Economic Papers, 40(3):505–522."]
[]
["Step 3:\nCalculate the second BV weighting component","Betti-Verma's second weight:",{"$f":"1 /((1 + SUM(IF(C$4:C$13<0, C$4:C$13))) * SUM(IF(C$4:C$13>0, C$4:C$13)))","v":0.45344609319379514},{"$f":"1 /((1 + SUM(IF(D$4:D$13<0, D$4:D$13))) * SUM(IF(D$4:D$13>0, D$4:D$13)))","v":0.53069602425121665},{"$f":"1 /((1 + SUM(IF(E$4:E$13<0, E$4:E$13))) * SUM(IF(E$4:E$13>0, E$4:E$13)))","v":-0.85704543489455942},{"$f":"1 /((1 + SUM(IF(F$4:F$13<0, F$4:F$13))) * SUM(IF(F$4:F$13>0, F$4:F$13)))","v":-1.4327773193692779},{"$f":"1 /((1 + SUM(IF(G$4:G$13<0, G$4:G$13))) * SUM(IF(G$4:G$13>0, G$4:G$13)))","v":0.54174797747310377},{"$f":"1 /((1 + SUM(IF(H$4:H$13<0, H$4:H$13))) * SUM(IF(H$4:H$13>0, H$4:H$13)))","v":-0.56438296162428425},{"$f":"1 /((1 + SUM(IF(I$4:I$13<0, I$4:I$13))) * SUM(IF(I$4:I$13>0, I$4:I$13)))","v":0.34758103193444745},{"$f":"1 /((1 + SUM(IF(J$4:J$13<0, J$4:J$13))) * SUM(IF(J$4:J$13>0, J$4:J$13)))","v":0.2992824694894507},{"$f":"1 /((1 + SUM(IF(K$4:K$13<0, K$4:K$13))) * SUM(IF(K$4:K$13>0, K$4:K$13)))","v":0.56530559718979101},{"$f":"1 /((1 + SUM(IF(L$4:L$13<0, L$4:L$13))) * SUM(IF(L$4:L$13>0, L$4:L$13)))","v":0.44423961171123277},"The formula implements this algebra:"]
[null,"Formula (enter as array formula!):","{=1 /((1 + SUM(IF(R4C:R13C<0, R4C:R13C))) * SUM(IF(R4C:R13C>0, R4C:R13C)))}",null,null,null,null,null,null,null,null,null,"Choice of rhoH = 0"]
[]
["Step 4:\nMultiply: First x second weight x (second w. is > 0)",null,"Exclude items with negative second weights (they likely belong to a different concept, e.g., not typical deprivation)"]
[null,"Three analyic choices:"]
[null,"Equal first weights",{"$f":"C22*C$29*(C$29>0)","v":0.45344609319379514},{"$f":"D22*D$29*(D$29>0)","v":0.53069602425121665},0,0,0.54174797747310377,0,0.34758103193444745,0.2992824694894507,0.56530559718979101,0.44423961171123277,"Inclusion is modeled through the indicator  (red) in this formula:"]
[null,null,null,null,null,null,null,null,null,null,null,null,"=R[-12]C*R29C*(R29C>0)"]
[null,"User-defined first weights",{"$f":"C24*C$29*(C$29>0)","v":1.3603382795813854},0.53069602425121665,0,0,0.54174797747310377,0,0.6951620638688949,0.5985649389789014,0.56530559718979101,0.44423961171123277]
[null,null,null,null,null,null,null,null,null,null,null,null,"Note mixed-type vs. absolute cell references in formulas!"]
[null,"Desai-Shah weighting: first weight = prop. of households who own the item",{"$f":"C26*C$29*(C$29>0)","v":0.20405074193720779},0.14079690439317991,0,0,0.32504878648386226,0,0.17726632628656822,0.1795694816936704,0.15828556721314149,0.15548386409893147]
[]
[null,"BETTI-VERMA FORMULA ELEMENTS, NORMALIZED (= summing to 1)"]
["Step 5:\nNormalize the weights such that they sum to 1 over all items","Three analyic choices:"]
[null,"Equal first weights",{"$f":"C34 / SUM($C34:$L34)","v":0.14249010572065521},{"$f":"D34 / SUM($C34:$L34)","v":0.16676498868580839},0,0,0.17023793509916163,0,0.10922325438509603,9.404599875925039E-2,0.17764064023730722,0.13959707711272118,"For the calculation of the BV scores, we name this range \"EqualFirst\""]
[]
[null,"User-defined first weights",{"$f":"C36 / SUM($C36:$L36)","v":0.28723028452825788},0.11205445905014143,0,0,0.11438803718740631,0,0.14678084149757092,0.12638472379418422,0.11936213952327147,9.3799514419167881E-2,"For the calculation of the BV scores, we name this range \"UserDef\""]
[]
[null,"Desai-Shah weighting: first weight = prop. of households who own the item",{"$f":"C38 / SUM($C38:$L38)","v":0.1522196847517151},0.1050329942311235,0,0,0.24248293996758466,0,0.13223879535189167,0.13395692480672694,0.11807935081826497,0.11598931007269303,"For the calculation of the BV scores, we name this range \"DesaiShah\""]
[]
["Step 6:\nUse the weights to calculate the Betti-Verma score for every observed unit (e.g., household), as in sheet \"Betti_Verma_scores\"","Review weights critically on substantive grounds:\n1. Indicators with very high weights may not fit (very low pos. or some neg. correl. with all others)\n2. Indicators with very low weights: Check if they are very highly correl. with others --> high redundancy. If corr. coef. = +1, one or the other is implied (e.g. \"toilet use\" implies \"toilet access\").\nEliminate items with excessive weights and implied ones from index; recalculate."]
[]
["Step 7:\nCalculate the frequency table and distribution statistics (mean, st.dev.) of the BV scores, as in sheet \"Indices_compared\".","Typically, one decides on one option only (equal first weight, or user-defined, or Desai-Shah). If for any reason you work with several BV options or non-BV indices, calculate (Pearson-moment) correlations and inspect scatterplots, compare weights and aggregation methods, even check the association of their scores with external variables (e.g., groups of affected people, gender of household head, etc.) before settling on the best candidate."]
[]
["Step 8:\nUse the final index in the further analysis of the survey / assessment, etc. data."]

{"sheet":"Betti_Verma_scores","index":10,"n_rows":104,"n_cols":16,"rows_kept":104,"cut":false,"hidden":false,"merged":[[1,13,1,15],[2,0,2,10]]}
["Calculation of five variants of the same index, incl. three Betti-Verma options"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Betti-Verma"]
["Static copy of the data \n(to keep the Betti-Verma stuff out of the sheet \"Data\", not needed in \"real life\"):",null,null,null,null,null,null,null,null,null,null,"Naïve count-based score (no variables excluded)","Exclusions after inspection of correlation table; no Betti-Verma","Equal first weight on all items","User-defined for each weight","Emphasize social justice with higher weights on gaps that chiefly affect the poorest"]
["id","var1","var2","var3","var4","var5","var6","var7","var8","var9","var10","Index1a","Index1b","Index2a","Index2b","Index2c"]
[1,1,0,0,1,0,1,1,1,1,1,{"$f":"AVERAGE($B5:$K5)","v":0.7},{"$f":"AVERAGE(B5:C5,F5,H5:K5)","v":0.7142857142857143},{"$f":"SUMPRODUCT($B5:$K5, EqualFirst)","v":0.66299707621503001},{"$f":"SUMPRODUCT($B5:$K5, UserDef)","v":0.77355750376245236},{"$f":"SUMPRODUCT($B5:$K5,DesaiShah)","v":0.65248406580129159}]
[2,1,null,0,1,0,1,1,1,1,1,{"$f":"AVERAGE($B6:$K6)","v":0.77777777777777779},{"$f":"AVERAGE(B6:C6,F6,H6:K6)","v":0.83333333333333337},0.66299707621503001,0.77355750376245236,0.65248406580129159]
[3,1,null,1,1,1,1,1,1,1,1,1,1,0.83323501131419164,0.88794554094985867,0.89496700576887633]
[4,1,1,0,1,0,1,1,0,1,1,0.7,0.7142857142857143,0.73571606614158802,0.75922723901840961,0.62356013522568832]
[5,1,1,0,1,1,1,1,1,1,0,0.8,0.8571428571428571,0.86040292288727882,0.90620048558083222,0.8840106899273068]
[6,0,1,1,1,0,1,1,1,1,1,0.8,0.7142857142857143,0.68727195918018325,0.59838167828433597,0.60529737528070016]
[7,0,0,1,1,0,0,0,0,0,0,0.2,0,0,0,0]
[8,1,1,0,1,0,1,1,0,1,0,0.6,0.5714285714285714,0.59611898902886684,0.66542772459924171,0.50757082515299523]
[9,1,1,1,1,1,1,1,1,1,0,0.9,0.8571428571428571,0.86040292288727882,0.90620048558083222,0.8840106899273068]
[10,0,1,1,0,0,1,0,0,1,1,0.5,0.42857142857142855,0.48400270603583678,0.3252161129925808,0.33910165512208151]
[11,0,1,0,1,0,1,0,0,1,1,0.5,0.42857142857142855,0.48400270603583678,0.3252161129925808,0.33910165512208151]
[12,0,1,1,1,0,1,0,0,0,0,0.4,0.14285714285714285,0.16676498868580839,0.11205445905014143,0.1050329942311235]
[13,0,1,1,1,0,1,1,0,1,1,0.7,0.5714285714285714,0.59322596042093279,0.47199695449015172,0.47134045047397316]
[14,0,0,1,1,0,1,0,0,0,0,0.3,0,0,0,0]
[15,1,1,1,1,0,1,0,0,0,0,0.5,0.2857142857142857,0.30925509440646359,0.39928474357839933,0.2572526789828386]
[16,1,0,0,1,1,1,1,1,1,1,0.8,0.8571428571428571,0.83323501131419164,0.88794554094985867,0.89496700576887633]
[17,0,1,0,1,1,1,0,0,1,1,0.6,0.5714285714285714,0.65424064113499836,0.43960415017998711,0.5815845950896662]
[18,0,1,1,1,0,1,0,0,1,1,0.6,0.42857142857142855,0.48400270603583678,0.3252161129925808,0.33910165512208151]
[19,0,0,1,1,0,1,0,1,1,1,0.6,0.42857142857142855,0.41128371610927877,0.33954637773662361,0.36802558569768495]
[20,1,1,1,1,1,1,0,0,0,0,0.6,0.42857142857142855,0.47949302950562522,0.51367278076580569,0.49973561895042329]
[21,0,0,1,0,0,1,0,0,0,0,0.2,0,0,0,0]
[22,1,1,0,1,1,1,0,0,0,0,0.5,0.42857142857142855,0.47949302950562522,0.51367278076580569,0.49973561895042329]
[23,1,0,1,1,0,1,1,0,0,0,0.5,0.2857142857142857,0.25171336010575124,0.43401112602582881,0.28445848010360675]
[24,1,1,1,1,0,1,0,0,1,1,0.7,0.5714285714285714,0.62649281175649196,0.61244639752083874,0.49132133987379661]
[25,1,1,1,0,1,1,1,1,1,1,0.9,1,1,1,0.99999999999999978]
[26,1,1,0,1,0,1,0,0,1,1,0.6,0.5714285714285714,0.62649281175649196,0.61244639752083874,0.49132133987379661]
[27,1,0,1,1,1,0,0,0,0,0,0.4,0.2857142857142857,0.31272804081981687,0.40161832171566419,0.39470262471929973]
[28,0,1,0,0,1,1,1,0,0,0,0.4,0.42857142857142855,0.44622617817006605,0.37322333773511868,0.47975472955059983]
[29,1,1,0,1,0,1,1,1,1,1,0.8,0.8571428571428571,0.82976206490083837,0.8856119628125938,0.75751706003241526]
[30,0,1,0,1,1,1,1,1,1,1,0.8,0.8571428571428571,0.85750989427934476,0.71276971547174228,0.84778031524828479]
[31,0,1,0,1,1,1,1,0,1,1,0.7,0.7142857142857143,0.76346389552009442,0.58638499167755798,0.71382339044155785]
[32,1,0,1,1,1,0,0,0,1,1,0.6,0.5714285714285714,0.62996575816984524,0.61477997565810361,0.62877128561025764]
[33,1,1,0,1,0,1,1,1,1,1,0.8,0.8571428571428571,{"$f":"SUMPRODUCT($B37:$K37, EqualFirst)","v":0.82976206490083837},{"$f":"SUMPRODUCT($B37:$K37, UserDef)","v":0.8856119628125938},{"$f":"SUMPRODUCT($B37:$K37,DesaiShah)","v":0.75751706003241526}]
[34,1,1,1,1,0,1,0,1,1,1,0.8,0.7142857142857143,0.72053881051574242,0.73883112131502293,0.6252782646805235]
[35,1,1,1,1,1,1,1,0,1,0,0.8,0.7142857142857143,0.76635692412802847,0.77981576178664802,0.75005376512057986]
[36,1,0,0,1,1,0,0,0,1,1,0.5,0.5714285714285714,0.62996575816984524,0.61477997565810361,0.62877128561025764]
[37,1,1,0,1,1,1,1,1,1,1,0.9,1,1,1,0.99999999999999978]
[38,1,1,1,0,0,1,1,0,1,1,0.7,0.7142857142857143,0.73571606614158802,0.75922723901840961,0.62356013522568832]
[39,1,1,0,1,0,1,1,1,1,1,0.8,0.8571428571428571,0.82976206490083837,0.8856119628125938,0.75751706003241526]
[40,0,1,0,1,0,1,0,1,1,1,0.6,0.5714285714285714,0.57804870479508719,0.45160083678676499,0.47305857992880845]
[41,0,0,1,1,0,0,0,0,0,0,0.2,0,0,0,0]
[42,1,1,1,1,1,1,0,1,1,1,0.9,0.8571428571428571,0.89077674561490394,0.85321915850242924,0.86776120464810824]
[43,0,0,1,1,0,1,0,0,0,0,0.3,0,0,0,0]
[44,1,0,1,1,0,1,0,0,0,0,0.4,0.14285714285714285,0.14249010572065521,0.28723028452825788,0.1522196847517151]
[45,1,1,0,1,0,1,0,0,1,1,0.6,0.5714285714285714,0.62649281175649196,0.61244639752083874,0.49132133987379661]
[46,1,0,0,1,0,1,0,0,1,1,0.5,0.42857142857142855,0.45972782307068361,0.50039193847069718,0.38628834564267311]
[47,0,1,0,1,0,1,1,1,1,1,0.7,0.7142857142857143,0.68727195918018325,0.59838167828433597,0.60529737528070016]
[48,1,1,1,1,1,1,1,0,1,1,0.9,0.8571428571428571,0.90595400124074965,0.87361527620581592,0.86604307519327284]
[49,1,1,0,1,1,1,0,1,1,1,0.8,0.8571428571428571,0.89077674561490394,0.85321915850242924,0.86776120464810824]
[50,1,1,0,1,0,1,1,0,1,1,0.7,0.7142857142857143,0.73571606614158802,0.75922723901840961,0.62356013522568832]
[51,1,1,0,0,0,1,1,1,1,1,0.7,0.8571428571428571,0.82976206490083837,0.8856119628125938,0.75751706003241526]
[52,0,1,0,1,0,1,1,1,1,1,0.7,0.7142857142857143,0.68727195918018325,0.59838167828433597,0.60529737528070016]
[53,0,1,0,1,0,1,1,0,0,0,0.4,0.2857142857142857,0.27598824307090442,0.25883530054771237,0.23727178958301517]
[54,0,1,0,1,1,1,1,1,1,1,0.8,0.8571428571428571,0.85750989427934476,0.71276971547174228,0.84778031524828479]
[55,0,1,0,1,1,1,0,0,1,1,0.6,0.5714285714285714,0.65424064113499836,0.43960415017998711,0.5815845950896662]
[56,0,1,0,1,0,1,0,1,0,0,0.4,0.2857142857142857,0.26081098744505876,0.23843918284432564,0.23898991903785044]
[57,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0.99999999999999978]
[58,0,1,0,1,0,1,1,0,1,1,0.6,0.5714285714285714,0.59322596042093279,0.47199695449015172,0.47134045047397316]
[59,1,1,0,1,0,0,0,0,0,0,0.3,0.2857142857142857,0.30925509440646359,0.39928474357839933,0.2572526789828386]
[60,0,1,1,1,0,1,0,0,1,1,0.6,0.42857142857142855,0.48400270603583678,0.3252161129925808,0.33910165512208151]
[61,1,1,1,1,1,1,0,1,1,1,0.9,0.8571428571428571,0.89077674561490394,0.85321915850242924,0.86776120464810824]
[62,0,0,0,0,1,1,0,0,1,1,0.4,0.42857142857142855,0.48747565244919,0.32754969112984567,0.47655160085854265]
[63,1,1,0,1,0,1,1,1,1,1,0.8,0.8571428571428571,0.82976206490083837,0.8856119628125938,0.75751706003241526]
[64,1,1,1,1,1,1,1,0,0,0,0.7,0.5714285714285714,0.58871628389072128,0.66045362226337656,0.63197441430231494]
[65,0,1,0,1,0,1,0,0,1,1,0.5,0.42857142857142855,{"$f":"SUMPRODUCT($B69:$K69, EqualFirst)","v":0.48400270603583678},{"$f":"SUMPRODUCT($B69:$K69, UserDef)","v":0.3252161129925808},{"$f":"SUMPRODUCT($B69:$K69,DesaiShah)","v":0.33910165512208151}]
[66,0,0,0,1,1,1,1,0,0,0,{"$f":"AVERAGE($B70:$K70)","v":0.4},{"$f":"AVERAGE(B70:C70,F70,H70:K70)","v":0.2857142857142857},0.27946118948425769,0.26116887868497723,0.37472173531947633]
[67,0,1,1,1,0,1,0,0,1,1,0.6,0.42857142857142855,0.48400270603583678,0.3252161129925808,0.33910165512208151]
[68,0,0,1,1,0,1,1,1,1,1,0.7,0.5714285714285714,0.52050697049437478,0.48632721923419453,0.5002643810495766]
[69,0,1,0,1,1,1,1,0,1,1,0.7,0.7142857142857143,0.76346389552009442,0.58638499167755798,0.71382339044155785]
[70,0,1,1,1,1,1,0,0,1,1,0.7,0.5714285714285714,0.65424064113499836,0.43960415017998711,0.5815845950896662]
[71,0,1,1,1,0,1,0,0,0,0,0.4,0.14285714285714285,0.16676498868580839,0.11205445905014143,0.1050329942311235]
[72,1,1,1,0,1,1,0,1,1,0,0.7,0.7142857142857143,0.75117966850218276,0.75941964408326135,0.75177189457541516]
[73,0,1,0,1,0,1,0,0,0,0,0.3,0.14285714285714285,0.16676498868580839,0.11205445905014143,0.1050329942311235]
[74,1,1,1,1,1,1,1,0,1,1,0.9,0.8571428571428571,0.90595400124074965,0.87361527620581592,0.86604307519327284]
[75,1,1,0,1,0,1,1,1,0,0,0.6,0.5714285714285714,0.51252434755081,0.67245030887015445,0.52344839914145724]
[76,0,1,0,1,0,1,1,1,1,1,0.7,0.7142857142857143,0.68727195918018325,0.59838167828433597,0.60529737528070016]
[77,1,1,1,1,0,1,1,1,1,1,0.9,0.8571428571428571,0.82976206490083837,0.8856119628125938,0.75751706003241526]
[78,0,1,0,1,0,0,0,0,1,1,0.4,0.42857142857142855,0.48400270603583678,0.3252161129925808,0.33910165512208151]
[79,1,1,0,1,1,1,1,1,1,1,0.9,1,1,1,0.99999999999999978]
[80,0,1,1,1,1,1,1,0,0,0,0.6,0.42857142857142855,0.44622617817006605,0.37322333773511868,0.47975472955059983]
[81,0,1,0,1,1,1,1,0,1,1,0.7,0.7142857142857143,0.76346389552009442,0.58638499167755798,0.71382339044155785]
[82,1,0,1,1,0,1,0,0,1,1,0.6,0.42857142857142855,0.45972782307068361,0.50039193847069718,0.38628834564267311]
[83,1,0,1,1,0,1,1,0,1,1,0.7,0.5714285714285714,0.56895107745577966,0.64717277996826816,0.51852714099456476]
[84,1,0,0,0,0,1,0,0,1,0,0.3,0.2857142857142857,0.32013074595796243,0.40659242405152934,0.27029903556998008]
[85,1,0,1,1,0,0,0,1,0,0,0.4,0.2857142857142857,0.23653610447990558,0.41361500832244213,0.28617660955844204]
[86,1,1,0,1,1,1,1,1,1,1,0.9,1,1,1,0.99999999999999978]
[87,0,1,1,0,1,1,0,1,1,1,0.7,0.7142857142857143,0.74828663989424882,0.5659888739741713,0.71554151989639303]
[88,1,1,1,1,0,1,1,1,1,1,0.9,0.8571428571428571,0.82976206490083837,0.8856119628125938,0.75751706003241526]
[89,0,1,1,1,1,1,0,1,1,1,0.8,0.7142857142857143,0.74828663989424882,0.5659888739741713,0.71554151989639303]
[90,1,1,1,1,1,1,1,0,1,0,0.8,0.7142857142857143,0.76635692412802847,0.77981576178664802,0.75005376512057986]
[91,1,0,1,1,0,1,1,1,1,1,0.8,0.7142857142857143,0.66299707621503001,0.77355750376245236,0.65248406580129159]
[92,1,1,1,1,1,1,0,0,0,0,0.6,0.42857142857142855,0.47949302950562522,0.51367278076580569,0.49973561895042329]
[93,1,0,0,1,1,1,1,1,1,1,0.8,0.8571428571428571,0.83323501131419164,0.88794554094985867,0.89496700576887633]
[94,0,1,1,1,1,0,0,0,1,1,0.6,0.5714285714285714,0.65424064113499836,0.43960415017998711,0.5815845950896662]
[95,0,0,1,1,0,0,0,0,0,0,0.2,0,0,0,0]
[96,0,0,1,1,0,0,0,0,0,0,0.2,0,0,0,0]
[97,1,1,1,1,0,1,1,1,0,0,0.7,0.5714285714285714,0.51252434755081,0.67245030887015445,0.52344839914145724]
[98,1,0,0,1,0,1,0,0,1,1,0.5,0.42857142857142855,0.45972782307068361,0.50039193847069718,0.38628834564267311]
[99,0,1,1,1,0,1,0,0,0,0,0.4,0.14285714285714285,0.16676498868580839,0.11205445905014143,0.1050329942311235]
[100,0,1,0,1,0,1,0,0,1,1,0.5,0.42857142857142855,0.48400270603583678,0.3252161129925808,0.33910165512208151]

{"sheet":"Indices_compared","index":11,"n_rows":23,"n_cols":10,"rows_kept":23,"cut":false,"hidden":false,"merged":[[1,5,1,9],[9,5,9,9]]}
[]
["Index",null,"Mean","SD","Information value (CoV)","Correlations:"]
[null,null,null,null,"= SD / Mean","Index1a","Index1b","Index2a","Index2b","Index2c"]
["Index1a","Count-based, no exclusions",{"$f":"AVERAGE(INDIRECT($A4))","v":0.62577777777777788},{"$f":"_xlfn.STDEV.S(INDIRECT($A4))","v":0.19962478833231759},{"$f":"D4/C4","v":0.31900268021854722},{"$f":"CORREL(INDIRECT($A4),INDIRECT(F$3))","v":1},{"$f":"CORREL(INDIRECT($A4),INDIRECT(G$3))","v":0.95404493613556396},0.93895000020791153,0.91767880923581335,0.93164143684513578]
["Index1b","Some items excluded, no BV",0.56404761904761924,0.27169583813795439,0.48168953996598063,0.95404493613556396,1,0.9871829494106098,0.95536488183495916,0.97720093162745758]
["Index2a","Equal first weight on all items",0.57631267928633423,0.26649999788745421,0.46242258320165608,0.93895000020791153,0.9871829494106098,1,0.93538117188936398,0.97656469722699535]
["Index2b","User-defined weights",0.5537980085623041,0.27496582519622942,0.49650923431461719,0.91767880923581335,0.95536488183495916,0.93538117188936398,1,0.94865485678308847]
["Index2c","Emphasize social justice",0.53512772222840466,0.2683872059289068,0.50153859495687469,0.93164143684513578,0.97720093162745758,0.97656469722699535,0.94865485678308847,1]
[]
[null,null,"Distributions:",null,"Index values","Frequencies of occurrence"]
[null,null,null,null,"Upper limit of interval","Index1a","Index1b","Index2a","Index2b","Index2c"]
[null,null,null,null,0,{"$f":"FREQUENCY(INDIRECT(F$11), $E$12:$E$21) / 100","v":0},{"$f":"FREQUENCY(INDIRECT(G$11), $E$12:$E$21) / 100","v":7.0000000000000007E-2},{"$f":"FREQUENCY(INDIRECT(H$11), $E$12:$E$21) / 100","v":7.0000000000000007E-2},{"$f":"FREQUENCY(INDIRECT(I$11), $E$12:$E$21) / 100","v":7.0000000000000007E-2},{"$f":"FREQUENCY(INDIRECT(J$11), $E$12:$E$21) / 100","v":7.0000000000000007E-2}]
[null,null,null,null,0.1,0,0,0,0,0]
[null,null,null,null,0.2,0.05,0.05,0.05,0.04,0.05]
[null,null,null,null,0.3,0.05,0.09,0.05,0.04,7.0000000000000007E-2]
[null,null,null,null,0.4,0.12,0,0.04,0.14000000000000001,0.14000000000000001]
[null,null,null,null,0.5,0.1,0.18,0.18,0.12,0.12]
[null,null,null,null,0.6,0.18,0.18,0.09,0.15,0.09]
[null,null,null,null,0.7,0.2,0,0.16,0.1,0.14000000000000001]
[null,null,null,null,0.8,0.17,0.18,0.12,0.12,0.15]
[null,null,null,null,0.9,0.11,0.19,0.17,0.15,0.12]
[null,null,null,null,1,0.02,0.06,7.0000000000000007E-2,7.0000000000000007E-2,0.05]
[null,null,null,null,"Total",{"$f":"SUM(F12:F22)","v":1},{"$f":"SUM(G12:G22)","v":1},1,1,1]

{"sheet":"Practicalities","index":12,"n_rows":33,"n_cols":6,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
["The practical steps to make this work:"]
["To access the user-written function for tetrachoric correlations:"]
[null,"Option 1: ","Make a copy of this workbook and copy-paste your binary indicators (plus a record ID variable) into a new sheet. In yet another new sheet, calculate the correlation table as described below."]
[null,"Option 2: Keep this workbook open while continuing to work in your current data-cum-analysis workbook. Access the Tetra function via user-defined functions, use it, then replace the calculated coefficients with their values, to be independent of the formulas."]
[null,"Option 3 (recommended): Save your data-cum-analysis workbook with the macro-enabled extension .xlsm. Press Alt+F11 to access the VBA window and insert a new module. Copy the Tetra function code to your module. Save. Proceed in your workbook as demonstrated in this one."]
[null,"Make sure to enable all macros under the tab \"Developer\" - \"Code\" - \"Macro security\"."]
[]
["To calculate the tetrachoric correlation table:"]
[null,"In the table that holds the selected binary indicators, give them short variable names, and use these names to create a named range of each data column."]
[null,"[If the variable names end in numeric characters, e.g. var1, var2, etc., Excel will append an underscore to the range names, e.g. var1_, var2_, etc.]"]
[null,null,"To see this, open the drop down in the cell address bar above and click on one of the names, e.g. var1_ ."]
[null,"Open a new sheet for the calculation of the correlation table. Name rows and columns with the names of the data column ranges."]
[null,null,"As in sheet \"Betti_Verma_Weights\", row 3 - 13."]
[null,"In each cell that is to receive the correlation coefficient for the two indicators shown in the row and column headings, "]
[null,null,"call the user-defined function by typing =Tetra() and"]
[null,null,"access the corresponding data columns via the function INDIRECT and with mixed-type cell references."]
[null,null,null,"As in sheet \"Betti_Verma_Weights\": =Tetra(INDIRECT(RC2), INDIRECT(R3C))"]
[null,"If your dataset is large and final, you may want to replace the formulas with their values only."]
[null,"Note: Our code tolerates missing values in the indicators. It calculates the coefficients pairwise, not casewise. If missings are few, this is irrelevant."]
[]
["To calculate the weights and scores:"]
[null,"Make sure all indicators are oriented in the same substantive direction, with \"1\" denoting lackings, hazards, etc., and \"0\" denoting the preferred state."]
[null,"Follow the pattern, formulas and formats from sheets \"Betti_Verma_weights\", \"Betti-Verma_scores\" and \"Indices_compared\"."]
[null,"We demonstrate three analytic variants of the weights. If you decide on a suitable one for your purposes right away, no need to compute the others."]
[null,"A frequency table of the scores, with appropriate intervals, can by obtained with the function FREQUENCY."]
[null,null,"As in sheet \"Indices_compared\". FREQUENCY is a multi-cell array function and has to be entered by pressing Shift-Ctrl-Enter."]
[null,null,"The intervals (which Excel calls \"bins\") are passed to the function by their upper bounds. For n bounds, select the first n-1, and select all n target cells."]
[null,null,null,"Example from sheet \"Indices compared\":"]
[null,null,null,null,"1. Select the range R12C6:R22C6 (11 cells); type \"=FREQUENCY(\" in the formula bar (without the inverted quotes)."]
[null,null,null,null,"2.  The final formula is \"=FREQUENCY(INDIRECT(R11C), R12C5:R21C5) / 100\", where"]
[null,null,null,null,null,"INDIRECT(R11C) references the data range, and "]
[null,null,null,null,null,"R12C5:R21C is the bins_range (the upper bounds) with only 10 cells."]
[null,null,null,null,null,"\"/ 100\" is ok here (we have 100 observations in the data table), but this has to be adjusted to your actual sample size."]