Avatar of EverettD
EverettD
 asked on

Averaging a column of numbers with varying column length

I need to average a column of numbers in a Table (of varying sizes) that contains data taken on various tests.  The number data points of each test may vary from case to case.  Data for different tests are separated by an empty row.  The relative position of the start point is known but the number of columns to include is not known beforehand.  This macro will run automatically based on a comparison of values in a different column as the data are accumulated.

Ideas?
Microsoft ExcelVB Script

Avatar of undefined
Last Comment
EverettD

8/22/2022 - Mon
Saqib Husain

There are ways depending on the situation. You should provide a sample which closely represents your problem for an appropriate solution.
Norie

Do you want an average for each test?
ScriptAddict

Try this Sub:

It takes whatever cell is active and assumes that it is the first row in the column.  

It then looks for the bottom row in the column and gives an average of the values in the column via messagebox.



Sub Averagecolumn()

Dim AveRange As Range
Dim LastRow As Long
Dim FRow As Long
Dim FColumn As Long

FRow = ActiveCell.Row
FColumn = ActiveCell.Column
LastRow = Cells(Rows.Count, FColumn).End(xlUp).Row

Set AveRange = Range(Cells(FRow, FColumn), Cells(LastRow, FColumn))
MsgBox Application.Average(AveRange)
Set AveRange = Nothing

End Sub

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
EverettD

ASKER
Wow.  Works like a charm in my test case.  Should work just as well in the full script.

Thanks.
Everett
EverettD

ASKER
It turns out my test case is too simple.  In the real case there are data below the data of interest.  The sets of data are separated by an empty row.  

The solution above unfortunately does not recognize the empty row and goes all the way to the bottom.  

Can we get the average range to stop at the empty row?
Saqib Husain

As I said, you should post a sample showing what you want. Experts can go on guessing your scenario and may or may not hit the target.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
ScriptAddict

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
EverettD

ASKER
Hope this file explains things a bit better than I did.
My scripts now used work for data sets of uniform size.


Thanks.
Example-case-for-experts.xlsx
ScriptAddict

Does this code block do what you need?


Sub Averagecolumn()

Dim AveRange As Range
Dim LastRow As Long
Dim FRow As Long
Dim FColumn As Long
Dim A As Integer

FRow = ActiveCell.Row
FColumn = ActiveCell.Column
LastRow = Cells(Rows.Count, FColumn).End(xlUp).Row

For A = FRow To LastRow
    If Cells(A, FColumn).Value = Empty Then
        LastRow = A - 1
        GoTo LRI
    End If
Next A

LRI:
Set AveRange = Range(Cells(FRow, FColumn), Cells(LastRow, FColumn))
MsgBox Application.Average(AveRange)
Set AveRange = Nothing

End Sub

Open in new window

Saqib Husain

This macro will insert the averages wherever it finds the word "Averages" in column F

Sub blockave()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange.Columns(6).Cells
If cel = "Averages" Then
cel.Offset(, -3).Resize(, 3).FormulaR1C1 = "=average(r[2]c:r[" & cel.Offset(2, -3).End(xlDown).Row - cel.Row & "]c)"
End If
Next cel
End Sub

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
EverettD

ASKER
Tried this on a real data set and it works.
Can't say I understand it but thanks.
This was driving me nuts.