how to store dates/times in array and use WorksheetFunction.Average to find average time

BWallace15

New Member
Joined
Jun 29, 2016
Messages
1
Hello, this is my first time on mr. excel so I hope I do not mess up anything in the post.

I have to create what is basically an averaging program. The data (thousands of points) is imported from a notepad file and i need to create a userform/macro that accepts a block size and compresses the data by averaging. (Example: If the block size is 5 the macro takes the first five averages them and puts them in a cell, then takes the next 5 averages them and puts them in the cell below the first, and so on). Normally I could just input equations into excel but unfortunately there are so many data points the file can take a long time to load. I created a function that does want, but I need to do this for multiple columns and when I wrote the code to average the dates (these cells contain a timestamp consisting of a date and time) I get the "unable to get the average function of the WorksheetFunction class." To save some time I have checked that the first array is properly populating.

Here is my code:
Code:
Sub MainTest()
'TestForm.Show
Dim C As Integer
Dim Block1 As Integer
Dim Ary() As Variant
Dim AryTemp1() As Variant
'Counts the Number of dates in data
    C = Worksheets("Sheet1").Range("A1", Range("A1").End(xlDown)).Cells.Count
'Makes Ary the size of C
    ReDim Ary(C) As Variant
'Stores Dates in Ary then performs desired operation
    For i = 1 To C
        Ary(i) = Cells(i, 1)
        Worksheets("Sheet1").Cells(i, 2).Value = (Ary(i) - Ary(1)) * 24
    Next i
'Block 1 represents the number of dates to be averaged
    Block1 = 3
    w = 1
    R = 1
'AryTemp1 stores the amount of dates designated in Block1 so they can be averaged
    ReDim AryTemp1(Block1) As Variant
    For v = 1 To C Step Block1
        For g = 1 To Block1
            AryTemp1(g) = Ary(w)
            w = w + 1
        Next g
        If WorksheetFunction.Count(AryTemp) > 0 Then
             Worksheets("Sheet1").Cells(R, 4).Value = WorksheetFunction.Average(AryTemp1)
        End If
        R = R + 1
    Next v
    
End Sub

By the way the Testform.show is there because I took this code out of the userform code and put it in the sheet code so I could change the block size without the userform popping up everytime, so ignore that.

Any help would be much appreciated.

Thanks
Ben
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top