Menu ENN Search
Language: English Fran├žais

Excel Calculation: Z-score calculation

This question was posted the Assessment and Surveillance forum area and has 12 replies.

» Post a reply

Anonymous 38163

Normal user

29 Jan 2020, 20:06

Hi there,

I'm conducting a SMART survey with Kobo, and I would like teams to see the WHZ score after they've inserted the child's weight and height. Is anyone familiar with a validated Excel function for this, based on 2006 WHO growth standards? 

Mark Myatt

Epidemiologist at Brixton Health

Frequent user

30 Jan 2020, 10:03

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.
 

Mark Myatt

Epidemiologist at Brixton Health

Frequent user

30 Jan 2020, 10:15

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.

Anonymous 38163

Normal user

30 Jan 2020, 13:27

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

Ernest Guevarra

Frequent user

31 Jan 2020, 08:29

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 Mureverwi

Normal user

31 Jan 2020, 09:28

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

Ernest Guevarra

Frequent user

31 Jan 2020, 20:47

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

Blessing Mureverwi

Normal user

1 Feb 2020, 01:35

Thanks again Ernest

Mark Myatt

Epidemiologist at Brixton Health

Frequent user

1 Feb 2020, 14:35

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

Bill Kinsey

African Studies Centre, University of Leiden

Normal user

4 Feb 2020, 15:10

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. 

Mark Myatt

Epidemiologist at Brixton Health

Frequent user

5 Feb 2020, 10:35

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 Kinsey

African Studies Centre, University of Leiden

Normal user

5 Feb 2020, 15:31

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?

Mark Myatt

Epidemiologist at Brixton Health

Frequent user

5 Feb 2020, 16:03

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.

If you have any problem posting a response, please contact the moderator at post@en-net.org.

Back to top

» Post a reply