This tip describes a technique that may be helpful in some situations - making a single worksheet function act like multiple functions. For example, the VBA listing below is for a custom function called StatFunction. It takes two arguments: the range (rng), and the operation (op). Depending on the value of op, the function will return any of the following: AVERAGE, COUNT, MAX, MEDIAN, MIN, MODE, STDEV, SUM, or VAR.
For example, you can use this function in your worksheet as follows:
The result of the formula depends on the contents of cell A24 -- which should be a string such as Average, Count, Max, etc. You can adapt this technique for other types of functions.
The StatFunction Function
Function STATFUNCTION(rng, op) Select Case UCase(op) Case "SUM" STATFUNCTION = Application.Sum(rng) Case "AVERAGE" STATFUNCTION = Application.Average(rng) Case "MEDIAN" STATFUNCTION = Application.Median(rng) Case "MODE" STATFUNCTION = Application.Mode(rng) Case "COUNT" STATFUNCTION = Application.Count(rng) Case "MAX" STATFUNCTION = Application.Max(rng) Case "MIN" STATFUNCTION = Application.Min(rng) Case "VAR" STATFUNCTION = Application.Var(rng) Case "STDEV" STATFUNCTION = Application.StDev(rng) Case Else STATFUNCTION = Evaluate("NA()") End Select End Function
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks