Macro for calculating stats(ie, Average) over variable range

Hi All,

I have a macro (unsuccessfully) started to help me automate calculating statistics for a variable set of data.

Sub Statistics()
Dim FinalRow, LastCell As Range

Worksheets("Section01").Select
ActiveSheet.Range("D2").Select

Selection.End(xlDown).Select 

FinalRow = ActiveCell.row + 1 

ActiveSheet.Range("D2").Select
ActiveSheet.Range(Selection.End(xlDown), Selection.End(xlToRight)).Select \
Selection.Copy

Worksheets("Stats01").Select
ActiveSheet.Range("b2").Select
ActiveSheet.Paste 'pastes it

'calc mean
Dim cc, FinalCell As Range
For Each cc In ActiveSheet.UsedRange.Columns
    Set FinalCell = ActiveSheet.Columns(cc.Column).Rows(ActiveSheet.Columns(cc.Column).Rows.Count).End(xlUp)
    If FinalCell.row > 1 Then
        FinalCell.Offset(1, 0).Value = Application.WorksheetFunction.Average(ActiveSheet.Columns(cc.Column))
    End If
Next cc
'calc median
For Each cc In ActiveSheet.UsedRange.Columns
    Set FinalCell = ActiveSheet.Columns(cc.Column).Rows(ActiveSheet.Columns(cc.Column).Rows.Count).End(xlUp)
    If FinalCell.row > 1 Then
        FinalCell.Offset(1, 0).Value = Application.WorksheetFunction.Median(ActiveSheet.Columns(cc.Column))
    End If
Next cc
'calc stdev
For Each cc In ActiveSheet.UsedRange.Columns
    Set FinalCell = ActiveSheet.Columns(cc.Column).Rows(ActiveSheet.Columns(cc.Column).Rows.Count).End(xlUp)
    If FinalCell.row > 1 Then
        FinalCell.Offset(1, 0).Value = Application.WorksheetFunction.StDev(ActiveSheet.Columns(cc.Column))
    End If
Next cc
'calc variance
For Each cc In ActiveSheet.UsedRange.Columns
    Set FinalCell = ActiveSheet.Columns(cc.Column).Rows(ActiveSheet.Columns(cc.Column).Rows.Count).End(xlUp)
    If FinalCell.row > 1 Then
        FinalCell.Offset(1, 0).Value = Application.WorksheetFunction.Var(ActiveSheet.Columns(cc.Column))
    End If
Next cc
'calc mode
For Each cc In ActiveSheet.UsedRange.Columns
    Set FinalCell = ActiveSheet.Columns(cc.Column).Rows(ActiveSheet.Columns(cc.Column).Rows.Count).End(xlUp)
    If FinalCell.row > 1 Then
        FinalCell.Offset(1, 0).Value = Application.WorksheetFunction.Mode(ActiveSheet.Columns(cc.Column)) 'what if no mode?
    End If
Next cc
     
End Sub

The problem is, this code seems unnecessarily long, and also will not work if I have labels in the rows and columns (ie, text for headers in columns), as well as the first few column's data consisting of names and text associated with the numeric data I need to calculate statistics for.

An example is below, and is to be read as a worksheet (Capital letters represent the labeled column headers).

Column  A   B   C   D   E   F
        x   t   y   5   1   7                
        y   x   z   4   9   5
        z   y   x   2   1   1
        t   z   t   9   1   3
Mean                5   3   4

Any help or insight would be greatly appreciated.

Thanks in advance.

-P

Hi,

Please attached example workbook than its more easy to solve ur query.

Sure, probably should have added to the original post. It's attached now.

I deleted the irrelevant macros because the module was pretty crowded, but left the results they produced for the starting point of the macro(s) I'm having trouble with.

My end goal is to produce the stats on the "Stats2" sub in the sheets labeled "Stats01" and "Stats02." It will ideally produce a set of separate stats for the students by section.

My work so far is pretty messy, but hopefully isn't too difficult to interpret.

Thanks

-P

Hi Par,

Please find the attached workbook and plz have review and let me know is it that u want output or not.

Thanks - Naveed.

Yep, that's it exactly. Thanks a lot, I really appreciate the help.

K thanks to u as well brother i also learn new thing equally .