Auto select highest 25 scores.

[Terry]

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

 

[JE McGimpsey]

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

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Dave Peterson]

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;20;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.

 

[Terry]

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,14,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

 

[Dave Peterson]

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(INDIRECT("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]

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,14,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

 

[Dave Peterson]

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

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,14,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

 

[Terry]

Thanks Dave...still working on ya suggestion...I accidentally repeated post
due to not seing it come "thro'".
Will feedback to group thanks.
Terry

 

[Terry]

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]

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

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

 

[Terry]

Yes Dave...

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

 

[Dave Peterson]

I think that a Woohoo is in order!!

Yes Dave...

 

[Terry]

WOOHOO...lol

I think that a Woohoo is in order!!