average part of array

Thanks, I think I didn't explain my problem very well. I want to take the average of the values in column n between rows x1 and x2. So my problem is how do you specify the range within an array to apply a function to?

Or, more specifically... I have written some code to achieve using a loop between x1 and x2 and adding the next value of and then dividing by the number. It works ok for the most part. But I have just discovered that the column contains some "-" values (I guess where a reading couldn't be taken). My code falls over on this, but I've noticed that the worksheetfunction.average copes fine - hence my initial question.

Ok, I've created an example of what I'm trying to do. It's a little limited as I didn't want to swamp you with loads of unnecessary code.

My basic problem is how to deal with entries like the one seen in D18, which currently cause the algorithm to crash.

Excel's in-built average function handles values such as these, so I was trying to work out how to use a section of my array in that function.

However, methods of dealing with non-numeric data would also be appreciated.

Please understand that this is a simplified version, so not using an array is not really an option because of how everything has been set up - and I can't start from scratch...again!!

Many thanks for your time

Thanks for your response.

But, if you assume that the array is already in existence, large, and filled before the averages are taken - as opposed to the array being filled from the worksheet as you have done in your solution.

So the array exists, is not related to the contents of the worksheet and I want to calculate the averages of parts of it. Is that possible?

Many thanks

Does VBA not give the arrays that kind of functionality then?

The problem with the loop, as I have it, is that is crashes when it comes across a non-numeric value. I don't know how to catch that.

I thought about trying to filter all the rows with non-numeric values, but the other columns in the row still have good information I don't want to loose.

Thanks for all your help. I now have a working solution. I doubt it is the best way, but it works!

Option Explicit

Sub main()
    Dim u As Double
    Dim v As Double
    Dim w As Double
    Dim gStart As Long
    Dim gEnd As Long
    Dim av() As Variant
    Dim lRows As Long
    Dim c As Long
    Dim lenU As Long
    Dim lenV As Long
    Dim lenW As Long
    
    With Worksheets("Sheet1")
        'Find last used row
        lRows = .Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
        
        'Fill array
        av = .Range("A2:D" & lRows).Value
    End With
    'Use these value of gStart and gEnd as an example
    gStart = 12
    gEnd = 39
    
    'Initialise u, v and w
    u = 0
    v = 0
    w = 0
    
    lenU = 0
    lenV = 0
    lenW = 0
    
    'Sum between gStart and gEnd
    For c = gStart To gEnd
        If IsNumeric(av(c, 2)) Then
            u = u + av(c, 2)
            lenU = lenU + 1
        End If
        If IsNumeric(av(c, 3)) Then
            v = v + av(c, 3)
            lenV = lenV + 1
        End If
        If IsNumeric(av(c, 4)) Then
            w = w + av(c, 4)
            lenW = lenW + 1
        End If
    Next c
    
    'Find average values
    u = u / lenU
    v = v / lenV
    w = w / lenW
    
    'Display results in message box
    MsgBox ("u = " & u & ", v = " & v & ", w = " & w)

End Sub

Thanks again for your help

The workbook I uploaded in an example trying to illustrate the problem I am having, in a somewhat simplified manner.

In the actual macro I am writing the worksheet has over 20000 rows, which has been saved to an array to quicken analysis. I am looping through this array searching for particular conditions, which when met mean I then take averages of a small section of the array. In fact the result is about 300 averages are taken, for 6 variables.

Also whilst all the variables I am averaging in exist in the array, they aren't all present on the worksheet.

The facts that reading and writing to the worksheet slows everything down (I tried this approach in my first attempt at building a macro for this application and could go and make a cup of tea in the time it took to run!) and the fact that not all variables exist on the worksheet meant that I wanted to do it all via the array.

However, when I started down this path I hadn't realised that the functionality of arrays in VBA is so limited.