# Finding the mean value of a collection...or array?

#### SBF12345

##### Well-known Member
Greetings,

I am adding integer values to a collection. After adding all the values meeting the add criteria I would like to take the average value of the integer and output it to a sheet.

I am unsure of how to calculate the mean value of the contents in a collection.

Would this be easier to do with an array?

Code:
``````Dim a As IntegerDim b As Integer
Dim NCL As Integer
Dim NRL As Integer
Rng1 As Range
Rng2 As Range
LastRow As Integer
coll As New Collection

With ActiveWorkbooks.ActiveSheet

Rng1 = .Range("BY11", "CR11")
Rng2 = .Range("BX12", "BX21")

For Each a In Rng1
NCL = a
For Each b In Rng2
NRL = b

LastRow = Cells(Rows.Count, "BE").End(xlUp).Row

For i = 2 To LastRow

If Cells(i, "BS") < Abs(NCL) And Cells(i, "BV") > NRL Then

Next i

'#################I would like to define a variable as the average value of the items included in the collection here``````

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### iliace

##### Well-known Member
I think an array would be easier. An array is more lightweight, and with an array, you can just say:

Code:
``Application.WorksheetFunction.Average(myArr)``

Any particular reason you want a collection?

#### Leith Ross

##### Well-known Member
Hello SBF12345,

Here is a revised version of your macro. You probably do not need a Collection since you are using only numbers. The Collection object saves item and key pairs. This is useful when you need to retrieve and object by name. For example, worksheets are a collection.

This version saves the comparisons in a scalar array called Data. The Application function Average is called and a message box displays the result. You can use any cell you like to save the result to.

Code:
``````Sub ShowAverage()

Dim cnt     As Long
Dim Data()  As Variant
Dim NCL     As Variant
Dim NRL     As Variant
Dim r       As Long
Dim Rng1    As Variant
Dim Rng2    As Variant
Dim LastRow As Integer
Dim Wks     As Worksheet

Set Wks = ActiveWorkbook.ActiveSheet

LastRow = Wks.Cells(Rows.Count, "BE").End(xlUp).Row

ReDim Data(1 To 1)

Rng1 = Wks.Range("BY11", "CR11").Value
Rng2 = Wks.Range("BX12", "BX21").Value

For Each NCL In Rng1
For Each NRL In Rng2
For r = 2 To LastRow
If Wks.Cells(r, "BS") < Abs(NCL) And Wks.Cells(r, "BV") > NRL Then
cnt = cnt + 1
ReDim Preserve Data(1 To cnt)
Data(cnt) = Wks.Cells(r, "BT").Value
End If
Next r
Next NRL
Next NCL

MsgBox "Äverage Is " & Application.Average(Data)

End Sub``````

• iliace

Replies
2
Views
85
Replies
6
Views
92
Replies
1
Views
51
Replies
5
Views
189
Replies
14
Views
614