Calculate average of specific range

Question

I'm trying to calculate the average of the numbers of the third row for each category (a, b, c):

Data

For instance: the average for all 'a', the average for all 'b', the average for all 'c'.

Then, I would like to insert three rows, one for 'a', one for 'b', one for 'c', with the average values on the third column and delete the old values. It would look like this:

Sub calculate_averages()
    
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add2 Key:=Range("A2:A10") _
      , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").sort
        .SetRange Range("A2:C10")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Selection.Offset(0, 2).Select
    Set rg1 = Selection
    
    n_rows = rg1.Rows.Count
    
    For i = 1 To n_rows
        
    Next
        
    media = Application.Average(rg1)
    MsgBox (media)
               
End Sub
    
Sub selecionar()
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
End Sub

do you need a vba solution for a specific reason? because this can be easily solved with a Pivot Table or an AVERAGEIF function — Fernando J. Rivera
– Fernando J. Rivera, Commented Dec 3, 2020 at 20:20
I could fix this by making a pivot table, I know. The point is that I am trying to create an automation tool for work. The purpose of the sub was to select the first range and then offset 2 columns to the right. Your code is fine but I could not group averages by categories (a, b,c) — Carlos Sette
– Carlos Sette, Commented Dec 4, 2020 at 11:23

bhbennett3 · Accepted Answer · 2020-12-03 21:56:33Z

0

I also agree with Fernando and Michelle but I've made some changes to your first sub that may fix your issues:

Sub calculate_averages()

Dim r, rg1 As Range

ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add2 Key:=Range("A2:A10") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").sort
        .SetRange Range("A2:C10")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Set rg1 = Selection.Offset(0, 2)

For each r in rg1
    
media = Application.Average(rg1)
MsgBox (media)

Next
           
End Sub

I'm not sure the purpose of your second sub. Is your intention to run both subs consecutively? If so they should probably just be one sub. And if this is the case there is a better way of doing all this than using .Selection and .Select, relying on these methods like this is dicey stuff.

Edit: My code may also not work properly, as I'm not sure what .Selection is... I assume this code is meant to run after a user has selected several rows in one column? If not, you need to rethink some more things. But the most glaring issue was that you didn't have any commands between For and Next

answered Dec 3, 2020 at 21:56

bhbennett3

13310 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carlos Sette Over a year ago

I could fix this by making a pivot table, I know. The point is that I am trying to create an automation tool for work. The purpose of the sub was to select the first range and then offset 2 columns to the right. Your code is fine but I could not group averages by categories (a, b,c).

bhbennett3 Over a year ago

Pivot tables would be fine but so would be just basic worksheet functions. you are aware of =AVERAGEIF() ? If you want to make this into an automation, what kind of action do you anticipate the user taking to trigger it? Do you want them to select their data, then click a button? This is certainly doable using VBA but I don't understand the use case, i.e. how much of the process you want to automate.

Collectives™ on Stack Overflow

Calculate average of specific range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related