Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying adjacent cells
If there is a better way to do this, I am certainly open to suggestions, but here it is. I have 2 worksheets. Worksheet 1 has the master set of data. There is a list of people with scores for each one in a variety of games. On worksheet 2, I want to list the bottom 5 scores in each game with the name of the person next to each score. I can identify the lowest 5 scores with the small function. I want sheet 2 to display the name that is in the cell next to the lowest score found on sheet 1. The name can be displayed in the same cell or in an adjacent cell on sheet 2. Hope this problem description makes sense. Thanks for any help. -- daddioja ------------------------------------------------------------------------ daddioja's Profile: http://www.excelforum.com/member.php...o&userid=35024 View this thread: http://www.excelforum.com/showthread...hreadid=547728 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying adjacent cells
Hi!
Are there any ties involved or the possibility of ties? If so, it can be kind of complicated depending on how you define the bottom 5. Assuming no ties: Names in the range A1:10 Scores in the range B1:B10 Enter this formula in E1: =SMALL(B$1:B$10,ROWS($1:1)) Enter this formula in D1: =INDEX(A$1:A$10,MATCH(E1,B$1:B$10,0)) Select both D1 and E1 and copy down to row 5. Biff "daddioja" wrote in message ... If there is a better way to do this, I am certainly open to suggestions, but here it is. I have 2 worksheets. Worksheet 1 has the master set of data. There is a list of people with scores for each one in a variety of games. On worksheet 2, I want to list the bottom 5 scores in each game with the name of the person next to each score. I can identify the lowest 5 scores with the small function. I want sheet 2 to display the name that is in the cell next to the lowest score found on sheet 1. The name can be displayed in the same cell or in an adjacent cell on sheet 2. Hope this problem description makes sense. Thanks for any help. -- daddioja ------------------------------------------------------------------------ daddioja's Profile: http://www.excelforum.com/member.php...o&userid=35024 View this thread: http://www.excelforum.com/showthread...hreadid=547728 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying adjacent cells
You are a champ! Thanks Biff. It works perfectly and exactly as I hoped. If you don't mind, can you explain a few things so I understand why? 1. What is the ROWS($1:1) about in the SMALL function? I had thought to just list 1, 2, etc on that why this syntax? 2. Can you explain how the INDEX and MATCH function are working? 3. What does $ do in front of a reference? Thanks again for your help! Biff Wrote: Hi! Are there any ties involved or the possibility of ties? If so, it can be kind of complicated depending on how you define the bottom 5. Assuming no ties: Names in the range A1:10 Scores in the range B1:B10 Enter this formula in E1: =SMALL(B$1:B$10,ROWS($1:1)) Enter this formula in D1: =INDEX(A$1:A$10,MATCH(E1,B$1:B$10,0)) Select both D1 and E1 and copy down to row 5. Biff -- daddioja ------------------------------------------------------------------------ daddioja's Profile: http://www.excelforum.com/member.php...o&userid=35024 View this thread: http://www.excelforum.com/showthread...hreadid=547728 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying adjacent cells
One problem is that if 2 people have duplicate scores, it lists the first name twice instead of each name. I have attached a sample. You can see C18 is not right. Thanks for any help. +-------------------------------------------------------------------+ |Filename: MATCHING TEST.xls.zip | |Download: http://www.excelforum.com/attachment.php?postid=4838 | +-------------------------------------------------------------------+ -- daddioja ------------------------------------------------------------------------ daddioja's Profile: http://www.excelforum.com/member.php...o&userid=35024 View this thread: http://www.excelforum.com/showthread...hreadid=547728 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying adjacent cells
Ok........
In my reply you'll notice that I mentioned ties. Depending on how *YOU* define what the lowest 5 scores are this can be complicated. Here is a sample file that demonstrates how to extract names based on the lowest 5 scores that accounts for *ALL* ties. http://cjoint.com/?gcutiV1YA5 This file is based on your request for the bottom 5 but as you can see there are 6 names returned. Try playing around with it by changing the criteria in the yellow cell and changing the values in column B (include ties) and see what happens. Biff "daddioja" wrote in message ... One problem is that if 2 people have duplicate scores, it lists the first name twice instead of each name. I have attached a sample. You can see C18 is not right. Thanks for any help. +-------------------------------------------------------------------+ |Filename: MATCHING TEST.xls.zip | |Download: http://www.excelforum.com/attachment.php?postid=4838 | +-------------------------------------------------------------------+ -- daddioja ------------------------------------------------------------------------ daddioja's Profile: http://www.excelforum.com/member.php...o&userid=35024 View this thread: http://www.excelforum.com/showthread...hreadid=547728 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying adjacent cells
Thanks Biff. I think it will work. I will need to play with it, but it looks promising. Thanks once again. -- daddioja ------------------------------------------------------------------------ daddioja's Profile: http://www.excelforum.com/member.php...o&userid=35024 View this thread: http://www.excelforum.com/showthread...hreadid=547728 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Fill cells if adjacent cell match criteria | Excel Discussion (Misc queries) | |||
Text comparison in adjacent cells | Excel Discussion (Misc queries) | |||
Finding min/max of adjacent data in a range of cells | Excel Worksheet Functions | |||
I want to format a cell based on an adjacent cells value | Excel Discussion (Misc queries) |