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:

=CALC()

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.

### Like this:

Like Loading...

*Related*

## 0 Responses to “Formula Builder”