SSCI Excel Add-ins

Smooth, deseasonalise and analyse data

Rim Weighting

This add-in will allow you to demographically weight a sample via the rim weighting method.

What is rim weighting?

Basically rim weighting is a method of calculating a set of weights. Generally these weights are used to demographically weight a sample i.e. align the demographic profile of the sample to that of the population.

So, for example, if you have a sample with 3 demographics that you want to weight the sample against, the weights are calculated as follows:

  1. Weights are calculated for the first demographic by dividing the target number or proportion by the actual number or proportion for each split within that demographic.
  2. This step is repeated for each demographic in turn but the weights are multiplied by the weight calculated in the previous step.
  3. Once all the demographics have been weighted to, a test is performed to see whether the weights have made the sample profile match that of the population profile. If not, then steps 1 and 2 above are repeated until they do or until a certain number of iterations have been performed.

Installation

Save the file somewhere on your computer, then within Excel:

  1. Click on file
  2. Go to options
  3. Within options go to the Add-ins menu
  4. At the bottom of the form select 'Excel add-ins' from the Manage box and click 'Go...'
  5. Click on 'Browse...' on the form that appears, navigate to the place that you put the add-in and select OK.
  6. OK on every subsequent form until you are back in Excel.

You should now have either an additional tab called 'SSCI' or an extra group within the existing 'SSCI' tab.

Usage

Before you start you will need two things:

  1. The sample to weight. It will need to have 1 row per individual or household with the demographics in the columns.
  2. The targets for the demographics that you wish to weight to.

The image below gives an idea of the layout required.

Example of data used for the macro

Once these are in place you can click on the 'Rim Weight' button in the 'Weighting' group. This brings up the rim weighting form

Rim Weighting form

Demographics Range

This box should contain the range of the cells containing the demographics data. It will fill automatically with the current region of the selected cell when the rim weighing button was pressed. It should include a header as these will be used to match the demographic columns to the targets.

Targets Range

This box should contain the targets you wish to use for weighting. There should be three columns (rim type, rim cell and target) and no header.

Maximum Iterations

The number of iterations you want the procedure to run for. More complex weighting schemes will need more iterations.

In terms of setting a value, this will depend largely on how many rims you have, how small the cells are and how close the actuals are to the targets. The only way to tell for sure is to see what difference it makes to the weights when you run the program again with one more iteration. If it makes no difference to the weights then you're ok to leave it as it is. Non-convergence in this case will be down to either the rims having conflicting targets (i.e. one rim causes the weights to go up and another causes them to go down) or the weight cap bringing the weights back down (or up).

25 is generally ok for a small number of rims (5-10).

Upper Weight Cap

The maximum value the weight can take. A lower value will reduce the variation of the weights and hence the precision of the estimate.

A good starting point for this figure is to divide the actual proportions (or base sizes) by the targets for each cell and look at the largest. So, if for example, you had 20 percent males in the sample but the target was 45 percent and this was the biggest difference, then the biggest initial weight would be 0.45/0.2=>2.25. Given the way the algorithm works, it will not stay at that but it should be of that order. It will depend on the other rims.

One consequence of lowering the upper weight cap is that it will reduce the WEFF - the weighting efficiency. A higher WEFF means that you will have lower precision in your estimates i.e. it increases the standard error. However lowering the weight cap can also increase the number of iterations and also potentially lead to non-convergence.

I'd set a value that allows the procedure to converge and gives a reasonable WEFF. Generally a value of 5 or 6 is fine for proportional targets and a multiple of 5 or 6 above the total base size divided by the total number of panellists for base size targets (e.g. if there are 1000 panellists and a total base size of 4500, then set a value of (4500/1000)*5=22.5).

A WEFF above 1.5 - 1.6 is high and is an indication of poor representation within the panel.

Lower Weight Cap

The minimum value the weight can take.

I'd leave this at 0 unless the WEFF needs to be lowered. A good indication of problems with the targets or with the panel is whether all the weights drop to near zero.

Convergence Criteria

The level below which the profiles are said to match. So if the difference between the target and the sample is below this number the procedure has converged. So for example, if you wanted all the weighted figures to be within 0.01% of the target you would put 0.0001.

Convergence Type

Percentage or absolute. This describes how the differences between the target and the actual figures are checked. Percentage will potentially allow quite large differences between the target and actual figures for large targets. This is usually only the case for 'base size' target types.

Target Type

Set as Default

Clicking this button will save the current variables to be the default variables - except the demographic and target ranges.

Running

Once all the variables have been set click the OK button to run the macro

Output

Once the macro has run it will put a set of weights next to the area designated as the demographic range. Therefore some space next to the demographic range would be advisable.

The first image above shows where the weights are placed.

In addition a sheet is created that holds the results of the weighting run.

Output of macro

This shows the degree of convergence for each rim and cell, highlights any cells that have not converged and lists the input variables with a summary of the procedure run (the number of iterations and the time taken etc.). It also shows the spread of the weights.

Troubleshooting

Problem - The weights are very small

Potential causes:

Changes

v1.1.0

v1.1.1