| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Newbie question
Hi,
I have two columns of data, 5 students and their grades in columns, say B (name) and C (Grade). How can I reproduce the highest grade and corresponding name in ssay, H7 (name) and I7 (Grade)? TIA Wayne |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Newbie question
Wayne,
In cell I7, use =MAX(C:C) In cell H7, use =INDEX(B:B,MATCH(I7,C:C,FALSE)) Of course, you can replase the C:C and B:B with the addresses of the five cells in those columns. HTH, Bernie MS Excel MVP "wayne" wrote in message . com... Hi, I have two columns of data, 5 students and their grades in columns, say B (name) and C (Grade). How can I reproduce the highest grade and corresponding name in ssay, H7 (name) and I7 (Grade)? TIA Wayne |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Newbie question
Many thanks for that
Wayne On Thu, 26 Jun 2008 14:29:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Wayne, In cell I7, use =MAX(C:C) In cell H7, use =INDEX(B:B,MATCH(I7,C:C,FALSE)) Of course, you can replase the C:C and B:B with the addresses of the five cells in those columns. HTH, Bernie MS Excel MVP "wayne" wrote in message .com... Hi, I have two columns of data, 5 students and their grades in columns, say B (name) and C (Grade). How can I reproduce the highest grade and corresponding name in ssay, H7 (name) and I7 (Grade)? TIA Wayne |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Newbie question
Sorry but I actually need the top 2 entries and corresponding names.
Sorry again Wayne On Fri, 27 Jun 2008 09:26:19 +0100, wayne wrote: Many thanks for that Wayne On Thu, 26 Jun 2008 14:29:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Wayne, In cell I7, use =MAX(C:C) In cell H7, use =INDEX(B:B,MATCH(I7,C:C,FALSE)) Of course, you can replase the C:C and B:B with the addresses of the five cells in those columns. HTH, Bernie MS Excel MVP "wayne" wrote in message e.com... Hi, I have two columns of data, 5 students and their grades in columns, say B (name) and C (Grade). How can I reproduce the highest grade and corresponding name in ssay, H7 (name) and I7 (Grade)? TIA Wayne |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Newbie question
Wayne,
That's a harder question - what if you have a tie for second, or a 3-way tie for first, or.... of course, we had ignored ties before, so we will do it again ;-) Use this in, let's say, I8 =LARGE(C:C,2) and =INDEX(B:B,MATCH(I8,C:C,FALSE)) If there is a tie for first, the formula immediately above will not return the second name... Bernie MS Excel MVP "wayne" wrote in message . com... Sorry but I actually need the top 2 entries and corresponding names. Sorry again Wayne On Fri, 27 Jun 2008 09:26:19 +0100, wayne wrote: Many thanks for that Wayne On Thu, 26 Jun 2008 14:29:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Wayne, In cell I7, use =MAX(C:C) In cell H7, use =INDEX(B:B,MATCH(I7,C:C,FALSE)) Of course, you can replase the C:C and B:B with the addresses of the five cells in those columns. HTH, Bernie MS Excel MVP "wayne" wrote in message re.com... Hi, I have two columns of data, 5 students and their grades in columns, say B (name) and C (Grade). How can I reproduce the highest grade and corresponding name in ssay, H7 (name) and I7 (Grade)? TIA Wayne |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Newbie question
Bernie,
Thanks a million for even bothering to reply. I'll have to see if there's another way to approach the problem. (There usually is)! Thanks again Wayne On Fri, 27 Jun 2008 08:36:07 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Wayne, That's a harder question - what if you have a tie for second, or a 3-way tie for first, or.... of course, we had ignored ties before, so we will do it again ;-) Use this in, let's say, I8 =LARGE(C:C,2) and =INDEX(B:B,MATCH(I8,C:C,FALSE)) If there is a tie for first, the formula immediately above will not return the second name... Bernie MS Excel MVP "wayne" wrote in message .com... Sorry but I actually need the top 2 entries and corresponding names. Sorry again Wayne On Fri, 27 Jun 2008 09:26:19 +0100, wayne wrote: Many thanks for that Wayne On Thu, 26 Jun 2008 14:29:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Wayne, In cell I7, use =MAX(C:C) In cell H7, use =INDEX(B:B,MATCH(I7,C:C,FALSE)) Of course, you can replase the C:C and B:B with the addresses of the five cells in those columns. HTH, Bernie MS Excel MVP "wayne" wrote in message ere.com... Hi, I have two columns of data, 5 students and their grades in columns, say B (name) and C (Grade). How can I reproduce the highest grade and corresponding name in ssay, H7 (name) and I7 (Grade)? TIA Wayne |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Newbie question
Wayne,
Of course there is - you can do anything in Excel. First, you need to decide how to handle ties, and then implement the tiebreaker using a column of ranking formulas, and use that column in the MAX and LARGE (or MIN and SMALL, depending on how you do the ranking) to extract the two sets of values of interest. For many cases, the solution I've given will work fine. The simplest fix is that when you are entering your data, realize that there is a tie and do something to change the tie - enter 89.0000001 instead of 89, for example, but format the cell to only show 89 - Excel will treat those as two distinct values, and it won't affect the average etc. in a meaningful way. HTH, Bernie MS Excel MVP "wayne" wrote in message . com... Bernie, Thanks a million for even bothering to reply. I'll have to see if there's another way to approach the problem. (There usually is)! Thanks again Wayne On Fri, 27 Jun 2008 08:36:07 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Wayne, That's a harder question - what if you have a tie for second, or a 3-way tie for first, or.... of course, we had ignored ties before, so we will do it again ;-) Use this in, let's say, I8 =LARGE(C:C,2) and =INDEX(B:B,MATCH(I8,C:C,FALSE)) If there is a tie for first, the formula immediately above will not return the second name... Bernie MS Excel MVP "wayne" wrote in message e.com... Sorry but I actually need the top 2 entries and corresponding names. Sorry again Wayne On Fri, 27 Jun 2008 09:26:19 +0100, wayne wrote: Many thanks for that Wayne On Thu, 26 Jun 2008 14:29:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Wayne, In cell I7, use =MAX(C:C) In cell H7, use =INDEX(B:B,MATCH(I7,C:C,FALSE)) Of course, you can replase the C:C and B:B with the addresses of the five cells in those columns. HTH, Bernie MS Excel MVP "wayne" wrote in message here.com... Hi, I have two columns of data, 5 students and their grades in columns, say B (name) and C (Grade). How can I reproduce the highest grade and corresponding name in ssay, H7 (name) and I7 (Grade)? TIA Wayne |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Real Newbie newbie question | New Users to Excel | |||
| If Then Question from Newbie. . . | Excel Discussion (Misc queries) | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
| Newbie Question | Excel Discussion (Misc queries) | |||
| Newbie question | New Users to Excel | |||
Linear Mode
