Integrated Spreadsheets for Nutritional Analysis of Population Diet Surveys

Summary

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.

Abstract

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.

Introduction

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 programs^1,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)³, a process that used to be performed manually until the advent of mainframe computers⁴. There are numerous software tools that do this, but they are generally oriented toward the analysis of individuals rather than population surveys^5,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 surveys⁷, 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 countries^2,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 analysis⁹. 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 LMICs². The recently developed INDDEX24 Dietary Assessment Platform is a notable effort to address this data gap in LMICs^10,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 data¹³. 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 size¹⁴. 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 consumed³. 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.

Protocol

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

1. If food intake data are readily available in g/day, skip to step 2.
2. If food intake in g/day must be calculated based on intake frequencies and serving sizes, select cell B1 of the Food Intake Method spreadsheet and use the dropdown menu to specify whether each food is associated with a standard serving size (the default option) or whether serving sizes for each food vary between observations (variable serving sizes).
   NOTE: If food intake must be calculated but serving sizes are not readily available (e.g., in analysis of data collected using a nonquantitative food-frequency questionnaire), consider conducting a separate analysis of quantitative survey data to derive standard or variable serving sizes for different demographic strata^15,16,17,18.

2. Structuring and populating the input data spreadsheets using the pre-populated example data as a guide

1. In column A of the Food Composition spreadsheet, enter a list of all the unique foods reported to be consumed in the survey data (subsequently referred to as the food list). In the Food Intake sheet, enter the food list in row 1 and a list of all the unique observations in the survey data (subsequently referred to as the observation list) in column A.
   1. Ensure that the order and length of the food list in row 1 and the observation list in column A of any other sheet in which data is entered are consistent with the order and length of these lists in the Food Composition and Food Intake sheets.
2. Populate food intake in g/day by entering the necessary input data depending on the method specified in step 1.
   1. If food intake data are readily available in g/day, populate the Food Intake sheet for all foods and observations in the data and skip to step 2.3.
   2. If food intake must be calculated based on intake frequencies (expressed in servings/day) and either standard or variable serving sizes (in g/serving), in the Intake Frequencies sheet, enter the food list and the observation list and populate the sheet for all foods and observations in the data.
   3. If calculating food intake using standard serving sizes, enter these data in column C of the Food Composition sheet. If using variable serving sizes, in the Variable Serving Sizes sheet, enter the food list and the observation list and populate the sheet for all foods and observations in the data.
3. If interested in calculating food group intakes or the contribution of food groups to nutrient intakes, enter food groups in column B of the Food Composition sheet. In the Food Group Intake sheet, enter a list of all the unique food groups represented in the data (subsequently referred to as the food group list) in row 1 and the observation list in column A.
   1. Ensure that the length of the food group list and the name of each food group are consistent between column B of the Food Composition sheet and row 1 of the Food Group Intake sheet (and if calculating the contribution of food groups to nutrient intakes, row 1 of the Nutrient Sources sheet). Food groups are not case-sensitive and may be entered in any order.
4. If interested in calculating nutrient intakes or the contribution of food groups to nutrient intakes, in the Food Composition sheet, enter the list of all the nutrients to be analyzed in row 1 (subsequently referred to as the nutrient list) and populate the sheet with food composition data (expressed in unit/100 g). In the Nutrient Intake sheet, enter the nutrient list in row 1 and the observation list in column A. Summary guidance and online resources on how to identify, obtain, compile, match, and assess the quality of food composition data is provided in Supplemental File 3.
   NOTE: Despite its name, the nutrient list can include ingredients, foods, or food groups (e.g., Flour (g), Bread (g), or Refined grains (g)) and the data populated in the Food Composition sheet can therefore include ingredient, food, or food group concentrations (expressed in g/100 g) obtained from data on recipes or equivalency factors. This enables an additional or alternative approach for calculating food or food group intakes. When using this approach, foods or food groups are treated as if they were nutrients and their intakes are populated in the Nutrient Intake sheet. This approach provides a solution for situations in which there is a need to calculate intakes of specific ingredients or foods of interest (such as fortification vehicles) that are not included in the food list, or when analyzing data collected using a dish-based dietary assessment method and there is a need to assign food groups at the level of ingredients or foods. 
   1. Ensure that the order and length of the nutrient list are consistent between row 1 of the Food Composition sheet and row 1 of the Nutrient Intake sheet (and if calculating the contribution of food groups to nutrient intakes, row 1 of the Nutrient Sources sheet).
5. If interested in calculating the contribution of food groups to nutrient intakes: in the Nutrient Sources spreadsheet, enter the nutrient list in row 1. In column A, enter the observation list # times by appending the list with copies of itself in a vertical fashion, where # is the number of food groups in the food group list. Populate every cell in column B with a different food group for each stack of observations.

3. Modifying the pre-populated formulas in the output spreadsheets to match the structure of the input data

1. If interested in calculating food group intakes or the contribution of food groups to nutrient intakes: in the formula in cell B2 of the Food Group Intake spreadsheet, {=SUM(('Food Composition'!$B$2:$B$21=B$1)*TRANSPOSE('Food Intake'!$B2:$U2))}, change $B$21 to $B$#, where # is the bottom row of the data entered in the Food Composition spreadsheet and change $U2 to $*2, where * is the rightmost column of the data entered in the Food Intake sheet. Enter the modified formula.
2. If interested in calculating nutrient intakes or the contribution of food groups to nutrient intakes: in the formula in cell B2 of the Nutrient Intake spreadsheet, {=SUM(('Food Intake'!$B2:$U2)*TRANSPOSE('Food Composition'!D$2:D$21))/100}, change $U2 to $#2, where # is the rightmost column of the data entered in the Food Intake spreadsheet and change $D$21 to $D$*, where * is the bottom row of the data entered in the Food Composition spreadsheet. Enter the modified formula.
3. If interested in calculating the contribution of food groups to nutrient intakes: in the formula in cell C2 of the Nutrient Sources spreadsheet, {=SUM(INDEX('Food Intake'!$B$1$U$11,ROW()-ROUNDDOWN((ROW()-2)/(10),0)*(10),0)*(TRANSPOSE
   ('Food Composition'!D$2:D$21*($B2='Food Composition'!$B$2:$B$21))))/100};, change $U$11 to $#11, where # is the rightmost column of the data entered in the Food Intake spreadsheet; change $D$21 and $B$21 to $D$* and $B$*, respectively, where * is the bottom row of the data entered in the Food Composition spreadsheet; and change both instances of (10) to (!), where ! is the length of the observation list. Enter the modified formula.
   NOTE: If using a software version prior to 2021, enter formulas as array formulas using CTRL + SHIFT + ENTER/RETURN (refer to Supplemental File 2 for guidance).

4. Propagating the formulas within the output spreadsheets to populate the desired results

1. If food intake must be calculated based on intake frequencies and either standard or variable serving sizes: in the Food Intake spreadsheet, select cell B2 and copy and paste or drag it rightward and downward until every observation in the data is populated with intake of every food in g/day.
2. If interested in calculating food group intakes or the contribution of food groups to nutrient intakes: in the Food Group Intake spreadsheet, select cell B2 and copy and paste or drag it rightward and downward until every observation in the data is populated with intake of every food group in g/day.
3. If interested in calculating nutrient intakes or the contribution of food groups to nutrient intakes: in the Nutrient Intake spreadsheet, select cell B2 and copy and paste or drag it rightward and downward until every observation in the data is populated with the intake of every nutrient group in units/day.
4. If interested in calculating the contribution of food groups to nutrient intakes: in the Nutrient Sources spreadsheet, select cell C2 and copy and paste or drag it rightward and downward until every observation in the data is populated with the contribution of every food group to every nutrient in units/day.

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

Discussion

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

Materials

List of materials used in this article

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.