Formula Builder

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
  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.

3 Responses to “Formula Builder”

  1. 1 Visit Website June 6, 2013 at 5:43 pm

    Very enlightening, look forth to visiting again.

  2. 2 anatomy images June 15, 2013 at 3:01 pm

    The tail or flagellum has a vital role in fertilization. Even when tests show no leukemia cells can be found, there may
    still be some left. This is just ONE of the many modules in this component – Note: Click On Each
    Image To View Larger Illustration: Click On Each Image To Zoom In – Lesson 1:
    Introduction to Basic Human Physiology Lesson 2: Physiology of Cells and Miscellaneous Tissues Lesson 3: Envelopes of the
    Body Lesson 4: The Skeletal System Lesson 5: Physiology and Actions of Muscles Lesson 6:
    The Human Digestive System Lesson 7: The Human Respiratory System and Breathing Lesson 8:
    The Human Urinary System Lesson 9: The Human Reproductive (Genital) System Click
    On Each Image To Zoom In – Lesson 10: Cardiovascular and Other Circulatory Systems of
    the Human Body Lesson 11: The Human Endocrine System Lesson 12:
    The Human Nervous System Lesson 13: The Special Senses Lesson 14: Some Elementary Human Genetics
    Ear-Eyes-Nose Injuries – Musculoskeletal System – Nursing Care Related to the
    Musculoskeletal System – Anatomy and Physiology Related to Clinical
    Pathology – AND MANY MORE.

  1. 1 Ian Leaf Trackback on March 15, 2015 at 3:13 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

July 2010
« Jun   Aug »

%d bloggers like this: