Suppose that we have a large number of numerical data sets, which we want to examine using aggregation functions, such as SUM, AVERAGE, STDEV, and possibly some custom functions of our own devising. Each of these functions takes an entire dataset and retuns a single result.
Here’s the data:
where ‘data1’, ‘data2’, etc, name the dataset ranges; the column headers are themselves named ‘datasets’. We could, of course, have larger and more numerous datasets.
Now, we could have another worksheet with formulas such as:
=SUM(data1), =SUM(data2), …
= STDEV(data1), = STDEV(data2), …
and so on, for each combination of function and dataset. However, that’s rather a sledgehammer approach.
Alternatively, we could select a function from a drop-down list, select a dataset from another list, and so build a particular formula of interest:
The list of available functions is defined elsewhere, and picked up by data validation in cell B1:
(SUMDIFFS is a sample custom function). Similarly, the data validation on cell D1 picks up the ‘datasets’ list (the data column headers).
The result cell (F1) contains the formula:
where CALC is a public VBA function:
Public Function CALC() As Variant Dim func As String Dim dataset As String Dim formula As String func = Range("function").Value dataset = Range("dataset").Value formula = func & "(" & dataset & ")" CALC = Evaluate(formula) End Function
The formula String does not need an initial ‘=’.
As CALC does not reference its input cells explicitly, it does not get recalculated automatically if the input cells change. So we need to handle Change events that emanate from the two input cells. In the ‘Analysis’ Sheet module:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("function").Address Or _ Target.Address = Range("dataset").Address Then Range("result").Dirty End If End Sub
I’ve always wanted use the ‘Dirty’ method … This is equivalent to re-entering the =CALC() formula, triggering recalculation.
A constraint here is that the functions must form a family that have the same argument signature: here, a single array of values. Other statistical functions take more arguments, and might form other families. Alternatively, a multi-argument built-in function could be wrapped in a custom function that defaulted all but the main ‘data’ argument.