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.

Thank you, Mr. Myatt. I am interested in the ODK calculation- do you mind sharing that resource, as well? 

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

Dear Ernest 

Many thanks for this great information. I have a few questions. Which flags does the embedded z-score calculator use? And how can I access the R package if I may want to use it. I could not figure it out from an earlier link.

Regards

Blessing

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

Thanks again 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,).

Ideally, you'll use the WHO flags and enter data in the field as the measurements are done. The significance of flags here is not to aid the decision as to whether or not to include the case for analysis. Instead the flags tell the teams to immediately repeat the measurements and verify the age data. Supposedly the flags show 'implausible' values, but implausibility does not mean impossibility. In my fieldwork, we analyzed the anthro data immediately after collection, then all cases that were flagged were revisited again and measurements repeated. On occasion, more than 91% of flagged cases were found to be valid. Some flags told us the children should not even be alive...but they were. Excluding them would have badly biased our results.

If you want to see how wasteful it can be to accept flags as absolute indicators, download some of the recent DHS/MICS data sets and see how many thousands of observations cannot be used because flags have been used without questioning their validity. 

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.

An interesting piece of work would be for someone to do an analysis solely of the flagged data from multiple data sets and surveys. I would be surprised if such an analysis did not find fairly consistent patterns in at least some data sets. What do the flags tell us, for example, in relation to the day of the week, the month, and the time of day when the measurements were done? The same question could be asked about the ages, genders, educational backgrounds, and composition and sizes of survey teams (maybe even salaries?). Perhaps also some specifics of locale. Of course, there may be few surveys containing these details. But such an exercise could shed light on issues around the quality of our evidence.

Mark, have you come across any work in this direction?

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.

Hello,

This might be an overdue response. 

We are working on developing the algorithm of z-score calculation which can be used in excel in order to be incorporated into the Kobo/ODK tool for the SENS survey (SENS is rooted in SMART survey which is designed for using in refugee setting). The aim of this is to mark out the children with SAM/MAM by either WHZ or MUAC criteria and refer them to the CMAM programs accordingly. The old fashion of referral in the previous survey is using WHO paper form to do one-by-one checking on-site, which is time-consuming and subject to both systematic error (rounding number on the table) and observational error. We developed the algorithm based on the method Robert Johnson used for the flagging as mentioned above, which can classify the child as SAM or MAM based on the measured WHZ and MUAC. However, it won't give an actual z-score number and shouldn't be used as a z-score calculator as ENA or other analysis tools many of you have introduced. We tested its performance in two sets of real data and comparing the accuracy to using the WHO paper form, and the algorithm has a lower miss-referral rate. We are planning on applying them into the Kobo tool for future SENS survey. 

We would love to discuss more if interested. Please contact by kuang@unhcr.org.

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.

Dear Mark,

I agree using ENA to calculate is easy and fast on the computer. However, the purpose of applying an algorithm on ODK is just like you said, inform the mother/caregiver that their child needs treatment onsite during the survey, where we cannot bring a computer with us. We involved IT technician in developing this tool, we have considered the complexity of the algorithm and don't want to overload the excel and ODK function. We would love to learn if there will be an easier way to calculate z-score in excel! 
As of using WHZ or MUAC as the enrolling criterion, that's another huge discussion. But MUAC would overlook lots of SAM/MAM cases comparing to WHZ and using both criteria if possible is recommended in the CMAM guideline for now. 

Best regards,

Heqian

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.
 

Dear Mark,

Thank you so much on the super detailed reply! 

I've checked your R-package, it is super helpful. The LOOKUP function is useful in Excel and I agree that looking up in the database is the best way of calculating the z-score. I'm not an expert on coding for Kobo, but according to my understanding, in order to use the LOOKUP function, an independent database or table is needed which is not possible to be deployed into Kobo so far. So the algorithm we build now is a polynomial that can be coded into the excel and deployed in Kobo without adding an additional reference table. And that's also the reason why the polynomial shouldn't be used for calculating the z-score directly because it is just a best-fitting model to the real database with a mismatching rate. However, if it performs better than using the paper-based WHO form for referral, it is practically and ethically feasible. After the child is referred to the program, they will be measured again and the z-score can be calculated on the computer by either using ENA or the R-package as preferred by the facility. 

Best regards,

Heqian

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.

Hi All,

I'd like to share the mobile app we've developed in the Philippines. The main purpose of the app is for reporting Milk Code Violation but we added some modules in the app, including growth monitoring. The modules are embedded in the app so you might need to explore the app to see the growth monitoring tool. The growth monitoring chart is supposedly for the mothers so they can monitor their child's growth, but we can see that health workers can use this app to detect and monitor stunting and wasting in their communities. The growth monitoring module is a simple calculator that can compute the child's Weight for Height/Length, Weight for Age, and Height/Length for Age. You just need to enter the child's birth date, weight in kg and height/length in cm. The app can assess the child in real time. You can use it also offline.

https://apps.apple.com/us/app/mbf-ph/id1260502250

https://play.google.com/store/apps/details?id=org.motherbabyfriendlyphilippines.android

https://play.google.com/store/apps/details?id=com.motherbabyfriendlyphilippines.eopt

I would like to have the syntax that automatically calculates the Body Mass Index, in short the syntaxes whose denominator is squared

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.
 

Good evening,

In the absence of having a z table to interpret manually I suggest you use ENA FOR SMART

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.

Hello,

To be able to generate the BMI I think you can prepare an excel file and format the results cells with the formula W/ HxH. Once done, it will be automatically calculated as soon as the agent enters the weight and height.

And this file can also be downloaded in the ODK so the result cell is automatically generated.

Thank you

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.