CODEWITHSUNDEEP
vbaexcelaveragestandard-error
DCZ
DCZ

Reputation: 1744

Dynamic range calculations macro

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:

  1. I managed to let every cell under the last row calculate the average.
  2. 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.
  3. 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.

Upvotes: 1

Views: 701

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 2

Related Questions