This code does the task:
Private Sub Worksheet_Change(ByVal Target As Range) ' It is executed each time there is a change on the sheet Application.EnableEvents = False 'Disable detection of further events Dim wks As Worksheet Set wks = ActiveSheet 'Assign a variable name to the active sheet Dim months(1 To 12) As String ' Creates an array of 12 elements to store month names months(1) = "january" months(2) = "february" months(3) = "march" months(4) = "april" months(5) = "may" months(6) = "june" months(7) = "july" months(8) = "august" months(9) = "september" months(10) = "october" months(11) = "november" months(12) = "december" totalrows = wks.Cells(Rows.Count, 2).End(xlUp).Row 'Row of the last value entered If totalrows <= 12 Then 'If it is inside the defined range of months wks.Cells(totalrows + 1, 1) = months(totalrows) 'Put the next month name For j = totalrows + 2 To 13 'Cleans the rows below this wks.Cells(j, 1) = "" Next j End If dataAverage = 0 'calculate the average For i = 2 To totalrows dataAverage = dataAverage + wks.Cells(i, 2) Next i dataAverage = dataAverage / (totalrows - 1) wks.Cells(2, 5) = dataAverage 'Put the average in the designated cell Application.EnableEvents = True 'Enable the detection of events ErrorHandler: Application.EnableEvents = True 'Whenever the Sub generates an error executes this line End Sub
It works with a sheet like this:
You can customize the code to adapt it to a different position of rows /columns.
Open VBA / Macros with Alt+ F11, double click the worksheet that you want to use and paste the code on the right side.