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

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

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

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

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

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

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