Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
function for ranking ?
Hello to y'all Excel Guru's,
We have hundrets of students in different years of study ( year 1 through year 4 ). In Column "A" I write the year of Study, "B" the name of the Student, "C" contains the Major-of-study and "D" contains the GPA. The other columns are used to enter the different Grades. E.G. A B C D E F G =...... Year Name Major GPA Math English Phyiscs 1 name1 Mechanics 2,98 1 name2 Art 1,96 2 name3 Mechanics 2,25 1 name4 Art 1,96 3 name5 Mechanics 2,36 4 name6 History 3,2 3 name7 Mechanics 2,42 1 name8 Art 2,02 etc.... We keep entering the grades as they come.( Column E through AZ ). I use the Worksheet_Change event to calculate the new GPA when a new grade is entered. This works fine. Here my event. ---------------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column 5 And Target.Row 3 Then RememberRow = Target.Row Calculate_GPA End If End Sub ' I use a public variable (RememberRow ) to store Target.Row. because the event changes the focus of the active ' cell after the user hits Enter. ' In my procedure "Calculate_GPA" I use this variable for calculating and writing the GPA in the correct Cell. ---------------------------------------------------------------------------------- The Sheet must also be flexible in terms of entry of new students, changing Majors, or eliminating students. This means I do not want to work with fixed formulas ( keeping in mind that unexperienced users will enter Data ) and for this reason I thought that the Change-Event would be a good solution. This way users can not easily manipulate or delete or overwrite formulas ;-) Now my Problem : We do have system that the currently best GPA of the Major (Column C) in the year of study ( Column A ) gets on the Board of Exclence. ( Champion ) Of course this may change on every Entry..which may be on a daily basis. There may also be two or more Champions ( see name2 and name4 ) which are in the same year, same major and do have an equal GPA, which happens to be the best in the category. Can a function be written, that triggers on Data-Entry, and finds the following : a) What is the best grade in the Major ( Column C ) and the Year of Study (Column A ) of ActiveCell.Row b) Highlite the Name and the GPA of the found Values. c) maybe even write the best GPA in a List on another Sheet ? ( I could creat a List with the years of Study and the possible majors, where the best GPA could be stored and maybe used for a later V- Lookup ? ) In other words... Find the year of Study AND the Major of Study of the Active Cell.Row ( where I enter the Grades and calculate the new GPA ) Then go ahead and rank all entries of the active Year of study AND the active Major of study. In the above Example : Let's say I enter Grades of a Math Test for name 4. The Worksheet_Change event triggers and calculates the GPA to be 1.96. Now I need my funktion ( or any other solution ) that Resets all "Old" highlites of the ACTIVE Year and ACTIVE Major and then highlites name2 AND name4 because they currently carry the title of best GPA in their major and year of study. I need the Reset because the old Champion loeses his/her Title ;o) However, the other majors and years of study did NOT change and shall remain as they are. Can somebody advise me please ? Oh.. we are using Excel 2003 on Windows XP ;-) and we are on a Newtork with different users ( teachers ) Regards, Gernoth |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking function | Excel Worksheet Functions | |||
Ranking without preset Excel function. | Excel Worksheet Functions | |||
Ranking or sorting function | Excel Worksheet Functions | |||
Ranking Function | Excel Worksheet Functions | |||
need function to sum top ranking items in list | Excel Worksheet Functions |