Weighted averages are useful for a variety of purposes, such as survey research or when you want to include demographic information in your pivot tables. For example, assume you took a survey of 100 people, 75 male and 25 female. But according to census data in that geographic region you should have surveyed 50 males and 50 females. The data you have is skewed toward males.
To correct for this, you assign a weight or weighting factor to correct for the sampling error in your survey. To calculate a weight you take the expected amount and divide it by the actual amount.
In the example, the men would have a weighting factor of:
50 ÷ 75 = 0.6666
The women would have a weight of:
50 ÷25 = 2
Any calculation would calculate each man as 0.6666 and each woman as 2.
Weighted averages can also be used to apply different levels of importance to a given item. Take, for example, a survey, which has multiple questions. The responses can be rated on a scale of 1 to 5. By assigning a weight to each question based on the level of importance (the higher the number the more important), and using that weight in calculating a weighted average, you can arrive at averages that are more meaningful.
To use weighted averages, you must add a column of data to the database. This data indicates the relative weight of each corresponding value in another column. The statistical calculation for weighted averages depends on the following mathematical formula:
To use weighted averages:
Ensure that a column of data with the weighted values exists in the database.
In the Query section, select the Topic item for which weighted values are needed.
Select the item that contains the weighted values and click OK.
The item in the Request line is renamed to indicate it is a weighted value.
Go to the Pivot section and drag the Weighted item from Elements to the data layout.
You may now use all of the various data functions on the weighted values.