Try:


'-----------------------------------------------------------------
Private Sub Worksheet_Selectionchange(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B1:B1000" '<===Change as required


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Row = Target.Row
col = Target.Column
Range("D3") = Application.Average(Cells(Row, col), Cells(Row -
11, col))
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

Right-click on sheet tab, view code and copy/paste code above

" wrote:

I have a simple worksheet that has two columns of data, column A is
month and year, column B is a four digit number. What I'm looking to
do is get an average when a cell is selected. The average would
populate in cell D3. So, let say I click or select cell B18, the
formula should be something like =average(b7:b18) for cell D3. In
other words when a cell is select that the average counts back 12
cells up. I'm looking to get the average number for a 12 month span.
If I select cell B19, then the code should be for an average
=average(b8:b19). Make sense?

Any ideas on some vba code that would do this?