Calculating average in a large dataset

Joined
May 27, 2013
Messages
2
Reaction score
0
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.​
 
Joined
May 1, 2013
Messages
22
Reaction score
0
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?
 
Last edited:
Joined
May 27, 2013
Messages
2
Reaction score
0
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top