My colleagues at the NCCR on the move have published two useful datasets: an overview of all relevant laws, both at the national and cantonal level (this will come in handy when updating MIPEX), and an overview of all referendums on immigration and immigrant integration (this is quite a bit like my own timeline, also on Figshare). It’s nice to see these data out there.

## MIPEX as a Measure of Citizenship Models: Small Update

I have just added an additional document to the replication material for MIPEX as a Measure of Citizenship Models. The paper in the SSQ uses MIPEX data up to 2010, but the MIPEX releases 2012+ use a slightly different question order because a few questions were added and removed. (It’s this updated version we’ve used for the time series of MIPEX/immigration policy in Switzerland 1848 to 2015.) With this, replicating my MIPEX-based measure of citizenship models was no longer straightforward with the more recent MIPEX releases. There’s one important point to consider, though: with the additional questions in the latest MIPEX data, it probably makes sense to include one or two additional (relevant) questions rather than slavishly following the items used in the SSQ paper.

Ruedin, Didier. 2015. “Increasing validity by recombining existing indices: MIPEX as a measure of citizenship models.” Social Science Quarterly 96(2): 629-638. doi:10.1111/ssqu.12162

Ruedin, Didier, Camilla Alberti, and Gianni D’Amato. 2015. “Immigration and integration policy in Switzerland, 1848 to 2014”, Swiss Political Science Review 21(1): 5-22. doi:10.1111/spsr.12144

## Quickly Count Cases in Excel Using =subtotal()

Here’s a quick way to count the number of cases of subgroups in Excel. Let’s say you have an Excel sheet with information on survey respondents, and you wanted to count how many women and how many men there are. The first thing is to add a filter on the data. You can simply select the header and press `Filter`

. The second thing is to add a little code to count. I usually put this on the first row, right next to the header. In that cell, type `=SUBTOTAL(3,A2:A5)`

. Subtotal is to count things (it can also to other things like averages). The `3`

at the beginning is to count non-empty cells. The range (here `A2:A5`

) indicates what to count; it should include the entire data, so if there are 10,000 cases in the data, the range would probably be `A2:A10001`

. Press enter, and the cell shows the number of cases.

Now, obviously we didn’t need a fancy formula for just this. We could have, for example, simply have selected the first column and checked in the status bar to read `Count: 5`

, which gives us 4 cases once we take into account the header. But we wanted to know how many women there are. Now we can use the filter, click on that small arrow next to `Gender`

and choose only `F`

. Once you click OK, Excel filters accordingly. The cell with our count is updated. We could have clicked on the column to check the count, but guess what, we can copy the value the formula gives: No more typos and forgetting to subtract 1 because of the header, and fewer clicks, too. We can use multiple filters, like how many women are there called Berta?

I find this useful for a quick look; for more complicated tables, a decent statistical program will be easier to use.