Excel VBA average last 15 non blank cells

Question

I am using the formula below to enter into a cell from VBA and it works fine until I have a formula in the cells I am counting which I leave blank until other data is entered. How can I get this formula to average the last 15 cells and ignore blank cells that contain a formula?

=AVERAGE(OFFSET(AE8,COUNTA(AE:AE)-16,0,16,1))

neuralgroove neuralgroove · Accepted Answer · 2015-11-04T14:47:37

Id write a UDF and reference it in the sheet, this displays a message if it can't find 15 numbers to average, you can adjust it take average of all the numbers it finds up to a max of 15, or something like that

Function Average15(rng As Range) 'take entire column as argument
ColumnNumber = rng.Column
rownumber = Cells(65536, ColumnNumber).End(xlUp).Row
If rownumber < 15 Then
    Average15 = "Not Enough Cells"
    Exit Function
End If
Counter = 0
While Counter < 15
    If rownumber = 1 Then 'assumes no header row(s), change this to the first row with data in it
        Average15 = "Not enough values"
        Exit Function
    End If
    If Cells(rownumber, ColumnNumber).Value <> "" Then
        Counter = Counter + 1
        Totals = Totals + Cells(rownumber, ColumnNumber).Value
    End If
    rownumber = rownumber - 1
Wend
Average15 = Totals / 15
End Function

in the sheet the formula would be

=AVERAGE15(A:A)

Excel VBA average last 15 non blank cells

1 Answers