Average of 2nd/3rd/4th/5th/6th last cells
 

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/

Average of 2nd/3rd/4th/5th/6th last cells
 

Rico,

Why not just use the AVERAGE worksheet function which takes care of blanks.
You can have one for the latest and one for the previous scores.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rico " wrote in message
...
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/

Average of 2nd/3rd/4th/5th/6th last cells
 

Thanks Bob, but I don't think it is that easy or, perhaps I haven'
explained it well enough.
Say row 3 has dates starting from column G and added to each day tha
there is a competition.
Column A has competitors names and columns b,c,d,e,f have various othe
stats such as year to date best score and number of times competito
has competed.
F3 may have the function that looks for the best 3 of the last 5 score
for that competitor. (That's the one below).
So it looks to the last score entered on the right and counts back 5
then averages the best 3 of those.
I want it to ignore the entry just made but that entry must be ther
for the rest of the calculations to work.
In other words, the 2nd last, 3rd last....6th last.
I have tried some functions, some of them fairly complex, but they see
to fall over when there are blanks.
I am sure that this can be done, but perhaps I should try a redesign o
use Access

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

Average of 2nd/3rd/4th/5th/6th last cells
 

Hi Rico

This array formula seems to work:

In F3 enter

=AVERAGE(LARGE(OFFSET(G3,0,MIN(LARGE(IF(ISNUMBER(G 3:IV3),
COLUMN(G3:IV3),0),6)-COLUMN())-1,1,SUM(LARGE(IF(ISNUMBER(G3:IV3),
COLUMN(G3:IV3),0),{2,6})*{1,-1})+1),{1,2,3}))

The formula must be entered with <Shift<Ctrl<Enter, also
if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in curly brackets { } Don't enter
these brackets yourself.

Copy F3 down with the fill handle (the little square in the lower right
corner of the cell)

When fewer than 6 dates, the formula can't be calculated (of course :-),
so a #REF! error is returned.

If you want to avoid this error message, use this variation instead:

=IF(COUNTIF(G3:IV3,"<")<6,"Can't calculate!",AVERAGE(LARGE(OFFSET(G3,0,
MIN(LARGE(IF(ISNUMBER(G3:IV3),COLUMN(G3:IV3),0),6)-COLUMN())-1,1,
SUM(LARGE(IF(ISNUMBER(G3:IV3),COLUMN(G3:IV3),0),{2 ,6})*{1,-1})+1),{1,2,3})))


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Rico " skrev i en meddelelse
...
Thanks Bob, but I don't think it is that easy or, perhaps I haven't
explained it well enough.
Say row 3 has dates starting from column G and added to each day that
there is a competition.
Column A has competitors names and columns b,c,d,e,f have various other
stats such as year to date best score and number of times competitor
has competed.
F3 may have the function that looks for the best 3 of the last 5 scores
for that competitor. (That's the one below).
So it looks to the last score entered on the right and counts back 5,
then averages the best 3 of those.
I want it to ignore the entry just made but that entry must be there
for the rest of the calculations to work.
In other words, the 2nd last, 3rd last....6th last.
I have tried some functions, some of them fairly complex, but they seem
to fall over when there are blanks.
I am sure that this can be done, but perhaps I should try a redesign or
use Access?


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

Average of 2nd/3rd/4th/5th/6th last cells
 

On Tue, 27 Apr 2004 02:46:24 -0500, Rico
wrote:

Thanks Bob, but I don't think it is that easy or, perhaps I haven't
explained it well enough.
Say row 3 has dates starting from column G and added to each day that
there is a competition.
Column A has competitors names and columns b,c,d,e,f have various other
stats such as year to date best score and number of times competitor
has competed.
F3 may have the function that looks for the best 3 of the last 5 scores
for that competitor. (That's the one below).
So it looks to the last score entered on the right and counts back 5,
then averages the best 3 of those.
I want it to ignore the entry just made but that entry must be there
for the rest of the calculations to work.
In other words, the 2nd last, 3rd last....6th last.
I have tried some functions, some of them fairly complex, but they seem
to fall over when there are blanks.
I am sure that this can be done, but perhaps I should try a redesign or
use Access?


If I understand what you are trying to do, the following **array-entered**
formula should do that:

=AVERAGE(LARGE(INDIRECT(ADDRESS(ROW(),
LARGE(ISNUMBER(G2:Z2)*
COLUMN(G2:Z2),{2,3,4,5,6}))),{1,2,3}))

To **array-enter** a formula, after typing or pasting in the formula, hold down
<ctrl<shift while hitting <enter. XL will place braces {...} around the
formula.

The above formula looks for the rightmost 6 columns in which there is data;
then with regard to the leftmost five columns of that group of six, it averages
the three highest values. It assumes that if there is no data in a cell, that
cell will be blank.

As written, it requires that there be at least six entries, otherwise you will
obtain an error message. If this is an issue, the formula can be modified.


--ron

Average of 2nd/3rd/4th/5th/6th last cells
 

Many many thanks Leo and Ron. It looks like both of those work.
Though vba is powerful, I am more comfortable with functions.
I have to learn to crawl before I walk I suppose.
Thanks again.
Richar

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

Average of 2nd/3rd/4th/5th/6th last cells
 

You're welcome, Richard.
Thanks for the feedback.

LeoH


"Rico " skrev i en meddelelse
...
Many many thanks Leo and Ron. It looks like both of those work.
Though vba is powerful, I am more comfortable with functions.
I have to learn to crawl before I walk I suppose.
Thanks again.
Richard


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