Calculating Average on variable sized datasets w/VBA
 

I am having difficulty determining the best way to calculate the averag
value of a variable length dataset w/VBA. Additionally, I would lik
to loop the average calculation across all datasets in the column an
display the calculated value.

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

0.049
0.0495
0.05
0.0504
0.0509
0.0513

0.049
0.0495
0.05
0.0505
0.0509
0.0514
0.0517
0.0521
0.0525
0.0528
0.0531
0.0535
0.0537
0.0539
0.0551

0.0489
0.0517
0.0521
0.0525
0.0528


This is an integral component of the macro I am creating, so I am ver
greatful for any help.

Thank

--
Message posted from http://www.ExcelForum.com

Calculating Average on variable sized datasets w/VBA
 

Nano

Assuming that you start in the first cell in the data range then the following will loop down the sets and output the average to right of the last entry in each set.

Tony

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

----- nano wrote: -----

I am having difficulty determining the best way to calculate the average
value of a variable length dataset w/VBA. Additionally, I would like
to loop the average calculation across all datasets in the column and
display the calculated value.

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

0.049
0.0495
0.05
0.0504
0.0509
0.0513

0.049
0.0495
0.05
0.0505
0.0509
0.0514
0.0517
0.0521
0.0525
0.0528
0.0531
0.0535
0.0537
0.0539
0.0551

0.0489
0.0517
0.0521
0.0525
0.0528


This is an integral component of the macro I am creating, so I am very
greatful for any help.

Thanks


---
Message posted from http://www.ExcelForum.com/