Formula Builder

The example on the right shows a situation where data for two different experimental groups (“Control” and “Treated”) are written underneath (or horizontally) in the order that they were obtained.

Without the Formula Builder, one would have to either sort this list, thus breaking the information that is encoded in the order of the list (e.g., if the original data is in chronological order); or one would manually click on each individual cell to generate the AVERAGE() formula.

Using the XL Toolbox, you just need to select the input and output cells and indicate what kind of formula you wish to generate, and the Formula Builder will automatically collect the cell references for you (see example on the right).

Supported Formulas

• SUM()
• AVERAGE()
• STDEV()
• STDEV()/SQRT(COUNT())
• Any other formula that can use the pound sign (“#”) as a placeholder for the collected cell references, e.g.: ABS(#)

Using the formula builder

The Formula Builder needs to know four cell ranges:

• Input groups: Cells where your group names are written (for each sample/animal).
• Input data: Cell range that contains the data you want to use for the formulas.
• Output groups: Cells where you have written the group names for which to build the formulas. These can also be generated automatically (see below, option “Collect group names (auto)”).
• Output formulas: Cells where the Formula Builder will write the formulas. If no data is found for a given output group, the cell contents will be cleared.

Note: Group names are case insensitive, but make sure there are no invisible trailing spaces.

Entering your own formulas

You can enter your own formula. Use the pound sign (“#”) where the collected cell references shall be inserted. For example, a formula for standard error of the means (SEM) could be written as follows:

=STDEV(#)/SQRT(COUNT(#))

Explanation of the way the formula builder works.

Options

Collect group names (auto)

Instead of writing our every group for which you want to generate a formula, you can let the Formula Builder collect all group names that occur in the “Input Groups” cells. The output will be in alphabetical order.

Exclude outliers (+/- 2 SD)

The Formula Builder provides a primitive mechanism for outlier detection. When this option is checked, outliers will not be included in the final formula.

An outlier is defined as any value that is less than the gross average minus two standard deviations, or greater than the gross average plus two standard deviations.

The Formula Builder does not currently support detection of outliers within groups. It will always look at the complete dataset to detect outliers.

Important note: If you alter any of your data after a formula was generated, you must run the Formula Builder again. Formulas will not be regenerated automatically, i.e. the cell references in the formula will remain the same, even though the calculated result of a formula may change. Thus, if a cell was not included in the formula before the data was changed, it will of course not be included after the data was changed, unless you use the Formula Builder again.

Lock cell references

When the “Lock cell referenes” option is checked, the cell references will be written to the formula in the ”$A$1” style rather than the default ”A1” style.

Generate names for collected cells

The Formula Builder can assign names to the collected cell ranges. For example, instead of a formula

=AVERAGE(A1:A3,A7,A9:A11)

the resulting formula could be

=AVERAGE(CONTROL_GROUP)

This makes formulas more human-readable, and it can make it possible to build very long formulas (e.g., formulas that reference multiple discontiguous ranges on another sheet).

Note that when copying cells, the names will not be adjusted. Therefore, if you are planning to fill several adjacent cells with the same formula (e.g., for multiple time points in an experiment), the Formula Builder has to generate new names for each copy of the formula.

Excel limitations

Excel formulas can take a maximum of 255 arguments.

If you have a large data set with a lot of discontiguous groups, you may end up building a formula with more than 255 arguments. The Toolbox will detect this situation and try to generate a formula that circumvents this problem.

For example, a SUM() formula with 255 < N < 512 arguments will be generated as follows:

= SUM(CellRange1, ... ,CellRange255)
+ SUM(CellRange256, ... ,CellRangeN)

Example for a AVERAGE() formula with many arguments:

= AVERAGE(
AVERAGE(CellRange1, ... ,CellRange255)
AVERAGE(CellRange256, ... ,CellRangeN)
)

It should be noted that there are situations where this will not work. If the Formula Builder cannot provide a “workaround” formula for many arguments, it will notify the user and clear the corresponding output cell.

It is well possible that there are ways to express, for example a STDEV() or an SEM formula as a construct with many arguments. However, I, the programmer, do not know enough about mathematics to equip the Formula Builder with this capability.

If you have an idea how to express standard deviations and standard errors as composed formulas, please let me know!