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

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

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" wrote:

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

Sorry Tyro....a single cell showing a players highest 25 scores(combined),
will be something like 350.
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

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

Terry

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

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

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

=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

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

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

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


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

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

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

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

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

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

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

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

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

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

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