Just for fun (and a boring lunch) I wrote a function to return a to
value using only code. It will return one value sort of like the SMAL
and LARGE functions do. Call it like:

=ModePlus(D10:D2000,1) - Same as Mode
=ModePlus(D10:D2000,2) - Second Rank
etc.


Code
-------------------
Public Function ModePlus(rIn As Range, iNum As Integer)

Dim r As Range
Dim sItem() As String
Dim iItem() As Long
Dim iMax As Long
Dim i As Long
Dim SortArray() As String
Dim iCount As Integer
Dim strTemp As String
Dim iComma As Long
Dim retVal As String

iMax = rIn.Rows.Count
ReDim sItem(rIn.Rows.Count)
ReDim iItem(rIn.Rows.Count)

For Each r In rIn

For i = 1 To iMax
If sItem(i) = r.Value Then
iItem(i) = iItem(i) + 1
Exit For
End If
If sItem(i) = "" Then
sItem(i) = r.Value
iItem(i) = 1
Exit For
End If

Next

Next

For i = 1 To iMax
If sItem(i) = "" Then Exit For
Next
ReDim Preserve sItem(i - 1)
ReDim Preserve iItem(i - 1)
ReDim SortArray(i - 1)


For i = 1 To UBound(sItem)
If SortArray(iItem(i)) = "" Then
SortArray(iItem(i)) = sItem(i)
Else
SortArray(iItem(i)) = SortArray(iItem(i)) & "," & sItem(i)
End If
Next

iCount = 1
For i = UBound(SortArray) To 1 Step -1
If SortArray(i) < "" Then
strTemp = strTemp & "," & SortArray(i)
End If
Next

iComma = 0
For i = 1 To iNum
iComma = InStr(iComma + 1, strTemp, ",")
Debug.Print "FINDING COMMA: " & iComma
Next

retVal = Mid(strTemp, iComma + 1, InStr(iComma + 1, strTemp, ",") - iComma - 1)
ModePlus = retVal

End Functio
-------------------



--
Message posted from http://www.ExcelForum.com