Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column of negative numbers into positive numbers | Excel Worksheet Functions | |||
Can I change a column of calculated numbers to absolute numbers? | Excel Discussion (Misc queries) | |||
Match Column B numbers to Column A numbers | Excel Worksheet Functions | |||
Changing column of numbers made of formulas to just numbers | Excel Discussion (Misc queries) | |||
Excel Formula - Add column of numbers but ignore negative numbers | Excel Worksheet Functions |