ExcelBanter - Excel- find the last filled cells in column with empty cells

Here is a user defined function that should do what you want. Once you save
the function in a module, you should be able to enter
=AvLatestThree(O:O)
to get the average of the three last non blank values in column O. You
could change the column reference given to the formula to do the same thing
in other columns.

______________________________________

Function AvLatestThree(columnToCheck As Range)
Dim topRow As Integer
Dim valCount As Integer
Dim intTotal As Integer
Dim x As Integer
Dim valArray()

colNum = columnToCheck.Column
topRow = Cells(65536, colNum).End(xlUp).Row
valCount = 0

For R = 1 To topRow
If Application.WorksheetFunction.IsNumber(Cells(R, colNum)) Then
ReDim Preserve valArray(valCount)
valArray(valCount) = Cells(R, colNum).Value
valCount = valCount + 1
End If
Next R

If UBound(valArray) 1 Then
x = UBound(valArray)
intTotal = valArray(x) + valArray(x - 1) + valArray(x - 2)
AvLatestThree = intTotal / 3
Else
AvLatestThree = "Fewer than three values to average"
End If

End Function

_____________________________________

Steve

wrote in message
oups.com...
I have tried several times from other resources for help on this
problem to no avail.

I have a sheet that data in column O is ever expanding with new data
added to the bottom of the column.
Example.
120
blank cell
blank cell
175
blank cell
blank cell
blank cell
166
blank cell
blank cell
450
blank cell
blank cell
blank cell
blank cell
620

I am looking for a formula that will average the last three cells with
data. In this case 166+450+620/3=412
Blank cell is random.

Thank you in advance for your prompt attention to this matter.

Steven R. Dekker