Picking the latest entries from a column
 

I have a problem I'm trying to solve for my spreadsheet. I have a column of
data which I'm trying to use the latest 20 entries and get the average of
the ten smallest values of those 20. How do I accomplish this? In my
current setup, the user will always enter the new data on the last row and
so the lastest 20 values will be the 20 rows from the bottom.

In the mean time, I've rearranged the data entry in descending order so that
the latest one is on top and I can just look at the top 20 cells.

Thanks!

Picking the latest entries from a column
 

Sub ABBB()
Dim rng As Range
Dim rng1 As Range
Dim sForm As String
Set rng = Cells(Rows.Count, 1).End(xlUp)
Set rng1 = rng.Offset(-19, 0).Resize(20, 1)
sForm = "Average(small(" & rng1.Address & _
",{1,2,3,4,5,6,7,8,9,10}))"
MsgBox Evaluate(sForm)

End Sub


--
Regards,
Tom Ogilvy



"<<<Golf4U" wrote in message
news:BSoWc.61$A8.29@edtnps89...
I have a problem I'm trying to solve for my spreadsheet. I have a column
of
data which I'm trying to use the latest 20 entries and get the average of
the ten smallest values of those 20. How do I accomplish this? In my
current setup, the user will always enter the new data on the last row and
so the lastest 20 values will be the 20 rows from the bottom.

In the mean time, I've rearranged the data entry in descending order so
that
the latest one is on top and I can just look at the top 20 cells.

Thanks!