Hi All,


I am trying to write UDF's functions for calculating percentiles from
different sheets which I have pasted below.
if I want to calculate mean for variable1 i will give =mean(variable1)
will give me the averge value of variable from the 3 sheets.

Now can you please give me any suggestion on who to write a udf for a
expression say for example?
variable11 = variable1*variable2/variable3
variable12 = (variable4+variable5)/100

Your help will be highly appreciated.


Here

Public Function numvalidn(ByVal metric As Variant)
Application.Volatile (True)
k = 1
Do While (Len(Worksheets("DataSheet1").Cells(k, 1)) 0)
If (InStr(Trim(UCase(Worksheets("DataSheet1").Cells(k , 1))),
Trim(UCase(metric))) 0) Then
i = k
Exit Do
End If
k = k + 1
Loop
j = 1
n = 0
Cmax3 = ThisWorkbook.Worksheets("DataSheet3").UsedRange.Co lumns.Count
Cmax2 = ThisWorkbook.Worksheets("DataSheet2").UsedRange.Co lumns.Count
Cmax1 = ThisWorkbook.Worksheets("DataSheet1").UsedRange.Co lumns.Count
For m = 1 To Cmax1
If j = 256 Then Exit For
If (IsNumeric(Trim(Worksheets("DataSheet1").Cells(i, j))) And
Len(Trim(Worksheets("DataSheet1").Cells(i, j))) 0) Then n = n + 1
j = j + 1
Next
j = 1
For m = 1 To Cmax2
If j = 256 Then Exit For
If (IsNumeric(Trim(Worksheets("DataSheet2").Cells(i, j))) And
Len(Trim(Worksheets("DataSheet2").Cells(i, j))) 0) Then n = n + 1
j = j + 1
Next
j = 1
For m = 1 To Cmax3
If j = 256 Then Exit For
If (IsNumeric(Trim(Worksheets("DataSheet3").Cells(i, j))) And
Len(Trim(Worksheets("DataSheet3").Cells(i, j))) 0) Then n = n + 1
j = j + 1
Next
numvalidn = n
End Function

Private Function percentile25(ByVal metric As Variant)
Application.Volatile (True)
Dim mrange As Variant
mrange = vrange(metric)
percentile25 = Application.WorksheetFunction.Percentile(mrange, 0.25)
End Function

Private Function percentile50(ByVal metric As Variant)
Application.Volatile (True)
Dim mrange As Variant
mrange = vrange(metric)
percentile50 = Application.WorksheetFunction.Percentile(mrange, 0.5)
End Function

Private Function percentile75(ByVal metric As Variant)
Application.Volatile (True)
Dim mrange As Variant
mrange = vrange(metric)
percentile75 = Application.WorksheetFunction.Percentile(mrange, 0.75)
End Function
Private Function mmin(ByVal metric As Variant)
Application.Volatile (True)
Dim mrange As Variant
mrange = vrange(metric)
mmin = Application.WorksheetFunction.Min(mrange)
End Function
Private Function mmax(ByVal metric As Variant)
Application.Volatile (True)
Dim mrange As Variant
mrange = vrange(metric)
mmax = Application.WorksheetFunction.Max(mrange)
End Function
Private Function mean(ByVal metric As Variant)
Application.Volatile (True)
Dim mrange As Variant
mrange = vrange(metric)
mean = Application.WorksheetFunction.Average(mrange)
End Function

Public Function vrange(ByVal metric As Variant) As Variant
Application.Volatile (True)
Dim mrange()
Dim i, k, m, j As Integer
Dim n As Long
Cmax3 = ThisWorkbook.Worksheets("DataSheet3").UsedRange.Co lumns.Count
Cmax2 = ThisWorkbook.Worksheets("DataSheet2").UsedRange.Co lumns.Count
Cmax1 = ThisWorkbook.Worksheets("DataSheet1").UsedRange.Co lumns.Count
k = 1
Do While (Len(Worksheets("DataSheet1").Cells(k, 2)) 0)
If (InStr(Trim(UCase(Worksheets("DataSheet1").Cells(k , 1))),
Trim(UCase(metric))) 0) Then
i = k
Exit Do
End If
k = k + 1
Loop
n = numvalidn(metric)
ReDim mrange(1 To n)
n = 1
j = 1
For m = 1 To Cmax1
If j = 256 Then Exit For
If (Len(Trim(Worksheets("DataSheet1").Cells(i, j))) 0) Then
If (IsNumeric(Trim(Worksheets("DataSheet1").Cells(i, j)))) Then
mrange(n) = Worksheets("DataSheet1").Cells(i, j)
n = n + 1
End If
End If
j = j + 1
Next m
j = 1
For m = 1 To Cmax2
If j = 256 Then Exit For
If (Len(Trim(Worksheets("DataSheet2").Cells(i, j))) 0) Then
If (IsNumeric(Trim(Worksheets("DataSheet2").Cells(i, j)))) Then
mrange(n) = Worksheets("DataSheet2").Cells(i, j)
n = n + 1
End If
End If
j = j + 1
Next m
j = 1
For m = 1 To Cmax3
If j = 256 Then Exit For
If (Len(Trim(Worksheets("DataSheet3").Cells(i, j))) 0) Then
If (IsNumeric(Trim(Worksheets("DataSheet3").Cells(i, j)))) Then
mrange(n) = Worksheets("DataSheet3").Cells(i, j)
n = n + 1
End If
End If
j = j + 1
Next m
vrange = mrange
End Function





Regards,
Srinivas