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

Regards
Terry
"Sandy Mann" wrote in message
...
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,RO W($A$1:$A$25))),IF(ISNUMBER(AX4),"Unqualified","") )


--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


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


Replace @mailinator.com with @tiscali.co.uk


"Terry" wrote in message
...
OK Sandy.

On tesing a players scores manually, the result does not match your
formulae....
This player has 29 drive scores:(ignor 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" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Terry" wrote in message
...
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" wrote in message
...
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),AX425),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


Replace @mailinator.com with @tiscali.co.uk


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


Replace @mailinator.com with @tiscali.co.uk


"Terry" wrote in message
...
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" wrote in message
...
=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


Replace @mailinator.com with @tiscali.co.uk


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

"Tyro" wrote in message
et...
What do you mean by "flag"? Format the cells in a certain
color? Or what?

Tyro

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