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

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

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

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