Excel Calculation: Z-score calculation

I am not aware of a validated Excel tool for calculating WHZ. It should be possible to make one as all that is required is some simple arithmetic and a lookup to find reference values and some more simple arithmetic ... All possible do do in Excel. Your IT department should be able to build something for you.

I have seen WHZ calculated inside CSPro and Open Data Kit (ODK) data-entry systems on tablet devices. This seems to be what you are looking for.

A simple solution would, I think, be to use ENA for SMART or one of the anthropometric calculators available from the WHO. These all require less computing power to run than Excel and will run on older machines and on lo power "Netbook" devices. Some versions might run on a Windows mobile 'phone ... if you can find one (Mircosoft no longer support Windows Phones).

I use the R package "zScorer" which can be used within R or through a web-browser (online or off-line). This has been discussed in this thread.

I hope this is of some use.
 

Just following up ...

There is an Excel Add-in that might do what you want. It uses the LMS method so can be made to work with many references. I have not used this software. It would be interesting to hear how you get on with it.

Hi,

I hope this response still gets to you in time for your SMART survey.

There is a SMART ODK questionnaire (in XLSForm format) that we have come across on GitHub developed by Robert Johnston from UNICEF (here is the link to the GitHub repository). In that repository, you will see different XLSForms. The one specific to SMART survey is the titled SMART_Questionnaire.xls. In that XLSForm, there is embedded calculation (you will see it as a calculation field) for checking whether to flag weight and/or height measurements made by the enumerator. However, the logic of the implementation is not to show the enumerator the z-score based on the measurements taken but to prompt the enumerator to re-measure the child in real time. You will notice also that the form does a random re-measure for some entries even if the measurements are not flagged by the calculation (random check). We have used variation of this in our ODK data collection approach to anthropometric surveys. If you have any further questions about the tool, you may want to contact Robert Johnston via the GitHub repository that I linked above. Otherwise, I am happy to help as well.

With regard to CSPro, I created a script in CSPro syntax based on the calculations used in Robert Johnston's XLSform above. We tested this in a couple of anthropometric surveys I have been involved in and they worked as intended though I have to admit that I only learned CSPro syntax for the purpose of writing that script and have not written any before or since then so my code is most likely not optimal. But I share it anyway here if you or anyone finds it helpful/useful. Here is a link to the code.

As Mark said, more often I use the zscorer app mentioned in the link shared by Mark and I used it as an R package as I use R for my survey analysis work. Again, this is a resource you might want to consider depending on you needs.

I hope this is helpful.

Best,

Ernest

Blessing,

The XLSForm I refer to uses the WHO flagging criteria. If the SMART flagging criteria is needed, then you just apply this criteria when you use ENA at analysis stage. Remember that the logic in the XLSform is to try to catch possible measurement errors as a form of data quality checks during data collection and enumerators are asked to re-measure if this is the case. No censoring of data happens at this stage and all data for sampled children are still collected. The analyst should then apply the approrpate flagging he/she prefers (WHO and/or SMART) during data analysis.

The zscorer package mentioned by Mark is an R package. If you use R for data analysis, you can use this package to calculate various anthropometric z-scores. The package can be installed from CRAN via the usual package installation syntax. Full instructions/guide on installing the R package is available here. Remember, this is an R package so installation requires that you have R installed on your computer as well.

If you are not an R user, then we created a web-based application that uses the R package in calculating the z-scores. This can be found here. Instructions on how to use the web application can be found here.

Hope this answers your question.

Best,

Ernest

I guess that using SMART flags during date-entry might be difficult as these use the mean z-score in the survey data as the basis for the flagging decisions and a mean will be unavailable for the first record and may be unreliable until you have entered 60 or so records. I think Ernest is correct ... work with the WHO flagging criteria (these will flag biologically implausible values). You can then apply SMART (or WHO) flagging criteria before analysing data using ENA for SMART or usimg a script in your data-analysis system which is what I usually do (this is quite easy to do in R, SPSS, EpiInfo, &c,).

I think Bill is correct. DHS/MICS data often have a lot of records that would be flagged (particularly using SMART flagging criteria). I also think that flagging at data-collection / data-entry should use the WHO flagging criteria to trigger re-measurement and checking of other data (e.g. age, sex) data that are used in the z-score calculations.

Note that the flagging criteria were designed for survey use. You will find many more extreme values in therapeutic feeding program admissions. Such cases are alive but would likely not be alive for long without therapeutic feeding. My experience is that I see very few flagged records in survey data when using WHO flagging criteria ... more when SMART flags are used.

Bill,

I have concerns about the use of SMART flagging criteria in large surveys. Wide-area surveys such as MICS and DHS will usually collect data from many populations. Each population may have different distributions of anthropometric indices and different prevalence of anthropometric indicators. In this case the mean of the entire survey sample will not be a suitable reference mean and the assumed standard deviation (i.e. SD = 1) will usually be too narrow to set limits that define statistical outliers with the expected probabilities. This will lead to records being flagged incorrectly likely leading to biased prevalence estimates. Stratum or district specific means should be used instead of whole sample means, but this may not solve the problem entirely.

I did a little work related to data quality in nutritional anthropometry surveys for the EU and UNICEF Nigeria (DHS data has higher proportions flagged than MICS which have higher proportions flagged than national SMART surveys). I think the SMART project will have done more. I am sure CDC/NHANES will have looked at this in some detail. I am not aware of any systematic "international" study. Maybe ENN could pick this up.

I am a little confused ... I think the idea is to identify SAM cases found in a survey sample and refer them to CMAM. A simple approach would be to enter survey data and use ENA (or another system) to calculate z-scores and then apply case-definitions and make a list of cases meeting SAM case-definitions that could be reported to block leaders to arrange referral. This would not be hard to do. I guess that you want to use ODK to do this as data is collected. I do not know enough about ODK or Kobo to know how feasible this is but calculating (e.g.) W/H z-scores in Excel should be possible as it is little more than table lookup and some simple arithmetic. Just about any IT department should have sufficient skill to do this.

My inclination would be to drop the complicated WHZ and keep simple MUAC classes to find and refer SAM cases.

WRT the algorithm for calculating WHZ or other z-scores ... I can only say what Ernest and I used in the R zscorer library which is available as open-source software (this means that you can see the algorithms and lookup tables for yourself).

We treated the problem as a table lookup problem just as we would if we were doing the calculations by hand but having the computer do that for us. We had a table which looked like this:

 index sex given       l      m       s
   wfh   1  65.0 -0.3521 7.4327 0.08217
   wfh   1  65.1 -0.3521 7.4563 0.08216
   wfh   1  65.2 -0.3521 7.4799 0.08216
   wfh   1  65.3 -0.3521 7.5034 0.08215
   wfh   1  65.4 -0.3521 7.5269 0.08214
   wfh   1  65.5 -0.3521 7.5504 0.08214
    .
    .
    .
   wfh   2 119.5 -0.3833 22.6012 0.09808
   wfh   2 119.6 -0.3833 22.6444 0.09812
   wfh   2 119.7 -0.3833 22.6877 0.09816
   wfh   2 119.8 -0.3833 22.7309 0.09820
   wfh   2 119.9 -0.3833 22.7741 0.09824
   wfh   2 120.0 -0.3833 22.8173 0.09828
   
The "index" column allows us to select the index, from many available, that we want to calculate. The "sex" column is the sex of the child (1 = Male, 2 = Female), and the "given" column is the measures height of the child. The "l", "m", and "s" columns are the L, M, and S values used to calculate the z-score as described in "WHO Child Growth Standards : Methods and development: Length/height-for-age, weight-for-age, weight-for-length, weight-for-height and body mass index-for-age" on pages 301-304:

    WHO Guide

The process is to (1) Look up the L, M, S values for the the child given the sex and height and (2) plug these values with the measured weight into the formulae provided by the WHO (see above). Excel's VLOOKUP() function makes looking up data in a table very easy and the rest is just simple arithmetic. I have knocked together a simple proof-of-concept demonstration for WHZ in Excel and put it here:

    Excel Demo for WHZ

This is a simplified WHZ calculator and does not correct extreme values when WHZ > +3 or WHZ < -3. You or someone else here can add that from the formulae presented in "WHO Child Growth Standards : Methods and development: Length/height-for-age, weight-for-age, weight-for-length, weight-for-height and body mass index-for-age" on pages 301-304. It could be made prettier. As I say ... I "knocked this together" so you really should test it before using it for screening &c.

There is nothing here that should overload Excel running on a PC. I do not know if it would overload ODK running on a basic mobile phone ... You can run Excel on an Android or Apple's IOS. You could give it a try.

You can get the lookup table data from the source code of the R zscorer library. This has tables for BMI/A, head-circumference/A. H/A, L/A, MUAC/A, sun-scapular skinfold/A, triceps skinfold/A, W/A, W/H, and W/L. Tables are also available from the WHO child growth standards section of the WHO website:

    WHO Child Growth Standards

I hope this is of some use.
 

I know next to nothing about Kobo so cannot help. The use of polynomials was common with the NCHS reference and was used in (e.g.) the EpiNut module of EpiInfo. I have not come across it being used for the WHO Child Growth Standards. I think that all the WHO provided software and other software (e.g ZSCOER06 in STATA and the 'zscorer' library in R) use table lookup. Do you have a link to information about using polynomials for the WHO Child Growth Standards? That would be very useful as it could be implemented in (e.g.) ODK and CSPro.

WRT table lookup in Excel ... you could use this on mobile 'phone versions of Excel or other spreadsheets to calculate z-scores in the field using table lookup.

Assuming:

    Cell A1 holds weight in kg
  
    Cell A2 holds height in cm
  
We can use:

    =A1/(A2/100)^2

to calculate BMI. The "A2/100" converts height in centimetres to height in metres and "^2" raised a number to the power of 2 (i.e. squares a number).

I hope this is what you need.
 

ENA for smart works well. I am not sure if it will calculate BMI/A z-scores. I think it does H/A, W/A, and W/H for children only. One reason for wanting to do this in EXCEL is that data-entry systems for mobile devices such as ODK and derivatives use EXCEL format files to specify data-entry systems and to provide on-screen calculations. ENA, as far as I know does not run on mobile devices.

It is possible to develop simple code to calculate z-scores that runs on mobile devices or within EXCEL. The typical approach is to develop estimating equations using polynomial regression from the WGS reference data. I have tried this for WAZ for the ENN concurrent wasting and stunting group and it works well on Android devices. The installer is here.

This is a Webkit application and is little more than a small web page (HTML forms an JavaScript) wrapped up as an app. The web-page is here. This should work in most browsers.

If anyone is interested I can make up a short HowTo guide on finding the estimating equation for z-scores (i.e. how I did if for WAZ). Please let me know if there is an interest for this.

Yes. That would work but it would be simple BMI. That is (sort of) OK with adults but for children and young people you'd probably want to have a BMI/A z-score.