Best 25 scores..help please

[Terry]

Hi group

Win XP
MS Office 2003

I use a spreadsheet to record players bowls drives scores, and wish to flag
up the best 25 scores for each player, from the row of scores.
I.E.
A4(name), B19:AW12(row of scores), AX4(player's total played games)..
The individual scores will range from zero to around 40 each drive.

TIA

Terry

 

[Tyro]

What do you mean by "flag"? Format the cells in a certain color? Or what?

Tyro

 

[DKS]

If by "flag up best 25 scores", you mean put a special formatting in the cell
(cell color or font color) and if you can live with the same formatting in
the cells then I would suggest to use a combination of

Conditional Formatting with Formula
and in the formula play with the RANK function.

Rank function usage is as follows
RANK(number,ref,order)

Number is the number whose rank you want to find.

Ref is an array of, or a reference to, a list of numbers. Nonnumeric
values in ref are ignored.

Order is a number specifying how to rank number.

If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were
a list sorted in descending order.
If order is any nonzero value, Microsoft Excel ranks number as if ref were a
list sorted in ascending order.



Thus you can use RANK (....) <= 25 as the formula in the conditional
formatting. That should do the trick.

 

[Terry]

Sorry Tyro....a single cell showing a players highest 25 scores(combined),
will be something like 350.
Terry

 

[Terry]

Sorry to you too DKS...see my reply to Tyro's post.

Terry

 

[Terry]

One new cell summing the BEST 25 scores.(some players will not have played
25 drives, so they will not come into the equation).

Terry

 

[Sandy Mann]

=IF(COUNT(H1:H150)<25,"Not Qualified",SUM(LARGE(H1:H50+0,ROW(1:25))))

Entered as an array formula by holding down the Ctrl and Shift keys while
you press Enter.

--
HTH

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

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

 

[Terry]

Thank you Sandy.
#NUM! errors.
The last part of your formula is (1:25) for my first row, then it goes:
2:26(row 2), 3:27(row 3) and repeats this pattern, when it gets to the #NUM!
error that cell shows 45:49(row 45).
I must be entering wrong, but I did enter first cell as an array and copied
cells down??

Terry

 

[Sandy Mann]

mmmm.....

I gor #NUM! errors when I had blank cells so I added the +0 but I don't??

I didn't realise that you would be dragging the formula down, I thought that
you just wanted the sum of the largest 25 scores. Try making the ranges
absolute:

=IF(COUNT($H$1:$H$150)<25,"Not
Qualified",SUM(LARGE($H$1:$H$50,ROW($A$1:$A$25))))

Still array entering it.

--
HTH

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

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

 

[Sandy Mann]

Terry,

How is your data laid out. The absolute formula I gave you just repeats the
same calculation. If the data is in Rows then change the formula to:

=IF(COUNT(A4:AX4)<25,"Not Qualified",SUM(LARGE(A4:AX4,ROW($A$1:$A$25))))

Post back if you data is not otherwise.
--
HTH

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

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

 

[Terry]

I have not explained my "case" very well, so I will try in more detail:
The bowls drives cover about 7 months (twice a week).

At season end there is a "play off" with the top 4 highest (combined) scores
from the spreadsheet, currently the highest aggregate is the one taken into
account, but committee wish to have it as outlined below.

To qualify for the play off's they have to play a minimum of 25 drives.
Once the 25 drives are reached, ONLY their highest 25 drive scores will
count and summed after that.

My spreadsheet has all NAMES in the leftmost column(A4:A50).
Their scores go in rows (B4:AW50), with the days heading shown in rows
(B3:AW3)
Their total drives played are in shown in Columns (AX4:AX50).

Now I need a formula/function to look at each players drives
total(AX4:AX50),to see if they have reached the qualifying 25 drives
played....if not then "unqualified".
Now if they have reached 25 or more drives, then find the highest 25 drive
scores and sum them.
I hope then to RANK them by highest to lowest.

I do hope I have explained better (novice really)...I am not obtaining the
result hoped for with suggested formulae so far, but it may be me.?

Terry

 

[Sandy Mann]

Hi Terry,

With the data laid out as you describe:

AX4:
=IF(COUNT(B4:AW4),COUNT(B4:AW4),"")
and drag down on the fill handle.

AY4:
=IF(AND(ISNUMBER(AX4),AX4>25),SUM(LARGE(A4:AW4,ROW($A$1:$A$25))),IF(ISNUMBER(AX4),"Unqualified",""))
Array entered with Ctrl + Shift + Enter and then dragged down on the fill
handle.

=IF(ISNUMBER(AY4),RANK(AY4,$AY$4:$AY$50),"")
and Copy down using the fill handle.


--
HTH

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

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

 

[Terry]

Thank you Sandy

That appears great.....now you have the correct info from me..LOL
I amin the process of "proofing" now.......will come back to group if any
different.

Just one question Sandy...what part does ($A$1:$A$25))) play in AY4 please?

Regards

Terry

 

[Sandy Mann]

If you highlight just the ROW($A$1:$A$25) part and then press the function
key F9 to calculate it you will seel that if evaluates to:

1; 2; 3;........;24;25

This is used in the LARGE part of the formula, as the 2nd argument, first as
1, the then 3 and so on This is why it has to be an array formula.

Be sure that you press the 'X' in the formula bar or the Esc key to get out
of that mode otherwse you will hard code the 1;2;3 etc. into the formula

--
HTH

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

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

 

[Terry]

OK Sandy.

On tesing a players scores manually, the result does not match your
formulae....
This player has 29 drive scoresignor green highlight...high score that
day)
19 28 15 17 32 27 17 18 10 20 11 17 22 12 25 20 27 15 18
20 16 17 20 17 16 10 19 19 24


Total above is 29 drives and total of 548
To make just 25 highest scores I have removed a 15, 12, 11, and a 10 (total
48).do you agree?
That should leave 500 for 25 drives, but when I used your formulae the
result was 505.....anything to do with blank cells(not played in)??

I hope I have explained OK?

Terry

 

[Sandy Mann]

No I get what you expect:

19 28 15 17 32 27 17 18 10 20 11 17 22 12 25 20 27 15 18
20 16 17 20 17 16 10 19 19 24


Total above is 29 drives and total of 548

Yes and I find that my formula returns 505, the sum of everything except 10,
10,11 & 12

To make just 25 highest scores I have removed a 15, 12, 11, and a 10

The 10, 11 12 were not being counted anyway so we are actually only removing
the 15 and including the 10 in its place and I find that the formula returns
500 as expected.

result was 505.....anything to do with blank cells(not played in)??

It sound as though it you have not actually removed the values that you
think that you have.


--
HTH

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

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

 

[Terry]

You are correct Sandy....bed time for me..LOL
I missed the other number 10 score.
BTW I mentioned green highlight, but did not show up on your email!!.
Appreciate your patience and understanding also.

Terry

 

[Sandy Mann]

Just glad that you got it sorted out, sweet dreams <g>

--

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

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

 

[Sandy Mann]

Come on chaps, where are all my formula checkers - I really need them!

I can just hear it now.....

"I think that Sandy meant: =IF(AND(ISNUMBER(AX4),AX4>=25) not >25
otherwise it will return "Unqualified" for 25 scores"

Which is what I was thinking if not what I said.

To Terry:

My apologies, make the formula in AY4:

=IF(AND(ISNUMBER(AX4),AX4>=25),SUM(LARGE(A4:AW4,ROW($A$1:$A$25))),IF(ISNUMBER(AX4),"Unqualified",""))


--
HTH

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

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

 

[Terry]

Thank you Sandy.....I have now applied same, as ARRAY....so, I could have
nearly come back to you..LOL.

Regards
Terry