VBA Custom Function to Sort and average two numbers of a range

[Toddler]

Hello. I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:
Original unsorted data:
79
45
23
56
1
The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5
So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range)
Here is what I have. I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:

Function CSORT(r, p)

Application.Volatile

'rs or something similar that will contain the sorted range r

Dim rs As Range

'Sort rs (The following line one doesn't work, so I need one that does)

rs = Application.Range(r).Sort

Dim p As Integer

N = Application.Count(rs)

'Index for first row and second row of the range to be averaged

Dim FR As Integer
Dim SC As Integer

if N = 4 Then
FR=3
SC=4
Else if N = 5
FR=2
SC=3
Else
FR=1
SC=1
End If


'Minimum
If p = 0 Then
CSORT = WorksheetFunction.Min(rs)
'Average
ElseIf p = 1 Then

'I need to somehow use the above sorted range and the row indexes (FR,SR) for the average

CSORT = WorksheetFunction.Average(rs(FR:SR))

'Maximum
ElseIf p = 2 Then
CSORT= WorksheetFunction.Max(rs)
Else
CSORT = 0
End If

End Function


Thanks for your help !

 

[Worf]

Hello
See if this example is useful:

Sub CSort_calc(r As Range, p%)
Dim fr%, lr%, csort!, sr As Range
' reserve column y to store filtered range
If r.Columns.Count > 1 Then         ' range is a row
    Range("y1:y" & r.Columns.Count).Value = WorksheetFunction.Transpose(r)
Else
    r.Copy Destination:=Range("y1") ' range is a column
End If
lr = Range("y" & Rows.Count).End(xlUp).Row
Set sr = Range("y1:y" & lr)
sr.Sort Key1:=Range("y1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
csort = 0
Select Case p
    Case Is = 0
        csort = WorksheetFunction.Min(sr)
    Case Is = 1
        If lr = 4 Then
            csort = WorksheetFunction.Average(Range("y3:y4"))
        ElseIf lr = 5 Then
            csort = WorksheetFunction.Average(Range("y2:y3"))
        End If
    Case Is = 2
        csort = WorksheetFunction.Max(sr)
End Select
MsgBox "Csort = " & csort, vbInformation
End Sub
Sub Main()
    CSort_calc Range("s17:s21"), 2
End Sub