I am trying to solve a problem with a scoresheet for a sports club and
have had help in the past, but can't quite get this last bit right.

I have the competitors names in col A and then various other
calculations in the next half a dozen columns.
Then starting from say column I the date of the competition is entered
and we run down the column entering the scores for those competitors
who competed that day. Note that there will be gaps in attendance so
some functions are troublesome.

We then need to calculate the best 3 of the last 5 scores for each
competitor and I was kindly given the following macro;
Function AvgLast3_5(rRange As Range) As Double


Dim Cell As Range
Dim r As Range
Application.ScreenUpdating = False

For Each Cell In rRange.Cells
Set r = Cell.Resize(1, rRange.Cells.Count - _
(Cell.Row - rRange.Row))
If Application.Count(r) = 5 Then
AvgLast3_5 = (Int(Application.Large(r, 1)) + Int(Application.Large(r,
2)) + Int(Application.Large(r, 3))) / 3
Exit For
End If
Next Cell
Application.ScreenUpdating = True
End Function

Thanks Dave, if your reading this.
Works a treat, but what I need is for the average before todays score
is put in, so that we can then run another function comparing the
average score before today with todays score. We can then see if todays
score is better than the average up to today, and by how much.
So how do I get a function like the one above to offset to the left?
I think that is what I need. Ive played around for hours but can't get
it to work.
Any help greatly appreciated.


I


---
Message posted from http://www.ExcelForum.com/