Excel & Grading (fwd)

["Daniel L. MacIsaac" <macisaac@bohr.phy.nau.edu>]

Actually, I think Guntbert wins the prize on this one.  He actually did the
function as a macro add-in in exactly the format I wanted.  Thank you all.

Dan M


********************
Hi Dan!

I wrote a small function that should match your needs. Just copy
it into the "general"section of the VBA-module in your Excel.
Then you can use it as you would with any built-in function.

N is the number of top items you want to sum up
Results is the array of cells where you've put your gradings

=46or instance you might write=20
=3DTopNSum(10, A3:A20) or  =3DTopNSum(10,activities)

Note: you need not mark a certain range of cells, you rather name
the range in the function call

> ------------- Begin of VBA Code
' returns the Sum of the N top values given in Results
' by Guntbert Reiter, Graz  for Daniel MacIsaac
' 14.12.97
Public Function TopNSum(N As Integer, Results As Range) As Single
Dim I As Integer, Cell As Range, Size As Integer
Dim m As Integer, k As Integer, Sum As Single
'Worksheets("Tabelle2").Activate
Size =3D Results.Count
ReDim Arr(1 To Size) As Single, topN(N) As Single
I =3D 1
=46or Each Cell In Results
    Arr(I) =3D Cell.Value
    I =3D I + 1
Next Cell
' Find the N biggest values
=46or I =3D 1 To Size
    For m =3D 1 To N
        If Arr(I) >=3D topN(m) Then
            For k =3D N To m + 1 Step -1
                topN(k) =3D topN(k - 1)
            Next k
            topN(m) =3D Arr(I)
            Exit For
        End If
    Next m
Next I
' Compute the sum
Sum =3D 0
=46or I =3D 1 To N
    Sum =3D Sum + topN(I)
Next I
TopNSum =3D Sum
End Function

> ----------- End of VBA Code

Hope it helps. If you have any questions feel free to ask - of
course you may give it to anyone who wants it
Bye, bye
	Guntbert
-------------------------------------------------
Guntbert Reiter, Graz, Austria
e-mail     : guntbert@asn-graz.ac.at