A subscription to JoVE is required to view this content. Sign in or start your free trial.
This paper presents a system of integrated spreadsheets using simple formulas to calculate nutrient and food group intakes and the contributions of food groups to nutrient intakes for analysis of population diet survey data. The system accommodates quantitative, semiquantitative, and nonquantitative food intake data and a user-supplied food composition table.
It can be challenging to calculate nutrient intakes in population diet surveys because existing nutritional analysis software is generally oriented toward analyzing intakes of individuals and may not allow users to input or easily modify the food composition data used in the analysis. These are drawbacks that are more problematic in low- and middle-income country settings. While there are numerous software-assisted dietary assessment platforms that conduct onboard nutritional analysis and are appropriate for use in large surveys, they are often similarly limited, and further restrict users to specific assessment modalities. This paper presents a multifunctional system of integrated spreadsheets for nutritional analysis of population diet surveys (ISNAPDS) that provides a solution for situations in which data have been collected but cannot be adequately analyzed with existing software. The protocol involves supplying the system with fully customizable data on food composition, food group classifications, and food intake (food intake in g/day may be entered directly or calculated based on user-supplied intake frequencies and either standard or variable serving sizes). Following data entry, the user modifies a set of simple pre-populated formulas to match them to the structure of the input data and the system applies these formulas to calculate nutrient and food group intakes, and the contributions of food groups to nutrient intakes for all members of the survey population. The flexibility of the ISNAPDS system allows it to accommodate the global diversity of foods consumed and analyze quantitative, semiquantitative, and nonquantitative food consumption data collected using prospective and retrospective assessment methods employing different reference periods and portion size estimation methods. To date, the system has been applied in published and ongoing analyses of 24 h recall, diet record, food frequency, and disaggregated household consumption data from population surveys in China, Ethiopia, India, Mongolia, Thailand, and a multi-country analysis of 10 sub-Saharan African countries.
Data on population, food, and nutrient intake are important for understanding the burden of malnutrition in populations and the relationships between diet and health, and play important roles in designing, monitoring, and evaluating evidence-based nutrition policies and programs1,2.
After data on food intake have been collected, software is used to multiply the amount of each food consumed by its nutrient composition to obtain data on nutrient intake (nutritional analysis)3, a process that used to be performed manually until the advent of mainframe computers4. There are numerous software tools that do this, but they are generally oriented toward the analysis of individuals rather than population surveys5,6. Investigators wanting to calculate nutrient intakes in large surveys may write programs using statistical software that they are not highly proficient with or resort to applying software designed for individuals to every member of the survey population and compiling the results; this is time-consuming and error-prone. Furthermore, existing nutritional analysis software may not include all the foods consumed or nutrients of interest in a particular survey or allow users to input or easily customize data on food composition, serving sizes, and food group classifications used in the analysis. While many software-assisted dietary assessment platforms exist that conduct onboard nutritional analysis and are appropriate for use in large surveys7, they are often similarly limited and further restrict users to specific assessment modalities (e.g., diet record, 24 h diet recall, or recall of the frequencies at which foods were typically consumed over a specified reference period).
These drawbacks are more problematic in low- and middle-income countries (LMICs) where local food composition, recipes, and other reference data are often largely unrepresented in existing software for dietary assessment and nutritional analysis, most of which is designed for use in high-income countries2,7,8. Investigators collecting data on population nutrient intakes may therefore use software-assisted dietary assessment tools that are poorly suited to their survey population or research questions, or resort to using purpose-built tools that may not contain onboard food composition data or arduous pen-and-paper methods; both of these require a separate solution for nutritional analysis9. Inadequacies in the existing software thus compound numerous other impediments to producing high-quality and time-relevant food and nutrient intake data needed for implementing effective nutrition strategies in LMICs2. The recently developed INDDEX24 Dietary Assessment Platform is a notable effort to address this data gap in LMICs10,11,12. INDDEX24 uses a mobile app for streamlined data collection which is linked seamlessly with the Global Food Matters Database, an extensive and growing repository of global food composition, standard recipes, and dietary reference data13. However, INDDEX24 is limited to collecting 24 h recalls, which is considered the most broadly applicable assessment method for use in population surveys, but which may not fulfill all the research objectives, particularly if a lengthy reference period is needed (in which it may be more appropriate to collect food-frequency questionnaires).
This paper presents a multifunctional system of integrated spreadsheets for nutritional analysis of population diet surveys (ISNAPDS) that provides a solution for situations in which data have been collected but existing software is not adequately suited to analyze them. The protocol involves supplying the system with fully customizable data on food composition, food group classifications, and food intake (food intake in g/day may be entered directly or calculated based on the user-supplied intake frequencies and either the standard or variable serving sizes). Following data entry, the user modifies a set of simple pre-populated formulas to match them to the structure of the input data. The system then applies these formulas to calculate nutrient and food group intakes and the contributions of food groups to nutrient intakes for all members of the survey population. The flexibility of the ISNAPDS system allows it to accommodate the global diversity of foods consumed and analyze quantitative, semiquantitative, and nonquantitative (i.e., qualitative) food consumption data collected using prospective and retrospective assessment methods employing different reference periods and portion size estimation methods (e.g., single, or repeated diet records or recalls, or food-frequency questionnaires).
Orientation to ISNAPDS, a summary of protocol steps, and explanation of formulas:
The ISNAPDS system (Supplemental File 1) is a Microsoft Excel Open XML (.xlsx) file originally developed in 2012. The version used here was developed in 2022 using Excel 365.
ISNAPDS is comprised of eight spreadsheets connected by formulas that convert input data on standard or variable serving sizes (expressed in g/serving), food composition (in units/day), food group classifications, food intake frequencies (in servings/day) into output data on food intake (in g/day, which may alternatively be supplied as input data if they are readily available), food group intake (in g/day), nutrient intake (in units/day), and the contributions of food groups to nutrient intakes (in units/day). The system is integrated in the sense that the formulas match the foods, observations, nutrients, and food groups in each sheet to their correct counterparts in the other sheets; all the sheets are contained in a single file. The system is pre-populated with example input and output data for 10 observations that demonstrate the system's functionality (in the system and the protocol, the term observation may refer to individuals or person-days such as in the case of repeated diet records or recalls).
The protocol for using ISNAPDS involves four main steps: (1) specifying whether food intake data will be supplied by the user in g/day or must be calculated based on intake frequencies and serving sizes, and (if food intake must be calculated) whether each food is associated with a standard serving size or whether serving sizes for each food vary between observations; (2) structuring and entering data on food composition, food group classifications, and either food intake or intake frequencies accompanied by standard or variable serving sizes; (3) modifying pre-populated formulas in the output spreadsheets to match the structure of the input data; and (4) propagating formulas within the output data sheets to populate the desired results. Parts of some protocol steps can be skipped depending on the specified method for populating food intake data and whether the user is interested in calculating nutrient intakes, food group intakes, or the contributions of food groups to nutrient intakes. Figure 1 is a flowchart that summarizes the protocol steps and input data required for a given method of populating food intake data and the desired output data.
Figure 1: Protocol flowchart. Summary of the protocol steps and input data required given the method of populating food intake data and the desired output data. Step 2 (structuring and entering data) is simplified to exclude steps and parts of steps that involve structuring data. Please click here to view a larger version of this figure.
For each observation, ISNAPDS calculates the food intake in g/day (if these data are not user-supplied) by multiplying the intake frequency of each food by its serving size14. Calculating the intake of each food group involves a simple summation of the intake of all foods classified as belonging to that group. The formula for calculating nutrient intake involves multiplying the intake of each food by the concentration of each nutrient in that food per 100 g, dividing by 100, and summing the results across all foods consumed3. To calculate the contribution of each food group to the intake of each nutrient, the same formula is simply applied separately for each combination of food group and nutrient.
New users are advised to review the two-page supplemental guidelines for entering and manipulating data in the ISNAPDS spreadsheets (Supplemental File 2) to prevent errors and ensure that they maintain integration between the sheets. These guidelines can also be used to troubleshoot visible errors (e.g., cells displaying #REF! or #VALUE) and errors that may be detected upon running the data integrity checks described in the representative results.
The protocol is accompanied by example results based on applying the ISNAPDS system in analysis of actual population survey data in Mongolia. The procedures followed for collecting data in this survey were in accordance with the ethical standards of the Mongolian Ministry of Health Ethical Review Board and the Harvard T.H. Chan School of Public Health Institutional Review Board (Protocol #21002). The eligible participants provided written informed consent to join the study and provide publishable data prior to enrolment and were free to withdraw from the study at any time.
1. Specifying the method for populating food intake data
2. Structuring and populating the input data spreadsheets using the pre-populated example data as a guide
3. Modifying the pre-populated formulas in the output spreadsheets to match the structure of the input data
4. Propagating the formulas within the output spreadsheets to populate the desired results
Checks for ensuring integrity of the output data
The checks below demonstrate the accuracy of the calculations in the ISNAPDS system using observation 1 of the pre-populated example data. To ensure the protocol is correctly adhered to when applying the system in analysis of actual survey data, it is recommended that users run each of these checks themselves for few different observations and output columns (errors in a given input spreadsheet may propagate additional errors in some output spreadshe...
The ISNAPDS system presented in this paper provides a convenient starting point for numerous analyses central to nutritional surveillance and epidemiology such as: estimating intake distributions of nutrients and food groups, determining the prevalence of nutrient inadequacy and excess, identifying key food sources of each nutrient, deriving food- or nutrient-based diet metrics or adherence to dietary guidelines, analyzing relationships between diet and health outcomes, and informing the design of nutrition programs. ISN...
The author has nothing to disclose.
The author would like to thank Dr. Rosalind S. Gibson, Dr. Walter C. Willett, Dr. Rebecca L. Lander, Dr. Teresa T. Fung, Theresa L. Han-Markey, Dr. Guy Crosby, Dr. Megan Deitchler, Dr. Mourad Moursi, Dr. Helena Pachón, Dr. Suzanne M. Cole, Dr. Tzy-Wen L. Gong, and Laura A. Sampson for education and guidance provided over the past decade on dietary assessment and nutritional analysis; Dr. Kelvin Gorospe for advice about translating the functionality of the ISNAPDS system into a statistical program; Dr. Sinara L. Rossato for information about DietSys; and Dr. Winnie Bell for information about INDDEX24 Dietary Assessment Platform and Global Food Matters Database. The author received support from the National Institutes of Health (T32 DK 007703).
Name | Company | Catalog Number | Comments |
Excel 365 | Microsoft Corporation | The ISNAPDS system (Supplemental File 1) is a Microsoft Excel Open XML (.xlsx) file originally developed in 2012. The published version was developed in 2022 using Excel 365. |
Request permission to reuse the text or figures of this JoVE article
Request PermissionThis article has been published
Video Coming Soon
Copyright © 2025 MyJoVE Corporation. All rights reserved