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

#### eberns

##### New Member

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
ABCDEFGHIJK
1Household IDPersonIDSexAgePerson weightPerson ID youngest childPaid care youngest childPerson ID 2nd youngest childPaid care 2nd youngest childPerson ID motherEmployed
235678101135245.67-1-1-1-199991
335678102232245.671041103299991
43567810328314.53-1-1-1-11022
53567810423256.98-1-1-1-11022
645962101224236.81022-1-199992
74596210211302.56-1-1-1-11012

<tbody>
</tbody>
Sheet1

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.

 101 2

<tbody>
</tbody>

### 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.
Hello!

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

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,
Elizabeth

Replies
5
Views
317
Replies
0
Views
443
Replies
2
Views
1K
Replies
7
Views
2K
Replies
0
Views
939

1,203,397
Messages
6,055,165
Members
444,767
Latest member
bryandaniel5

### 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 MrExcel.com.

### Which adblocker are you using?

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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