Newbie question

[wayne]

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

 

[Bernie Deitrick]

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]

Many thanks for that

Wayne

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]

Sorry but I actually need the top 2 entries and corresponding names.

Sorry again
Wayne

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

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