{"text_format":"1","workbook":"xlsx","n_sheets":19,"sheets":["TOC","Acknowledgement","Rationale_Betti_Verma","B_V_weight_formulas","Demonstration_Setup","Simulated_Data","Betti_Verma_weights","Betti_Verma_scores","Indices_compared","Contributions_by_indicator","BV_w_sampling_weights","Weighting_function_code","MSNA_Setup","MSNA_Data","MSNA_BV_weights","MSNA_BV_scores","MSNA_Indices_compared","MSNA_group_diff","Named_ranges"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":52,"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":"TOC","index":0,"n_rows":35,"n_cols":2,"rows_kept":35,"cut":false,"hidden":false,"merged":[[4,0,4,8]]}
["Aldo Benini"]
["Combining 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 and/or expert judgments. The so-called \"Betti-Verma double weighting rule\" provides an aggregation method that penalizes redundant indicators and rewards informative ones.\n\nThe workbook lays out the rationale for Betti-Verma. It demonstrates the use of the double-weighting rule for continuous indicators. Several analytic options are offered for the calculation of weights and scores; the results are compared. Further, the workbook stores three user-defined functions that are needed for data with sampling weights. We demonstrate the use of these functions in a worked example with data from a multi-sectoral needs assessment in Bangladesh. The outcome is an inter-sectoral living standards gap measure. We provide formulas for group-specific averages, and calculated them for all the combinations of administrative units and gender of household head.\n\nThis workbook must be saved with the macro-enabled extension .xlsm. Tables should not be sorted differently.\n\nA separate workbook, published earlier, demonstrates the Betti-Verma method for binary indicators:"]
["http://aldo-benini.org/Level2/HumanitData/Benini__Betti_Verma_w_binary_indicators_in_Excel__190407.xlsm"]
[]
["Table of Contents"]
[null,"Acknowledgement"]
["Motivation: What are redundancy and discrimination? When is Betti-Verma needed? Practicalities and formulas. Select literature."]
[null,"Rationale_Betti_Verma"]
[null,"B_V_weight_formulas"]
["Calculation of weights and scores with simulated data"]
[null,"Demonstration_Setup"]
[null,"Simulated_Data"]
[null,"Betti_Verma_weights"]
[null,"Betti_Verma_scores"]
[null,"Indices_compared"]
[null,"Contributions_by_indicator"]
["Special functions needed when sampling weights are incorporated"]
[null,"BV_w_sampling_weights"]
[null,"Weighting_function_code"]
["Worked example with real data and sampling weights"]
[null,"MSNA_Setup"]
[null,"MSNA_Data"]
[null,"MSNA_BV_weights"]
[null,"MSNA_BV_scores"]
[null,"MSNA_Indices_compared"]
[null,"MSNA_group_diff"]
["Convenience listing of all named ranges in the workbook"]
[null,"Named_ranges"]
[]
["Version 1.0.2.; 3 September 2019"]
["aldobenini [at] gmail.com"]
["Deprecated 26 January 2020"]

{"sheet":"Acknowledgement","index":1,"n_rows":16,"n_cols":1,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Acknowledgement"]
[]
["This workbook uses data from the 2018 Host Community Multi-Sector Needs Assessment (MSNA), Cox's Bazar, Bangladesh, in which the following organizations were involved:"]
["ECHO, ISCG, REACH, IOM NPM/ACAPS Analysis Hub, Translators Without Borders."]
[]
["I thank Mr. Sudeep Shrestha, Information Analyst, IOM NPM/ACAPS Analysis Hub, Cox's Bazar, for sharing a clean dataset and for his lead in creating the sector gap indices."]
[]
["For demonstration purposes, I collapsed the household-level indices to their means in 22 sub-samples. These are explained in the text."]
["There are no data at the individual household level in this workbook."]
[]
["Community-contributed Excel functions are acknowledged in the respective VBA modules, which are accessible via Alt+F11."]
[]
["The content of this workbook were developed by me; and errors of modeling, computation, or interpretation are entirely mine."]
[]
["Aldo Benini"]
["3 September 2019"]

{"sheet":"Rationale_Betti_Verma","index":2,"n_rows":59,"n_cols":3,"rows_kept":59,"cut":false,"hidden":false,"merged":[]}
["Redundancy and discrimination among indicators"]
[null,"The situation: 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. they reinforce each other to unknown degrees."]
[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\"). Indicators, of course, are formed also from data already collected, for this or other purposes."]
[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 strongly positively correlated indicators (the reverse need not be the case), some of which are likely to be redundant."]
[null,null,"Indicators that have weak positive correlations with most of the others may either capture some aspect uniquely or be foreign to the concept in point."]
[null,null,"When numerous indicators are combined, some weak or moderate negative correlations are to be expected. However, 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). They express deviations from the mean more distinctly."]
[null,null,"The aggregation of the indicators into the index should penalize redundancy (lower their weights) and reward discrimination (increase weights)."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"The solution: The Betti-Verma double-weighting rule"]
[null,null,"The Betti-Verma double-weighting rule is an aggregation method that achieves both objectives. It works for binary, count as well as continuous indicators (but not for ordinal ones)."]
[null,null,"The Betti-Verma method has been implemented primarily in the statistical program STATA, in the context of multiple-deprivation studies. The STATA procedure \"mdepriv\" handles mixtures of binary, count and continuous indicators."]
[null,null,"For Excel users, this workbook demonstrates the calculation of Betti-Verma weights and scores for the case of continuous indicators. Count variables can be treated as continuous."]
[null,null,"A separate workbook demonstrates the method for binary indicators. \nSee http://aldo-benini.org/Level2/HumanitData/Benini__Betti_Verma_w_binary_indicators_in_Excel__190407.xlsm ."]
[null,null,"Betti-Verma returns weights on indicators. The weights are used for the simple additive aggregation. The resulting measure is an index of deprivation (or, if so interpreted, of severity)."]
[null,null,"The indicators are commonly normalized in the range [0, 1]. The weights sum to 1. The resulting index is so-called \"additively decomposable\". I.e., the relative contribution of each indicators to the index is straightforward to calculate."]
[null,null,"This is of policy interest in terms of easy comparison of affected groups (e.g., by gender of the household head). Groups may differ not only on the index distribution, but also in terms of how much different indicators each contribute to the index."]
[null,null,"Betti-Verma weights adjust for redundancy and discrimination. However, there are no confidence intervals for the weights. The weights are driven by the distribution of the sample data; they are not population estimates. "]
[null,null,"Betti-Verma and expert judgment work together. If the the experts agree or the analyst knows for sure that the correlated indicators each express a truly distinct dimension (no redundancy), Betti-Verma is not suitable."]
["When is Betti-Verma justified for Excel users?"]
[null,"Effort"]
[null,null,"Calculating Betti-Verma weights and scores in STATA is fast and convenient. In Excel, calculations require several steps. The extra effort over much simpler methods, e.g., taking the mean of the normalized indicators, must be justified."]
[null,"Situations that call for Betti-Verma"]
[null,null,"# 1: The concept has several dimensions; some are covered by just one indicator, others each by several. If in the latter groups, there is clear conceptual overlap, Betti-Verma will produce lower weights that compensate for redundancy in these indicators."]
[null,null,"Example: In the analysis of a needs assessment in host communities surrounding Rohingya refugee camps in Bangladesh, a living standards measure at the household level (n = 2,881) was constructed. It incorporated continuous gap measures in food security, livelihoods, shelter, WASH and education, as well as a binary measure of health care access. The first three were more strongly correlated as a result of being part of the same local economy that differentiates households by incomes and wealth. The last two are responsive in part to different providers from outside; they were only weakly correlated with the others. WASH holds a middle position. The Betti-Verma weights, in the order of first appearance, were: 0.05, 0.06, 0.10, 0.23, 0.19, and 0.37. The 0.37 for health care access is inflated by the binary form of the measure. Note that the sum of the first three weights is similar to the weights for WASH and education, reflecting the redundancy correction."]
[null,null,"#2: There is considerable measurement error in all indicators, but its extent may vary greatly. The pattern is poorly understood. If the errors are uncorrelated across indicators, the correlations will be attenuated, more so for indicators that are infected with larger errors. If the concept that they are supposed to measure is known to have only one dimension, this would be a classic situation for factor analysis. However, if there are several correlated, but conceptually distinct dimensions to it, factor analysis would likely not work (technically, the eigenvalues would be < 1, and some negative). Betti-Verma may come to the rescue. This would still be problematic because, other things being equal, it would assign higher weights to indicators with higher errors. But it would still adjust for conceptual overlap."]
[null,null,"#3: Again there is considerable measurement error. Different this time: The errors among a subset of the indicators are strongly correlated; in the rest, they are uncorrelated. Large correlated errors strengthen the correlations; uncorrelated ones weaken them. Correlated errors are fairly common in indicators when individual respondents offer estimates with the same bias.\n\nExample: Assume four WASH indicators measured by different methods. #1: Enumerators estimate the daily water use of the household by measuring storage vessels on site. #2-4: Household heads offer subjective estimates of the number of times garbage pits are emptied in a month, the number of days in the last week when they saw rats, and the distance to the nearest \"acceptable\" community latrine. The last two are inverted so that all four have a positive orientation. Plausibly, the respondent's personal value orientation towards hygiene moves the three estimates in the same direction. These errors, however, are likely uncorrelated with those in the estimates that the enumerators make of daily water volumes. Betti-Verma will decrease the weights on the correlated subjective estimates, and increase the weight on the water use. In this situation, it works as a partial error corrective."]
["Practicalities"]
[null,"Preparation"]
[null,null,"Normalization: Betti-Verma expects indicators with ranges in the interval [0, 1] (the minimum may be greater than zero, the maximum may be smaller than one). Normalizing indicators that originally have minima >= 0 and maxima > 1 by dividing each by its respective maximum preserves the coefficients of variation, which Betti-Verma uses in calculating the weights."]
[null,null,"Consistent orientation: Ensure that all indicators have the same orientation. Normally, Betti-Verma is deprivation- (or severity-) oriented. I.e., higher values denote worse conditions, lower values point towards preferred states. It also works with indicators all of which have positive orientation (the scores would then be interpreted positively, too - e.g., as living standards). Normalized indicators that need reversion are replaced by x' = 1 - x."]
[null,null,"Incorporating ordinal and/or binary indicators: One or two such variables, if they have to figure in the composite measure, may be admitted, without endangering the validity of the weighting too much. For the first of the two Betti-Verma weightings, the option \"Equal\" has to be taken (see sheet \"Betti_Verma_weights\"). For the ordinal indicator(s), a suitable transformation to a ratio-level variable must be applied. In the severity context, an indicator with five levels (e.g., from 1 = \"no problem\" to 5 = \"catastrophic\"), could plausibly be transformed via an escalating function such as x' = x^2/25. This returns the sequence 0.04 0.16 0.36 0.64 and 1.00, with increasing differences between adjacent values."]
[null,"Calculation"]
[null,null,"Follow the pattern, formulas and formats from sheets \"Betti_Verma_weights\", \"Betti-Verma_scores\" and \"Indices_compared\".\nWe 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,null,"With sampling weights: For interested users, we demonstrate three user-written Excel functions for weighted mean, weighted standard deviation, and weighted Pearson-moment correlation coefficient. These are needed in order to produce the building blocks for the calulation of the Betti-Verma weights, scores and subsequent statistics of interest. We provide a worked example using aggregated (by admin. unit and HH head gender) data from a host community needs assessment in Bangladesh."]
[null,"Review"]
[null,null,"The results of the calculations should not be accepted blindly. The weights need to be plausible in the light of the analysis objectives, and in terms of the intended redundancy reduction and preference for indicators with high variability. Implausibly high weights on weakly correlated indicators may justify assigning defensibly lower weights manually (then rescaling all weights to sum to one) or excluding some when their relationship with the concept of interest must be doubted. Conversely, very low weights resulting from very small coefficients of variation throw up two questions: 1. Do the indicators discriminate enough for the objective of the study / assessment? 2. Are the small C.o.V. a statistical artefact because the indicators lack natural zero points? In the latter case, Betti-Verma with equal first weights will be more appropriate."]
["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 . We 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":3,"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 two alternatives to  the coefficient of variation."]

{"sheet":"Demonstration_Setup","index":4,"n_rows":16,"n_cols":18,"rows_kept":16,"cut":false,"hidden":false,"merged":[[6,0,9,0],[10,0,11,0],[12,0,13,0],[6,5,9,5],[10,5,11,5],[12,5,13,5],[6,2,9,2],[10,2,11,2],[12,2,13,2]]}
["Set-up for simulating the demonstration data"]
["We demonstrate the effect of five fairly typical indicator situations on the resulting weights when the Betti-Verma double-weighting rule is used in aggregation. "]
["To this purpose, we create 10 normally-distributed indicators, with 100 observations and approximating the correlation pattern shown on the right side of this table."]
["The next sheet holds descriptive statistics and actual correlation coefficients of the simulated variables."]
[null,null,null,null,null,null,null,null,"Desired correlation pattern:"]
["Situation of interest","Indicator","Correlations with the others","Mean is relatively ..","Spread is relatively ..","Supposed to demonstrate what?",null,null,"x1","x2","x3","x4","x5","x6","x7","x8","x9","x10"]
["Several indicators strongly associated","x1","Strong among x1 - x4, \nweak or zero with the others","small","narrow","Strong intercorrelations diminish weights; high relative information value (wide spread/small mean) increases them.",null,"x1",1,0.8,0.8,0.8,0,-0.1,0,0.1,-0.1,0]
[null,"x2",null,"small","wide",null,null,"x2",0.8,1,0.8,0.8,0,-0.1,0,0.1,-0.1,0]
[null,"x3",null,"large","narrow",null,null,"x3",0.8,0.8,1,0.8,-0.1,0,0,0.1,0,-0.1]
[null,"x4",null,"large","wide",null,null,"x4",0.8,0.8,0.8,1,-0.1,0,0,0.1,0,-0.1]
["One pair strongly associated","x5","Strong between x5 - x6, weak or zero with the others","centered","narrow","Only two strongly correlated, weights higher, more so for x6",null,"x5",0,0,-0.1,-0.1,1,0.8,0.1,0,0.1,-0.1]
[null,"x6",null,"centered","wide",null,null,"x6",-0.1,-0.1,0,0,0.8,1,0.1,0,0.1,-0.1]
["Isolates, but very few negative associations","x7","Zero between x7-x8, weak (mostly positively) or zero with the others","centered","narrow","A bit stronger than x5 x6 because zero between themselves",null,"x7",0,0,0,0,0.1,0.1,1,0,0.1,-0.1]
[null,"x8",null,"centered","wide",null,null,"x8",0.1,0.1,0.1,0.1,0,0,0,1,0.1,-0.1]
["Isolate, mixed","x9","Weak or zero","centered","medium","Low weight sum boosts weight high",null,"x9",-0.1,-0.1,0,0,0.1,0.1,0.1,0.1,1,-0.1]
["Isolate , negative","x10","Zero or weakly negative","centered","medium","Unsuitable indicator; will get zero weight",null,"x10",0,0,-0.1,-0.1,-0.1,-0.1,-0.1,-0.1,-0.1,1]

{"sheet":"Simulated_Data","index":5,"n_rows":104,"n_cols":24,"rows_kept":104,"cut":false,"hidden":false,"merged":[]}
["Simulated data, descriptive statistics, correlation matrix, visualization of association pattern:"]
[]
[]
["id","x1","x2","x3","x4","x5","x6","x7","x8","x9","x10",null,null,"The following statistics are noteworthy:"]
[1,0,9.6805699999999995E-2,0.36879220000000001,0.31250610000000001,0.17411189999999999,0,0.6878377,0.19589889999999999,5.5654799999999997E-2,0.72126049999999997,null,null,"Indicator","Mean","SD ","C.o.V.",null,"SD ="]
[2,0.38586369999999998,0.31185550000000001,0.76197079999999995,0.77994240000000004,0.8320824,0.88053459999999995,0.67231750000000001,1,1,0.27313080000000001,null,null,"x1",{"$f":"AVERAGE(INDIRECT($N6&\"_\"))","v":0.30735680500000001},{"$f":"_xlfn.STDEV.S(INDIRECT($N6&\"_\"))","v":0.18606778254974576},{"$f":"P6/O6","v":0.60538039022674561},null,"Standard deviation"]
[3,0.45835239999999999,0.58566859999999998,0.70788549999999995,1,0.77450969999999997,0.90741439999999995,0.60619149999999999,0.83344339999999995,0.4730663,0.65859760000000001,null,null,"x2",{"$f":"AVERAGE(INDIRECT($N7&\"_\"))","v":0.34529384000000007},{"$f":"_xlfn.STDEV.S(INDIRECT($N7&\"_\"))","v":0.31166658848155221},{"$f":"P7/O7","v":0.90261265153630355}]
[4,0.121715,0,0.42940990000000001,2.5131799999999999E-2,0.42405130000000002,7.9077700000000001E-2,0.45662940000000002,0.91168159999999998,0.31436350000000002,0.29639120000000002,null,null,"x3",0.69575399999999976,0.19190441727284216,0.27582222635132853]
[5,0.35888350000000002,0.25929200000000002,0.65047529999999998,0.61478679999999997,0.54972129999999997,0.58387920000000004,0.26710640000000002,0.80575509999999995,0.61743999999999999,0.47659849999999998,null,null,"x4",0.65987497400000006,0.31960823602541022,0.48434665447004843]
[6,0.1229391,0,0.2809352,4.64795E-2,0.37882130000000003,0.10472339999999999,0.53353070000000002,0.55874630000000003,0.78453910000000004,0.77996500000000002,null,null,"x5",0.5001030179999999,0.1997369634508214,0.39939163784614762,null,"C.o.V. ="]
[7,0.52080040000000005,0.57332810000000001,0.8956906,1,0.64453320000000003,0.90931229999999996,0.28491739999999999,5.3319900000000003E-2,0.45272519999999999,0.59345899999999996,null,null,"x6",0.50818216299999996,0.33556117741569641,{"$f":"P11/O11","v":0.66031671681419568},null,"Coefficient of variation ="]
[8,0.30427149999999997,0.46575929999999999,0.80818820000000002,0.6814616,0.4735529,0.69086369999999997,0.64392910000000003,1,1,0.48451739999999999,null,null,"x7",0.49953742699999992,0.19879990384166793,0.39796798617387275,null,"Standard deviation / Mean"]
[9,0.48286410000000002,0.7545037,0.93756510000000004,1,0.41354859999999999,0.56522819999999996,0.13754769999999999,0,0.72485279999999996,0.14017160000000001,null,null,"x8",0.49921009999999993,0.33471924134642811,0.67049773501463239]
[10,0.60532430000000004,0.44566109999999998,0.8799941,0.97894820000000005,0.2311956,1.7764700000000001E-2,0.53707400000000005,0.97544810000000004,0.27293460000000003,0.50023930000000005,null,null,"x9",0.50234909000000005,0.2666228210669499,0.53075207335789121]
[11,1.2052E-2,0,0.6653038,0.51392260000000001,0.60411550000000003,0.94963430000000004,0.49563600000000002,0.13920260000000001,0.52678009999999997,0.12762499999999999,null,null,"x10",0.49364980199999969,0.27763206946436358,0.56240692964840644]
[12,0.1111596,0.31232080000000001,0.51860589999999995,0.50930640000000005,0.55693239999999999,0.35986200000000002,0.60489309999999996,1,0.62471500000000002,0.6274248]
[13,0.11681179999999999,0.25363059999999998,0.59500799999999998,0.50990829999999998,0.63368340000000001,0.90685780000000005,0.41045490000000001,7.7721799999999994E-2,0.20030339999999999,0.41840129999999998,null,null,"Observed correlation matrix:"]
[14,0.57756600000000002,0.99016800000000005,0.92857659999999997,0.96269260000000001,0.67429349999999999,0.65979279999999996,0.27692099999999997,0.52028189999999996,0,0.85532520000000001,null,null,null,"x1","x2","x3","x4","x5","x6","x7","x8","x9","x10"]
[15,0.22669629999999999,0.24279439999999999,0.74882199999999999,0.41345710000000002,0.53756890000000002,0.80086930000000001,0.33044679999999998,0.91888000000000003,0.44137729999999997,0.16380929999999999,null,null,"x1",{"$f":"CORREL(INDIRECT($N19&\"_\"),(INDIRECT(O$18&\"_\")))","v":1},{"$f":"CORREL(INDIRECT($N19&\"_\"),(INDIRECT(P$18&\"_\")))","v":0.78672245133808949},0.79889174150850917,0.77641899323233288,3.9503173781473841E-3,-6.7275487808262577E-2,1.4487215936882274E-2,7.7519211116446676E-2,-0.10095833024521196,-9.463199427006316E-3]
[16,0.16239190000000001,0,0.59931129999999999,0.232293,0.7688294,1,0.2406394,1,0.40822999999999998,0.69714989999999999,null,null,"x2",{"$f":"CORREL(INDIRECT($N20&\"_\"),(INDIRECT(O$18&\"_\")))","v":0.78672245133808949},1,0.72257491161806853,0.70955877796490285,3.4378474203994425E-2,-3.8716698083832005E-2,-1.1908707262028514E-2,7.6141607101858966E-2,-0.11990384311695441,1.8594707001218062E-2]
[17,0.4185179,0,0.82958900000000002,0.90495479999999995,0.16104979999999999,1.39629E-2,0.23317589999999999,0.84168759999999998,0.61320430000000004,0.3630371,null,null,"x3",0.79889174150850917,0.72257491161806853,1,0.81456959919740124,-0.10983564891929191,1.0192219565967985E-2,6.3634634465746425E-3,6.5050206424977774E-2,-4.7785724433966427E-4,-0.11326984215621688]
[18,7.67288E-2,0,0.32892769999999999,0,0.50709029999999999,0.2190742,0.60645539999999998,0.54708959999999995,0.96376450000000002,0.82997120000000002,null,null,"x4",0.77641899323233288,0.70955877796490285,0.81456959919740124,1.0000000000000002,-0.13780042852972502,1.5678220796814513E-2,4.3431575266350803E-2,7.1569958748614368E-2,-1.968544677569839E-2,-0.12477705963284186]
[19,0.2200056,0.14729709999999999,0.52077099999999998,0.29393370000000002,0.41325659999999997,8.3432900000000004E-2,0.1061209,0.3033052,0.29714859999999998,0.36684939999999999,null,null,"x5",3.9503173781473841E-3,3.4378474203994425E-2,-0.10983564891929191,-0.13780042852972502,1.0000000000000002,0.78544115032911455,9.9737621598529211E-2,-9.6126654390062388E-3,0.10903368003074521,-9.5877757551776591E-2]
[20,0.3125771,0.228437,0.73162070000000001,0.54152940000000005,0.70623270000000005,0.71762740000000003,0.58776280000000003,0.12625410000000001,0.44999090000000003,0,null,null,"x6",-6.7275487808262577E-2,-3.8716698083832005E-2,1.0192219565967985E-2,1.5678220796814513E-2,0.78544115032911455,0.99999999999999989,9.7325618071822106E-2,-1.4914573154876586E-2,0.10225763062505386,-0.10176159590732109]
[21,0.30009370000000002,0.26848519999999998,0.90264449999999996,0.59465900000000005,0.42652780000000001,0.61194979999999999,0.98119630000000002,0.2425937,1,0.51166829999999996,null,null,"x7",1.4487215936882274E-2,-1.1908707262028514E-2,6.3634634465746425E-3,4.3431575266350803E-2,9.9737621598529211E-2,9.7325618071822106E-2,1,-7.4797501551908114E-4,9.1754951378848273E-2,-8.2719274088880371E-2]
[22,0,0,0.60180880000000003,0.30137829999999999,0.57725329999999997,1,0.63885420000000004,1,0.58074650000000005,0.1725565,null,null,"x8",7.7519211116446676E-2,7.6141607101858966E-2,6.5050206424977774E-2,7.1569958748614368E-2,-9.6126654390062388E-3,-1.4914573154876586E-2,-7.4797501551908114E-4,0.99999999999999989,8.050851000851568E-2,-9.4524792338847446E-2]
[23,0.40216020000000002,0.88253809999999999,0.78071559999999995,0.91405479999999995,0.5745323,0.55699779999999999,0.68172809999999995,0.98082879999999995,9.9649699999999994E-2,0.26960309999999998,null,null,"x9",-0.10095833024521196,-0.11990384311695441,-4.7785724433966427E-4,-1.968544677569839E-2,0.10903368003074521,0.10225763062505386,9.1754951378848273E-2,8.050851000851568E-2,1.0000000000000002,-9.5555724866570532E-2]
[24,0.25985000000000003,9.1380400000000001E-2,0.799543,0.87742450000000005,0.34245730000000002,0.3223761,0.5799668,0.72748080000000004,0.40612779999999998,0.325681,null,null,"x10",-9.463199427006316E-3,1.8594707001218062E-2,-0.11326984215621688,-0.12477705963284186,-9.5877757551776591E-2,-0.10176159590732109,-8.2719274088880371E-2,-9.4524792338847446E-2,-9.5555724866570532E-2,1]
[25,0.18183060000000001,0,0.50690040000000003,0.69007099999999999,0.63246939999999996,0.74644639999999995,0.49786979999999997,0.51540090000000005,1,0.49433890000000003]
[26,0.56253880000000001,0.80925539999999996,0.82705680000000004,0.79971360000000002,0.43811840000000002,0.19027920000000001,0.59614460000000002,1,0,0.64123850000000004]
[27,0.70377579999999995,1,0.99358579999999996,1,0.82100680000000004,0.83469499999999996,0.41837400000000002,0.38681729999999998,0.29327579999999998,0.93496069999999998]
[28,0.80054829999999999,1,1,1,0.59478569999999997,0.59912359999999998,0.44571349999999998,1,0.67510130000000002,0.47422809999999999]
[29,0.18910879999999999,8.4351499999999996E-2,0.42501800000000001,0.59548219999999996,0.78336939999999999,0.73271719999999996,0.92438010000000004,0.56899310000000003,0.43382799999999999,0]
[30,0.37016559999999998,0.48282609999999998,1,1,0.54405139999999996,0.70014390000000004,0.31420870000000001,0.94597229999999999,0.41267700000000002,0.1672727]
[31,0.42938500000000002,0.37690950000000001,0.87787510000000002,1,0.24703649999999999,0.55585739999999995,0.31075599999999998,0.15500620000000001,0.26955839999999998,0.37116110000000002]
[32,0.4654393,0.85591910000000004,0.85934509999999997,1,0.58557110000000001,0.70119279999999995,0.54816759999999998,0.46672180000000002,0.40715829999999997,1]
[33,0,0,0.2329369,0.1940093,0.43878610000000001,9.5164399999999996E-2,0.58309270000000002,6.4512100000000003E-2,0.87068840000000003,0.61448190000000003]
[34,0.39897949999999999,0.39301760000000002,0.85675199999999996,1,0,0,0.2319756,0.19580520000000001,0.45462459999999999,1]
[35,0.26062730000000001,0.2912093,0.71317909999999995,0.94570330000000002,9.8822699999999999E-2,0,0.25411529999999999,0.8841928,0.56467319999999999,1]
[36,0.36399229999999999,0.38722230000000002,0.76276409999999994,0.59735150000000004,0.60939889999999997,0.49989359999999999,0.60946140000000004,0.86738689999999996,0.29700460000000001,0.90448740000000005]
[37,0,0,0.28397159999999999,0,0.55272679999999996,0.98298700000000006,0.77567770000000003,0.1691598,0,0.7561175]
[38,0.28114509999999998,6.4146999999999996E-2,0.70247839999999995,0.86743130000000002,0.4978475,0.48416229999999999,0.38605499999999998,8.8490399999999997E-2,0.20375599999999999,0.62910319999999997]
[39,0,0,0.22656689999999999,0,0.66107879999999997,0.70783940000000001,0.435861,0.2060389,0.31790489999999999,0.67544349999999997]
[40,0.3664017,0.48807499999999998,0.88393730000000004,0.88882740000000005,0.30445070000000002,0.519312,0.73109250000000003,5.7239699999999998E-2,0.8439354,0.1594769]
[41,0.34777469999999999,0.71919759999999999,0.72549090000000005,0.98196450000000002,0.56022870000000002,0.60019270000000002,0.31266490000000002,5.6454400000000002E-2,0,0.51726559999999999]
[42,0.30797910000000001,0.16133600000000001,0.70568690000000001,0.84006409999999998,0.2810281,0.28981760000000001,0.36913180000000001,0.36973400000000001,0.7528108,0.34760590000000002]
[43,4.7495200000000001E-2,0.12785250000000001,0.58498320000000004,0.26059359999999998,0.1404646,0,0.80956189999999995,0.94046640000000004,0.33499000000000001,0.83846010000000004]
[44,0.40675830000000002,0.68354950000000003,0.94693989999999995,1,0.3697859,0,1,0.79293170000000002,0.55035599999999996,0]
[45,0,0,0.32756970000000002,0,0.93253490000000006,1,0.63275400000000004,0.57076000000000005,0.33257680000000001,0.46108270000000001]
[46,0.39397280000000001,0,0.66279670000000002,0.1881032,0.61137920000000001,0.47706739999999997,0.50701090000000004,0.5750343,0.67183720000000002,0.89687340000000004]
[47,0.4914771,0.74227540000000003,0.75021590000000005,0.8080292,0.1606939,0,0.44895600000000002,1,0.32749600000000001,0.32273990000000002]
[48,0.12073589999999999,0,0.2635962,2.1825399999999998E-2,0.86725940000000001,0.70542139999999998,0.238626,0.41188819999999998,0.59379870000000001,0.74675420000000003]
[49,0.1523349,0,0.65226969999999995,0.49153429999999998,0.30036279999999999,0.45277919999999999,0.40913389999999999,0.77783239999999998,0.72670939999999995,0.1542463]
[50,0.27177289999999998,0.2310903,0.56597719999999996,0.27096599999999998,0.47109519999999999,0.1246777,0.3944146,0,0,0.62725629999999999]
[51,0.1734011,0,0.4125529,0.45402029999999999,0.76848559999999999,0.97675860000000003,0.48703540000000001,0,0.51600330000000005,1]
[52,0.54396699999999998,1,0.83058670000000001,1,0.71038690000000004,0.6982931,0.4346853,0.52159509999999998,0.27392329999999998,0.2166053]
[53,0.4292685,0.48241679999999998,0.78504560000000001,0.85870190000000002,0.74649430000000006,0.77204050000000002,0.72483030000000004,0.39591209999999999,0.56889239999999996,0.51051060000000004]
[54,0.57296910000000001,0.80612450000000002,1,0.96289290000000005,0.46869709999999998,0.49476510000000001,0.30687520000000001,0.60766989999999999,0.91329439999999995,0.43139070000000002,null,null,"Following:"]
[55,0.2325169,0.24018249999999999,0.75442220000000004,0.47753479999999998,0.49122100000000002,0.64865519999999999,0.51294019999999996,0,0.63611980000000001,1,null,null,"Vigneau, E. and E. Qannari (2003). \"Clustering of variables around latent components.\" Communications in Statistics-Simulation and Computation 32(4): 1131-1150."]
[56,0.42856119999999998,0.3285111,0.59620989999999996,1,0.61856409999999995,1,0.47197119999999998,0.91542760000000001,0.68258450000000004,0.21973290000000001]
[57,0.1359909,0.14044219999999999,0.59078379999999997,0.82454830000000001,0.36043449999999999,0.52509249999999996,0.54127159999999996,0.77709079999999997,0.28410180000000002,0.89579279999999994]
[58,0,0,0.61198629999999998,0.2241706,0.34491660000000002,0.38901449999999999,0.43845050000000002,0.38833060000000003,0.30842370000000002,0.46570339999999999]
[59,0.43078090000000002,0.21541009999999999,0.82181110000000002,1,0.14947759999999999,0,0.54453090000000004,0.53611620000000004,0.52134979999999997,0.38110500000000003]
[60,0.44216050000000001,0.32793290000000003,0.89249040000000002,1,4.5488899999999999E-2,8.1623299999999996E-2,0.76078129999999999,3.3070299999999997E-2,0,0.69652139999999996]
[61,0.2997245,0.2088671,0.83574020000000004,0.87941550000000002,0.88319899999999996,1,0.60645349999999998,0.72612089999999996,0.50003089999999994,0.10307089999999999]
[62,0.2089075,0.52290009999999998,0.76783440000000003,0.77002579999999998,0.68272270000000002,0.95839730000000001,0.32371119999999998,0.30141790000000002,0.70483059999999997,0.44961580000000001]
[63,0.15333759999999999,0.14497070000000001,0.47470689999999999,0.4860488,0.49352649999999998,0.48046559999999999,0.80768660000000003,0.33013350000000002,0.53609379999999995,0.64546309999999996]
[64,0.179364,0,0.43877440000000001,0.3217525,0.56515769999999999,0.60118039999999995,0.38160909999999998,0.5170593,0.3669965,0.35280470000000003]
[65,0.14165929999999999,0.17559140000000001,0.69989590000000002,0.61751529999999999,0.44148599999999999,0.42758459999999998,0.42558970000000002,0.86452030000000002,0.35257850000000002,0.39995910000000001]
[66,0.3649675,0.38463459999999999,0.75612699999999999,0.66548450000000003,0.7029301,0.96240840000000005,0.77171190000000001,0.35328379999999998,0.69148730000000003,0.98970820000000004]
[67,7.0103799999999994E-2,0,0.62934009999999996,0.35543710000000001,0.3741969,0.66871670000000005,0.48619079999999998,0.7705476,0.81673580000000001,0.23702989999999999]
[68,0.47511199999999998,0.31287939999999997,0.69897869999999995,1,0.46446090000000001,0.54356579999999999,0.67736609999999997,0.67480370000000001,0.53417150000000002,0.85685630000000002]
[69,2.3302699999999999E-2,0,0.30411009999999999,0.22808149999999999,0.580453,0.51743220000000001,0.15310360000000001,1,0.30416500000000002,0.26027050000000002]
[70,0.1735179,0.10376150000000001,0.75523819999999997,0.51795190000000002,0.75147980000000003,1,0.45144780000000001,0.4660454,0.81455560000000005,0.55950310000000003]
[71,0.53846850000000002,0.63242520000000002,0.79757579999999995,1,0.85796249999999996,1,0.59204970000000001,6.29802E-2,0.54444190000000003,0.2432425]
[72,0.15368850000000001,8.8471999999999995E-3,0.55605170000000004,0.83660469999999998,0.69729459999999999,0.99541060000000003,0.82080779999999998,0.75132149999999998,0.97026860000000004,0.15226300000000001]
[73,0.6409475,1,1,1,0.51510230000000001,0.45491870000000001,0.5360106,0.52547980000000005,0.24986330000000001,0.38561990000000002]
[74,0.36098479999999999,0.67038520000000001,0.78894390000000003,0.964638,0.3907603,0.38926889999999997,9.3367099999999995E-2,0.69947870000000001,0.30361129999999997,0.14382929999999999]
[75,0.34082119999999999,0.43828109999999998,0.66085090000000002,0.72955270000000005,0.29647319999999999,4.7863099999999999E-2,0.3659945,0.38558779999999998,0.77059270000000002,0.2851649]
[76,0.34140609999999999,1,0.67965019999999998,0.92027689999999995,0.17895230000000001,0,0.37249749999999998,0.28983300000000001,0.51418779999999997,0.60208709999999999]
[77,0.37924570000000002,0.46656910000000001,0.68862279999999998,0.43119449999999998,0.82118119999999994,0.70875809999999995,0.50239210000000001,0.45244050000000002,0.52480539999999998,0.22055459999999999]
[78,0.29816179999999998,0.29621180000000003,0.73823859999999997,0.71639220000000003,0.39335409999999998,0.35828650000000001,0.78291829999999996,0,0.1306426,0.2531233]
[79,0.48241250000000002,0.53805780000000003,0.95234660000000004,0.97351920000000003,0.31735269999999999,0,0.49678230000000001,0.18840280000000001,0.6301717,0.52280780000000004]
[80,0.29970530000000001,0.21664910000000001,0.58500929999999995,0.176676,0.37713350000000001,0,0.6689174,0,0.35274460000000002,0.54499070000000005]
[81,0.13071179999999999,0,0.62537589999999998,0.1199998,0.54338280000000005,0.2474913,0.1669011,0,0.62416280000000002,0.32621289999999997]
[82,0.39811760000000002,0.54407970000000005,0.76837560000000005,1,0.3451089,0.53139139999999996,0.4437681,0.91207609999999995,0.52880419999999995,0.75294819999999996]
[83,0.376938,0.2410823,0.72875889999999999,0.74816939999999998,0.33521129999999999,0.40173809999999999,0.33173540000000001,0.20817749999999999,7.4758000000000003E-3,0.57610749999999999]
[84,0,0,0.44241589999999997,0.25431779999999998,0.32191009999999998,0,0.29364479999999998,0,1,0.37319970000000002]
[85,0.24529339999999999,0.51848939999999999,0.77204669999999997,0.7479903,0.60121230000000003,0.36252620000000002,0.36335440000000002,0.35513210000000001,0.99234820000000001,0.68749579999999999]
[86,0.54724320000000004,0.80588300000000002,0.93965799999999999,1,0.76699260000000002,0.84643330000000006,0.86735680000000004,0.33168619999999999,0.8430898,0.12007900000000001]
[87,0.518177,0.52676319999999999,0.88029380000000002,1,0.63482470000000002,1,1,4.9882799999999998E-2,0.34704410000000002,0.25277189999999999]
[88,0.29313879999999998,0.78986849999999997,0.85589950000000004,1,0.49808350000000001,0.48223680000000002,0.41964269999999998,0.4227958,0.75411720000000004,0.33920719999999999]
[89,0.6677362,0.82495870000000004,0.99644860000000002,1,0.56025179999999997,0.5882252,0.46914990000000001,0.7718758,0.68961519999999998,0.72392350000000005]
[90,0.24513389999999999,0.75911070000000003,0.73648119999999995,0.53004399999999996,0.42733409999999999,4.7952700000000001E-2,0.55339059999999995,0.72930649999999997,0.68720239999999999,0.78026499999999999]
[91,0,0,0.5374506,0.29869020000000002,0.42247580000000001,0.90656939999999997,0.20743239999999999,0.65316949999999996,0.55310029999999999,0.87011490000000002]
[92,0.37404219999999999,1.54363E-2,0.51899589999999995,0.58990220000000004,0.57405399999999995,0.44516149999999999,0.57199350000000004,0.26162649999999998,0.66932259999999999,0.40680699999999997]
[93,0.37670900000000002,0.14803839999999999,0.68007490000000004,0.77569600000000005,0.3078552,5.4951199999999999E-2,0.58824929999999997,9.5028199999999993E-2,0.33843509999999999,0.26303149999999997]
[94,0.47500569999999998,0.74477389999999999,0.89780870000000002,1,0.57054360000000004,0.62446140000000006,0.4030823,2.7813000000000001E-2,0.539601,0.8459721]
[95,0.51659719999999998,0.50478679999999998,0.79599039999999999,0.58150120000000005,0.54469920000000005,0.22852620000000001,0.29984309999999997,0.53640030000000005,0.2480521,1]
[96,0.1649284,7.1026300000000001E-2,0.73155709999999996,0.52870410000000001,0.49157070000000003,0.918489,0.61132660000000005,0.27983609999999998,0.68587849999999995,0.75853939999999997]
[97,0.23311100000000001,0.1658202,0.66843649999999999,0.59188510000000005,0.55241569999999995,0.92281809999999997,0.3143398,0.55809149999999996,0.1804926,1.7862200000000002E-2]
[98,0.59943270000000004,0.65182949999999995,0.84377809999999998,0.90207360000000003,0.26318979999999997,0,0.53672160000000002,0.83686130000000003,0.24303630000000001,0.4285542]
[99,0.37160969999999999,0.4518837,0.68390949999999995,0.87535149999999995,0.2981895,0.26983499999999999,0.68478490000000003,0.8521898,0.7785379,0.3670735]
[100,0.41443600000000003,0.2953423,0.75767099999999998,0.60029549999999998,0.57734600000000003,0.29790410000000001,0.5005134,0.67793349999999997,0.56454930000000003,6.1460099999999997E-2]

{"sheet":"Betti_Verma_weights","index":6,"n_rows":55,"n_cols":18,"rows_kept":55,"cut":false,"hidden":false,"merged":[[3,0,14,0],[23,0,29,0],[31,0,32,0],[34,0,35,0],[43,0,44,0],[1,2,1,11],[26,12,26,50],[50,1,50,11],[52,1,52,11],[34,2,34,11]]}
["Work flow to obtain Betti-Verma weights (and then use them to obtain scores and statistics)"]
[null,null,"Continuous indicators (normalized to [0, 1]; higher values = less desirable)"]
["1. Step:","Correlation table:","x1","x2","x3","x4","x5","x6","x7","x8","x9","x10"]
["Verify all indicators have the same substantive direction; else reverse the incorrectly coded ones [= replace x by 1 -x]\n\nCalculate the correlation table \n(use function CORREL)","x1",{"$f":"CORREL(INDIRECT($B4&\"_\"),(INDIRECT(C$3&\"_\")))","v":1},{"$f":"CORREL(INDIRECT($B4&\"_\"),(INDIRECT(D$3&\"_\")))","v":0.78672245133808949},0.79889174150850917,0.77641899323233288,3.9503173781473841E-3,-6.7275487808262577E-2,1.4487215936882274E-2,7.7519211116446676E-2,-0.10095833024521196,-9.463199427006316E-3]
[null,"x2",{"$f":"CORREL(INDIRECT($B5&\"_\"),(INDIRECT(C$3&\"_\")))","v":0.78672245133808949},1,0.72257491161806853,0.70955877796490285,3.4378474203994425E-2,-3.8716698083832005E-2,-1.1908707262028514E-2,7.6141607101858966E-2,-0.11990384311695441,1.8594707001218062E-2]
[null,"x3",0.79889174150850917,0.72257491161806853,1,0.81456959919740124,-0.10983564891929191,1.0192219565967985E-2,6.3634634465746425E-3,6.5050206424977774E-2,-4.7785724433966427E-4,-0.11326984215621688]
[null,"x4",0.77641899323233288,0.70955877796490285,0.81456959919740124,1.0000000000000002,-0.13780042852972502,1.5678220796814513E-2,4.3431575266350803E-2,7.1569958748614368E-2,-1.968544677569839E-2,-0.12477705963284186]
[null,"x5",3.9503173781473841E-3,3.4378474203994425E-2,-0.10983564891929191,-0.13780042852972502,1.0000000000000002,0.78544115032911455,9.9737621598529211E-2,-9.6126654390062388E-3,0.10903368003074521,-9.5877757551776591E-2]
[null,"x6",-6.7275487808262577E-2,-3.8716698083832005E-2,1.0192219565967985E-2,1.5678220796814513E-2,0.78544115032911455,0.99999999999999989,9.7325618071822106E-2,-1.4914573154876586E-2,0.10225763062505386,-0.10176159590732109]
[null,"x7",1.4487215936882274E-2,-1.1908707262028514E-2,6.3634634465746425E-3,4.3431575266350803E-2,9.9737621598529211E-2,9.7325618071822106E-2,1,-7.4797501551908114E-4,9.1754951378848273E-2,-8.2719274088880371E-2]
[null,"x8",7.7519211116446676E-2,7.6141607101858966E-2,6.5050206424977774E-2,7.1569958748614368E-2,-9.6126654390062388E-3,-1.4914573154876586E-2,-7.4797501551908114E-4,0.99999999999999989,8.050851000851568E-2,-9.4524792338847446E-2]
[null,"x9",-0.10095833024521196,-0.11990384311695441,-4.7785724433966427E-4,-1.968544677569839E-2,0.10903368003074521,0.10225763062505386,9.1754951378848273E-2,8.050851000851568E-2,1.0000000000000002,-9.5555724866570532E-2]
[null,"x10",-9.463199427006316E-3,1.8594707001218062E-2,-0.11326984215621688,-0.12477705963284186,-9.5877757551776591E-2,-0.10176159590732109,-8.2719274088880371E-2,-9.4524792338847446E-2,-9.5555724866570532E-2,1]
[]
[null,"Statistics:"]
[null,"Mean",{"$f":"AVERAGE(INDIRECT(C$3&\"_\"))","v":0.30735680500000001},{"$f":"AVERAGE(INDIRECT(D$3&\"_\"))","v":0.34529384000000007},0.69575399999999976,0.65987497400000006,0.5001030179999999,0.50818216299999996,0.49953742699999992,0.49921009999999993,0.50234909000000005,0.49364980199999969]
[null,"SD ",{"$f":"_xlfn.STDEV.S(INDIRECT(C$3&\"_\"))","v":0.18606778254974576},{"$f":"_xlfn.STDEV.S(INDIRECT(D$3&\"_\"))","v":0.31166658848155221},0.19190441727284216,0.31960823602541022,0.1997369634508214,0.33556117741569641,0.19879990384166793,0.33471924134642811,0.2666228210669499,0.27763206946436358]
["Calculate the needed statistics:","C.o.V.",{"$f":"C17/C16","v":0.60538039022674561},{"$f":"D17/D16","v":0.90261265153630355},0.27582222635132853,0.48434665447004843,0.39939163784614762,{"$f":"H17/H16","v":0.66031671681419568},{"$f":"I17/I16","v":0.39796798617387275},{"$f":"J17/J16","v":0.67049773501463239},{"$f":"K17/K16","v":0.53075207335789121},{"$f":"L17/L16","v":0.56240692964840644},"C.o.V. ="]
[null,null,null,null,null,null,null,null,null,null,null,null,"Coefficient of variation ="]
[null,null,null,null,null,null,null,null,null,null,null,null,"Standard deviation / Mean"]
[]
[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 component\n\n2.a.: Decide, on substantive grounds, on the analytic option: Information value, user-defined, equal\n\n2.b.: Set or calculate the value for each indicator. No normalization of weight required at this point.","Three analyic choices:"]
[null,"Information value \n(here: = coefficient of variation)",{"$f":"C18","v":0.60538039022674561},{"$f":"D18","v":0.90261265153630355},0.27582222635132853,0.48434665447004843,0.39939163784614762,0.66031671681419568,0.39796798617387275,0.67049773501463239,0.53075207335789121,0.56240692964840644,"Betti-Verma default option"]
[]
[null,"User-defined",1,2,2,5,1,3,1,1,3,5,"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).\nFor the calculation of simple weighted scores (not BV), we name this range \"UserDef_first\"."]
[]
[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 indicators"]
[]
[]
["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.3516773597430129},{"$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.36009512755238915},{"$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.37685872351565669},{"$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":0.40605527716540007},{"$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.76057364664067684},{"$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.63974115826420963},{"$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.81696196253923181},{"$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.8287964298904309},{"$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":1.0894714914896737},{"$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":3.4807378817592727},"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,null,null,null,null,null,"Choice of rhoH = 0"]
[]
["Step 4:\nMultiply: First x second weight x (second w. is > 0)","Combine first and second weights:","Exclude indicators whose sum of correlation coefficients with the other indicators is negative. Most likely, they relate to a different concept."]
[null,"Three analyic choices from first weight:"]
["The assigment of zero weights to indicators with negative second weights is a safeguard.","Information value",{"$f":"C25*C$32*(SUM(C$4:C$13) - 1 > 0)","v":0.21289857727513675},0.3250264178853654,0.10394601214000819,0.19667151502496977,0.30376675443443707,0.42243178123593367,0.32512470701239293,0.55570612902974748,0.57823925297245848,0,"Inclusion is modeled through the indicator  (red) in this formula:"]
["It will rarely come into play (here, it does for x10).",null,null,null,null,null,null,null,null,null,null,null,"=R[-12]C*R32C*(sum(R4C:R13C) - 1 > 0)"]
[null,"User-defined",{"$f":"C27*C$32*(SUM(C$4:C$13) - 1 > 0)","v":0.3516773597430129},0.7201902551047783,0.75371744703131338,2.0302763858270003,0.76057364664067684,1.9192234747926289,0.81696196253923181,0.8287964298904309,3.2684144744690213,0]
[null,null,null,null,null,null,null,null,null,null,null,null,"Note mixed-type vs. absolute cell references in formulas!"]
[null,"Equal",{"$f":"C29*C$32*(SUM(C$4:C$13) - 1 > 0)","v":0.3516773597430129},0.36009512755238915,0.37685872351565669,0.40605527716540007,0.76057364664067684,0.63974115826420963,0.81696196253923181,0.8287964298904309,1.0894714914896737,0]
[]
[null,"BETTI-VERMA FORMULA ELEMENTS, NORMALIZED (= summing to 1)"]
["Step 5:\nNormalize the weights such that they sum to 1 over all indicators","Three analyic choices from first weight:"]
[null,"Information value",{"$f":"C37 / SUM($C37:$L37)","v":7.040736571317395E-2},{"$f":"D37 / SUM($C37:$L37)","v":0.10748899388333466},{"$f":"E37 / SUM($C37:$L37)","v":3.4375828081319316E-2},{"$f":"F37 / SUM($C37:$L37)","v":6.5040938558419206E-2},{"$f":"G37 / SUM($C37:$L37)","v":0.10045824281544899},{"$f":"H37 / SUM($C37:$L37)","v":0.13970177391983593},0.10752149893151688,0.18377673142026651,0.1912286266766845,{"$f":"L37 / SUM($C37:$L37)","v":0},"For the calculation of the BV scores, we name this range \"InfoValue_Cont\""]
[]
[null,"User-defined",{"$f":"C39 / SUM($C39:$L39)","v":3.0714632063151257E-2},{"$f":"D39 / SUM($C39:$L39)","v":6.2899638228558985E-2},{"$f":"E39 / SUM($C39:$L39)","v":6.582782036939025E-2},{"$f":"F39 / SUM($C39:$L39)","v":0.17731932536899622},{"$f":"G39 / SUM($C39:$L39)","v":6.6426623910530933E-2},0.16762023838638485,7.1351440159010709E-2,7.2385033309906394E-2,0.28545524820407031,{"$f":"L39 / SUM($C39:$L39)","v":0},"For the calculation of the BV scores, we name this range \"UserDef_Cont\""]
[]
[null,"Equal",{"$f":"C41 / SUM($C41:$L41)","v":6.2462330355473931E-2},{"$f":"D41 / SUM($C41:$L41)","v":6.3957431985414384E-2},{"$f":"E41 / SUM($C41:$L41)","v":6.6934857856014829E-2},{"$f":"F41 / SUM($C41:$L41)","v":7.2120533671609671E-2},{"$f":"G41 / SUM($C41:$L41)","v":0.13508746315330958},0.1136260906834976,0.14510273857057887,0.14720468909082801,0.19350386463327324,{"$f":"L41 / SUM($C41:$L41)","v":0},"For the calculation of the BV scores, we name this range \"Equal_Cont\""]
[]
["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 indicators 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 (information value, or user-defined, or equal). 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:\nReview the weights and the score statistics for plausibility. Use the final index in the further analysis of the survey / assessment, etc. data."]

{"sheet":"Betti_Verma_scores","index":7,"n_rows":104,"n_cols":16,"rows_kept":104,"cut":false,"hidden":false,"merged":[[1,13,1,15],[2,0,2,5],[1,11,1,12]]}
["Calculation of five variants of the same index, incl. three Betti-Verma options"]
[null,null,null,null,null,null,null,null,null,null,null,"Additive aggregation",null,"Betti-Verma, by type of first weight"]
["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,"Unweighted\n(arithmetic mean)","Weighted mean\n(user defined weights)","Information value","User-defined","Equal"]
["id","x1","x2","x3","x4","x5","x6","x7","x8","x9","x10","Index1a","Index1b","Index2a","Index2b","Index2c"]
[1,0,9.6805699999999995E-2,0.36879220000000001,0.31250610000000001,0.17411189999999999,0,0.6878377,0.19589889999999999,5.5654799999999997E-2,0.72126049999999997,{"$f":"AVERAGE($B5:$K5)","v":0.26128677999999994},{"$f":"SUMPRODUCT($B5:$K5, UserDef_first) / SUM(UserDef_first)","v":0.30520173750000001},{"$f":"SUMPRODUCT($B5:$K5, InfoValue_Cont)","v":0.18150154116499206},{"$f":"SUMPRODUCT($B5:$K5, UserDef_Cont)","v":0.17649018036932215},{"$f":"SUMPRODUCT($B5:$K5, Equal_Cont)","v":0.21634872855760895}]
[2,0.38586369999999998,0.31185550000000001,0.76197079999999995,0.77994240000000004,0.8320824,0.88053459999999995,0.67231750000000001,1,1,0.27313080000000001,{"$f":"AVERAGE($B6:$K6)","v":0.68976976999999995},0.66437025000000005,0.79150596836890985,0.82860394443532326,0.8020189611647639]
[3,0.45835239999999999,0.58566859999999998,0.70788549999999995,1,0.77450969999999997,0.90741439999999995,0.60619149999999999,0.83344339999999995,0.4730663,0.65859760000000001,0.70051294000000008,0.73725147083333331,0.69798262876033301,0.71700415877993151,0.69550984973127639]
[4,0.121715,0,0.42940990000000001,2.5131799999999999E-2,0.42405130000000002,7.9077700000000001E-2,0.45662940000000002,0.91168159999999998,0.31436350000000002,0.29639120000000002,0.30584513999999996,0.23170148750000005,0.35537093517583934,0.26619520138560554,0.3657194513402266]
[5,0.35888350000000002,0.25929200000000002,0.65047529999999998,0.61478679999999997,0.54972129999999997,0.58387920000000004,0.26710640000000002,0.80575509999999995,0.61743999999999999,0.47659849999999998,0.51839380999999995,0.53591187500000004,0.54714993379597554,0.56718592473998142,0.54432876611859371]
[6,0.1229391,0,0.2809352,4.64795E-2,0.37882130000000003,0.10472339999999999,0.53353070000000002,0.55874630000000003,0.78453910000000004,0.77996500000000002,0.35906796000000007,0.37316324166666665,0.38409895946935735,0.3756925328518631,0.40438705451140688]
[7,0.52080040000000005,0.57332810000000001,0.8956906,1,0.64453320000000003,0.90931229999999996,0.28491739999999999,5.3319900000000003E-2,0.45272519999999999,0.59345899999999996,0.59280861000000007,0.68729232499999993,0.5129146246830818,0.63699393088905876,0.52845772315135731]
[8,0.30427149999999997,0.46575929999999999,0.80818820000000002,0.6814616,0.4735529,0.69086369999999997,0.64392910000000003,1,1,0.48451739999999999,0.65525436999999997,0.66133894166666662,0.73192075077703023,0.76372406610834109,0.72865324319411617]
[9,0.48286410000000002,0.7545037,0.93756510000000004,1,0.41354859999999999,0.56522819999999996,0.13754769999999999,0,0.72485279999999996,0.14017160000000001,0.51562817999999999,0.58288329166666675,0.48627823637341694,0.64026778066512824,0.49360352439636845]
[10,0.60532430000000004,0.44566109999999998,0.8799941,0.97894820000000005,0.2311956,1.7764700000000001E-2,0.53707400000000005,0.97544810000000004,0.27293460000000003,0.50023930000000005,0.54445840000000012,0.55284949166666664,0.49935702580098379,0.48331349004089885,0.50340335541038739]
[11,1.2052E-2,0,0.6653038,0.51392260000000001,0.60411550000000003,0.94963430000000004,0.49563600000000002,0.13920260000000001,0.52678009999999997,0.12762499999999999,0.40342719000000005,0.42577478749999997,0.43010806430653525,0.5304140327968303,0.4662042484516693]
[12,0.1111596,0.31232080000000001,0.51860589999999995,0.50930640000000005,0.55693239999999999,0.35986200000000002,0.60489309999999996,1,0.62471500000000002,0.6274248,0.52252200000000004,0.52384272916666663,0.56685172247799009,0.53869615065091059,0.57034818409824894]
[13,0.11681179999999999,0.25363059999999998,0.59500799999999998,0.50990829999999998,0.63368340000000001,0.90685780000000005,0.41045490000000001,7.7721799999999994E-2,0.20030339999999999,0.41840129999999998,0.41227812999999996,0.45412419583333336,0.37617400903489345,0.43531704952877548,0.39852354873439044]
[14,0.57756600000000002,0.99016800000000005,0.92857659999999997,0.96269260000000001,0.67429349999999999,0.65979279999999996,0.27692099999999997,0.52028189999999996,0,0.85532520000000001,0.64456175999999998,0.70650079166666657,0.52693531522630466,0.52465611413517665,0.51381697326902798]
[15,0.22669629999999999,0.24279439999999999,0.74882199999999999,0.41345710000000002,0.53756890000000002,0.80086930000000001,0.33044679999999998,0.91888000000000003,0.44137729999999997,0.16380929999999999,0.48247213999999994,0.44207902500000001,0.54938079136959495,0.5308770988496877,0.5418683866707843]
[16,0.16239190000000001,0,0.59931129999999999,0.232293,0.7688294,1,0.2406394,1,0.40822999999999998,0.69714989999999999,0.51088449000000002,0.51009949166666668,0.55179688999586229,0.51040657893302033,0.54561279993526457]
[17,0.4185179,0,0.82958900000000002,0.90495479999999995,0.16104979999999999,1.39629E-2,0.23317589999999999,0.84168759999999998,0.61320430000000004,0.3630371,0.4379179299999999,0.48062792916666669,0.43198931749166636,0.49357449962444305,0.44667043190103572]
[18,7.67288E-2,0,0.32892769999999999,0,0.50709029999999999,0.2190742,0.60645539999999998,0.54708959999999995,0.96376450000000002,0.82997120000000002,0.40791016999999996,0.42056631666666666,0.44830458349541968,0.45239905634533262,0.47522812481014592]
[19,0.2200056,0.14729709999999999,0.52077099999999998,0.29393370000000002,0.41325659999999997,8.3432900000000004E-2,0.1061209,0.3033052,0.29714859999999998,0.36684939999999999,0.27521209999999996,0.28435352083333326,0.2454872811195592,0.25820921733829166,0.26207091822308032]
[20,0.3125771,0.228437,0.73162070000000001,0.54152940000000005,0.70623270000000005,0.71762740000000003,0.58776280000000003,0.12625410000000001,0.44999090000000003,0,0.44020321000000007,0.4109768333333334,0.45058540310279072,0.51488434252128723,0.4900514003730263]
[21,0.30009370000000002,0.26848519999999998,0.90264449999999996,0.59465900000000005,0.42652780000000001,0.61194979999999999,0.98119630000000002,0.2425937,1,0.51166829999999996,0.58398183000000004,0.61083986666666668,0.5893444492951645,0.69490168569523514,0.63796276843712274]
[22,0,0,0.60180880000000003,0.30137829999999999,0.57725329999999997,1,0.63885420000000004,1,0.58074650000000005,0.1725565,0.48725975999999999,0.43881827499999998,0.60150387768227387,0.58276652149925512,0.60590420007530688]
[23,0.40216020000000002,0.88253809999999999,0.78071559999999995,0.91405479999999995,0.5745323,0.55699779999999999,0.68172809999999995,0.98082879999999995,9.9649699999999994E-2,0.26960309999999998,0.61428084999999999,0.57724953333333318,0.61760679947173047,0.56094939852042358,0.60323134979191861]
[24,0.25985000000000003,9.1380400000000001E-2,0.799543,0.87742450000000005,0.34245730000000002,0.3223761,0.5799668,0.72748080000000004,0.40612779999999998,0.325681,0.47322877000000013,0.49552670416666666,0.46582658329041948,0.50870202594751524,0.49159559115868212]
[25,0.18183060000000001,0,0.50690040000000003,0.69007099999999999,0.63246939999999996,0.74644639999999995,0.49786979999999997,0.51540090000000005,1,0.49433890000000003,0.52653273999999994,0.58344834166666659,0.58240587746184413,0.68673454986948523,0.60692519642975129]
[26,0.56253880000000001,0.80925539999999996,0.82705680000000004,0.79971360000000002,0.43811840000000002,0.19027920000000001,0.59614460000000002,1,0,0.64123850000000004,0.58643453000000001,0.56854267916666668,0.52550784803368322,0.44034624677582634,0.51444197051705653]
[27,0.70377579999999995,1,0.99358579999999996,1,0.82100680000000004,0.83469499999999996,0.41837400000000002,0.38681729999999998,0.29327579999999998,0.93496069999999998,0.73864912000000005,0.80732755833333325,0.62747648698312086,0.66325773757667528,0.62669235630184128]
[28,0.80054829999999999,1,1,1,0.59478569999999997,0.59912359999999998,0.44571349999999998,1,0.67510130000000002,0.47422809999999999,0.75895005000000004,0.75145261249999995,0.76751922294415142,0.76746871969400776,0.74395474885789337]
[29,0.18910879999999999,8.4351499999999996E-2,0.42501800000000001,0.59548219999999996,0.78336939999999999,0.73271719999999996,0.92438010000000004,0.56899310000000003,0.43382799999999999,0,0.47372482999999993,0.4150682083333333,0.54369914191903879,0.55051832813137891,0.57951720206212121]
[30,0.37016559999999998,0.48282609999999998,1,1,0.54405139999999996,0.70014390000000004,0.31420870000000001,0.94597229999999999,0.41267700000000002,0.1672727,0.59373176999999999,0.5964531833333333,0.61639097927791575,0.64707831328504961,0.61080494820449593]
[31,0.42938500000000002,0.37690950000000001,0.87787510000000002,1,0.24703649999999999,0.55585739999999995,0.31075599999999998,0.15500620000000001,0.26955839999999998,0.37116110000000002,0.45935452000000004,0.54099190833333333,0.38188199561646152,0.49192643088219751,0.39840874581655311]
[32,0.4654393,0.85591910000000004,0.85934509999999997,1,0.58557110000000001,0.70119279999999995,0.54816759999999998,0.46672180000000002,0.40715829999999997,1,0.6889515100000001,0.78422839583333326,0.59870992911807164,0.64757424869346436,0.59926355978805568]
[33,0,0,0.2329369,0.1940093,0.43878610000000001,9.5164399999999996E-2,0.58309270000000002,6.4512100000000003E-2,0.87068840000000003,0.61448190000000003,0.30936718000000002,{"$f":"SUMPRODUCT($B37:$K37, UserDef_first) / SUM(UserDef_first)","v":0.35384496250000003},{"$f":"SUMPRODUCT($B37:$K37, InfoValue_Cont)","v":0.31905163285152049},{"$f":"SUMPRODUCT($B37:$K37, UserDef_Cont)","v":0.38965067298668254},{"$f":"SUMPRODUCT($B37:$K37, Equal_Cont)","v":0.36225771392932182}]
[34,0.39897949999999999,0.39301760000000002,0.85675199999999996,1,0,0,0.2319756,0.19580520000000001,0.45462459999999999,1,0.45311544999999998,0.61209055416666669,0.3126927017798144,0.43119275262548828,0.32998004825187527]
[35,0.26062730000000001,0.2912093,0.71317909999999995,0.94570330000000002,9.8822699999999999E-2,0,0.25411529999999999,0.8841928,0.56467319999999999,1,0.50125229999999998,0.62204462500000002,0.4434035846911642,0.49084773277386962,0.44049186234873899]
[36,0.36399229999999999,0.38722230000000002,0.76276409999999994,0.59735150000000004,0.60939889999999997,0.49989359999999999,0.60946140000000004,0.86738689999999996,0.29700460000000001,0.90448740000000005,0.58989630000000004,0.61042089166666669,0.54510950395770874,0.50699499131230152,0.55435094880363522]
[37,0,0,0.28397159999999999,0,0.55272679999999996,0.98298700000000006,0.77567770000000003,0.1691598,0,0.7561175,0.35206404000000002,0.36646066666666671,0.3171024137501785,0.28776788076632309,0.34282110419648565]
[38,0.28114509999999998,6.4146999999999996E-2,0.70247839999999995,0.86743130000000002,0.4978475,0.48416229999999999,0.38605499999999998,8.8490399999999997E-2,0.20375599999999999,0.62910319999999997,0.42046161999999992,0.51388400833333325,0.32164349097737638,0.41906495537109412,0.36198139385212308]
[39,0,0,0.22656689999999999,0,0.66107879999999997,0.70783940000000001,0.435861,0.2060389,0.31790489999999999,0.67544349999999997,0.32307333999999999,0.34210678749999995,0.31860776031822119,0.31423691181852353,0.33998804882364614]
[40,0.3664017,0.48807499999999998,0.88393730000000004,0.88882740000000005,0.30445070000000002,0.519312,0.73109250000000003,5.7239699999999998E-2,0.8439354,0.1594769,0.52427486000000001,0.56393637083333348,0.5201017980920386,0.66223199788680998,0.55532035426823556]
[41,0.34777469999999999,0.71919759999999999,0.72549090000000005,0.98196450000000002,0.56022870000000002,0.60019270000000002,0.31266490000000002,5.6454400000000002E-2,0,0.51726559999999999,0.48212339999999998,0.56096784583333326,0.37471974955809317,0.44201189726456919,0.384657593511812]
[42,0.30797910000000001,0.16133600000000001,0.70568690000000001,0.84006409999999998,0.2810281,0.28981760000000001,0.36913180000000001,0.36973400000000001,0.7528108,0.34760590000000002,0.44251942999999994,0.50533974999999998,0.43823967833630839,0.55026300105838666,0.46193127881682239]
[43,4.7495200000000001E-2,0.12785250000000001,0.58498320000000004,0.26059359999999998,0.1404646,0,0.80956189999999995,0.94046640000000004,0.33499000000000001,0.83846010000000004,0.40848674999999995,0.41099574999999994,0.39219693725909588,0.32501146585210922,0.40880127412429312]
[44,0.40675830000000002,0.68354950000000003,0.94693989999999995,1,0.3697859,0,1,0.79293170000000002,0.55035599999999996,0,0.57503213000000009,0.52006344583333342,0.59534136896582124,0.60555622795455777,0.62290441824495724]
[45,0,0,0.32756970000000002,0,0.93253490000000006,1,0.63275400000000004,0.57076000000000005,0.33257680000000001,0.46108270000000001,0.42572781000000004,0.37893050833333336,0.48116834151863641,0.43252676661143674,0.50171347860819648]
[46,0.39397280000000001,0,0.66279670000000002,0.1881032,0.61137920000000001,0.47706739999999997,0.50701090000000004,0.5750343,0.67183720000000002,0.89687340000000004,0.49840751,0.51185780833333339,0.47948942443925724,0.4792428212570401,0.50755524409077768]
[47,0.4914771,0.74227540000000003,0.75021590000000005,0.8080292,0.1606939,0,0.44895600000000002,1,0.32749600000000001,0.32273990000000002,0.50518834000000001,0.48843512916666665,0.50355310484441429,0.46302715824962559,0.48409283228203348]
[48,0.12073589999999999,0,0.2635962,2.1825399999999998E-2,0.86725940000000001,0.70542139999999998,0.238626,0.41188819999999998,0.59379870000000001,0.74675420000000003,0.39699054,0.41276084166666666,0.41955775738471568,0.41712620960850161,0.43422893133342605]
[49,0.1523349,0,0.65226969999999995,0.49153429999999998,0.30036279999999999,0.45277919999999999,0.40913389999999999,0.77783239999999998,0.72670939999999995,0.1542463,0.41172028999999999,0.4046488416666667,0.48445146563133745,0.52356070702793356,0.4951353709562421]
[50,0.27177289999999998,0.2310903,0.56597719999999996,0.27096599999999998,0.47109519999999999,0.1246777,0.3944146,0,0,0.62725629999999999,0.29572502000000001,0.31652342916666665,0.18820543652850721,0.18852127387073331,0.22421766047514122]
[51,0.1734011,0,0.4125529,0.45402029999999999,0.76848559999999999,0.97675860000000003,0.48703540000000001,0,0.51600330000000005,1,0.47882572000000001,0.58343396250000001,0.420617469440971,0.50980892306359549,0.45650622731320833]
[52,0.54396699999999998,1,0.83058670000000001,1,0.71038690000000004,0.6982931,0.4346853,0.52159509999999998,0.27392329999999998,0.2166053,0.62300426999999992,0.61964514166666662,0.60327536012764638,0.62280302571780533,0.59381978323397344]
[53,0.4292685,0.48241679999999998,0.78504560000000001,0.85870190000000002,0.74649430000000006,0.77204050000000002,0.72483030000000004,0.39591209999999999,0.56889239999999996,0.51051060000000004,0.62741130000000012,0.65417879999999995,0.60724524824378101,0.66923677490582612,0.63424802765910271]
[54,0.57296910000000001,0.80612450000000002,1,0.96289290000000005,0.46869709999999998,0.49476510000000001,0.30687520000000001,0.60766989999999999,0.91329439999999995,0.43139070000000002,0.65646789000000005,0.69850236666666665,0.65951739502352935,0.74552429736153891,0.65396545867344136]
[55,0.2325169,0.24018249999999999,0.75442220000000004,0.47753479999999998,0.49122100000000002,0.64865519999999999,0.51294019999999996,0,0.63611980000000001,1,0.49935925999999997,0.60282860416666673,0.41594297263886815,0.51612783029179177,0.45240483139989435]
[56,0.42856119999999998,0.3285111,0.59620989999999996,1,0.61856409999999995,1,0.47197119999999998,0.91542760000000001,0.68258450000000004,0.21973290000000001,0.62615624999999997,0.64293267083333328,0.70237401639533692,0.75388862572903315,0.69231604949568404]
[57,0.1359909,0.14044219999999999,0.59078379999999997,0.82454830000000001,0.36043449999999999,0.52509249999999996,0.54127159999999996,0.77709079999999997,0.28410180000000002,0.89579279999999994,0.50755491999999991,0.59610534166666673,0.46351173776176424,0.48603621063815822,0.47274811167542202]
[58,0,0,0.61198629999999998,0.2241706,0.34491660000000002,0.38901449999999999,0.43845050000000002,0.38833060000000003,0.30842370000000002,0.46570339999999999,0.31709962000000003,0.33072312083333327,0.30210195727382949,0.31558841414416217,0.32839225606306127]
[59,0.43078090000000002,0.21541009999999999,0.82181110000000002,1,0.14947759999999999,0,0.54453090000000004,0.53611620000000004,0.52134979999999997,0.38110500000000003,0.46005815999999999,0.50853843333333337,0.41856346372561437,0.49460903484093199,0.44682049281284197]
[60,0.44216050000000001,0.32793290000000003,0.89249040000000002,1,4.5488899999999999E-2,8.1623299999999996E-2,0.76078129999999999,3.3070299999999997E-2,0,0.69652139999999996,0.42800690000000002,0.51874268749999997,0.26595212076366548,0.34365770816960284,0.31113044300538523]
[61,0.2997245,0.2088671,0.83574020000000004,0.87941550000000002,0.88319899999999996,1,0.60645349999999998,0.72612089999999996,0.50003089999999994,0.10307089999999999,0.60426224999999989,0.58405154999999997,0.65217852864700798,0.69815213872051529,0.67602374037916879]
[62,0.2089075,0.52290009999999998,0.76783440000000003,0.77002579999999998,0.68272270000000002,0.95839730000000001,0.32371119999999998,0.30141790000000002,0.70483059999999997,0.44961580000000001,0.56903632999999998,0.63275500000000007,0.57485099975712495,0.67850281128184742,0.58227692591198388]
[63,0.15333759999999999,0.14497070000000001,0.47470689999999999,0.4860488,0.49352649999999998,0.48046559999999999,0.80768660000000003,0.33013350000000002,0.53609379999999995,0.64546309999999996,0.45524331000000001,0.48880321249999992,0.44104207629238779,0.47913925125776002,0.47647194485195499]
[64,0.179364,0,0.43877440000000001,0.3217525,0.56515769999999999,0.60118039999999995,0.38160909999999998,0.5170593,0.3669965,0.35280470000000003,0.37246985999999999,0.36658564999999999,0.39563446968214466,0.39917390835031874,0.41093457617737267]
[65,0.14165929999999999,0.17559140000000001,0.69989590000000002,0.61751529999999999,0.44148599999999999,0.42758459999999998,0.42558970000000002,0.86452030000000002,0.35257850000000002,0.39995910000000001,0.45463801000000004,{"$f":"SUMPRODUCT($B69:$K69, UserDef_first) / SUM(UserDef_first)","v":0.46050379999999996},{"$f":"SUMPRODUCT($B69:$K69, InfoValue_Cont)","v":0.46921837159517549},{"$f":"SUMPRODUCT($B69:$K69, UserDef_Cont)","v":0.46555407581683783},{"$f":"SUMPRODUCT($B69:$K69, Equal_Cont)","v":0.47692667616455264}]
[66,0.3649675,0.38463459999999999,0.75612699999999999,0.66548450000000003,0.7029301,0.96240840000000005,0.77171190000000001,0.35328379999999998,0.69148730000000003,0.98970820000000004,{"$f":"AVERAGE($B70:$K70)","v":0.66427433000000002},0.73800279583333328,0.62151502703419315,0.68921696733997462,0.64810306740724843]
[67,7.0103799999999994E-2,0,0.62934009999999996,0.35543710000000001,0.3741969,0.66871670000000005,0.48619079999999998,0.7705476,0.81673580000000001,0.23702989999999999,0.44082986999999996,0.43243382499999999,0.53076789413618086,0.5671623036432184,0.54068829464650792]
[68,0.47511199999999998,0.31287939999999997,0.69897869999999995,1,0.46446090000000001,0.54356579999999999,0.67736609999999997,0.67480370000000001,0.53417150000000002,0.85685630000000002,0.62381943999999989,0.70137301250000006,0.57774093789482139,0.62922845268079652,0.59408643545129536]
[69,2.3302699999999999E-2,0,0.30411009999999999,0.22808149999999999,0.580453,0.51743220000000001,0.15310360000000001,1,0.30416500000000002,0.26027050000000002,0.33709185999999997,0.30298462916666663,0.41593055292160008,0.35660222967600153,0.40374373181378592]
[70,0.1735179,0.10376150000000001,0.75523819999999997,0.51795190000000002,0.75147980000000003,1,0.45144780000000001,0.4660454,0.81455560000000005,0.55950310000000003,0.55935012000000006,0.5996430041666666,0.58816928103939914,0.66941850321512164,0.61225300782600156]
[71,0.53846850000000002,0.63242520000000002,0.79757579999999995,1,0.85796249999999996,1,0.59204970000000001,6.29802E-2,0.54444190000000003,0.2432425,0.62691463000000014,0.65670837916666669,0.60358558659981076,0.71296819102008424,0.62964513912910458]
[72,0.15368850000000001,8.8471999999999995E-3,0.55605170000000004,0.83660469999999998,0.69729459999999999,0.99541060000000003,0.82080779999999998,0.75132149999999998,0.97026860000000004,0.15226300000000001,0.59425581999999999,0.59976192916666671,0.70628271481727523,0.79331522651609121,0.7324718099419496]
[73,0.6409475,1,1,1,0.51510230000000001,0.45491870000000001,0.5360106,0.52547980000000005,0.24986330000000001,0.38561990000000002,0.63079421000000013,0.63583273750000002,0.56931704580848119,0.58381012969498236,0.5678016051552004]
[74,0.36098479999999999,0.67038520000000001,0.78894390000000003,0.964638,0.3907603,0.38926889999999997,9.3367099999999995E-2,0.69947870000000001,0.30361129999999997,0.14382929999999999,0.48052675,0.50350942499999995,0.47761928088546679,0.51140525693046723,0.4600843701442906]
[75,0.34082119999999999,0.43828109999999998,0.66085090000000002,0.72955270000000005,0.29647319999999999,4.7863099999999999E-2,0.3659945,0.38558779999999998,0.77059270000000002,0.2851649,0.43211820999999995,0.46317067083333335,0.43531826984737448,0.51261337615379443,0.45063762254948658]
[76,0.34140609999999999,1,0.67965019999999998,0.92027689999999995,0.17895230000000001,0,0.37249749999999998,0.28983300000000001,0.51418779999999997,0.60208709999999999,0.48988909000000003,0.57068219583333335,0.42436642116267154,0.48753117895693632,0.41753232596514167]
[77,0.37924570000000002,0.46656910000000001,0.68862279999999998,0.43119449999999998,0.82118119999999994,0.70875809999999995,0.50239210000000001,0.45244050000000002,0.52480539999999998,0.22055459999999999,0.51957639999999994,0.47604497083333336,0.54760299584315264,0.55454034821282128,0.56323638137140042]
[78,0.29816179999999998,0.29621180000000003,0.73823859999999997,0.71639220000000003,0.39335409999999998,0.35828650000000001,0.78291829999999996,0,0.1306426,0.2531233,0.39673291999999993,0.41073749166666668,0.32353675496536255,0.38275658204133667,0.3713806496621489]
[79,0.48241250000000002,0.53805780000000003,0.95234660000000004,0.97351920000000003,0.31735269999999999,0,0.49678230000000001,0.18840280000000001,0.6301717,0.52280780000000004,0.51018533999999993,0.57657955000000005,0.42828338202258009,0.53402559508694158,0.46313059093615461]
[80,0.29970530000000001,0.21664910000000001,0.58500929999999995,0.176676,0.37713350000000001,0,0.6689174,0,0.35274460000000002,0.54499070000000005,0.32218258999999999,0.31731834583333335,0.25325424208335628,0.26614316662476351,0.30074129101390784]
[81,0.13071179999999999,0,0.62537589999999998,0.1199998,0.54338280000000005,0.2474913,0.1669011,0,0.62416280000000002,0.32621289999999997,0.27842383999999998,0.28907388750000002,0.26497129396490815,0.33411899727475058,0.30519985373360814]
[82,0.39811760000000002,0.54407970000000005,0.76837560000000005,1,0.3451089,0.53139139999999996,0.4437681,0.91207609999999995,0.52880419999999995,0.75294819999999996,0.62246698,0.69455454583333331,0.60332830869241327,0.63498072656799043,0.59119625357626659]
[83,0.376938,0.2410823,0.72875889999999999,0.74816939999999998,0.33521129999999999,0.40173809999999999,0.33173540000000001,0.20817749999999999,7.4758000000000003E-3,0.57610749999999999,0.39553941999999997,0.46003211666666671,0.29132095444335854,0.33785813587012609,0.31285896976641936]
[84,0,0,0.44241589999999997,0.25431779999999998,0.32191009999999998,0,0.29364479999999998,0,1,0.37319970000000002,0.26854883000000002,0.31824892499999996,0.28688976003962996,0.40200936384406583,0.32755312889064192]
[85,0.24529339999999999,0.51848939999999999,0.77204669999999997,0.7479903,0.60121230000000003,0.36252620000000002,0.36335440000000002,0.35513210000000001,0.99234820000000001,0.68749579999999999,0.56458887999999996,0.64117158750000003,0.55333319605285403,0.65920852083648374,0.57353786360049352]
[86,0.54724320000000004,0.80588300000000002,0.93965799999999999,1,0.76699260000000002,0.84643330000000006,0.86735680000000004,0.33168619999999999,0.8430898,0.12007900000000001,0.70684218999999993,0.6947218791666665,0.73323354131583307,0.82606184816426609,0.758351438010884]
[87,0.518177,0.52676319999999999,0.88029380000000002,1,0.63482470000000002,1,1,4.9882799999999998E-2,0.34704410000000002,0.25277189999999999,0.62097574999999994,0.63841626250000005,0.57493520210072591,0.66813324610265057,0.61608288267646194]
[88,0.29313879999999998,0.78986849999999997,0.85589950000000004,1,0.49808350000000001,0.48223680000000002,0.41964269999999998,0.4227958,0.75411720000000004,0.33920719999999999,0.58549899999999999,0.6387622833333334,0.58443986209271204,0.68207898146250645,0.58937107916164622]
[89,0.6677362,0.82495870000000004,0.99644860000000002,1,0.56025179999999997,0.5882252,0.46914990000000001,0.7718758,0.68961519999999998,0.72392350000000005,0.72921848999999983,0.7735402916666666,0.69761090677140358,0.73732737875241861,0.6909508811964582]
[90,0.24513389999999999,0.75911070000000003,0.73648119999999995,0.53004399999999996,0.42733409999999999,4.7952700000000001E-2,0.55339059999999995,0.72930649999999997,0.68720239999999999,0.78026499999999999,0.54962211000000005,0.57097330000000002,0.53321902070510774,0.52261081036968982,0.53519403184835546]
[91,0,0,0.5374506,0.29869020000000002,0.42247580000000001,0.90656939999999997,0.20743239999999999,0.65316949999999996,0.55310029999999999,0.87011490000000002,0.44490031000000008,0.52420889583333341,0.45510233936733024,0.48833144624065472,0.45087266512226087]
[92,0.37404219999999999,1.54363E-2,0.51899589999999995,0.58990220000000004,0.57405399999999995,0.44516149999999999,0.57199350000000004,0.26162649999999998,0.66932259999999999,0.40680699999999997,0.44273416999999993,0.46573245416666659,0.44163767537849896,0.51478753297823343,0.48079023205524996]
[93,0.37670900000000002,0.14803839999999999,0.68007490000000004,0.77569600000000005,0.3078552,5.4951199999999999E-2,0.58824929999999997,9.5028199999999993E-2,0.33843509999999999,0.26303149999999997,0.36280688,0.39157769583333329,0.30030099113835029,0.37831562435447119,0.3471275529815433]
[94,0.47500569999999998,0.74477389999999999,0.89780870000000002,1,0.57054360000000004,0.62446140000000006,0.4030823,2.7813000000000001E-2,0.539601,0.8459721,0.61290617000000014,0.72848572916666665,0.50559547963002693,0.62523308817222001,0.52454484407998825]
[95,0.51659719999999998,0.50478679999999998,0.79599039999999999,0.58150120000000005,0.54469920000000005,0.22852620000000001,0.29984309999999997,0.53640030000000005,0.2480521,1,0.52563965000000001,0.57651396250000009,0.42071266208254815,0.40864522296465561,0.4297867197046919]
[96,0.1649284,7.1026300000000001E-2,0.73155709999999996,0.52870410000000001,0.49157070000000003,0.918489,0.61132660000000005,0.27983609999999998,0.68587849999999995,0.75853939999999997,0.52418562000000002,0.60008952500000001,0.50479660283608085,0.59771283074032155,0.53532940787174665]
[97,0.23311100000000001,0.1658202,0.66843649999999999,0.59188510000000005,0.55241569999999995,0.92281809999999997,0.3143398,0.55809149999999996,0.1804926,1.7862200000000002E-2,0.4205272699999999,0.40354750000000011,0.45100330481988943,0.47227106269837771,0.45476677768755086]
[98,0.59943270000000004,0.65182949999999995,0.84377809999999998,0.90207360000000003,0.26318979999999997,0,0.53672160000000002,0.83686130000000003,0.24303630000000001,0.4285542,0.53054771000000001,0.52540285416666666,0.48436608698853589,0.4606412393489378,0.48431928806882624]
[99,0.37160969999999999,0.4518837,0.68390949999999995,0.87535149999999995,0.2981895,0.26983499999999999,0.68478490000000003,0.8521898,0.7785379,0.3670735,0.56333649999999991,0.57648350000000004,0.60195273252122838,0.63789561976348075,0.60742368191605123]
[100,0.41443600000000003,0.2953423,0.75767099999999998,0.60029549999999998,0.57734600000000003,0.29790410000000001,0.5005134,0.67793349999999997,0.56454930000000003,6.1460099999999997E-2,0.47474511999999996,0.42384973750000005,0.51199398143390695,0.52185004995620465,0.53228941714749145]

{"sheet":"Indices_compared","index":8,"n_rows":23,"n_cols":14,"rows_kept":23,"cut":false,"hidden":false,"merged":[[1,5,1,9],[9,5,9,9]]}
["Comparison of five indices:"]
["Index",null,"Mean","SD","Information value (CoV)","Correlations:"]
[null,null,null,null,"= SD / Mean","Index1a","Index1b","Index2a","Index2b","Index2c"]
["Index1a","Unweighted (arithmetic mean)",{"$f":"AVERAGE(INDIRECT($A4))","v":0.50113112189999998},{"$f":"_xlfn.STDEV.S(INDIRECT($A4))","v":0.11629996548912194},{"$f":"D4/C4","v":0.23207492092724075},{"$f":"CORREL(INDIRECT($A4),INDIRECT(F$3))","v":1},0.93945974294170054,0.89795165267984745,0.87705161708141866,0.88795108349732299]
["Index1b","Weighted mean (user defined weights)",0.52864669454166668,0.12879854255532289,0.24363822546358754,0.93945974294170054,1.0000000000000002,0.74204544966397634,0.8091658054787616,0.73483887589641206]
["Index2a","Betti-Verma: Information value",0.48834263800774536,0.13004827280893927,0.26630538209706078,0.89795165267984745,0.74204544966397634,0.99999999999999978,0.92796917751033703,0.9918650267060406]
["Index2b","Betti-Verma: User-defined first weights",0.5275457724640128,0.14161479393222165,0.26844077106481234,0.87705161708141866,0.8091658054787616,0.92796917751033703,0.99999999999999978,0.94500510821874684]
["Index2c","Betti-Verma: Equal first weights",0.5039202670532863,0.12240624215551354,0.24290795619571673,0.88795108349732299,0.73483887589641206,0.9918650267060406,0.94500510821874684,1.0000000000000002]
[]
[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":0},{"$f":"FREQUENCY(INDIRECT(H$11), $E$12:$E$21) / 100","v":0},{"$f":"FREQUENCY(INDIRECT(I$11), $E$12:$E$21) / 100","v":0},{"$f":"FREQUENCY(INDIRECT(J$11), $E$12:$E$21) / 100","v":0},null,"FREQUENCY is a multi-cell array function and has to be entered by pressing Shift-Ctrl-Enter."]
[null,null,null,null,0.1,0,0,0,0,0,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,null,0.2,0,0,0.02,0.02,0,null,null,"1. Select the range R12C6:R22C6 (11 cells); type \"=FREQUENCY(\" in the formula bar (without the inverted quotes)."]
[null,null,null,null,0.3,0.05,0.03,0.06,0.04,0.03,null,null,"2.  The final formula is \"=FREQUENCY(INDIRECT(R11C), R12C5:R21C5) / 100\", where"]
[null,null,null,null,0.4,0.13,0.13,0.15,0.12,0.17,null,null,null,"INDIRECT(R11C) references the data range for the particular index, and "]
[null,null,null,null,0.5,0.32,0.23,0.28999999999999998,0.24,0.3,null,null,null,"R12C5:R21C is the bins_range (the upper bounds) with only 10 cells."]
[null,null,null,null,0.6,0.28000000000000003,0.31,0.28000000000000003,0.26,0.27,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."]
[null,null,null,null,0.7,0.17,0.21,0.14000000000000001,0.22,0.18]
[null,null,null,null,0.8,0.05,0.08,0.06,0.08,0.04]
[null,null,null,null,0.9,0,0.01,0,0.02,0.01]
[null,null,null,null,1,0,0,0,0,0]
[null,null,null,null,"Total",{"$f":"SUM(F12:F22)","v":1},{"$f":"SUM(G12:G22)","v":0.99999999999999989},1,0.99999999999999989,1]

{"sheet":"Contributions_by_indicator","index":9,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[[6,0,9,0],[10,0,11,0],[12,0,13,0]]}
["By way of example, we calculate the contributions of the various indicators to one of the indices,"]
["Index2a: Betti-Verma (variant: information value)"]
[]
[null,null,null,null,"Contribution = Mean * weight"]
[]
["Case of interest","Indicator","Mean","BV-Weight","Contribution","Share","Comment"]
["Four indicators strongly associated","x1",{"$f":"AVERAGE(INDIRECT($B7&\"_\"))","v":0.30735680500000001},{"$f":"Betti_Verma_weights!$C$45","v":7.040736571317395E-2},{"$f":"C7*D7","v":2.1640182974067692E-2},{"$f":"E7/E$17","v":4.4313523517732378E-2},"Sum of weights on  the "]
[null,"x2",{"$f":"AVERAGE(INDIRECT($B8&\"_\"))","v":0.34529384000000007},{"$f":"Betti_Verma_weights!$D$45","v":0.10748899388333466},{"$f":"C8*D8","v":3.7115287455713145E-2},{"$f":"E8/E$17","v":7.6002553467642286E-2},"four indicators = "]
[null,"x3",0.69575399999999976,{"$f":"Betti_Verma_weights!$E$45","v":3.4375828081319316E-2},2.391711989089023E-2,4.8976104131441614E-2,{"$f":"SUM(D7:D10)","v":0.27731312623624715}]
[null,"x4",0.65987497400000006,{"$f":"Betti_Verma_weights!$F$45","v":6.5040938558419206E-2},4.2918887640172473E-2,8.7886832522479363E-2,"Deflated for reduncancy"]
["Two strongly associated","x5",0.5001030179999999,{"$f":"Betti_Verma_weights!$G$45","v":0.10045824281544899},5.0239470414982848E-2,0.10287750137882908,"Two indic., less redund."]
[null,"x6",0.50818216299999996,{"$f":"Betti_Verma_weights!$H$45","v":0.13970177391983593},7.0993949645519208E-2,0.14537733165211189,{"$f":"D11+D12","v":0.24016001673528492}]
["Isolates, very few negative associations","x7",0.49953742699999992,{"$f":"Betti_Verma_weights!$I$45","v":0.10752149893151688},5.3711012923433187E-2,0.10998632669585019,"Indep., not deflated"]
[null,"x8",0.49921009999999993,{"$f":"Betti_Verma_weights!$J$45","v":0.18377673142026651},9.1743200469984382E-2,0.1878664554958015,{"$f":"D13+D14","v":0.29129823035178337}]
["Isolate, mixed","x9",0.50234909000000005,{"$f":"Betti_Verma_weights!$K$45","v":0.1912286266766845},9.6063526592982187E-2,0.1967133711381118,"Deflate? Exclude?"]
["Isolate , negative","x10",0.49364980199999969,{"$f":"Betti_Verma_weights!$L$45","v":0},0,0,"Correctly excluded"]
[null,null,"Total:",{"$f":"SUM(D7:D16)","v":1},{"$f":"SUM(E7:E16)","v":0.4883426380077453},1]
[]
[null,"Aggregate deprivation (or gap, or severity, etc.) level ="]
[null,"Total of contributions = 0.488."]

{"sheet":"BV_w_sampling_weights","index":10,"n_rows":101,"n_cols":25,"rows_kept":101,"cut":false,"hidden":false,"merged":[]}
["id","s_weights","y1","y2","y3","y4","y5","y6","y7","y8","y9","y10",null,null,"The following statistics are noteworthy:"]
[1,0.89100000000000001,0,9.6805699999999995E-2,0.36879220000000001,0.31250610000000001,0.17411189999999999,0,0.6878377,0.19589889999999999,5.5654799999999997E-2,0.72126049999999997,null,null,"Indicator","Weighted mean","Weighted SD ","Weighted C.o.V."]
[2,0.63500000000000001,0.38586369999999998,0.31185550000000001,0.76197079999999995,0.77994240000000004,0.8320824,0.88053459999999995,0.67231750000000001,1,1,0.27313080000000001,null,null,"y1",{"$f":"WeightedAvg(INDIRECT($O3&\"_\"),s_weights)","v":0.30271030387105041},{"$f":"WeightedSD2(INDIRECT($O3&\"_\"),s_weights)","v":0.1751710293991974},{"$f":"$Q3/$P3","v":0.57867547671524711}]
[3,0.88,0.45835239999999999,0.58566859999999998,0.70788549999999995,1,0.77450969999999997,0.90741439999999995,0.60619149999999999,0.83344339999999995,0.4730663,0.65859760000000001,null,null,"y2",0.3342459124103111,0.30298170494805021,{"$f":"$Q4/$P4","v":0.90646345609192736}]
[4,2.3780000000000001,0.121715,0,0.42940990000000001,2.5131799999999999E-2,0.42405130000000002,7.9077700000000001E-2,0.45662940000000002,0.91168159999999998,0.31436350000000002,0.29639120000000002,null,null,"y3",0.68779186415953975,0.18508576801697693,0.26910142102821466]
[5,1.202,0.35888350000000002,0.25929200000000002,0.65047529999999998,0.61478679999999997,0.54972129999999997,0.58387920000000004,0.26710640000000002,0.80575509999999995,0.61743999999999999,0.47659849999999998,null,null,"y4",0.66157876282509032,0.3145007014236243,0.47537907668111273]
[6,2.2200000000000002,0.1229391,0,0.2809352,4.64795E-2,0.37882130000000003,0.10472339999999999,0.53353070000000002,0.55874630000000003,0.78453910000000004,0.77996500000000002,null,null,"y5",0.49029235027573193,0.19847927819698943,0.40481822342397983]
[7,0.63800000000000001,0.52080040000000005,0.57332810000000001,0.8956906,1,0.64453320000000003,0.90931229999999996,0.28491739999999999,5.3319900000000003E-2,0.45272519999999999,0.59345899999999996,null,null,"y6",0.48479813751375261,0.33359650946989866,0.68811425551410099]
[8,0.59899999999999998,0.30427149999999997,0.46575929999999999,0.80818820000000002,0.6814616,0.4735529,0.69086369999999997,0.64392910000000003,1,1,0.48451739999999999,null,null,"y7",0.50431023411789333,0.19388616804801895,0.38445812702403714]
[9,1.242,0.48286410000000002,0.7545037,0.93756510000000004,1,0.41354859999999999,0.56522819999999996,0.13754769999999999,0,0.72485279999999996,0.14017160000000001,null,null,"y8",0.52529634896153254,0.317677945303944,0.6047594770684529]
[10,1.19,0.60532430000000004,0.44566109999999998,0.8799941,0.97894820000000005,0.2311956,1.7764700000000001E-2,0.53707400000000005,0.97544810000000004,0.27293460000000003,0.50023930000000005,null,null,"y9",0.51555370812954338,0.25149359443077518,0.48781259927934084]
[11,1.016,1.2052E-2,0,0.6653038,0.51392260000000001,0.60411550000000003,0.94963430000000004,0.49563600000000002,0.13920260000000001,0.52678009999999997,0.12762499999999999,null,null,"y10",0.47745214116880735,0.27876931088705809,0.58386859509023914]
[12,1.931,0.1111596,0.31232080000000001,0.51860589999999995,0.50930640000000005,0.55693239999999999,0.35986200000000002,0.60489309999999996,1,0.62471500000000002,0.6274248,null,null,null,null,"[If errors show, try F9 to cause recalculation.]"]
[13,0.65200000000000002,0.11681179999999999,0.25363059999999998,0.59500799999999998,0.50990829999999998,0.63368340000000001,0.90685780000000005,0.41045490000000001,7.7721799999999994E-2,0.20030339999999999,0.41840129999999998]
[14,1.92,0.57756600000000002,0.99016800000000005,0.92857659999999997,0.96269260000000001,0.67429349999999999,0.65979279999999996,0.27692099999999997,0.52028189999999996,0,0.85532520000000001,null,null,"Matrix of weighted correlation coefficients:"]
[15,1.1950000000000001,0.22669629999999999,0.24279439999999999,0.74882199999999999,0.41345710000000002,0.53756890000000002,0.80086930000000001,0.33044679999999998,0.91888000000000003,0.44137729999999997,0.16380929999999999,null,null,null,"y1","y2","y3","y4","y5","y6","y7","y8","y9","y10"]
[16,0.81100000000000005,0.16239190000000001,0,0.59931129999999999,0.232293,0.7688294,1,0.2406394,1,0.40822999999999998,0.69714989999999999,null,null,"y1",{"$f":"WtCorrel(INDIRECT($O17&\"_\"),INDIRECT(P$16&\"_\"),s_weights)","v":1},0.76505341108781488,0.7891864271388721,0.7615756110871007,-2.9548879244307602E-2,-6.1730749320150859E-2,3.1285400079512332E-2,-3.9645585460572914E-3,-0.13901015182408899,1.0363412470344321E-2]
[17,1.2609999999999999,0.4185179,0,0.82958900000000002,0.90495479999999995,0.16104979999999999,1.39629E-2,0.23317589999999999,0.84168759999999998,0.61320430000000004,0.3630371,null,null,"y2",0.76505341108781499,1,0.72765098486237056,0.67948268881921958,-1.2200833874595795E-2,-6.0322714520875377E-2,1.4407099023857711E-2,5.0048675561062191E-2,-0.15613633308749561,5.1514540468281876E-2]
[18,1.3759999999999999,7.67288E-2,0,0.32892769999999999,0,0.50709029999999999,0.2190742,0.60645539999999998,0.54708959999999995,0.96376450000000002,0.82997120000000002,null,null,"y3",0.78918642713887199,0.72765098486237056,1,0.80996950294230474,-0.1512836747528955,-1.1204301772308375E-2,3.6073492990982732E-2,-5.5868118025347684E-3,-7.7233098407062814E-2,-0.10342781748393151]
[19,1.8779999999999999,0.2200056,0.14729709999999999,0.52077099999999998,0.29393370000000002,0.41325659999999997,8.3432900000000004E-2,0.1061209,0.3033052,0.29714859999999998,0.36684939999999999,null,null,"y4",0.7615756110871007,0.6794826888192198,0.80996950294230474,1,-0.16058421776876677,3.6225245281173164E-2,7.2354670854518174E-2,1.7155244330540709E-2,-1.6887498018996777E-2,-0.10717740546600217]
[20,0.877,0.3125771,0.228437,0.73162070000000001,0.54152940000000005,0.70623270000000005,0.71762740000000003,0.58776280000000003,0.12625410000000001,0.44999090000000003,0,null,null,"y5",-2.9548879244307626E-2,-1.2200833874595756E-2,-0.1512836747528955,-0.16058421776876677,1,0.79173475044815211,0.13434460747648858,-5.2832553369989169E-2,7.5684773665989941E-2,-6.4912193036465893E-2]
[21,0.94499999999999995,0.30009370000000002,0.26848519999999998,0.90264449999999996,0.59465900000000005,0.42652780000000001,0.61194979999999999,0.98119630000000002,0.2425937,1,0.51166829999999996,null,null,"y6",-6.1730749320150859E-2,-6.0322714520875391E-2,-1.1204301772308413E-2,3.6225245281173178E-2,0.79173475044815222,1,0.12440045559750409,-6.6029101215171956E-2,6.5352218675524654E-2,-8.4044214520675578E-2]
[22,1.552,0,0,0.60180880000000003,0.30137829999999999,0.57725329999999997,1,0.63885420000000004,1,0.58074650000000005,0.1725565,null,null,"y7",3.1285400079512332E-2,1.4407099023857733E-2,3.6073492990982739E-2,7.235467085451816E-2,0.13434460747648852,0.12440045559750412,1,1.5750732809899221E-2,0.14379398358338411,-4.5171055353181465E-2]
[23,1.554,0.40216020000000002,0.88253809999999999,0.78071559999999995,0.91405479999999995,0.5745323,0.55699779999999999,0.68172809999999995,0.98082879999999995,9.9649699999999994E-2,0.26960309999999998,null,null,"y8",-3.9645585460572905E-3,5.0048675561062177E-2,-5.5868118025347649E-3,1.7155244330540696E-2,-5.2832553369989203E-2,-6.602910121517197E-2,1.5750732809899227E-2,1,-3.1414111919717401E-2,-8.4293140189456189E-2]
[24,1.8720000000000001,0.25985000000000003,9.1380400000000001E-2,0.799543,0.87742450000000005,0.34245730000000002,0.3223761,0.5799668,0.72748080000000004,0.40612779999999998,0.325681,null,null,"y9",-0.13901015182408888,-0.15613633308749561,-7.7233098407062856E-2,-1.688749801899676E-2,7.5684773665989968E-2,6.5352218675524681E-2,0.14379398358338411,-3.1414111919717401E-2,1,-8.2462362534979822E-2]
[25,2.4860000000000002,0.18183060000000001,0,0.50690040000000003,0.69007099999999999,0.63246939999999996,0.74644639999999995,0.49786979999999997,0.51540090000000005,1,0.49433890000000003,null,null,"y10",1.0363412470344339E-2,5.1514540468281897E-2,-0.10342781748393147,-0.10717740546600217,-6.4912193036465907E-2,-8.404421452067555E-2,-4.5171055353181479E-2,-8.4293140189456148E-2,-8.2462362534979808E-2,1]
[26,1.5309999999999999,0.56253880000000001,0.80925539999999996,0.82705680000000004,0.79971360000000002,0.43811840000000002,0.19027920000000001,0.59614460000000002,1,0,0.64123850000000004]
[27,0.85899999999999999,0.70377579999999995,1,0.99358579999999996,1,0.82100680000000004,0.83469499999999996,0.41837400000000002,0.38681729999999998,0.29327579999999998,0.93496069999999998]
[28,1.0309999999999999,0.80054829999999999,1,1,1,0.59478569999999997,0.59912359999999998,0.44571349999999998,1,0.67510130000000002,0.47422809999999999]
[29,1.3149999999999999,0.18910879999999999,8.4351499999999996E-2,0.42501800000000001,0.59548219999999996,0.78336939999999999,0.73271719999999996,0.92438010000000004,0.56899310000000003,0.43382799999999999,0]
[30,1.4,0.37016559999999998,0.48282609999999998,1,1,0.54405139999999996,0.70014390000000004,0.31420870000000001,0.94597229999999999,0.41267700000000002,0.1672727]
[31,2.3519999999999999,0.42938500000000002,0.37690950000000001,0.87787510000000002,1,0.24703649999999999,0.55585739999999995,0.31075599999999998,0.15500620000000001,0.26955839999999998,0.37116110000000002]
[32,2.46,0.4654393,0.85591910000000004,0.85934509999999997,1,0.58557110000000001,0.70119279999999995,0.54816759999999998,0.46672180000000002,0.40715829999999997,1]
[33,1.038,0,0,0.2329369,0.1940093,0.43878610000000001,9.5164399999999996E-2,0.58309270000000002,6.4512100000000003E-2,0.87068840000000003,0.61448190000000003]
[34,0.59399999999999997,0.39897949999999999,0.39301760000000002,0.85675199999999996,1,0,0,0.2319756,0.19580520000000001,0.45462459999999999,1]
[35,2.1040000000000001,0.26062730000000001,0.2912093,0.71317909999999995,0.94570330000000002,9.8822699999999999E-2,0,0.25411529999999999,0.8841928,0.56467319999999999,1]
[36,0.93600000000000005,0.36399229999999999,0.38722230000000002,0.76276409999999994,0.59735150000000004,0.60939889999999997,0.49989359999999999,0.60946140000000004,0.86738689999999996,0.29700460000000001,0.90448740000000005]
[37,0.83699999999999997,0,0,0.28397159999999999,0,0.55272679999999996,0.98298700000000006,0.77567770000000003,0.1691598,0,0.7561175]
[38,1.1619999999999999,0.28114509999999998,6.4146999999999996E-2,0.70247839999999995,0.86743130000000002,0.4978475,0.48416229999999999,0.38605499999999998,8.8490399999999997E-2,0.20375599999999999,0.62910319999999997]
[39,0.57899999999999996,0,0,0.22656689999999999,0,0.66107879999999997,0.70783940000000001,0.435861,0.2060389,0.31790489999999999,0.67544349999999997]
[40,2.452,0.3664017,0.48807499999999998,0.88393730000000004,0.88882740000000005,0.30445070000000002,0.519312,0.73109250000000003,5.7239699999999998E-2,0.8439354,0.1594769]
[41,0.53600000000000003,0.34777469999999999,0.71919759999999999,0.72549090000000005,0.98196450000000002,0.56022870000000002,0.60019270000000002,0.31266490000000002,5.6454400000000002E-2,0,0.51726559999999999]
[42,2.2349999999999999,0.30797910000000001,0.16133600000000001,0.70568690000000001,0.84006409999999998,0.2810281,0.28981760000000001,0.36913180000000001,0.36973400000000001,0.7528108,0.34760590000000002]
[43,1.9490000000000001,4.7495200000000001E-2,0.12785250000000001,0.58498320000000004,0.26059359999999998,0.1404646,0,0.80956189999999995,0.94046640000000004,0.33499000000000001,0.83846010000000004]
[44,2.0049999999999999,0.40675830000000002,0.68354950000000003,0.94693989999999995,1,0.3697859,0,1,0.79293170000000002,0.55035599999999996,0]
[45,2.3460000000000001,0,0,0.32756970000000002,0,0.93253490000000006,1,0.63275400000000004,0.57076000000000005,0.33257680000000001,0.46108270000000001]
[46,1.764,0.39397280000000001,0,0.66279670000000002,0.1881032,0.61137920000000001,0.47706739999999997,0.50701090000000004,0.5750343,0.67183720000000002,0.89687340000000004]
[47,2.4790000000000001,0.4914771,0.74227540000000003,0.75021590000000005,0.8080292,0.1606939,0,0.44895600000000002,1,0.32749600000000001,0.32273990000000002]
[48,1.1679999999999999,0.12073589999999999,0,0.2635962,2.1825399999999998E-2,0.86725940000000001,0.70542139999999998,0.238626,0.41188819999999998,0.59379870000000001,0.74675420000000003]
[49,1.0269999999999999,0.1523349,0,0.65226969999999995,0.49153429999999998,0.30036279999999999,0.45277919999999999,0.40913389999999999,0.77783239999999998,0.72670939999999995,0.1542463]
[50,0.79600000000000004,0.27177289999999998,0.2310903,0.56597719999999996,0.27096599999999998,0.47109519999999999,0.1246777,0.3944146,0,0,0.62725629999999999]
[51,1.9319999999999999,0.1734011,0,0.4125529,0.45402029999999999,0.76848559999999999,0.97675860000000003,0.48703540000000001,0,0.51600330000000005,1]
[52,0.72,0.54396699999999998,1,0.83058670000000001,1,0.71038690000000004,0.6982931,0.4346853,0.52159509999999998,0.27392329999999998,0.2166053]
[53,1.673,0.4292685,0.48241679999999998,0.78504560000000001,0.85870190000000002,0.74649430000000006,0.77204050000000002,0.72483030000000004,0.39591209999999999,0.56889239999999996,0.51051060000000004]
[54,0.68200000000000005,0.57296910000000001,0.80612450000000002,1,0.96289290000000005,0.46869709999999998,0.49476510000000001,0.30687520000000001,0.60766989999999999,0.91329439999999995,0.43139070000000002]
[55,0.92300000000000004,0.2325169,0.24018249999999999,0.75442220000000004,0.47753479999999998,0.49122100000000002,0.64865519999999999,0.51294019999999996,0,0.63611980000000001,1]
[56,1.639,0.42856119999999998,0.3285111,0.59620989999999996,1,0.61856409999999995,1,0.47197119999999998,0.91542760000000001,0.68258450000000004,0.21973290000000001]
[57,1.41,0.1359909,0.14044219999999999,0.59078379999999997,0.82454830000000001,0.36043449999999999,0.52509249999999996,0.54127159999999996,0.77709079999999997,0.28410180000000002,0.89579279999999994]
[58,1.871,0,0,0.61198629999999998,0.2241706,0.34491660000000002,0.38901449999999999,0.43845050000000002,0.38833060000000003,0.30842370000000002,0.46570339999999999]
[59,2.12,0.43078090000000002,0.21541009999999999,0.82181110000000002,1,0.14947759999999999,0,0.54453090000000004,0.53611620000000004,0.52134979999999997,0.38110500000000003]
[60,1.0549999999999999,0.44216050000000001,0.32793290000000003,0.89249040000000002,1,4.5488899999999999E-2,8.1623299999999996E-2,0.76078129999999999,3.3070299999999997E-2,0,0.69652139999999996]
[61,2.0249999999999999,0.2997245,0.2088671,0.83574020000000004,0.87941550000000002,0.88319899999999996,1,0.60645349999999998,0.72612089999999996,0.50003089999999994,0.10307089999999999]
[62,1.4790000000000001,0.2089075,0.52290009999999998,0.76783440000000003,0.77002579999999998,0.68272270000000002,0.95839730000000001,0.32371119999999998,0.30141790000000002,0.70483059999999997,0.44961580000000001]
[63,1.6,0.15333759999999999,0.14497070000000001,0.47470689999999999,0.4860488,0.49352649999999998,0.48046559999999999,0.80768660000000003,0.33013350000000002,0.53609379999999995,0.64546309999999996]
[64,1.464,0.179364,0,0.43877440000000001,0.3217525,0.56515769999999999,0.60118039999999995,0.38160909999999998,0.5170593,0.3669965,0.35280470000000003]
[65,1.913,0.14165929999999999,0.17559140000000001,0.69989590000000002,0.61751529999999999,0.44148599999999999,0.42758459999999998,0.42558970000000002,0.86452030000000002,0.35257850000000002,0.39995910000000001]
[66,2.3279999999999998,0.3649675,0.38463459999999999,0.75612699999999999,0.66548450000000003,0.7029301,0.96240840000000005,0.77171190000000001,0.35328379999999998,0.69148730000000003,0.98970820000000004]
[67,1.7210000000000001,7.0103799999999994E-2,0,0.62934009999999996,0.35543710000000001,0.3741969,0.66871670000000005,0.48619079999999998,0.7705476,0.81673580000000001,0.23702989999999999]
[68,1.8740000000000001,0.47511199999999998,0.31287939999999997,0.69897869999999995,1,0.46446090000000001,0.54356579999999999,0.67736609999999997,0.67480370000000001,0.53417150000000002,0.85685630000000002]
[69,2.1579999999999999,2.3302699999999999E-2,0,0.30411009999999999,0.22808149999999999,0.580453,0.51743220000000001,0.15310360000000001,1,0.30416500000000002,0.26027050000000002]
[70,0.64800000000000002,0.1735179,0.10376150000000001,0.75523819999999997,0.51795190000000002,0.75147980000000003,1,0.45144780000000001,0.4660454,0.81455560000000005,0.55950310000000003]
[71,1.9139999999999999,0.53846850000000002,0.63242520000000002,0.79757579999999995,1,0.85796249999999996,1,0.59204970000000001,6.29802E-2,0.54444190000000003,0.2432425]
[72,2.4089999999999998,0.15368850000000001,8.8471999999999995E-3,0.55605170000000004,0.83660469999999998,0.69729459999999999,0.99541060000000003,0.82080779999999998,0.75132149999999998,0.97026860000000004,0.15226300000000001]
[73,1.3240000000000001,0.6409475,1,1,1,0.51510230000000001,0.45491870000000001,0.5360106,0.52547980000000005,0.24986330000000001,0.38561990000000002]
[74,1.4710000000000001,0.36098479999999999,0.67038520000000001,0.78894390000000003,0.964638,0.3907603,0.38926889999999997,9.3367099999999995E-2,0.69947870000000001,0.30361129999999997,0.14382929999999999]
[75,2.4740000000000002,0.34082119999999999,0.43828109999999998,0.66085090000000002,0.72955270000000005,0.29647319999999999,4.7863099999999999E-2,0.3659945,0.38558779999999998,0.77059270000000002,0.2851649]
[76,1.2470000000000001,0.34140609999999999,1,0.67965019999999998,0.92027689999999995,0.17895230000000001,0,0.37249749999999998,0.28983300000000001,0.51418779999999997,0.60208709999999999]
[77,2.016,0.37924570000000002,0.46656910000000001,0.68862279999999998,0.43119449999999998,0.82118119999999994,0.70875809999999995,0.50239210000000001,0.45244050000000002,0.52480539999999998,0.22055459999999999]
[78,0.8,0.29816179999999998,0.29621180000000003,0.73823859999999997,0.71639220000000003,0.39335409999999998,0.35828650000000001,0.78291829999999996,0,0.1306426,0.2531233]
[79,2.371,0.48241250000000002,0.53805780000000003,0.95234660000000004,0.97351920000000003,0.31735269999999999,0,0.49678230000000001,0.18840280000000001,0.6301717,0.52280780000000004]
[80,1.161,0.29970530000000001,0.21664910000000001,0.58500929999999995,0.176676,0.37713350000000001,0,0.6689174,0,0.35274460000000002,0.54499070000000005]
[81,1.1599999999999999,0.13071179999999999,0,0.62537589999999998,0.1199998,0.54338280000000005,0.2474913,0.1669011,0,0.62416280000000002,0.32621289999999997]
[82,1.5129999999999999,0.39811760000000002,0.54407970000000005,0.76837560000000005,1,0.3451089,0.53139139999999996,0.4437681,0.91207609999999995,0.52880419999999995,0.75294819999999996]
[83,1.9630000000000001,0.376938,0.2410823,0.72875889999999999,0.74816939999999998,0.33521129999999999,0.40173809999999999,0.33173540000000001,0.20817749999999999,7.4758000000000003E-3,0.57610749999999999]
[84,1.8009999999999999,0,0,0.44241589999999997,0.25431779999999998,0.32191009999999998,0,0.29364479999999998,0,1,0.37319970000000002]
[85,0.98499999999999999,0.24529339999999999,0.51848939999999999,0.77204669999999997,0.7479903,0.60121230000000003,0.36252620000000002,0.36335440000000002,0.35513210000000001,0.99234820000000001,0.68749579999999999]
[86,0.97299999999999998,0.54724320000000004,0.80588300000000002,0.93965799999999999,1,0.76699260000000002,0.84643330000000006,0.86735680000000004,0.33168619999999999,0.8430898,0.12007900000000001]
[87,1.1930000000000001,0.518177,0.52676319999999999,0.88029380000000002,1,0.63482470000000002,1,1,4.9882799999999998E-2,0.34704410000000002,0.25277189999999999]
[88,1.9570000000000001,0.29313879999999998,0.78986849999999997,0.85589950000000004,1,0.49808350000000001,0.48223680000000002,0.41964269999999998,0.4227958,0.75411720000000004,0.33920719999999999]
[89,1.2609999999999999,0.6677362,0.82495870000000004,0.99644860000000002,1,0.56025179999999997,0.5882252,0.46914990000000001,0.7718758,0.68961519999999998,0.72392350000000005]
[90,2.0430000000000001,0.24513389999999999,0.75911070000000003,0.73648119999999995,0.53004399999999996,0.42733409999999999,4.7952700000000001E-2,0.55339059999999995,0.72930649999999997,0.68720239999999999,0.78026499999999999]
[91,1.0089999999999999,0,0,0.5374506,0.29869020000000002,0.42247580000000001,0.90656939999999997,0.20743239999999999,0.65316949999999996,0.55310029999999999,0.87011490000000002]
[92,2.1829999999999998,0.37404219999999999,1.54363E-2,0.51899589999999995,0.58990220000000004,0.57405399999999995,0.44516149999999999,0.57199350000000004,0.26162649999999998,0.66932259999999999,0.40680699999999997]
[93,1.1000000000000001,0.37670900000000002,0.14803839999999999,0.68007490000000004,0.77569600000000005,0.3078552,5.4951199999999999E-2,0.58824929999999997,9.5028199999999993E-2,0.33843509999999999,0.26303149999999997]
[94,1.018,0.47500569999999998,0.74477389999999999,0.89780870000000002,1,0.57054360000000004,0.62446140000000006,0.4030823,2.7813000000000001E-2,0.539601,0.8459721]
[95,1.109,0.51659719999999998,0.50478679999999998,0.79599039999999999,0.58150120000000005,0.54469920000000005,0.22852620000000001,0.29984309999999997,0.53640030000000005,0.2480521,1]
[96,0.73499999999999999,0.1649284,7.1026300000000001E-2,0.73155709999999996,0.52870410000000001,0.49157070000000003,0.918489,0.61132660000000005,0.27983609999999998,0.68587849999999995,0.75853939999999997]
[97,2.0699999999999998,0.23311100000000001,0.1658202,0.66843649999999999,0.59188510000000005,0.55241569999999995,0.92281809999999997,0.3143398,0.55809149999999996,0.1804926,1.7862200000000002E-2]
[98,0.83499999999999996,0.59943270000000004,0.65182949999999995,0.84377809999999998,0.90207360000000003,0.26318979999999997,0,0.53672160000000002,0.83686130000000003,0.24303630000000001,0.4285542]
[99,2.3460000000000001,0.37160969999999999,0.4518837,0.68390949999999995,0.87535149999999995,0.2981895,0.26983499999999999,0.68478490000000003,0.8521898,0.7785379,0.3670735]
[100,2.319,0.41443600000000003,0.2953423,0.75767099999999998,0.60029549999999998,0.57734600000000003,0.29790410000000001,0.5005134,0.67793349999999997,0.56454930000000003,6.1460099999999997E-2]

{"sheet":"Weighting_function_code","index":11,"n_rows":203,"n_cols":4,"rows_kept":203,"cut":false,"hidden":false,"merged":[]}
["User-defined functions needed for Betti-Verma with sampling weights (back-up copy in case modules inadvertantly get deleted)."]
["These functions for weighted mean, weighted standard deviation and weighted correlation coefficient may be useful in other contexts, too."]
["They are stored in three VBA modules, accessible by typing Alt+F11, as long as the workbook is saved with the .xlsm file extension."]
[]
[null,"Weighted mean:"]
[null,null,null,"Option Explicit"]
[]
[null,null,null,"Function WeightedAvg( _"]
[null,null,null,"                        Values, _"]
[null,null,null,"                        weights) As Double"]
[]
[null,null,null,"' By Jay Petrulis, Aug 19, 2005"]
[null,null,null,"' https://www.mrexcel.com/forum/excel-questions/158039-calculate-weighted-average-vba.html"]
[null,null,null,"    "]
[null,null,null,"Dim ValuesArray, _"]
[null,null,null,"    WeightsArray"]
[]
[null,null,null,"If TypeName(Values) = \"Range\" Then"]
[null,null,null,"    ValuesArray = Range_to_1D_Array(Values)"]
[null,null,null,"Else"]
[null,null,null,"    If TypeName(Values) Like \"*()\" Then"]
[null,null,null,"        If ArrayDimensions(Values) > 1 Then"]
[null,null,null,"            ValuesArray = Excel.Application.Transpose(Values)"]
[null,null,null,"        Else"]
[null,null,null,"            ValuesArray = Values"]
[null,null,null,"        End If"]
[null,null,null,"    End If"]
[null,null,null,"End If"]
[]
[null,null,null,"If TypeName(weights) = \"Range\" Then"]
[null,null,null,"    WeightsArray = Range_to_1D_Array(weights)"]
[null,null,null,"Else"]
[null,null,null,"    If TypeName(weights) Like \"*()\" Then"]
[null,null,null,"        If ArrayDimensions(weights) > 1 Then"]
[null,null,null,"            WeightsArray = Excel.Application.Transpose(weights)"]
[null,null,null,"        Else"]
[null,null,null,"            WeightsArray = weights"]
[null,null,null,"        End If"]
[null,null,null,"    End If"]
[null,null,null,"End If"]
[null,null,null,"    "]
[null,null,null,"    If UBound(ValuesArray) <> UBound(WeightsArray) Then"]
[null,null,null,"            WeightedAvg = CVErr(xlErrValue)"]
[null,null,null,"            Exit Function"]
[null,null,null,"        End If"]
[null,null,null,"        "]
[null,null,null,"    If UBound(ValuesArray) = 1 Then"]
[null,null,null,"            WeightedAvg = ValuesArray(1) * IIf(weights, 1, 0)"]
[null,null,null,"            Exit Function"]
[null,null,null,"        End If"]
[null,null,null,"                "]
[null,null,null,"            With Excel.Application"]
[null,null,null,"                If .Sum(WeightsArray) Then"]
[null,null,null,"                        WeightedAvg = .SumProduct(ValuesArray, WeightsArray) / _"]
[null,null,null,"                                        .Sum(WeightsArray)"]
[null,null,null,"                    Else"]
[null,null,null,"                        WeightedAvg = 0"]
[null,null,null,"                    End If"]
[null,null,null,"            End With"]
[null,null,null,"                "]
[null,null,null,"Erase WeightsArray"]
[null,null,null,"Erase ValuesArray"]
[null,null,null,"End Function"]
[]
[null,null,null,"Function Range_to_1D_Array(ByVal rng As Range)"]
[null,null,null,"    Dim ReturnArray, _"]
[null,null,null,"        arrTemp, _"]
[null,null,null,"        i As Long, _"]
[null,null,null,"        j As Long, _"]
[null,null,null,"        k As Long"]
[null,null,null,"    "]
[null,null,null,"    If rng.Cells.Count = 1 Then"]
[null,null,null,"        ''' converts to numeric values"]
[null,null,null,"        ''' do not use if you have non-numeric"]
[null,null,null,"        ''' values in a single cell range"]
[null,null,null,"        ReDim ReturnArray(1 To 1)"]
[null,null,null,"        ReturnArray(1) = CDbl(rng)"]
[null,null,null,"    Else"]
[null,null,null,"        If rng.Columns.Count > 1 And rng.Rows.Count > 1 Then"]
[null,null,null,"            arrTemp = rng.Value"]
[null,null,null,"            ReDim ReturnArray(1 To UBound(arrTemp, 1) * UBound(arrTemp, 2))"]
[null,null,null,"            k = 0"]
[null,null,null,"            For i = 1 To UBound(arrTemp, 1)"]
[null,null,null,"                For j = 1 To UBound(arrTemp, 2)"]
[null,null,null,"                    k = k + 1"]
[null,null,null,"                    ReturnArray(k) = arrTemp(i, j)"]
[null,null,null,"                Next j"]
[null,null,null,"            Next i"]
[null,null,null,"            Erase arrTemp"]
[null,null,null,"        Else"]
[null,null,null,"            With Excel.Application"]
[null,null,null,"                ReturnArray = .Transpose(rng)"]
[null,null,null,"                If rng.Rows.Count = 1 Then ReturnArray = .Transpose(ReturnArray)"]
[null,null,null,"            End With"]
[null,null,null,"        End If"]
[null,null,null,"    End If"]
[null,null,null,"    "]
[null,null,null,"    Range_to_1D_Array = ReturnArray"]
[null,null,null,"    Erase ReturnArray"]
[null,null,null,"    End Function"]
[]
[]
[null,null,null,"Function ArrayDimensions(InputArray As Variant) As Long"]
[null,null,null,"    Dim N As Long"]
[]
[null,null,null,"    If Not IsArray(InputArray) Or IsObject(InputArray) Then"]
[null,null,null,"        N = 0"]
[null,null,null,"        Exit Function"]
[null,null,null,"    Else"]
[null,null,null,"        N = 1"]
[null,null,null,"    End If"]
[]
[null,null,null,"    On Error Resume Next"]
[]
[null,null,null,"    Do"]
[null,null,null,"        N = N + 1"]
[null,null,null,"    Loop While (LBound(InputArray, N) <= UBound(InputArray, N))"]
[]
[null,null,null,"    ArrayDimensions = N - 1"]
[null,null,null,"End Function"]
[]
[null,"Weighted standard deviation:"]
[null,null,null,"Option Explicit"]
[]
[null,null,null,"Function WeightedSD2(vals As Range, weights As Range)"]
[]
[null,null,null,"' Adapted by Aldo Benini, 21 Aug 2019"]
[null,null,null,"' from https://stats.stackexchange.com/questions/6534/how-do-i-calculate-a-weighted-standard-deviation-in-excel"]
[null,null,null,"' posted by \"gung\" (psydonym), 12 March 2015"]
[]
[null,null,null,"' The function works only if both vals and weights are ranges with 1 column and the same number of rows."]
[null,null,null,"' The results is correct only if all weights > 0."]
[]
[null,null,null,"Dim i, xV, xW, y As Integer"]
[null,null,null,"Dim wi, xi, WgtAvg, N"]
[null,null,null,"Dim sumProd, SUMwi"]
[]
[null,null,null,"    sumProd = 0"]
[null,null,null,"    SUMwi = 0"]
[null,null,null,"    N = vals.Count  ' number of values to determine W Standard Deviation"]
[null,null,null,"    y = vals.Row - 1  ' Row number of the values and weights"]
[]
[null,null,null,"    WgtAvg = WorksheetFunction.SumProduct(vals, weights) / WorksheetFunction.Sum(weights)"]
[]
[null,null,null,"    For i = 1 To N  ' step through the elements, calculating the sum of values and the sumproduct"]
[null,null,null,"        wi = weights.Cells(i + y, 1).Value  ' (i+y, xW) is the cell containing the weight element"]
[null,null,null,"        SUMwi = SUMwi + wi"]
[null,null,null,"        xi = vals.Cells(i + y, 1).Value  ' (i+y, xV) is the cell containing the value element"]
[null,null,null,"        sumProd = sumProd + wi * (xi - WgtAvg) ^ 2"]
[null,null,null,"    Next i"]
[]
[null,null,null,"    WeightedSD2 = (sumProd / (SUMwi * N / (N - 1))) ^ (1 / 2)  ' output of weighted standard deviation"]
[]
[null,null,null,"End Function"]
[]
[null,"Weighted correlation coefficient:"]
[null,null,null,"Function WtCorrel(rXi As Excel.Range, _"]
[null,null,null,"                  rYi As Excel.Range, _"]
[null,null,null,"                  rWi As Excel.Range) As Double"]
[null,null,null,"                  "]
[null,null,null,"' shg (pseudonym) (2008). Calculate Weighted Correlation Coefficient [Excel VBA function]."]
[null,null,null,"' https://www.ozgrid.com/forum/forum/help-forums/excel-general/72015-calculate-weighted-correlation-coefficient."]
[]
[null,null,null,"    Dim ad()    As Double"]
[null,null,null,"    Dim sW      As Double   ' sum(Wi)"]
[null,null,null,"    Dim xW      As Double"]
[null,null,null,"    Dim Yw      As Double"]
[null,null,null,"    Dim vWi     As Variant"]
[null,null,null,"    Dim vXiXw   As Variant  ' Xi - Xw"]
[null,null,null,"    Dim vYiYw   As Variant  ' Yi - Yw"]
[null,null,null,"    "]
[null,null,null,"    Dim i       As Long"]
[null,null,null,"    Dim N       As Long"]
[]
[null,null,null,"    ReDim ad(1 To 3, 1 To rXi.Rows.Count)"]
[null,null,null,"    For i = 1 To rXi.Rows.Count"]
[null,null,null,"        If IsNumeric(rXi(i).Value) And IsNumeric(rYi(i).Value) And IsNumeric(rWi(i).Value) Then"]
[null,null,null,"            N = N + 1"]
[null,null,null,"            ad(1, N) = rXi(i).Value"]
[null,null,null,"            ad(2, N) = rYi(i).Value"]
[null,null,null,"            ad(3, N) = rWi(i).Value"]
[null,null,null,"        End If"]
[null,null,null,"    Next i"]
[null,null,null,"    ReDim Preserve ad(1 To 3, 1 To N)"]
[]
[null,null,null,"    With Application.WorksheetFunction"]
[null,null,null,"        vXiXw = .Index(ad, 1, 0)"]
[null,null,null,"        vYiYw = .Index(ad, 2, 0)"]
[null,null,null,"        vWi = .Index(ad, 3, 0)"]
[null,null,null,"        "]
[null,null,null,"        sW = .Sum(vWi)"]
[null,null,null,"        xW = .SumProduct(vWi, vXiXw) / sW"]
[null,null,null,"        Yw = .SumProduct(vWi, vYiYw) / sW"]
[null,null,null,"        "]
[null,null,null,"        For i = 1 To N"]
[null,null,null,"            vXiXw(i) = vXiXw(i) - xW"]
[null,null,null,"            vYiYw(i) = vYiYw(i) - Yw"]
[null,null,null,"        Next i"]
[]
[null,null,null,"        WtCorrel = .SumProduct(vWi, vXiXw, vYiYw) _"]
[null,null,null,"             / Sqr(.SumProduct(vWi, vXiXw, vXiXw) * .SumProduct(vWi, vYiYw, vYiYw))"]
[null,null,null,"    End With"]
[null,null,null,"End Function"]

{"sheet":"MSNA_Setup","index":12,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["Practical example with real data"]

{"sheet":"MSNA_Data","index":13,"n_rows":23,"n_cols":11,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
["Stratum","Union","HH head gender","Sample households","Sampl_w","ls1_food","ls2_livelih","ls3_shelter","ls4_wash","ls5_health","ls6_educ"]
[1,"Baharchara","Male",204,134.01840000000001,0.6254596,0.75367649999999997,0.57822320000000005,0.23526359999999999,0.32352940000000002,0.47336620000000001]
[2,"Baharchara","Female",51,33.504600000000003,0.66167719999999997,0.78921569999999996,0.53975139999999999,0.23385510000000001,0.41176469999999998,0.51073009999999996]
[3,"Nhilla","Male",221,240.04560000000001,0.60734489999999997,0.72511309999999995,0.51450309999999999,0.28822170000000003,0.46153850000000002,0.46569949999999999]
[4,"Nhilla","Female",43,46.705710000000003,0.7012043,0.72093019999999997,0.54698919999999995,0.26512829999999998,0.60465120000000006,0.5344411]
[5,"Sabrang","Male",219,287.82659999999998,0.59313850000000001,0.71461189999999997,0.44964290000000001,0.2039214,0.46118720000000002,0.49175459999999999]
[6,"Sabrang","Female",44,57.828189999999999,0.60765020000000003,0.70454539999999999,0.47208729999999999,0.1635585,0.52272730000000001,0.50090780000000001]
[7,"Teknaf","Male",229,252.7149,0.61749449999999995,0.72052400000000005,0.4910543,0.2089337,0.3930131,0.52525790000000006]
[8,"Teknaf","Female",37,40.831670000000003,0.63899609999999996,0.70945950000000002,0.52128859999999999,0.180011,0.40540540000000003,0.64281200000000005]
[9,"Teknaf Paurashava","Male",218,138.6694,0.59534730000000002,0.69266059999999996,0.35027560000000002,0.22851650000000001,0.24770639999999999,0.52375950000000004]
[10,"Teknaf Paurashava","Female",41,26.080020000000001,0.65472560000000002,0.73780489999999999,0.46655570000000002,0.35144720000000002,0.34146339999999997,0.52126019999999995]
[11,"Whykong","Male",215,252.26750000000001,0.64179819999999999,0.77558139999999998,0.56426880000000001,0.28884670000000001,0.46511629999999998,0.50270959999999998]
[12,"Whykong","Female",47,55.146850000000001,0.67857140000000005,0.77659579999999995,0.62683750000000005,0.30221350000000002,0.46808509999999998,0.5149764]
[13,"Haldia Palong","Male",228,274.86189999999999,0.62938590000000005,0.7039474,0.52242520000000003,0.18697220000000001,0.4210526,0.46964470000000003]
[14,"Haldia Palong","Female",31,37.371569999999998,0.63824890000000001,0.75,0.57448100000000002,0.19291700000000001,0.41935480000000003,0.45919209999999999]
[15,"Jalia Palong","Male",218,242.73849999999999,0.64072739999999995,0.73853210000000002,0.46006710000000001,0.2176708,0.38073390000000001,0.46416239999999998]
[16,"Jalia Palong","Female",47,52.333530000000003,0.67277739999999997,0.75531919999999997,0.5065615,0.25197209999999998,0.40425529999999998,0.42687560000000002]
[17,"Palong Khali","Male",227,164.7389,0.64694770000000001,0.77422899999999995,0.54001529999999998,0.2031152,0.4537445,0.50502400000000003]
[18,"Palong Khali","Female",40,29.028880000000001,0.6857143,0.78125,0.61723799999999995,0.2911724,0.42499999999999999,0.54060609999999998]
[19,"Raja Palong","Male",233,325.45400000000001,0.61953939999999996,0.69742490000000001,0.48736940000000001,0.18113280000000001,0.3862661,0.4634819]
[20,"Raja Palong","Female",30,41.903939999999999,0.69077379999999999,0.71666660000000004,0.4588873,0.18562319999999999,0.2,0.47179650000000001]
[21,"Ratna Palong","Male",211,120.1631,0.61994329999999997,0.71682469999999998,0.48432779999999998,0.17828359999999999,0.40284360000000002,0.46199709999999999]
[22,"Ratna Palong","Female",47,26.766190000000002,0.67087759999999996,0.69680850000000005,0.48866920000000003,0.19351389999999999,0.48936170000000001,0.42708469999999998]

{"sheet":"MSNA_BV_weights","index":14,"n_rows":50,"n_cols":14,"rows_kept":50,"cut":false,"hidden":false,"merged":[[45,1,45,7],[47,1,47,7],[49,1,49,7],[18,0,24,0],[21,8,21,46],[26,0,27,0],[29,0,30,0],[29,2,29,7],[38,0,39,0]]}
["Work flow to obtain Betti-Verma weights for the MSNA data (and then use them to obtain scores and statistics)"]
[null,null,"Continuous indicators (normalized to [0, 1]; higher values = less desirable)"]
["1. Step:","Correlation table:","ls1_food","ls2_livelih","ls3_shelter","ls4_wash","ls5_health","ls6_educ"]
["Verify all indicators have the same substantive direction; else reverse the incorrectly coded ones [= replace x by 1 -x]\n\nCalculate the correlation table \n(use function CORREL)","ls1_food",{"$f":"WtCorrel(INDIRECT($B4),INDIRECT(C$3), Sampl_w)","v":1},0.53871458654760007,0.5507466235284233,0.24986021463164321,0.1210503407716889,4.1489397352660183E-2]
[null,"ls2_livelih",0.53871458654759996,1,0.66133164318719539,0.58311897294111059,0.25970440211742885,0.14054605677083989]
[null,"ls3_shelter",0.5507466235284233,0.66133164318719539,1,0.37244307205608013,0.45777219078145315,7.3093882093900261E-3]
[null,"ls4_wash",0.24986021463164321,0.5831189729411107,0.37244307205608013,1,0.2155867484864904,0.13375679471261509]
[null,"ls5_health",0.1210503407716889,0.25970440211742885,0.45777219078145315,0.21558674848649037,1,3.1179570601752016E-2]
[null,"ls6_educ",4.1489397352660128E-2,0.14054605677083987,7.3093882093900261E-3,0.13375679471261509,3.1179570601752037E-2,1]
[]
[null,"Statistics:"]
[null,"Mean",{"$f":"WeightedAvg(INDIRECT(C$3),Sampl_w)","v":0.62776424856613211},0.72848821051746149,0.5010890631133107,0.2219693776289795,0.41273667598582503,0.48800554375779598]
[null,"SD ",{"$f":"WeightedSD2(INDIRECT(C$3),Sampl_w)","v":2.4324861671641492E-2},2.7328624177351702E-2,5.1591574405332838E-2,4.1449708889647996E-2,6.2900080658887192E-2,3.1490482464695942E-2]
["Calculate the needed statistics:","C.o.V.",{"$f":"C13/C12","v":3.8748402329698742E-2},{"$f":"D13/D12","v":3.7514161221551642E-2},0.10295889134915422,0.18673615852962808,0.1523976043772941,6.4528944122661677E-2,"C.o.V. ="]
[null,null,null,null,null,null,null,null,"Coefficient of variation ="]
[null,null,null,null,null,null,null,null,"Standard deviation / Mean"]
[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: Information value, user-defined, equal\n\n2.b.: Set or calculate the value for each indicator. No normalization required at this point.","Three analyic choices:"]
[null,"Information value \n(here: = coefficient of variation)",{"$f":"C14","v":3.8748402329698742E-2},{"$f":"D14","v":3.7514161221551642E-2},0.10295889134915422,0.18673615852962808,0.1523976043772941,6.4528944122661677E-2,"Betti-Verma default option"]
[]
[null,"User-defined",2,2,1,1,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).\nFor the calculation of simple weighted scores (not BV), we name this range \"UserDef_first\"."]
[]
[null,"Equal",1,1,1,1,1,1,"Recommended for simplicity and for avoiding precarious assumption about the greater or lesser importance of some indicators"]
[]
[]
["Step 3:\nCalculate the second BV weighting component","Betti-Verma's second weight:",{"$f":"1 /((1 + SUM(IF(C$4:C$9<0, C$4:C$9))) * SUM(IF(C$4:C$9>0, C$4:C$9)))","v":0.39970243547329243},{"$f":"1 /((1 + SUM(IF(D$4:D$9<0, D$4:D$9))) * SUM(IF(D$4:D$9>0, D$4:D$9)))","v":0.31412800159079729},{"$f":"1 /((1 + SUM(IF(E$4:E$9<0, E$4:E$9))) * SUM(IF(E$4:E$9>0, E$4:E$9)))","v":0.32791154355718161},{"$f":"1 /((1 + SUM(IF(F$4:F$9<0, F$4:F$9))) * SUM(IF(F$4:F$9>0, F$4:F$9)))","v":0.39142531142896658},{"$f":"1 /((1 + SUM(IF(G$4:G$9<0, G$4:G$9))) * SUM(IF(G$4:G$9>0, G$4:G$9)))","v":0.4795488589803924},{"$f":"1 /((1 + SUM(IF(H$4:H$9<0, H$4:H$9))) * SUM(IF(H$4:H$9>0, H$4:H$9)))","v":0.73839908163332124},"The formula implements this algebra:"]
[null,"Formula (enter as array formula!):","{=1 /((1 + SUM(IF(R4C:R9C<0, R4C:R9C))) * SUM(IF(R4C:R9C>0, R4C:R9C)))}",null,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)","Combine first and second weights:","Exclude indicators whose sum of correlation coefficients with the other indicators is negative. Most likely, they relate to a different concept."]
[null,"Three analyic choices from first weight:"]
["The assigment of zero weights to indicators with negative second weights is a safeguard.","Information value",{"$f":"C20*C$27*(SUM(C$4:C$9) - 1 > 0)","v":1.5487830781879585E-2},1.1784248495881E-2,3.3761408985237308E-2,7.3093259007508543E-2,7.3082097290476641E-2,4.7648113078941283E-2,"Inclusion is modeled through the indicator  (red) in this formula:"]
["It will rarely come into play.",null,null,null,null,null,null,null,"=R[-12]C*R27C*(sum(R4C:R9C) - 1 > 0)"]
[null,"User-defined",{"$f":"C22*C$27*(SUM(C$4:C$9) - 1 > 0)","v":0.79940487094658486},0.62825600318159458,0.32791154355718161,0.39142531142896658,0.4795488589803924,0.73839908163332124]
[]
[null,"Equal",{"$f":"C24*C$27*(SUM(C$4:C$9) - 1 > 0)","v":0.39970243547329243},0.31412800159079729,0.32791154355718161,0.39142531142896658,0.4795488589803924,0.73839908163332124]
[]
[null,"BETTI-VERMA FORMULA ELEMENTS, NORMALIZED (= summing to 1)"]
["Step 5:\nNormalize the weights such that they sum to 1 over all indicators","Three analyic choices from first weight:"]
[null,"Information value",{"$f":"C32 / SUM($C32:$H32)","v":6.0770680641026975E-2},4.6238676805247042E-2,0.13247199251643443,0.28680111260991598,0.28675731660318632,0.18696022082418914]
[]
[null,"User-defined",{"$f":"C34 / SUM($C34:$H34)","v":0.23756843331476243},0.18670613580289125,9.7449283210472692E-2,0.11632440753808723,0.14251310601967312,0.21943863411411318]
[]
[null,"Equal",{"$f":"C36 / SUM($C36:$H36)","v":0.15076765828532271},0.11848900331471006,0.12368815188304069,0.1476455291743943,0.18088570918078187,0.27852394816175036]
[]
["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 indicators 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 (information value, or user-defined, or equal). 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:\nReview the weights and the score statistics for plausibility. Use the final index in the further analysis of the survey / assessment, etc. data.","In purely mechanical terms, the results for weights and scores are correct. However, two major objections must be raised:\n\n1. Aggregation to subsamples before calculations loses a great deal of information. The differences in the mean scores are much smaller (than in a household-level analysis), and the correlation among the five measures are almost perfect - which implies that Betti-Verma was not needed (for this set of 22 aggregate observations!).\n2. Even if this analysis were done with the 2,000+ household records, the sectoral indicators denote gaps, not achievements. It is doubtful that they have natural zero points. If we believe that \"zero gaps do not exist in affected households\", then the coefficients of variation are meaningless, and the \"information value\" option is unsound."]

{"sheet":"MSNA_BV_scores","index":15,"n_rows":26,"n_cols":16,"rows_kept":26,"cut":false,"hidden":false,"merged":[[1,11,1,12],[1,13,1,15],[2,0,2,9]]}
["Calculation of five variants of the same index, incl. three Betti-Verma options"]
[null,null,null,null,null,null,null,null,null,null,null,"Additive aggregation",null,"Betti-Verma, by type of first weight"]
["Static copy of the data \n(to keep the Betti-Verma stuff out of the sheet \"MSNA_Data\", not needed in \"real life\"):",null,null,null,null,null,null,null,null,null,null,"Unweighted\n(arithmetic mean)","Weighted mean\n(user defined weights)","Information value","User-defined","Equal"]
["Stratum","Union","HH head gender","Sample households","Sampl_w","ls1_food","ls2_livelih","ls3_shelter","ls4_wash","ls5_health","ls6_educ","MSNA_Index1a","MSNA_Index1b","MSNA_Index2a","MSNA_Index2b","MSNA_Index2c"]
[1,"Baharchara","Male",204,134.01840000000001,0.6254596,0.75367649999999997,0.57822320000000005,0.23526359999999999,0.32352940000000002,0.47336620000000001,{"$f":"AVERAGE($F5:$K5)","v":0.49825308333333335},{"$f":"SUMPRODUCT($F5:$K5, MSNA_UserDef_first) / SUM(MSNA_UserDef_first)","v":0.54608182500000002},{"$f":"SUMPRODUCT($F5:$K5, MSNA_InfoValue_Cont)","v":0.39820592323346349},{"$f":"SUMPRODUCT($F5:$K5, MSNA_UserDef_Cont)","v":0.52300183154153845},{"$f":"SUMPRODUCT($F5:$K5, MSNA_Equal_Cont)","v":0.4802221021623222}]
[2,"Baharchara","Female",51,33.504600000000003,0.66167719999999997,0.78921569999999996,0.53975139999999999,0.23385510000000001,0.41176469999999998,0.51073009999999996,{"$f":"AVERAGE($F6:$K6)","v":0.52449903333333336},0.57473588749999993,0.42883746250309013,0.55510225431640514,0.51129433048218265]
[3,"Nhilla","Male",221,240.04560000000001,0.60734489999999997,0.72511309999999995,0.51450309999999999,0.28822170000000003,0.46153850000000002,0.46569949999999999,0.51040346666666669,0.54935984999999998,0.44067341141298516,0.53130196546273933,0.49687266213079928]
[4,"Nhilla","Female",43,46.705710000000003,0.7012043,0.72093019999999997,0.54698919999999995,0.26512829999999998,0.60465120000000006,0.5344411,0.56222404999999998,0.59943484999999996,0.49775474339612213,0.58877844226209708,0.5561697288995836]
[5,"Sabrang","Male",219,287.82659999999998,0.59313850000000001,0.71461189999999997,0.44964290000000001,0.2039214,0.46118720000000002,0.49175459999999999,0.48570941666666667,0.52775086250000003,0.41132546686456872,0.51550700304829411,0.48021094324881575]
[6,"Sabrang","Female",44,57.828189999999999,0.60765020000000003,0.70454539999999999,0.47208729999999999,0.1635585,0.52272730000000001,0.50090780000000001,0.49524608333333325,0.53545901250000005,0.42249737910417989,0.52534588433057283,0.49170388338416826]
[7,"Teknaf","Male",229,252.7149,0.61749449999999995,0.72052400000000005,0.4910543,0.2089337,0.3930131,0.52525790000000006,0.49271291666666667,0.53678700000000001,0.40671671151953342,0.52465182487945361,0.48744545335546613]
[8,"Teknaf","Female",37,40.831670000000003,0.63899609999999996,0.70945950000000002,0.52128859999999999,0.180011,0.40540540000000003,0.64281200000000005,0.51632876666666672,0.55580352499999996,0.42875342915988013,0.55483798748933333,0.52382871703198564]
[9,"Teknaf Paurashava","Male",218,138.6694,0.59534730000000002,0.69266059999999996,0.35027560000000002,0.22851650000000001,0.24770639999999999,0.52375950000000004,0.43971098333333331,0.49078422500000002,0.34910167771774203,0.48171033973862398,0.43958227525800497]
[10,"Teknaf Paurashava","Female",41,26.080020000000001,0.65472560000000002,0.73780489999999999,0.46655570000000002,0.35144720000000002,0.34146339999999997,0.52126019999999995,0.51220949999999998,0.55822343749999992,0.43187630424513329,0.54268987879427388,0.50267953106938434]
[11,"Whykong","Male",215,252.26750000000001,0.64179819999999999,0.77558139999999998,0.56426880000000001,0.28884670000000001,0.46511629999999998,0.50270959999999998,0.53972016666666667,0.58196257499999993,0.45981793824634942,0.56246338697112663,0.52525012203805677]
[12,"Whykong","Female",47,55.146850000000001,0.67857140000000005,0.77659579999999995,0.62683750000000005,0.30221350000000002,0.46808509999999998,0.5149764,0.56121328333333342,0.60280586250000001,0.47736651738198421,0.58215599599044876,0.5445806927234963]
[13,"Haldia Palong","Male",228,274.86189999999999,0.62938590000000005,0.7039474,0.52242520000000003,0.18697220000000001,0.4210526,0.46964470000000003,0.4889046666666666,0.53334516249999997,0.40217313856298464,0.51667661804866616,0.47749417523846327]
[14,"Haldia Palong","Female",31,37.371569999999998,0.63824890000000001,0.75,0.57448100000000002,0.19291700000000001,0.41935480000000003,0.45919209999999999,0.50569896666666658,0.55280533750000005,0.41100099421891201,0.53060915277934972,0.48838515732892362]
[15,"Jalia Palong","Male",218,242.73849999999999,0.64072739999999995,0.73853210000000002,0.46006710000000001,0.2176708,0.38073390000000001,0.46416239999999998,0.48364894999999991,0.53514414999999993,0.39241855664265557,0.51637344884841019,0.4713015376820654]
[16,"Jalia Palong","Female",47,52.333530000000003,0.67277739999999997,0.75531919999999997,0.5065615,0.25197209999999998,0.40425529999999998,0.42687560000000002,0.50296018333333337,0.55573221250000004,0.4109131122463433,0.53081263937151724,0.48280738624604214]
[17,"Palong Khali","Male",227,164.7389,0.64694770000000001,0.77422899999999995,0.54001529999999998,0.2031152,0.4537445,0.50502400000000003,0.52051261666666671,0.56803154999999994,0.429439298505712,0.54998533034098351,0.50879613200816798]
[18,"Palong Khali","Female",40,29.028880000000001,0.6857143,0.78125,0.61723799999999995,0.2911724,0.42499999999999999,0.54060609999999998,0.55683013333333331,0.60099313749999994,0.4660143023798009,0.58198603237636526,0.5427365754484974]
[19,"Raja Palong","Male",233,325.45400000000001,0.61953939999999996,0.69742490000000001,0.48736940000000001,0.18113280000000001,0.3862661,0.4634819,0.47253575000000003,0.51902234999999997,0.3838270283514465,0.5027142937946385,0.46202978043904569]
[20,"Raja Palong","Female",30,41.903939999999999,0.69077379999999999,0.71666660000000004,0.4588873,0.18562319999999999,0.2,0.47179650000000001,0.45395790000000003,0.51639847500000002,0.33470180569192043,0.49625584895434821,0.44081258276949042]
[21,"Ratna Palong","Male",211,120.1631,0.61994329999999997,0.71682469999999998,0.48432779999999998,0.17828359999999999,0.40284360000000002,0.46199709999999999,0.47737001666666673,0.5251235125,0.38800463504926719,0.50784116466754126,0.46617753742395451]
[22,"Ratna Palong","Female",47,26.766190000000002,0.67087759999999996,0.69680850000000005,0.48866920000000003,0.19351389999999999,0.48936170000000001,0.42708469999999998,0.49438593333333336,0.54175021249999999,0.4134000735993697,0.52306795489559987,0.48019669680645011]

{"sheet":"MSNA_Indices_compared","index":16,"n_rows":23,"n_cols":14,"rows_kept":23,"cut":false,"hidden":false,"merged":[[1,5,1,9],[9,5,9,9]]}
["Comparison of five indices in the MSNA example:"]
["Index",null,"Mean","SD","Information value (CoV)","Correlations:"]
[null,null,null,null,"= SD / Mean","MSNA_Index1a","MSNA_Index1b","MSNA_Index2a","MSNA_Index2b","MSNA_Index2c"]
["MSNA_Index1a","Unweighted (arithmetic mean)",{"$f":"WeightedAvg(INDIRECT($A4),Sampl_w)","v":0.49667551992825082},{"$f":"WeightedSD2(INDIRECT($A4),Sampl_w)","v":2.6463731357242688E-2},{"$f":"D4/C4","v":5.3281730819078034E-2},{"$f":"WtCorrel(INDIRECT($A4),INDIRECT(F$3),Sampl_w)","v":1},0.98853861517468256,0.9616038008577289,0.98847143598662746,0.98352714492175219]
["MSNA_Index1b","Weighted mean (user defined weights)",{"$f":"WeightedAvg(INDIRECT($A5),Sampl_w)","v":0.54203819733163738},{"$f":"WeightedSD2(INDIRECT($A5),Sampl_w)","v":2.4658438012633857E-2},{"$f":"D5/C5","v":4.5492067042550113E-2},0.98853861517468256,1,0.91519385067490844,0.98683711641649163,0.95926467397685289]
["MSNA_Index2a","Betti-Verma: Information value",{"$f":"WeightedAvg(INDIRECT($A6),Sampl_w)","v":0.41146820857195193},{"$f":"WeightedSD2(INDIRECT($A6),Sampl_w)","v":2.9534939824685944E-2},7.1779396826769135E-2,0.96160380085772867,0.91519385067490844,1,0.93602628218638018,0.96668251717481568]
["MSNA_Index2b","Betti-Verma: User-defined first weights",{"$f":"WeightedAvg(INDIRECT($A7),Sampl_w)","v":0.52570906978114318},{"$f":"WeightedSD2(INDIRECT($A7),Sampl_w)","v":2.2951564565138569E-2},4.3658300540056282E-2,0.98847143598662746,0.98683711641649141,0.93602628218638018,1,0.98836293302163725]
["MSNA_Index2c","Betti-Verma: Equal first weights",{"$f":"WeightedAvg(INDIRECT($A8),Sampl_w)","v":0.48629535118098077},{"$f":"WeightedSD2(INDIRECT($A8),Sampl_w)","v":2.4535557754268911E-2},5.0454024893891496E-2,0.98352714492175219,0.95926467397685267,0.96668251717481568,0.98836293302163725,1]
[]
[null,null,"Distributions:",null,"Index values","Frequencies of occurrence"]
[null,null,null,null,"Upper limit of interval","MSNA_Index1a","MSNA_Index1b","MSNA_Index2a","MSNA_Index2b","MSNA_Index2c"]
[null,null,null,null,0,{"$f":"FREQUENCY(INDIRECT(F$11), $E$12:$E$21) / 22","v":0},{"$f":"FREQUENCY(INDIRECT(G$11), $E$12:$E$21) / 22","v":0},{"$f":"FREQUENCY(INDIRECT(H$11), $E$12:$E$21) / 22","v":0},{"$f":"FREQUENCY(INDIRECT(I$11), $E$12:$E$21) / 22","v":0},{"$f":"FREQUENCY(INDIRECT(J$11), $E$12:$E$21) / 22","v":0},null,"FREQUENCY is a multi-cell array function and has to be entered by pressing Shift-Ctrl-Enter."]
[null,null,null,null,0.1,0,0,0,0,0,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,null,0.2,0,0,0,0,0,null,null,"1. Select the range R12C6:R22C6 (11 cells); type \"=FREQUENCY(\" in the formula bar (without the inverted quotes)."]
[null,null,null,null,0.3,0,0,0,0,0,null,null,"2.  The final formula is \"=FREQUENCY(INDIRECT(R11C), R12C5:R21C5) / 100\", where"]
[null,null,null,null,0.4,0,0,0.27272727272727271,0,0,null,null,null,"INDIRECT(R11C) references the data range for the particular index, and "]
[null,null,null,null,0.5,0.5,4.5454545454545456E-2,0.72727272727272729,9.0909090909090912E-2,0.63636363636363635,null,null,null,"R12C5:R21C is the bins_range (the upper bounds) with only 10 cells."]
[null,null,null,null,0.6,0.5,0.86363636363636365,0,0.90909090909090906,0.36363636363636365,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."]
[null,null,null,null,0.7,0,9.0909090909090912E-2,0,0,0]
[null,null,null,null,0.8,0,0,0,0,0]
[null,null,null,null,0.9,0,0,0,0,0]
[null,null,null,null,1,0,0,0,0,0]
[null,null,null,null,"Total",{"$f":"SUM(F12:F22)","v":1},{"$f":"SUM(G12:G22)","v":1},1,1,1]

{"sheet":"MSNA_group_diff","index":17,"n_rows":21,"n_cols":6,"rows_kept":21,"cut":false,"hidden":false,"merged":[[2,0,3,0],[2,1,2,2],[2,3,3,3]]}
["Index means, by Union and HH head gender, for the Betti-Verma information value option:"]
[]
["Union","Gender HH head",null,"Total",null,"Difference"]
[null,"Male","Female",null,null,"Female-Male"]
["Baharchara",{"$f":" SUMPRODUCT(MSNA_Index2a, Sampl_w, --(Union=$A5),--(HH_head_gender=B$4)) / SUMPRODUCT(Sampl_w, --(Union=$A5),--(HH_head_gender=B$4))","v":0.39820592323346349},0.42883746250309013,{"$f":" SUMPRODUCT(MSNA_Index2a, Sampl_w, --(Union=$A5)) / SUMPRODUCT(Sampl_w, --(Union=$A5))","v":0.4043322310873888},null,{"$f":"C5-B5","v":3.063153926962664E-2}]
["Nhilla",0.44067341141298516,0.49775474339612213,0.44997075041387108,null,{"$f":"C6-B6","v":5.7081331983136974E-2}]
["Sabrang",0.41132546686456872,0.42249737910417989,0.4131945324246194,null,1.117191223961117E-2]
["Teknaf",0.40671671151953342,0.42875342915988013,0.40978196955533275,null,2.2036717640346704E-2]
["Teknaf Paurashava",0.34910167771774203,0.43187630424513329,0.36220499495750469,null,8.277462652739126E-2]
["Whykong",0.45981793824634942,0.47736651738198427,0.46296596585568506,null,1.7548579135634845E-2]
["Haldia Palong",0.40217313856298464,0.41100099421891201,0.40322975438830078,null,8.8278556559273635E-3]
["Jalia Palong",0.39241855664265557,0.4109131122463433,0.39569872311767618,null,1.8494555603687735E-2]
["Palong Khali",0.429439298505712,0.46601430237980096,0.4349186996654944,null,3.6575003874088963E-2]
["Raja Palong",0.3838270283514465,0.33470180569192043,0.37822339179247788,null,-4.9125222659526069E-2]
["Ratna Palong",0.38800463504926719,0.41340007359936976,0.39263093613168148,null,2.5395438550102567E-2]
["Total",{"$f":" SUMPRODUCT(MSNA_Index2a, Sampl_w, --(HH_head_gender=B$4)) / SUMPRODUCT(Sampl_w,--(HH_head_gender=B$4))","v":0.40797787271570574},{"$f":" SUMPRODUCT(MSNA_Index2a, Sampl_w, --(HH_head_gender=C$4)) / SUMPRODUCT(Sampl_w,--(HH_head_gender=C$4))","v":0.43044855782417613},{"$f":" SUMPRODUCT(MSNA_Index2a, Sampl_w) / SUMPRODUCT(Sampl_w)","v":0.41146820857195193},null,2.247068510847039E-2]
[]
["Interpretation:"]
["It would appear that the intersectoral gap level is lowest in the urben"]
["Teknaf Paurashava Union, but this Union has the highest gap difference"]
["between male and female-headed households."]

{"sheet":"Named_ranges","index":18,"n_rows":52,"n_cols":3,"rows_kept":52,"cut":false,"hidden":false,"merged":[]}
["Name","RefersTo","Cells"]
["Equal_Cont","=Betti_Verma_weights!R49C3:R49C12",10]
["HH_head_gender","=MSNA_Data!R2C3:R23C3",22]
["id","=BV_w_sampling_weights!R2C1:R101C1",100]
["Index1a","=Betti_Verma_scores!R5C12:R104C12",100]
["Index1b","=Betti_Verma_scores!R5C13:R104C13",100]
["Index2a","=Betti_Verma_scores!R5C14:R104C14",100]
["Index2b","=Betti_Verma_scores!R5C15:R104C15",100]
["Index2c","=Betti_Verma_scores!R5C16:R104C16",100]
["InfoValue_Cont","=Betti_Verma_weights!R45C3:R45C12",10]
["ls1_food","=MSNA_Data!R2C6:R23C6",22]
["ls2_livelih","=MSNA_Data!R2C7:R23C7",22]
["ls3_shelter","=MSNA_Data!R2C8:R23C8",22]
["ls4_wash","=MSNA_Data!R2C9:R23C9",22]
["ls5_health","=MSNA_Data!R2C10:R23C10",22]
["ls6_educ","=MSNA_Data!R2C11:R23C11",22]
["MSNA_Equal_Cont","=MSNA_BV_weights!R44C3:R44C8",6]
["MSNA_Index1a","=MSNA_BV_scores!R5C12:R26C12",22]
["MSNA_Index1b","=MSNA_BV_scores!R5C13:R26C13",22]
["MSNA_Index2a","=MSNA_BV_scores!R5C14:R26C14",22]
["MSNA_Index2b","=MSNA_BV_scores!R5C15:R26C15",22]
["MSNA_Index2c","=MSNA_BV_scores!R5C16:R26C16",22]
["MSNA_InfoValue_Cont","=MSNA_BV_weights!R40C3:R40C8",6]
["MSNA_UserDef_Cont","=MSNA_BV_weights!R42C3:R42C8",6]
["MSNA_UserDef_first","=MSNA_BV_weights!R22C3:R22C8",6]
["s_weights","=BV_w_sampling_weights!R2C2:R101C2",100]
["Sampl_w","=MSNA_Data!R2C5:R23C5",22]
["Sample_households","=MSNA_Data!R2C4:R23C4",22]
["Stratum","=MSNA_Data!R2C1:R23C1",22]
["Union","=MSNA_Data!R2C2:R23C2",22]
["UserDef_Cont","=Betti_Verma_weights!R47C3:R47C12",10]
["UserDef_first","=Betti_Verma_weights!R27C3:R27C12",10]
["x1_","=Simulated_Data!R5C2:R104C2",100]
["x10_","=Simulated_Data!R5C11:R104C11",100]
["x2_","=Simulated_Data!R5C3:R104C3",100]
["x3_","=Simulated_Data!R5C4:R104C4",100]
["x4_","=Simulated_Data!R5C5:R104C5",100]
["x5_","=Simulated_Data!R5C6:R104C6",100]
["x6_","=Simulated_Data!R5C7:R104C7",100]
["x7_","=Simulated_Data!R5C8:R104C8",100]
["x8_","=Simulated_Data!R5C9:R104C9",100]
["x9_","=Simulated_Data!R5C10:R104C10",100]
["y1_","=BV_w_sampling_weights!R2C3:R101C3",100]
["y10_","=BV_w_sampling_weights!R2C12:R101C12",100]
["y2_","=BV_w_sampling_weights!R2C4:R101C4",100]
["y3_","=BV_w_sampling_weights!R2C5:R101C5",100]
["y4_","=BV_w_sampling_weights!R2C6:R101C6",100]
["y5_","=BV_w_sampling_weights!R2C7:R101C7",100]
["y6_","=BV_w_sampling_weights!R2C8:R101C8",100]
["y7_","=BV_w_sampling_weights!R2C9:R101C9",100]
["y8_","=BV_w_sampling_weights!R2C10:R101C10",100]
["y9_","=BV_w_sampling_weights!R2C11:R101C11",100]