Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· OpenAIAi2 WildChat-4.8M (AI2)

if volume be in column e write a vba to calculate last volume to average of volume of…

  1. User

    if volume be in column e write a vba to calculate last volume to average of volume of week in column j and volume to average of volume of month in column k and volume to average of volume of past 3 month in column L

  2. Assistant

    You can use the following VBA code to achieve this:

    Sub CalculateVolumes()
        Dim ws As Worksheet
        Dim LastRow As Long
        Dim i As Long
        Dim VolumeSum_WEEK, VolumeSum_MONTH, VolumeSum_3MONTHS As Double
        Dim Counter_WEEK, Counter_MONTH, Counter_3MONTHS As Integer
        
        ' Set the worksheet you're working on
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        
        ' Find the last row with data in column E
        LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
        
        ' Initialize counters for the sums and the number of datapoints
        VolumeSum_WEEK = 0
        VolumeSum_MONTH = 0
        VolumeSum_3MONTHS = 0
    
        Counter_WEEK = 0
        Counter_MONTH = 0
        Counter_3MONTHS = 0
        
        ' Loop through the data in column E (starting from row 2)
        For i = 2 To LastRow
            VolumeSum_WEEK = VolumeSum_WEEK + ws.Cells(i, "E").Value
            VolumeSum_MONTH = VolumeSum_MONTH + ws.Cells(i, "E").Value
            VolumeSum_3MONTHS = VolumeSum_3MONTHS + ws.Cells(i, "E").Value
    
            Counter_WEEK = Counter_WEEK + 1
            Counter_MONTH = Counter_MONTH + 1
            Counter_3MONTHS = Counter_3MONTHS + 1
            
            ' Volume to average of volume of week
            If Counter_WEEK = 5 Then
                ws.Cells(i, "J").Value = VolumeSum_WEEK / 5
                Counter_WEEK = 0
                VolumeSum_WEEK = 0
            End If
            
            ' Volume to average of volume of month
            If Counter_MONTH = 20 Then
                ws.Cells(i, "K").Value = VolumeSum_MONTH / 20
                Counter_MONTH = 0
                VolumeSum_MONTH = 0
            End If
            
            ' Volume to average of volume of past 3 months
            If Counter_3MONTHS = 60 Then
                ws.Cells(i, "L").Value = VolumeSum_3MONTHS / 60
                Counter_3MONTHS = 0
                VolumeSum_3MONTHS = 0
            End If
            
        Next i
        
    End Sub
    

    This code calculates moving averages for weeks, months (assuming 20 trading days), and past 3 months (assuming 60 trading days). If your dataset includes dates, you can adjust the calculation method instead of counting trading days for a more precise calculation.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.