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

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:

- 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.
- This step is repeated for each demographic in turn but the weights are multiplied by the weight calculated in the previous step.
- 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.

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

- Click on file
- Go to options
- Within options go to the Add-ins menu
- At the bottom of the form select 'Excel add-ins' from the Manage box and click 'Go...'
- Click on 'Browse...' on the form that appears, navigate to the place that you put the add-in and select OK.
- 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.

Before you start you will need two things:

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

The image below gives an idea of the layout required.

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

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.

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.

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

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.

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.

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.

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.

- Proportion - Proportional targets range from 0 to 1 and are the proportion of the demographic cell within the rim e.g. 0.5 males in the gender split. A check is made to ensure all the rims add to 1.
- Base Size - Base size targets are the number of the sample within that demographic cell. So for a sample representing the UK population the base size target for males would be roughly 30,000,000. Generally you'd scale the weights down to give more workable numbers i.e. divide by a thousand. No check is done on base size targets to ensure that they sum to the same amount.

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

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

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.

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.

Problem - The weights are very small

Potential causes:

- A target exists but no sample exists for that cell.
- The targets don't add up to the same as the other rims - this is only really a problem for base size targets.
- One or more cells have targets that are impossible to reach dragging down the average weight.

v1.1.0

- changed report sheet to add more detail, and highlight non-converging cells
- added ability to save input values as default values
- fixed a bug in the naming of the sheet when the sheet name is over 22 characters

v1.1.1

- fixed a bug when testing that the target existed when the cell value did not equal the cell text