Numbers column help please?
 

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

Numbers column help please?
 

Sorry group...duplicated this post

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

Numbers column help please?
 

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.

"Terry" wrote:

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

Numbers column help please?
 

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

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

Numbers column help please?
 

Sorry about reply delay, but with this post I managed to correct my problem
thank you.
Terry
"pshepard" wrote in message
...
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" wrote:

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