Dynamic range calculations macro

Question

I have an Excel document consisting of 12 sheets.
Every sheet contains a lot of data, ranging from column A to X and with a variable range in rows.
I'm trying to calculate the mean and the Standard error of every column, for every sheet. Preferably with an output on a summary sheet.

My thinking process:

I managed to let every cell under the last row calculate the average.
When I tried to do the same with the standard error, the problem is that the average calculated in step 1) was included in the calculations.
After all, it seems more convenient to display the results of these calculations in a separate 'summary' tab.

Here's the code I tried, which works for the averages to be produced right below the last value in the columns.

Sub ColumnAverageFormula()

For i = 3 To 24
    Columns(Columns(i).Address).Select
    Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Formula = "=Average(" & Cells(1, ActiveCell.Column).Address(0, 0) & ":" & Cells(1, ActiveCell.Column).End(xlDown).Address(0, 0) & ")"
    Next i
For j = 3 To 24
    Columns(Columns(j).Address).Select
    Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Formula = "=stdev.p(" & Cells(1, ActiveCell.Column).Address(0, 0) & ":" & Cells(1, ActiveCell.Column).End(xlDown).Address(0, 0) & ")"
    Next j
End Sub

Tl;dr: I'd like to write code that calculates the average and the standard error of every column of every sheet in my Excel file, and the results should be produced on a 'summary' sheet.

Can you give a quick example of what that summary sheet would look like? Would it just have a bunch of numbers, or some reference to which column the formula applied to? — Hambone
You will save a lot of hassle if your data are in proper Table. — PatricK
Hi all, thanks for your quick responses. Here's an example file. I've given the columns a header now, it can be removed if it makes it harder. <a href="filedropper.com/example_8">file</a> Thanks in advance! — DCZ

Unknown Unknown · Accepted Answer · 2016-02-01T04:13:44

The formula you were inserting referenced a range starting at row 1. Typically a data set like you have described has some sort of text based column header label in the first row. If this was the case, you would want to start at row 2, not row 1. The following starts at row 1.

Sub ColumnAverageFormula()
    Dim c As Long, sr As Long

    With Worksheets("Sheet1")
        sr = .Cells(Rows.Count, 3).End(xlUp).Row
        For c = 3 To 24
            .Cells(sr + 1, c).Formula = "=average(" & .Range(.Cells(1, c), .Cells(sr, c)).Address(0, 0) & ")"
            .Cells(sr + 2, c).Formula = "=stdev.p(" & .Range(.Cells(1, c), .Cells(sr, c)).Address(0, 0) & ")"
        Next c
    End With
End Sub


'as an alternate, you might consider putting all of the formulas in at once
Sub ColumnAverageFormula()
    Dim c As Long, sr As Long

    With Worksheets("Sheet1")
        sr = .Cells(Rows.Count, 3).End(xlUp).Row
        .Cells(sr + 1, 3).Resize(1, 22).Formula = "=average(" & Range(.Cells(1, 3), .Cells(sr, 3)).Address(0, 0) & ")"
        .Cells(sr + 2, 3).Resize(1, 22).Formula = "=stdev.p(" & Range(.Cells(1, 3), .Cells(sr, 3)).Address(0, 0) & ")"
    End With
End Sub

If you want to start at row 2, change .Range(.Cells(1, c), ... to .Range(.Cells(2, c), ....

Dynamic range calculations macro

1 Answers