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

[SBF12345]

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?

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
            coll.Add Cells(i, "BT").value


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

 

[iliace]

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

Application.WorksheetFunction.Average(myArr)

Any particular reason you want a collection?

 

[Leith Ross]

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.

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
                            coll.Add Wks.Cells(r, "BT").Value
                        End If
                    Next r
                Next NRL
            Next NCL
        
        MsgBox "Äverage Is " & Application.Average(Data)
        
End Sub