Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listing in Order
I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell which will always list the students from the highest grade to the lowest grade. This is not a sort problem, because I want it to be able to do this calculation when I change the grades around for example. Thanks for your help in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listing in Order
Short example:
In Range A1:B4 enter: Bob 80 Ted 75 Carol 85 Alice 60 In C1 enter: =LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4 In D1 enter: =OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4. If you insert or delete rows the Column C formula is going to HICK-UP as the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to Row(50:50), say. Hope this helps, Jim May "emarzuq" wrote: I have two columns The first has names of individuals the next has a list of grades, is it possible for me to be able to make a formula in another cell which will always list the students from the highest grade to the lowest grade. This is not a sort problem, because I want it to be able to do this calculation when I change the grades around for example. Thanks for your help in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listing in Order
Wow, thank you very much that works out great.
When I was using the Large I was saying =LARGE($B$1:$B$4,1) and then basically copying down and changing the 1 into 2 into 3 and so on.. could you explain the ROW(1:1) I know what it does but I don't understand how it works. Also, I had no idea Offset exists either. Could you please explain how that formula works? if not then don't worry about it, the information you gave me worked out great. Thanks, Eyad "JMay" wrote: Short example: In Range A1:B4 enter: Bob 80 Ted 75 Carol 85 Alice 60 In C1 enter: =LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4 In D1 enter: =OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4. If you insert or delete rows the Column C formula is going to HICK-UP as the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to Row(50:50), say. Hope this helps, Jim May "emarzuq" wrote: I have two columns The first has names of individuals the next has a list of grades, is it possible for me to be able to make a formula in another cell which will always list the students from the highest grade to the lowest grade. This is not a sort problem, because I want it to be able to do this calculation when I change the grades around for example. Thanks for your help in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listing in Order
Hello,
I just tested it out and there is one slight problem, I don't think these are able to deferentiate if two entries happen to have the same number. "JMay" wrote: Short example: In Range A1:B4 enter: Bob 80 Ted 75 Carol 85 Alice 60 In C1 enter: =LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4 In D1 enter: =OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4. If you insert or delete rows the Column C formula is going to HICK-UP as the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to Row(50:50), say. Hope this helps, Jim May "emarzuq" wrote: I have two columns The first has names of individuals the next has a list of grades, is it possible for me to be able to make a formula in another cell which will always list the students from the highest grade to the lowest grade. This is not a sort problem, because I want it to be able to do this calculation when I change the grades around for example. Thanks for your help in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listing in Order
You can use Rows(1:1) as a substitute for the number 1.
When you do a copy - during the paste it converts up (automatically) to 2, 3, 4 etc.. The offset() has 3 parameters, #1, The Anchor cell #2 the number of rows you wish to "move" up or down from the Anchor row. Negitives -2 (move up) Positive 2 (moves down) #3 the number of columns you wish to "move" to the left or right from the Anchor column. Negitives -2 (moves left) Positive 2 (moves right) Hope that helps,, Jim May "emarzuq" wrote: Wow, thank you very much that works out great. When I was using the Large I was saying =LARGE($B$1:$B$4,1) and then basically copying down and changing the 1 into 2 into 3 and so on.. could you explain the ROW(1:1) I know what it does but I don't understand how it works. Also, I had no idea Offset exists either. Could you please explain how that formula works? if not then don't worry about it, the information you gave me worked out great. Thanks, Eyad "JMay" wrote: Short example: In Range A1:B4 enter: Bob 80 Ted 75 Carol 85 Alice 60 In C1 enter: =LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4 In D1 enter: =OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4. If you insert or delete rows the Column C formula is going to HICK-UP as the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to Row(50:50), say. Hope this helps, Jim May "emarzuq" wrote: I have two columns The first has names of individuals the next has a list of grades, is it possible for me to be able to make a formula in another cell which will always list the students from the highest grade to the lowest grade. This is not a sort problem, because I want it to be able to do this calculation when I change the grades around for example. Thanks for your help in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listing in Order
I was afraid you would have this situation. Daaaaaa..
Maybe someone else can solve that part, I can't.. Jim "emarzuq" wrote: Hello, I just tested it out and there is one slight problem, I don't think these are able to deferentiate if two entries happen to have the same number. "JMay" wrote: Short example: In Range A1:B4 enter: Bob 80 Ted 75 Carol 85 Alice 60 In C1 enter: =LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4 In D1 enter: =OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4. If you insert or delete rows the Column C formula is going to HICK-UP as the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to Row(50:50), say. Hope this helps, Jim May "emarzuq" wrote: I have two columns The first has names of individuals the next has a list of grades, is it possible for me to be able to make a formula in another cell which will always list the students from the highest grade to the lowest grade. This is not a sort problem, because I want it to be able to do this calculation when I change the grades around for example. Thanks for your help in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listing in Order
Check out this old post.
It'll take care of duplicates! http://tinyurl.com/2tl9ar -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "emarzuq" wrote in message ... Hello, I just tested it out and there is one slight problem, I don't think these are able to deferentiate if two entries happen to have the same number. "JMay" wrote: Short example: In Range A1:B4 enter: Bob 80 Ted 75 Carol 85 Alice 60 In C1 enter: =LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4 In D1 enter: =OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4. If you insert or delete rows the Column C formula is going to HICK-UP as the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to Row(50:50), say. Hope this helps, Jim May "emarzuq" wrote: I have two columns The first has names of individuals the next has a list of grades, is it possible for me to be able to make a formula in another cell which will always list the students from the highest grade to the lowest grade. This is not a sort problem, because I want it to be able to do this calculation when I change the grades around for example. Thanks for your help in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Series order conflicts with line order | Charts and Charting in Excel | |||
Listing Order of Open Windows in Excel 2003 | Excel Discussion (Misc queries) | |||
sum and listing | Excel Discussion (Misc queries) | |||
I want a purchase order that includes page number (if to be order. | New Users to Excel | |||
Daily Macro to Download Data, Order and paste in order | Excel Worksheet Functions |