Creating a formula to identify highest figure

[Guest]

How do you create a formula to automatically calculate which figure are the
highest and place them into a seperate cell?

For example, if i have a spreadsheet containing students scores and i need
to identify their highest scoring activities and their marks given. This may
start off as having up to 20 marks and identifying their top three.

Thanks

 

[Earl Kiosterud]

For the highest: =LARGE(D221, 1)
For the second highest: =LARGE(D221, 2)
etc.

Or you could sort the table descending; the largest would be at the top.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Guest]

Thats great, thank you. Next question!!

Is there a way that the name of the column could be displayed as well as the
contents of the cell?

For example, if a students top grade was an A for Football, could the word
Football come up as well as the grade?

 

[dan]

Run this macro in a new worksheet, you will see.

Sub Macro1()
' data ---
Range("A4") = "football"
Range("A5") = "math"
Range("A6") = "history"
Range("B4") = "12"
Range("B5") = "19"
Range("B6") = "15"
' analizer
Range("D4") =
"=IF(A4="""","""",INDEX($A$4:$A$20,MATCH(E4,$B$4:$B$20,0)))"
Range("E4") = "=IF(A4="""","""",LARGE($B$4:$B$15,ROW()-3))"
Range("D4:E4").AutoFill Destination:=Range("D4:E20"),
Type:=xlFillDefault
End Sub

 

[Earl Kiosterud]

If the scores are in B2:E2, and the column headings (Football, etc.) are in B1:E1, then for
the top grade, use

=INDEX($B$1:$E$1,1,MATCH(LARGE(B2:E2,2),B2:E2,0))

For the second highest, use:
=INDEX($B$1:$E$1,1,MATCH(LARGE(B2:E2,2),B2:E2,0))

Copy down with the fill handle. This will mess up in the event there are two sports with
the same grade.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------