function for ranking ?

[gsd]

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

 

[Guest]

Hi Gernoth:

I inserted a new column in the worksheet at E and used this array formula
(using CTRL+SHIFT+ENTER to enter) (curley brackets should appear around it:
=IF(D2=MIN(IF($C:$C&":"&$A:$A=(C2&":"&A2),$D:$D)),"STAR","")

and them copy it down.

and if you are on 2007 you will find that it is increadably slow so a
restricted version may be more approriate as in:

=IF(D2=MIN(IF($C$2:$C$9&":"&$A$2:$A$9=(C2&":"&A2),$D$2:$D$9)),"STAR","")


(here just the 8 rows)
..
This identifies the stars and I believe your GPA run from 1 as star and 5 as
requires attention. You can then use conditional formating on the row with

the following formula:

indirect("E"&row(),true)<>""

and set the fill color to your liking.

Use auto filter to see the list of them all.

 

[gsd]

Hi Gernoth:

I inserted a new column in the worksheet at E and used this array formula
(using CTRL+SHIFT+ENTER to enter) (curley brackets should appear around it:
=IF(D2=MIN(IF($C:$C&":"&$A:$A=(C2&":"&A2),$D:$D)),"STAR","")

and them copy it down.

and if you are on 2007 you will find that it is increadably slow so a
restricted version may be more approriate as in:

=IF(D2=MIN(IF($C$2:$C$9&":"&$A$2:$A$9=(C2&":"&A2),$D$2:$D$9)),"STAR","")

(here just the 8 rows)
.
This identifies the stars and I believe your GPA run from 1 as star and 5as
requires attention. You can then use conditional formating on the row with

the following formula:

indirect("E"&row(),true)<>""

and set the fill color to your liking.

Use auto filter to see the list of them all.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.




















- Zitierten Text anzeigen -

Dear Martin,

I cant't express my gratitude for such quick reply. But now it seems I
need more Help.
I have tried your array formula , but I guess I am missing something.
I keep getting the #NAME Error when I hit CTRL+SHIFT+ENTER .

In my 1st post I used and Example of a possible Spreadsheet.
However... in my true Spreadsheet the Year= in Column A ( A13 & dwn )
The Major = in Column D ( D13 & dwn ) and the GPA = in Column I ( I13
and dwn )

So I though I need to adapt your Formula :

=IF(I13=MIN(IF($D:$D&":"&$A:$A=(D13&":"&A13);$I:$I));"STAR";"")

Did I do something wrong ?

Regeards
Gernoth

 

[Olly]

Why not use a Pivot Table to report on the data in Columns A?

Group Rows by Year, Major and Name, use Max(GPA) as data, and filter Name to
show top 1 based on Max(GPA)

This would easily give you a quick snapshot report of all the champions for
each Year / Major combination.

 

[Guest]

Hi:
The analysis belows capares the same so it must be some data.
Is column d numeric?
What is above row 13. is there any possibilities of picking up the data
above row 13.

Try using specific rows as in:

=IF(I13=MIN(IF($D13:$D3000&":"&$A13:$A3000=(D13&":"&A13);$I13:$I3000));"STAR";"")

Otherwise if you like sned me the sheet and I will have a look at it.

using martin_fishlock @ yahoo.co.uk.cutthis removing the .cutthis

Workings:

MF Original:
=IF(D2=MIN(IF($C:$C&":"&$A:$A=(C2&":"&A2),$D:$D)),"STAR","")
Yr=A
Mj=C
GPA=D

GSD Revised:
=IF(I13=MIN(IF($D:$D&":"&$A:$A=(D13&":"&A13);$I:$I));"STAR";"")
Yr=A
Mj=D
GPA=I

 

[gsd]

Hi Martin,

Thanks for your Support.... I found my Mistake. Using a German Window-
Setup
I had to change the Worksheet Comand "IF" to the German Command
"WENN".

Now I don't get the error anymore and it seems to work.
I now do have the issue that on a BLANK cell in the Collumn "GERADE" I
do get
the Output "STAR"...Maybe I can find a workaround..

I will also try Ollys Solution, not exactely knowing how it will work.
( Thanks Olly )

However...these seem to be Worksheet Solutions, where I have to ensure
that
the Formulas are correctly embeded.
We have many useres who enter and Delete Names, change Majors etc...
and I am afraid that inserting new Rows or names, somebody will not
think
of the formulas.

Do you guys have any ideas how I could accomplish the Task with a
function
or a routine that would be triggered at Data-Entry
( Workscheet.Cahange-Event ) ?
That way I would not have to worry about formulas in any of the Cells.
( Similar to my Calculate_GPA Routine in the Worksheet Change event )

Here again 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.
---------------------------------------------------------------------------­-------


Thansk a bunch for the Time you spent on my Challange ;o)