Numbers column help please?

[Terry]

Win XP Pro
MS-Office XP

In a workbook I have a sheet that results in a column of random scores via
"large" formula:
{=large$AZ$4:$AZ$53,ROW(1:1)))}.....
From this "large" formula the results are shown in another sheet from
highest score to lowest scores (eg. 434), including competitors name.
Now I have 2 players with a score of 434 in above mentioned column, using
"index/match functions, but it repeats the fisrt name with 434 instead of 2
different names.
It works OK if scores are all different.

I wonder if I may incorperate "round" function in the "large" formula ??
From a non IT senior citizen...

Regards

Terry

 

[Terry]

Sorry group...duplicated this post

Terry

 

[Guest]

Hi Terry,

If you sort the random results in descending score order, then use the
autofilter if you want to display a partial set of the competitors, such as
the top 10 - you can do that with an autofilter.

 

[Guest]

First, to make each score unique, add a column next to the random ordered
scores - such as BA. In BA4 enter:

=AZ4+(COUNTIF(AZ$4:AZ4,AZ4)*0.001)

then copy the formula from BA4 and paste into BA5:BA53.

Second, change your formula to look at BA instead of AZ. So
{=large$AZ$4:$AZ$53,ROW(1:1)))}....., is changed to
{=large$BA$4:$BA$53,ROW(1:1)))}.....

Third, to display which place each competitor ranked, add a column next to
the column in the second step - let's assume that the sorted rankings start
in A1 of sheet 2. In B1 of sheet 2 enter: 1. In B2 of sheet 2 enter:

=IF(TRUNC(A2)=TRUNC(A1),A1,A1+1)

copy the formula from B2 and paste into B3:B49

You can format the number to not display any places after the decimal, so
that there won't be confusion when someone views the scores.

the {=large$AZ$4:$AZ$53,ROW(1:1)))} formula will display an error when there
are no more values to rank.

The Match function should now look at column BA or column A in sheet 2,
where there are no identical scores.

 

[Terry]

Sorry about reply delay, but with this post I managed to correct my problem
thank you.
Terry