Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it will
show the highest 25 scores per player...If not played 25 games then text to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it will
show the highest 25 scores per player...If not played 25 games then text to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry

Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it
will
show the highest 25 scores per player...If not played 25 games then text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry

My appologies JE, I just remembered that yes it would work - until you
insert a row above the fomula. Time I went to bed.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it
will
show the highest 25 scores per player...If not played 25 games then text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry

With small numbers, you could do something like this to avoid the volatility:

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;2 0;21;22;23;24;25})))

(and you don't need to array enter it)

But I'm not sure I'd use this--just too much chance of a typing error.

Sandy Mann wrote:

My appologies JE, I just remembered that yes it would work - until you
insert a row above the fomula. Time I went to bed.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it
will
show the highest 25 scores per player...If not played 25 games then text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

Thanks group, but I am not achieving the desired result with suggested
formulae, unless I am not enterring correctly?
Here is an actual row (B6:AW6), of the spreadsheet with 36 scores for that
person:
12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.
What I appear to get is the result of 37, which is not correct, as the top
25 scores are to be accumulative (sum), which I test manually at 517 for top
25 scores.

My next question is exactly how to apply it to all other rows.? please.
Maybe this explains better?...not trying to be clever (novice).

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it
will
show the highest 25 scores per player...If not played 25 games then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry

I put those 36 entries in B6:AK6 (not AW6) and put this in an empty cell in that
same row.

=IF(COUNT(B6:AK6)<25,"NQ",SUM(LARGE(B6:AK6,ROW(IND IRECT("1:25")))))
(and remember to hit ctrl-shift-enter--not just enter)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And dragged down that column as far as I needed.

My first formula evaluated to 522.

Terry wrote:

Thanks group, but I am not achieving the desired result with suggested
formulae, unless I am not enterring correctly?
Here is an actual row (B6:AW6), of the spreadsheet with 36 scores for that
person:
12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.
What I appear to get is the result of 37, which is not correct, as the top
25 scores are to be accumulative (sum), which I test manually at 517 for top
25 scores.

My next question is exactly how to apply it to all other rows.? please.
Maybe this explains better?...not trying to be clever (novice).

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it
will
show the highest 25 scores per player...If not played 25 games then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

Thank you group, but I may not have described my task correctly.

Please see an actual row of 36 individual scores (B6:AW6) ranging from 5, to
37:
12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.

Now I need to find the top highest scores and sum them, if a row does not
contain at least 25 number of scores then text to be "NQ", otherwise
aggregate of top 25 scores.
The formulae suggested gave me an answer in a blank cell at the end of the
row of 37, but I did it manually and got 517 aggregate. I wonder if I am
entering formulae incorrectly, as I am unable to enter an array using
ctr,shift,enter keys...its the order at which I select these keys?

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it
will
show the highest 25 scores per player...If not played 25 games then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry

The last response didn't help?????

Terry wrote:

Thank you group, but I may not have described my task correctly.

Please see an actual row of 36 individual scores (B6:AW6) ranging from 5, to
37:
12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.

Now I need to find the top highest scores and sum them, if a row does not
contain at least 25 number of scores then text to be "NQ", otherwise
aggregate of top 25 scores.
The formulae suggested gave me an answer in a blank cell at the end of the
row of 37, but I did it manually and got 517 aggregate. I wonder if I am
entering formulae incorrectly, as I am unable to enter an array using
ctr,shift,enter keys...its the order at which I select these keys?

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each row it
will
show the highest 25 scores per player...If not played 25 games then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

Thanks Dave...still working on ya suggestion...I accidentally repeated post
due to not seing it come "thro'".
Will feedback to group thanks.
Terry
"Dave Peterson" wrote in message
...
The last response didn't help?????

Terry wrote:

Thank you group, but I may not have described my task correctly.

Please see an actual row of 36 individual scores (B6:AW6) ranging from
5, to
37:

12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.

Now I need to find the top highest scores and sum them, if a row does
not
contain at least 25 number of scores then text to be "NQ", otherwise
aggregate of top 25 scores.
The formulae suggested gave me an answer in a blank cell at the end of
the
row of 37, but I did it manually and got 517 aggregate. I wonder if I am
entering formulae incorrectly, as I am unable to enter an array using
ctr,shift,enter keys...its the order at which I select these keys?

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from
other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each
row it
will
show the highest 25 scores per player...If not played 25 games then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

OK Dave (and group).
That works ok except I failed to tell you there are some blank cells due to
not playing on those dates!!!.whilst others do play...sorry again...thats
why my illustration went to AW6...can we build in that situation in your
formula please?
In the same sheet I have conditional formatting where I place a zero in
players cells where we know they have played but not put a scorecard in,
which assists other formulae in the sheet.

Terry
"Dave Peterson" wrote in message
...
I put those 36 entries in B6:AK6 (not AW6) and put this in an empty cell
in that
same row.

=IF(COUNT(B6:AK6)<25,"NQ",SUM(LARGE(B6:AK6,ROW(IND IRECT("1:25")))))
(and remember to hit ctrl-shift-enter--not just enter)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

And dragged down that column as far as I needed.

My first formula evaluated to 522.

Terry wrote:

Thanks group, but I am not achieving the desired result with suggested
formulae, unless I am not enterring correctly?
Here is an actual row (B6:AW6), of the spreadsheet with 36 scores for
that
person:

12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.
What I appear to get is the result of 37, which is not correct, as the
top
25 scores are to be accumulative (sum), which I test manually at 517 for
top
25 scores.

My next question is exactly how to apply it to all other rows.? please.
Maybe this explains better?...not trying to be clever (novice).

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from
other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each
row it
will
show the highest 25 scores per player...If not played 25 games then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

If you change ak6 to aw6, does it work ok?

Terry wrote:

OK Dave (and group).
That works ok except I failed to tell you there are some blank cells due to
not playing on those dates!!!.whilst others do play...sorry again...thats
why my illustration went to AW6...can we build in that situation in your
formula please?
In the same sheet I have conditional formatting where I place a zero in
players cells where we know they have played but not put a scorecard in,
which assists other formulae in the sheet.

Terry
"Dave Peterson" wrote in message
...
I put those 36 entries in B6:AK6 (not AW6) and put this in an empty cell
in that
same row.

=IF(COUNT(B6:AK6)<25,"NQ",SUM(LARGE(B6:AK6,ROW(IND IRECT("1:25")))))
(and remember to hit ctrl-shift-enter--not just enter)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

And dragged down that column as far as I needed.

My first formula evaluated to 522.

Terry wrote:

Thanks group, but I am not achieving the desired result with suggested
formulae, unless I am not enterring correctly?
Here is an actual row (B6:AW6), of the spreadsheet with 36 scores for
that
person:

12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.
What I appear to get is the result of 37, which is not correct, as the
top
25 scores are to be accumulative (sum), which I test manually at 517 for
top
25 scores.

My next question is exactly how to apply it to all other rows.? please.
Maybe this explains better?...not trying to be clever (novice).

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from
other
sheets in the workbook, with much appreciated help from this NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of each
row it
will
show the highest 25 scores per player...If not played 25 games then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

--

Dave Peterson

Yes Dave...

"Dave Peterson" wrote in message
...
If you change ak6 to aw6, does it work ok?

Terry wrote:

OK Dave (and group).
That works ok except I failed to tell you there are some blank cells due
to
not playing on those dates!!!.whilst others do play...sorry
again...thats
why my illustration went to AW6...can we build in that situation in your
formula please?
In the same sheet I have conditional formatting where I place a zero in
players cells where we know they have played but not put a scorecard in,
which assists other formulae in the sheet.

Terry
"Dave Peterson" wrote in message
...
I put those 36 entries in B6:AK6 (not AW6) and put this in an empty
cell
in that
same row.

=IF(COUNT(B6:AK6)<25,"NQ",SUM(LARGE(B6:AK6,ROW(IND IRECT("1:25")))))
(and remember to hit ctrl-shift-enter--not just enter)

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

And dragged down that column as far as I needed.

My first formula evaluated to 522.

Terry wrote:

Thanks group, but I am not achieving the desired result with
suggested
formulae, unless I am not enterring correctly?
Here is an actual row (B6:AW6), of the spreadsheet with 36 scores
for
that
person:


12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.
What I appear to get is the result of 37, which is not correct, as
the
top
25 scores are to be accumulative (sum), which I test manually at 517
for
top
25 scores.

My next question is exactly how to apply it to all other rows.?
please.
Maybe this explains better?...not trying to be clever (novice).

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from
other
sheets in the workbook, with much appreciated help from this
NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of
each
row it
will
show the highest 25 scores per player...If not played 25 games
then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not
average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

--

Dave Peterson

I think that a Woohoo is in order!!

Terry wrote:

Yes Dave...

"Dave Peterson" wrote in message
...
If you change ak6 to aw6, does it work ok?

Terry wrote:

OK Dave (and group).
That works ok except I failed to tell you there are some blank cells due
to
not playing on those dates!!!.whilst others do play...sorry
again...thats
why my illustration went to AW6...can we build in that situation in your
formula please?
In the same sheet I have conditional formatting where I place a zero in
players cells where we know they have played but not put a scorecard in,
which assists other formulae in the sheet.

Terry
"Dave Peterson" wrote in message
...
I put those 36 entries in B6:AK6 (not AW6) and put this in an empty
cell
in that
same row.

=IF(COUNT(B6:AK6)<25,"NQ",SUM(LARGE(B6:AK6,ROW(IND IRECT("1:25")))))
(and remember to hit ctrl-shift-enter--not just enter)

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

And dragged down that column as far as I needed.

My first formula evaluated to 522.

Terry wrote:

Thanks group, but I am not achieving the desired result with
suggested
formulae, unless I am not enterring correctly?
Here is an actual row (B6:AW6), of the spreadsheet with 36 scores
for
that
person:


12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.
What I appear to get is the result of 37, which is not correct, as
the
top
25 scores are to be accumulative (sum), which I test manually at 517
for
top
25 scores.

My next question is exactly how to apply it to all other rows.?
please.
Maybe this explains better?...not trying to be clever (novice).

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores from
other
sheets in the workbook, with much appreciated help from this
NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of
each
row it
will
show the highest 25 scores per player...If not played 25 games
then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not
average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

WOOHOO...lol

"Dave Peterson" wrote in message
...
I think that a Woohoo is in order!!

Terry wrote:

Yes Dave...

"Dave Peterson" wrote in message
...
If you change ak6 to aw6, does it work ok?

Terry wrote:

OK Dave (and group).
That works ok except I failed to tell you there are some blank cells
due
to
not playing on those dates!!!.whilst others do play...sorry
again...thats
why my illustration went to AW6...can we build in that situation in
your
formula please?
In the same sheet I have conditional formatting where I place a zero
in
players cells where we know they have played but not put a scorecard
in,
which assists other formulae in the sheet.

Terry
"Dave Peterson" wrote in message
...
I put those 36 entries in B6:AK6 (not AW6) and put this in an
empty
cell
in that
same row.


=IF(COUNT(B6:AK6)<25,"NQ",SUM(LARGE(B6:AK6,ROW(IND IRECT("1:25")))))
(and remember to hit ctrl-shift-enter--not just enter)

This is an array formula. Hit ctrl-shift-enter instead of enter.
If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

And dragged down that column as far as I needed.

My first formula evaluated to 522.

Terry wrote:

Thanks group, but I am not achieving the desired result with
suggested
formulae, unless I am not enterring correctly?
Here is an actual row (B6:AW6), of the spreadsheet with 36
scores
for
that
person:



12,15,24,17,10,15,12,7,10,37,18,22,8,10,11,22,24,1 4,18,23,15,9,13,18,29,23,1
8,22,17,21,13,28,22,18,29,5.
What I appear to get is the result of 37, which is not correct,
as
the
top
25 scores are to be accumulative (sum), which I test manually at
517
for
top
25 scores.

My next question is exactly how to apply it to all other rows.?
please.
Maybe this explains better?...not trying to be clever (novice).

Regards
Terry

"Sandy Mann" wrote in message
...
Hi JE,

Wouldn't:
=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(1 :25))))

work just as well and stop it being volatile?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"JE McGimpsey" wrote in message
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):



=IF(COUNT(A1:A100)<25,"NQ",SUM(LARGE(A1:A100,ROW(I NDIRECT("1:25")))))

In article ,
"Terry" wrote:

Win XP Pro
MS-Office XP

I have a workbook with a spredsheet showing bowling scores
from
other
sheets in the workbook, with much appreciated help from
this
NG.

The scores are in ROWS, with up to 50 scores per season.

What I wish to do is automate where In a cell at the end of
each
row it
will
show the highest 25 scores per player...If not played 25
games
then
text
to
show "NQ" (meaning not qualified).

The highest 25 scores will be in aggregate form and not
average.

The actual scores will range from 1 to 40 each game.

The number of rows may be up to 40.

Also on this sheet are columns with date of each match.

Hope I have explained OK?

Regards

Terry



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson