indicating his/her membership in one of several possible categories. Examples of
categorical variables include gender, employment status, and legal status. Users should
consult the codebook for more information on the variables contained in the NAWSPAD.
Step 3a
Calculate Weighted Means
The mean, or average, of a variable is the sum of all values across all the observations
divided by the number of observations. This sample mean assumes that each
observation in the sample has an equal weight. However, when calculating the mean of
a variable in the NAWSPAD, users need to apply the sampling weight variable
PWTYCRD to adjust for the relative value of each farmworker in the sample, because
each farmworker contributes differently to the final mean depending on his/her sampling
weight.
The weighted mean can be calculated in Excel with a formula that uses both the
SUMPRODUCT and SUM functions. For example, if users want to calculate the mean
age of farmworkers then the SUMPRODUCT function is used to calculate the numerator
in the formula, which is the sum of the products of each farmworker’s age and his/her
sampling weight (PWTYCRD). The SUM function is used to calculate the denominator in
the formula, which is the sum of all farmworker sampling weights (PWTYCRD).
Calculate Weighted Means for All Fiscal Years
The following instructions show how to calculate the weighted mean of a continuous
variable for all fiscal years in the dataset combined in Excel. For the purpose of
illustration, we will use AGE as the variable of interest and we assume that the Excel
data set contains 500 farm workers interviewed in all fiscal years.
1. Determine the variable(s) for which you would like to calculate the weighted
mean(s). In our example, we want to calculate mean AGE.
2. Locate the variable of interest and the PWTYCRD variable in the NAWS Excel
file. For our example, the variable AGE is located in column A and the variable
PWTYCRD is located in column B (see Figure 1). There are 500 rows in our
example dataset, one for each farmworker interviewed. The first row contains
the variable names.
3. The Excel formula for calculating a weighted mean is
SUMPRODUCT(array1,array2,array3,...)/SUM(number1, [number2], [number3],
...). Using our example, the formula to calculate farmworkers’ mean age is
SUMPRODUCT(A2:A501, B2:B501)/SUM(B2:B501).
a. SUMPRODUCT(A2:A501, B2:B501) calculates the numerator, which
sums the products of each farmworker’s age (AGE) and his/her sampling
weight (PWTYCRD).