RIM weighting in Excel, is it possible?

[ricknye]

Hi

I don't know if anyone can help, but I'm wondering whether it is possible to use an Excel formula or VBA routine to weight individual cases of data using multiple variables so that my overall sample is representative of the general population as a whole for variables such as gender, age, region and social grade - I obviously have target weights for each of these variables.

I am currently using SPSS Advanced Models and a rake weighting module written in Python to do this but would like to be able to eliminate SPSS from the process altogether if I could.

Has anyone succeeded in doing this in Excel? Or could anyone provide me with any pointers?

Many thanks

Rick Nye

 

[jim may]

Excel would be an ideal environment in which to compute and capture your results.
Sounds like you just need to "map out" on paper the design BEFORE jumping into either the spreadsheet or the VBA. Once you get started there are many helpful sould here who can give you the direction you need.

Happy Holidays..

 

[ricknye]

Thanks Jim, Happy Christmas to you too.

It is less a question of jumping in, than that my data is collected via web forms and extracted in a CSV format.

I effectively always want to do the same thing, which is for any given set of cases containing values for variables a-n, I need to generate a different weighting coefficient for each case such that the sum count for variables a-n is equal to pre-defined target.

e.g. the UK adult population has 53% women and 47% ,men so in any 1000 cases the weighting co-efficient per case when applied to the gender variable in each case has to produce counts which sum to 530 women and 470 men. The challenge is it not only has to do this but also has to do the same to meet target quotas on age distribution, region, social class &c at the same time.

I gather this is an iterative process and may involve using root mean squares at some point, but frankly I'm pushing the envelope of my understanding here and would very much like to be liberated from my dependence on SPSS which automates this process but is horrible to use in every other respect.

 

[jim may]

For whatever calculations you may end up setting up, first at the menu,
Insert, Name, Define..., In the top Text box type in "women" (without the quotes) in the Refers to: box at the bottom enter =.53 then click on the ADD button on the right. Same for "men" in the Refers to: enter =.47 ADD

Now in all your formulas you can use =1000*women (for example) and you will get
530
=1000*men, you will get 470

HTH

 

[ricknye]

Thanks Jim.

To weight my sample using one variable at a time is I think relatively straightforward, though yours is a very elegant way of implementing that.

My problem is more complex in that I have to come up with a specific co-efficient for each case which will not only achieve what you've just done for gender, but also for social class, region, age range &c AT THE SAME TIME, so that you don't only end up with 530 women and 470 men, but also say 330 from the North, 330 from the Midlands and 340 for the South and so on.

 

[Phil_Hearn]

Yes, it is possible in Excel. I have a working example that I can send to anyone who is interested. However, you may find specialist market research software programs are more versatile than Excel.

 

[Leon Gunning]

Yes, it is possible in Excel. I have a working example that I can send to anyone who is interested. However, you may find specialist market research software programs are more versatile than Excel.

Hi Phil,

I would like to see your excel file. I am currently building a rim for the UK to handle age gender and social grade.

Please send it to leon.gunning@gfk.com

Cheers,

Leon

 

[kvincent]

Hi Phil,

I know this post is a few months old, but I would love to take a look at your spreadsheet if you wouldn't mind sending it to kvincent4@gmail.com.

Thanks so much,
Kristen

 

[hhaley]

Hi Phil:

It would be great if you could send to me as well: hhaley@rpa.com

THANKS! Hillary

 

[JanaR]

Hi Phil,

If you still are sharing the Excel file showing RIM weighting, I would be interested as well: jana_roderick@hsra.com

Thank you,

Jana