Is learning VBA my best route to run statistics from U.S. census data?


New Member
May 20, 2015
I am doing a research project using downloadable U.S. Census data.

So far I’ve been able to rely primarily on Excel. I am about to start a more complicated analysis, concerning childcare options used by parents. Census has suggested I need statistical software, maybe to learn R, which I understand has a pretty steep learning curve.

I want to describe what I need below and ask advice as to whether I can accomplish it with Excel formulas, whether learning VBA would be a good route to go here. I’ve got no experience with VBA either at this point.

Here is a much simplified spreadsheet showing data for two households:

Excel 2007
1Household IDPersonIDSexAgePerson weightPerson ID youngest childPaid care youngest childPerson ID 2nd youngest childPaid care 2nd youngest childPerson ID motherEmployed


I am interested in percentage of children in various childcare arrangements. The census associates the data as to childcare with a parent’s record (at this point it appears to me that in a two-parent household they associate the childcare data with the mother’s record, not with both mother and father, so as to avoid duplication). But I need to tag onto the parent’s record certain data about the children, specifically the “person weight” associated with each child.

I did something similar earlier that was much easier, with married couples where I had one row for each spouse. Since there were always two rows for each couple, I could easily tag information, for example, about husband’s employment status onto the wife’s row.

But here the number of rows for each family is variable. Determining which data to tag onto the parent’s row will require matching up the household ID, and also looking at the numbers indicating relationships so that each child's person weight is associated with the correct childcare data.

Aside from the fact that the real data could obviously include more than two children, it will also involve far more variables – yes or no on a whole list of specific childcare options. I may also want to tag information about the husband onto the wife’s record.

Would much appreciate advice about what direction to go in software-wise.



Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

As you mention R is a very good program to handle these kinds of problems. I learned this myself during my Master thesis (VaR modelling, Finance) and was very happy with the scripts I had produced in the end. As this seems like a simple multiple-regression model I could also suggest, stata, eViews or SPSS which are both easy to use. However, the thing about R is that its an Open source program, and you thereby have more freedom, and its free to download.

You can also perform simple regressions using Excel.
Head over to the data tab, then look at the analysis tools, should be located at the same place as the solver. (If you do not see it look here )
If you press "Data Analysis" you can choose Regression.

Although VBA could solve this for you, its about preference, I would most likely use R for this..

Hope some of this might help you, and good luck :)
Upvote 0
Thank you for the input, Sturla.

Because I am not affiliated with any institution, I would lean more towards R, which as you say is open-source, than towards purchasing a license for a proprietary program like Stata or SPSS.

In the meantime I am making some progress with Excel alone, so not yet undertaking to learn either VBA or R. If it comes to that, I will definitely take your comments into consideration.

Thanks again,
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back