ACS Data Users Group

I would like to share an Excel procedure that can be used in conjunction with Excel pivot table to calculate SE for ACS PUMAs data using replicate weights. This procedure works on a pivot table on the active worksheet. This procedure is written for person weight but it can be easily re-written for household weight.

(if you copy and paste procedure below to your personal macro workbook (personal.xlsb – will be created if you “record” a new macro (you may need to customize ribbon to show “developer” items) the procedure will be available anytime you open Excel)

If you have question/comment, please let me know.
Regards,
Toshihiko
Salem, OR

Sub get_se()
'Calculates SE for ACS PUMAs data using replication weights
'Procedure works on the pivot table on the active worksheet
'Created March 5, 2014 T.Murata
Dim pwgtp00 'main weight 2 dimentional array
Dim pwgtpxx '1 to 80th weight 2 dimentional array
Dim pwse() 'place to do calculation and hold final se
Dim i, j, k, x, y
'clear previous se automatically (next 4 lines to)
On Error Resume Next
ActiveSheet.Range("_get_se_").Clear
ActiveWorkbook.Names("_get_se_").Delete
On Error GoTo 0
With ActiveSheet.PivotTables(1)
'resetting current data field
For i = .DataFields.Count To 1 Step -1
.PivotFields(.DataFields(i).Name).Orientation = xlHidden
Next

'get main weight value
.PivotFields("PWGTP").Orientation = xlDataField
pwgtp00 = .DataBodyRange
x = .DataBodyRange.Row
y = .DataBodyRange.Column + .DataBodyRange.Columns.Count + 1
ReDim pwse(1 To UBound(pwgtp00, 1), 1 To UBound(pwgtp00, 2))

'clear main weight
.PivotFields("Sum of PWGTP").Orientation = xlHidden
i = 1

'cycle through all replicate weights - get sum((Xr -X)^2)
For i = 1 To 80
.PivotFields("pwgtp" & i).Orientation = xlDataField
pwgtpxx = .DataBodyRange
For j = 1 To UBound(pwgtp00, 1)
For k = 1 To UBound(pwgtp00, 2)
pwse(j, k) = pwse(j, k) + (pwgtpxx(j, k) - pwgtp00(j, k)) ^ 2
Next k
Next j
.PivotFields("Sum of PWGTP" & i).Orientation = xlHidden
Next i

'get final se for each cell - take sqrt of sum of difference squared * 4/80
For j = 1 To UBound(pwgtp00, 1)
For k = 1 To UBound(pwgtp00, 2)
pwse(j, k) = Sqr((4 / 80) * pwse(j, k))
Next k
Next j
'go back to main weight
.PivotFields("PWGTP").Orientation = xlDataField
End With
'paste the results next to the pivot table
ActiveSheet.Cells(x, y).Resize(UBound(pwgtp00, 1), UBound(pwgtp00, 2)).Select
Selection = pwse
'add name to se range so it can be cleared next run
ActiveWorkbook.Names.Add Name:="_get_se_", RefersToR1C1:="=" & ActiveSheet.Name & "!" & Selection.Address(ReferenceStyle:=xlR1C1)
End Sub

Setting aside the issue of Excel being an absolutely wrong tool for statistical analysis, this is an amazing piece of work. My reading of it, though, suggests that it uses specific conventions about how the weights are named, and where they are placed. This is a second nature for you as the developer and the regular user of the macro, but in all likelihood a new user won't be able to figure it out if their weights are elsewhere.