First three positions from a column

  • Thread starter Thread starter Godly James
  • Start date Start date
G

Godly James

If I have two columns for Names and grades, How can I make
a formula to find the top three students from the grades
column and clasify them as First= Name 1, Second= Name2
and Third=Name3?
 
One way:

Assume Names & Grades in cols A and B, row 2 downwards

Put in D2: =OFFSET($A$1,MATCH(E2,B:B,0)-1,0,1,1)
Put in E2: =LARGE(B:B,ROW()-1)

Select D2:E2, copy down to row 4

D2:E4 will return the Top 3 Names and their grades

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
 
Max,

Is there a way to deal with ties? Say for example that 2nd and 3rd place
students have tie scores. Is there a way to address that?

I had a solution very similar to yours but I was unable to deal with "ties",
so I am curious if that can be dealt with easily.

Regards,
Kevin
 
Thanx Max, It works fine..

Rgds

Godly
-----Original Message-----
One way:

Assume Names & Grades in cols A and B, row 2 downwards

Put in D2: =OFFSET($A$1,MATCH(E2,B:B,0)-1,0,1,1)
Put in E2: =LARGE(B:B,ROW()-1)

Select D2:E2, copy down to row 4

D2:E4 will return the Top 3 Names and their grades

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------



.
Click to expand...
 
Kevin Stecyk said:
Is there a way to deal with ties? Say for example that 2nd and 3rd place
students have tie scores. Is there a way to address that?

I had a solution very similar to yours but I was unable to deal with "ties",
so I am curious if that can be dealt with easily.
Click to expand...
....

Been addressed before in this newsgroup. It all depends on how ties should
be broken. First off, if you want the top 3, it doesn't really matter if 2nd
and 3rd are tied. It *DOES* matter if 3rd and 4th are tied, and in that
situation why would one student with the same score as another be excluded?

Still, if ties may be broken arbitrarily, entry order is the most convenient
way. If the scores were in a single column, add

ScalingFactor*ROW()/FinalRow

to all scores, where ScalingFactor is half the smallest possible difference
between any two scores. No more ties. But that's inelegant.

So, to pull the students (possibly more than 3) with the 3 highest scores,
using the previously mentioned layout (names in col A from A2, scores in col
B from B2, but I'll restrict myself to rows 2 to 101), use these formulas.

D2:E2 as an array formula
=INDEX(A2:B101,MATCH(MAX(B2:B101),B2:B101,0),{1,2})

D3:E3 as an array formula
=IF(OR(ROW()<5,COUNTIF($B$2:$B$101,E2)>COUNTIF(E$2:E2,E2)),
INDEX($A$2:$B$101,MATCH(MAX(IF(COUNTIF(D$2:D2,$A$2:$A$101)=0,$B$2:$B$101)),
IF(COUNTIF(D$2:D2,$A$2:$A$101)=0,$B$2:$B$101),0),{1,2}),"")

Select D3:E3 and fill down as far as needed. If only 3 students may be
pulled even if there are several tying for the 3rd highest score, only fill
D3:E3 into D4:E4.
 
It was assumed (albeit unstated earlier, I admit <g>) that ties would either
be very remote, such as the situation if the grades were say, final
"roll-in" grades computed based on some weightage factors attached to
individual grades of all subjects (i.e. final grades would be non-integers),
or, that ties, if any, would be arbitrarily broken via manual
intervention/adjustment of the grades prior to the extract of the top 3
names/grades.

cheers
Max
 
Harlan,

Thank you for detailed answer. I needed a while to understand it. I
created an small list and played with the values to fully understand the
equation. Getting the proper row number is quite a challenge.

Regards,
Kevin
 
Back
Top