# Statistical function

#### BernieRS

##### New Member
Is there a way to construct the WorksheetFuction call using any passed statistic name, rather than only allowing specific ones as in the following working example? Thanks in advance.

Code:
``````Function CalcStat(stat As String, col As Integer)
'
' Calculate statistics (from hidden Data sheet values)
'
On Error Resume Next
If col = 0 Then 'Entire Data sheet (all data sets)
firstcell = "\$A\$1"
lastrow = Worksheets("Data").UsedRange.Rows.Count
lastcol = Worksheets("Data").UsedRange.Columns.Count
Else 'One column (one data set)
lastrow = Worksheets("Data").Cells(Rows.Count, col).End(xlUp).row
End If
datarng = firstcell & ":" & lastcell
Select Case stat
Case "Min"
CalcStat = WorksheetFunction.Min(Worksheets("Data").Range(datarng))
Case "Max"
CalcStat = WorksheetFunction.Max(Worksheets("Data").Range(datarng))
Case "Average"
CalcStat = WorksheetFunction.Average(Worksheets("Data").Range(datarng))
Case "StDev"
CalcStat = WorksheetFunction.StDev(Worksheets("Data").Range(datarng))
End Select

CalcStat = Round(CalcStat, 1)

End Function``````

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi Bernie,

Unfortunately, no. Procedure calls must be "hard-wired."

Damon

I suspected not. I tried various Set constructions to no avail. Thanks.

Replies
0
Views
339
Replies
0
Views
430
Replies
1
Views
774
Replies
2
Views
358
Replies
0
Views
821

1,217,386
Messages
6,136,292
Members
450,002
Latest member
bybynhoc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back