Calculating average in a large dataset

[dolle]

I am having difficulty to calculate the average
value of a variable length dataset in VBA. In addition, I would like
to loop the average calculation across all datasets (different rows and columns) and display the calculated value in a separate sheet.

Each dataset is followed by a blank cell and then another dataset
begins. I'm assuming that I can calculate the average until a nul
value is encountered. An example dataset is below:


2
4
2
4
4
5
5
5
2.3
9
6
98
5.9
12
6.3
66
2
6
10
7
10
8
15
3
6
6
95
5
6
5
5
6
5
4.1
8
9
9
6.2
0
9
10
8


The code (below) I obtained from your website works perfectly for one column only and display the result on the same sheet.



Sub ddd()
While Not IsEmpty(ActiveCell)
starter = ActiveCell.Address
ActiveCell.End(xlDown).Select
ender = ActiveCell.Address
ActiveCell.Offset(0, 1).Value = WorksheetFunction.Average(Range(starter, ender))
ActiveCell.Offset(2, 0).Select
Wend
End Sub

As being a beginner in VBA programming, I've found it difficult to amend the above code in order to repeat the average calculation over 100 columns and to display all the results in a separate sheet. I would be very grateful for any help.​

 

[Sky Tucker]

So you might have multiple datasets in one column and you also have data in multiple columns? Please confirm and I will reply with the code for you!

EDIT: also, and this is important, is there always only one empty cell in between datasets in the same column?

 

[dolle]

Many thanks for your reply. Yes, I have multiple datasets in one column and also have data in multiple columns. Actually, my original data contain daily share prices for 100 companies over 20 years. Basically, I need to calculate the monthly averages for each company for 20 years. The difficulty is that different months have different trading days(after deleting holidays). There isn't one empty cell in between datasets in the same column in my original dataset, but I can manually insert an empty row at the end of each month for 20 years if your code requires. Many thanks in advance.