Dear Sam, 

This topic was discussed some years ago on the en.net form : https://www.en-net.org/forum/question/3841 

Hopefully this might help answer your question above. 

Lucy

Dear Sam,
To automate the calculation of z-scores (Weight for Height Z-scores, WFHZ, and Weight for Age Z-scores, WFAZ) in Excel, you can use a combination of formulas and Excel features like named ranges or tables. Here's a step-by-step guide to set this up effectively:

Step 1: Prepare Your Data

1. Create Data Structure:
   • Open a new Excel sheet and set up the following columns:
     • A: Patient ID
     • B: Weight (kg)
     • C: Height (cm)
     • D: Age (months)
     • E: Weight for Height Mean (WFH Mean)
     • F: Weight for Height SD (WFH SD)
     • G: Weight for Age Mean (WFA Mean)
     • H: Weight for Age SD (WFA SD)
     • I: WFHZ (Weight for Height Z-score)
     • J: WFAZ (Weight for Age Z-score)
2. Populate the Mean and SD Columns:
   • These means and standard deviations should either come from a trusted source (e.g., WHO growth standards, CDC growth charts) or be a part of your dataset if you have a reference dataset.

Step 2: Input Your Data

• Fill in the columns A through D with your patients’ data.
• Fill in the means and standard deviations in columns E, F, G, and H. If you have consistent means and standard deviations, you can simply copy them down.

Step 3: Calculate Z-scores

For Weight for Height Z-score (WFHZ):

1. In cell I2, enter the following formula to calculate the Weight for Height Z-score:
   • This calculates the z-score using the formula (X−μ)/σ(X−μ)/σ.

excel=IFERROR((B2 - E2) / F2, "")  

For Weight for Age Z-score (WFAZ):

2. In cell J2, enter the similar formula for Weight for Age Z-score:
   • Again, this uses the same z-score formula.

excel=IFERROR((B2 - G2) / H2, "")  

Step 4: Drag to Apply the Formulas

3. Fill Down the Formulas:
   • Select cells I2 and J2 and drag down the fill handle (small square at the bottom right corner of the selected cell) to apply the formulas to the entire column for all patient entries.

Step 5: Automate Data Entry for Future Patients

To streamline data entry for ongoing patient management:

1. Use Excel Tables:
   • Select your entire data range (A1 to J1).
   • Go to "Insert" > "Table". This will allow you to add rows and automatically extend formulas.
2. Use Data Validation:
   • For Age and Height, you can set up validation to ensure that valid numbers are entered.

Step 6: Protection and Accessibility

1. Protect the Worksheet:
   • If other users will be using this sheet, consider protecting the formulas by selecting "Review" > "Protect Sheet".
2. Save and Back Up:
   • Regularly save your workbook to prevent data loss, especially with ongoing patient management.

Optional: Use Named Ranges

If you frequently reference the same means and standard deviations, consider naming those ranges. To create a named range:

1. Select the cell with the mean or standard deviation.
2. Go to "Formulas" > "Define Name" and give it a descriptive name (e.g., WFH_Mean, WFA_SD).
3. Use those names in your calculations instead of cell references, which can enhance readability.

Example:

Here’s a visible example setup in the Excel sheet:

Table

Additional Analysis

With the z-scores calculated, you can now:

• Create charts to visualize distributions and trends.
• Use conditional formatting to highlight patients with z-scores that are significantly low or high.

By following these steps, you can effectively automate the calculation of z-scores for both height and weight for age within your Excel spreadsheet, facilitating ongoing patient management and historical data analysis.