How to get average of a Column Based on dates

user_13714142 0
Viewed 177

I'm still a newbie at this but I am trying to build a VB Macro to provide me with the Average Bet Value per session. Basically I have a list of Dates in Cell 'AD' and also a list of Bet Values in Cell 'AE'.

I am finding a difficulty in coding VB to calculate :

  1. From Col 'AD' which contains the dates I need to loop through the dates and if there is a 10 minute difference between a date and an other, then both dates will be considered as a start and end date.

  2. Following Step number 1. I need to get the Bet values which fall between the Start and End date and get the average of that session in Col 'AF'

  3. Last but not least I need to do this process for all the dates in Col 'AD' - for every session identified we get the average bet value in col AF'

I have pasted the code on how I generated Col AD and Col AE and a snip of the data I am using.

I hope i explained myself well and would like to thank you all

Excel Sample Data

Public Sub GetDebitValue()

    RowCounter = 1

    Profile.Range("AE1").Value = "BET VALUE"
    Profile.Range("AD1").Value = "BET TIME AND DATE"
    Profile.Range("AF1").Value = "SESSION BET AVERAGE"

    'Range Col 'H2' Contained the action types called Debit which are considered as bets - if value is debit take the bet amount from Col 'C'
    If Profile.Range("H2").Value = "debit" Then
        Profile.Range("C2" + CStr(RowCounter + 1)).Value = Profile.Range("C2" + CStr(RowCounter)).Value
    'Set Date
        Profile.Range("AD2").Value = Profile.Range("I2").Value
    'Set Value
        Profile.Range("AE2").Value = Profile.Range("C2").Value
    End If


    'Always start with debit
    Do
        Select Case Profile.Range("H" + CStr(RowCounter)).Value
            Case "debit"
                Profile.Range("AE" + CStr(RowCounter + 1)).Value = Profile.Range("C" + CStr(RowCounter)).Value
                'Copy Date
                Profile.Range("AD" + CStr(RowCounter + 1)).Value = Profile.Range("I" + CStr(RowCounter)).Value
            Case Else
                'Do Nothing
        End Select

        If Profile.Range("A" + CStr(RowCounter + 1)).Value = CStr("") Then
            Exit Do
        End If
        'Call GamblingLength
        RowCounter = RowCounter + 1
    Loop Until RowCounter = 99999
End Sub
vba excel
1 Answers

I knocked this up quickly, I didn't have time to recreate your exact conditions and figure out some bits of the code but I hope you can use this generic pattern to achieve the steps:

The data starts on row FOUR here (row 3 is the headers)

enter image description here

And the code to achieve this was:

Sub avgs()
Dim i As Long ' row counter
Dim c As Long ' average divisor counter
Dim cTot As Double ' cumulative value total
With Sheet2
    c = 1 ' initiate count as 1
    cTot = .Range("B4").Value2 ' initiate cumulative total as first row of data val
    For i = 5 To .Cells(.Rows.Count, 1).End(xlUp).Row ' cycle through to end row
        If .Range("A" & i).Value2 - .Range("A" & i - 1).Value2 < 0.00694 Then
            ' if less than ten minute gap on this row then add to both avg div and tot counter 
            c = c + 1
            cTot = cTot + .Range("B" & i).Value2
        Else
            ' else set the value in column c and reset the avg div and tot counter
            .Range("C" & i - 1).Value2 = cTot / c
            c = 1
            cTot = .Range("B" & i).Value2
        End If
    Next i
End With
End Sub
user_64806580
Related