large function help please?

[Terry]

Win XP Pro
MS-Office XP

I have bowls club drives scores workbook, with one sheet showing
accumulated highest to lowest scores using:
{=large$AZ$4:$AZ$53,ROW(1:1)))}.....
The names are correctly located at the next column to their score using
"Inex/Match" combination:
=INDEX(Main!$A$4:$A$53,MATCH(D9,Main!$AZ$4:$AZ$53,0))

My problem is I happen to have two scores the same (434), but the names are
both the same instead of two differnt names with that same score.

I seem to remember using a "round" function in one of my earlier
spreadsheets to get roun this same problem.
I trust the explanation is OK?

Regards (non IT seior citizen)

Terry

 

[Billy Liddel]

Win XP Pro
MS-Office XP

I have bowls club drives scores workbook, with one sheet showing
accumulated highest to lowest scores using:
{=large$AZ$4:$AZ$53,ROW(1:1)))}.....
The names are correctly located at the next column to their score using
"Inex/Match" combination:
=INDEX(Main!$A$4:$A$53,MATCH(D9,Main!$AZ$4:$AZ$53,0))

My problem is I happen to have two scores the same (434), but the names are
both the same instead of two differnt names with that same score.

I seem to remember using a "round" function in one of my earlier
spreadsheets to get roun this same problem.
I trust the explanation is OK?

Regards (non IT seior citizen)

Terry

I am not sure what you want but here goes. My data goes to row 12 i.e 11
scores

PLAYER Score Corrected rank
Jay 70 1
Fred 65 3.5
Jean 65 3.5
Lou 65 3.5

Note sorted by Score
Rank formula in C2; =RANK(B2,$B$2:$B$12)+((COUNTIF($B$2:$B$12,B2)-1)*0.5)

If you want to list the top 5 scores type 1 to 5 (from F2 to F7) in this
case and this is where the index match functions will take the ranking.

in g3 type the formula =INDEX($A$2:$A$12,MATCH(D2,$C2:$C$12))

The match function does not have an absolute first row - C2, this will this
will prevent duplicate names.

If the score column is not sorted then it will not work.

Peter

 

[Terry]

Thanks Billy..will try and let you know.?
Terry

Terry

I am not sure what you want but here goes. My data goes to row 12 i.e 11
scores

PLAYER Score Corrected rank
Jay 70 1
Fred 65 3.5
Jean 65 3.5
Lou 65 3.5

Note sorted by Score
Rank formula in C2; =RANK(B2,$B$2:$B$12)+((COUNTIF($B$2:$B$12,B2)-1)*0.5)

If you want to list the top 5 scores type 1 to 5 (from F2 to F7) in this
case and this is where the index match functions will take the ranking.

in g3 type the formula =INDEX($A$2:$A$12,MATCH(D2,$C2:$C$12))

The match function does not have an absolute first row - C2, this will this
will prevent duplicate names.

If the score column is not sorted then it will not work.

Peter

 

[Billy Liddel]

Terry

Keep posting in this thread

Thanks Billy..will try and let you know.?
Terry

 

[ragdyer]

Here's a different approach you might try, where *physically sorting* or
ranking is unnecessary.

Say names are in Column A and scores are in Column B.

In another column, you can use a formula to *instantly* and *automatically*
sort the scores descending (there by ranking highest to lowest), just as
they're entered or updated in column B.
Then, in an adjoining column, another formula matches up these scores to the
names as they appear in the original datalist in column A.

With original datalist in say A1 to B50, with names in A and scores in
B,enter this formula in say C1:
=LARGE(B$1:B$50,ROWS($1:1))

And enter this *array* formula in D1:
=INDEX(A$1:A$50,SMALL(IF(B$1:B$50=C1,ROW($1:$50)),COUNTIF(C1:$C$50,C1)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

After the CSE entry of D1, select *both* C1 and D1 and drag down to copy to
the end of the datalist.

NOW, as you make any changes to the scores in Column B, you'll see an
immediate revision of the lists in Column C and D.

Ties will display with the *last* entered name in the original datalist (A &
B) showing first.
If you prefer ties to display in the order that they are listed in the
original list, just change the SMALL function in the array formula to LARGE.

 

[T. Valko]

Just for the heck of it........

I'm going to assume that the scores are not calculated and are manually
entered.

A1:A10 = name
B1:B10 = score

You already have these formulas in place:

=LARGE(........................)
=INDEX(.........................)

Enter this formula in an empty cell:

=ROW()/10^10

Copy that cell
Select the range B1:B10
Then do: Edit>Paste Special>Subtract>OK

Delete the =ROW()/10^10 formula

The LARGE and INDEX formulas have updated and now the INDEX formula returns
the correct names for any duplicates.

Biff

 

[Terry]

Cracked it...thank you all.

I used combinations of suggested formulae and a previously suggested one as
follows:
=average+row()/1000 to make results unique.

Regards
Terry

 

[Bernd]

Hello,

Hmm, this will only ensure uniqueness if your scores are integers and
if you will have less than 1,000 of them.

To be safe in all cases I would introduce a helper column like this:
=COUNTIF($B$1:$B$10,"<"&B2)+COUNTIF(B$1:B2,B2)
Copy down and sort by this column.

Regards,
Bernd