You can create an array and do a manual average, try playing around
with this:
Sub LoopThruCells_Average()
Dim CategoryCell As Range, mArray() As Variant
Dim MyAverage As Double
Dim n As Integer, i As Integer
n = 1
For Each CategoryCell In Range("A1:A10") 'or whatever range
If CategoryCell.Value > 0 Then
ReDim Preserve mArray(1 To n)
mArray(n) = CategoryCell.Value
n = n + 1
End If
Next
MsgBox UBound(mArray)
For i = 1 To UBound(mArray)
MyAverage = (mArray(i) + MyAverage)
Next i
MyAverage = MyAverage / UBound(mArray)
MsgBox MyAverage
End Sub
Sandy
vilja wrote:
> Thanks Sandy. What if I need to change the data range to be averaged on the
> fly. For example:
> customer 1 meets the condition so his data will be included in the average,
> customer 2 doesn't meet the condition so his data won't be included,
> customers 5, 6 and 7 do meet the condition so their data will be included in
> the average
> etc.
> --
> vilja
>
>
> "Sandy" wrote:
>
> > Try this as a template, not sure where your data is to average so
> > you'll need to write that part maybe using
> > "Application.WorksheetFunction"
> >
> > Sub LoopThruCells()
> > Dim CategoryCell As Range
> > For Each CategoryCell In Range("A1:A1") 'or whatever range
> > If CategoryCell.Value = "somthing" Then
> >
> > '***average data code here***
> >
> > End If
> > Next
> >
> > End Sub
> >
> > Sandy
> >
> > vilja wrote:
> > > I have customer data that was collected over ten weeks. I need to be able to
> > > calculate the averages of the data for customers that fall under a particular
> > > category. Here are the steps in pseudocode:
> > >
> > > while(not end of worksheet)
> > > {
> > > Test categoryCell
> > > if categoryCell == condition
> > > average data for this customer
> > > increment to next categoryCell
> > > }
> > >
> > > So I need to know how to automate incrementing from one cell location to
> > > another whithout modifying it's contents.
> > > --
> > > vilja
> >
> >
|
Similar Threads
