Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
"Terry" wrote:
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
Thanks Billy..will try and let you know.?
Terry "Billy Liddel" wrote in message ... "Terry" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
Terry
Keep posting in this thread "Terry" wrote: Thanks Billy..will try and let you know.? Terry "Billy Liddel" wrote in message ... "Terry" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Terry" wrote in message ... Thanks Billy..will try and let you know.? Terry "Billy Liddel" wrote in message ... "Terry" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
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: EditPaste SpecialSubtractOK 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
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 "T. Valko" wrote in message ... 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: EditPaste SpecialSubtractOK 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
large function help please?
Thanks for that Bernd...very helpful.
Terry "Bernd" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use large function? | Excel Discussion (Misc queries) | |||
Using LARGE Function | Excel Discussion (Misc queries) | |||
IF Function too Large | Excel Discussion (Misc queries) | |||
Using LARGE Function | Excel Worksheet Functions | |||
Help on Large Function | Excel Worksheet Functions |