FormulaForge Help

Table of Contents

Introduction

FormulaForge, an add-on to Microsoft Excel®, provides a variety of features that help users understand and construct complicated formulas.

Multiple formula views

Multiple views of a formula clarify its meaning and its structure.

  • The Text View shows the text of a formula, as it appears in Excel’s formula bar.
  • The Math View shows a mathematically typeset, easier to read version of the formula.
  • The Tree View shows how Excel decomposes a formula into simpler elements. It eliminates the need for users to deal directly with parentheses (for example, those surrounding function arguments and those that constrain Excel’s order of evaluation).

FormulaForge makes it easy to see where a selected element in the Tree View occurs in the other two views by highlighting that element in all three views.

Aids for understanding

Annotations and abbreviations in the formula views, plus additional information in Property Panes, help users understand a formula and the elements it contains.

  • The Tree View annotates all elements into which Excel decomposes a formula with their values.
  • Users can simplify the formula views by abbreviating selected elements, either with ellipses or with aliases for expressions listed in the Scrapbook.
  • The Selection Pane shows detailed information about a selected element. In particular, it shows the element’s logic region, as defined by the tests in its containing IF functions.
  • The References Pane lists all references contained in a formula.

Synthesis

FormulaForge helps users synthesize complicated formulas by making it easy to create, test, and combine simpler elements.

  • A text editor allows users to edit the text of a selected element and to detect errors in the edited text.
  • Drag and drop editing capabilities allow users to rearrange elements in a formula or to insert templates for operators, functions, references, and names.
  • Fragments of formulas in the Scrapbook can be dragged into a formula or used to abbreviate its elements.

Back to top or start of Introduction.

Tutorials

We illustrate the use and features of FormulaForge with a sequence of tutorials.

Back to top or start of Tutorials.

Tutorial: Recognizing Formulas

Watch this screencast to see how the FormulaForge Math View makes it easier to recognize and understand formulas.

Back to top or start of Recognizing Formulas Tutorial.

Tutorial: Debugging Problems with Formulas

Spreadsheet formulas can be hard to get right, even when copied from published definitions, like the one for the familiar bell-shaped curve from which the spreadsheet shown below tries, but fails, to plot the curve.

Picture of bell curve and defining equation
Mathematical definition of bell curve
Spreadsheet with incorrect bell curve
Problematic transcription into Excel

FormulaForge, an add-on to Microsoft Excel®, helps find and fix problems like the one in this spreadsheet. To use FormulaForge, we right-click cell B2 and select View in FormulaForge from the bottom of the drop-down menu.

FormulaForge option upon right-click in cell
Invoking FormulaForge from Excel’s context menu

This action causes FormulaForge to display three views of the formula in cell B2.

FormulaForge views of incorrect formula
FormulaForge views of problematic formula
  • The Text View shows the formula’s text, as it appears in the formula bar. Nothing looks wrong here.
  • The Math View typesets the formula, making it much easier to read. Nothing looks wrong here either.
  • The Tree View shows how Excel decomposes the formula into smaller expressions, and it shows the values of those expressions in red. The top value looks wrong.

We trace the origin of the suspiciously large value by looking at other values lower in the Tree View. We find the problem when we get to the box containing the unary minus sign from -A2^2: Excel has computed -A2 and then squared the result. We expected it to square A2 first, as is customary in ordinary mathematical usage and most programming languages.

Excel decomposition of -A2^2
Excel decomposition of -A2^2

Let’s fix the problem. First, we’ll get rid of the misplaced unary minus sign. We left-click the box containing A2, drag it on top of the box containing the unary minus, and drop it there. Notice how the cursor changes shape, and the border of the drop location changes color, to show that the drop is allowed. This drag and drop operation replaces -A2 by A2 in the formula.

Dragging A2 on -A2
Eliminating the misplaced minus sign

Second, we’ll put the unary minus sign where it belongs. We left-click the template for a unary minus sign, drag it onto the vertical line above the box containing the power operator (which changes color to show that the drop is allowed), and drop it there.

Dragging - above A2^2
Inserting unary minus

FormulaForge inserts the minus sign, along with parentheses in the formula’s text to force Excel to compute -9 as the value of -(A2^2), rather than 9 as the surprising value of-A2^2.

Now that we have fixed the problem, we can copy the corrected formula back into cell B2 in the Excel spreadsheet by clicking the Save Formula button at the top of the FormulaForge window. We finish the repair by copying the corrected formula down from cell B2 through cell B32 to get exactly the graph we want.

Spreadsheet with corrected formula
Corrected transcription into Excel

Watch this screencast to see these steps in action.

Back to top or start of Debugging Formulas Tutorial.

Tutorial: Synthesizing Formulas

Watch this screencast to see how FormulaForge Math View makes it easy to synthesize an index formula using drag and drop.

Back to top or start of Synthesizing Formulas Tutorial.

Tutorial: Dealing with Problematic Parentheses

Watch this screencast to see how FormulaForge’s multiple views makes it easy to detect and correct a problem with misplaced parentheses.

Back to top or start of Problematic Parentheses Tutorial.

Tutorial: Clarifying Complicated Logic

Watch this screencast to see how FormulaForge Math View makes it easier to understand the logic regions defined by nested IF functions.

Back to top or start of Complicated Logic Tutorial.

Viewing Formulas

FormulaForge’s central panel provides three panes for viewing formulas: one for the Text View, one for the Math View, and one for the Tree View. Its side panels provide panes for the Scrapbook and Templates on the left and for the Selection Pane and the References Pane on the right.

Controls for viewing formulas

The following controls appear at the top of the central panel.

Get formula button
Shows the formula in the currently active cell in Excel (i.e., the formula in Excel’s formula bar) in the three Formula Views. (The same effect can be achieved by selecting the View in FormulaForge entry in the pop-up menu that appears in response to a right-click in a cell in a spreadsheet.)
Save formula button
Copies the formula shown in the Formula Views, which may have been edited or synthesized, into the cell from which it was originally obtained.
Revert button
Discards any edits and redisplays the formula obtained from the last click of Get formula back in the three Formula Views.
Options menu
Allows users to control whether values are shown in the Tree View and whether or not values or ellipses are substituted for elements that are collapsed in the Tree View.
Help menu
Allows users to get information about the installed version of FormulaForge.

The following mechanisms can be used to adjust FormulaForge’s appearance.

  • To resize the panels or panes, slide the dividers that separate them. Vertical and/or horizontal scroll bars appear if the contents of a pane are too large to fit within it.
  • To hide a side panel (or show it again), click the arrow next to it at the bottom of the central panel.

Back to top or start of Controls Viewing Formulas.

Text View

The Text View displays the text of a formula, just as the user typed it into Excel, but without the leading = sign. For example, the Text View would display the familiar quadratic formula using Excel’s notations for powers and square roots.

Text view of the quadratic formula
Text view of the quadratic formula

If the formula has been edited, the Text View (together with the Math View and Tree View) shows the edited version.

Back to top or start of Text View.

Math View

The Math View presents formulas as they would appear in a mathematics text. The Math View for the quadratic formula

Math view of the quadratic formula
Math view of the quadratic formula

employs the usual mathematical typesetting conventions for fractions, squares, and square roots.

Typesetting conventions for the Math View

The Math View uses the following conventions when typesetting formulas. Here, x, y, and z represent arbitrary expressions that are typeset as x, y, and z.

Math View typesetting conventions
Expression Representation
Product: x*y x ⋅ y
Simple fraction: x/y, (x)/y, x/(y), (x)/(y) x y
Fraction with fraction in numerator: x/y/z x y⋅ z
Simple power: x^y x y
Power of a power: x^y^z x y ⋅ z
Less than or equal to: x<=y x ≤ y
Not equal to: x<>y x ≠ y
Greater than or equal to: x>=y x ≥ y
Range union: x, y x ∪ y
Range intersection: x y x ∩ y
ABS(x) | x |
EXP(x) e x
INT(x) ⌊ x ⌋
LOG10(x) log10( x )
PI() π
SQRT(x) x

 

Typesetting technology for the Math View

Powered by MathJax

 

Back to top or start of Math View.

Tree View

Formulas in Excel specify computations to perform on values represented by constants and values found in spreadsheet cells. The Tree View portrays a formula in a way that clarifies the order in which these computations are performed.

For example, the Tree View for the formula B3^2 - 4*A3*C3 shows that Excel evaluates the power B2^2 and the product 4*A3*C3 before it evaluates their difference. The lowest rectangles in the diagram contain labels for primitive values: constants (2 and 4) and references (A3, B3, and C3) to values found in spreadsheet cells. Above them are rectangles that contain labels for operators: exponentiation ^, multiplication ×, and subtraction -. Next to each rectangle for a reference or operator is its value in red.

Tree View for B3^2 - 4*A3*C3
Tree View for B3^2 - 4*A3*C3

The Tree View is particularly valuable when formulas become long and complicated, or when Excel does not evaluate them in the order one might expect. For example, the Tree View shows that Excel evaluates -A1^2 as if it had been written (-A1)^2 and that one must write -(A1^2) to get the operations performed in the other order.

Tree View for -A1^2
-A1^2
Tree View for (-A1)^2
(-A1)^2
Tree View for -(A1^2)
-(A1^2)

Structure of the Tree View

The name “Tree View” is motivated by that fact that such diagrams look like (upside down) trees. Each rectangle in the Tree View represents an element in a formula and contains a symbol that identifies the type of that element:

  • a constant (e.g., 2 and "New York"),
  • a reference to a cell or range (e.g., A1 and B1:B2),
  • a user-defined name (e.g., floor),
  • an operator (e.g., +), or
  • a function (e.g., SQRT).

For increased readability, the Tree View substitutes common mathematical symbols for the notations used by Excel:

  • × for *,
  • for <=,
  • for >=,
  • for <>,
  • for a comma used as a range union operator,
  • for a space used as a range intersection operator,
  • {...} for an array constant, and
  • ... for a row in an array constant.

Each rectangle that contains a symbol for an operator or a function represents a compound expression. Immediately beneath it in the Tree View are other rectangles, called its children and connected to it as in an organization chart or family tree, that correspond to the operator’s operands or to the function’s arguments. Thus elements representing 4, A3, and C3 are children of an element representing 4*A3*C3, and an element representing A1 is a child of an element representing SQRT(A1).

The lowest elements in the Tree View represent constants, references, user-defined names, and functions without any arguments (e.g., PI()).

No parentheses appear in the Tree View.

Values in the Tree View

Next to each element in the Tree View (unless it represents a constant) is the value computed by Excel for the expression it represents. This display of values can be turned on and off by an option on the Options menu.

Labels that would contain more than 15 characters are truncated; an ellipsis (..) at the end of a label indicates that it has been truncated. To see the entire label, to see the full text of the expression represented by the element, or to see the value of that expression, either select the element by clicking it and look in the Selection Pane or hover the cursor over the element and look in the tooltip that pops up.

Manipulating the Tree View

An element in the tree may be collapsed or expanded by clicking the triangle just beneath it. A collapsed element is distinguished from a bottom element by the presence of the expand icon just beneath it.

Expanded element
Expanded element
Collapsed element
Collapsed element

Back to top or start of Tree View.

Element selection

Click an element in the Tree View to select that element. FormulaForge highlights the element in all three Formula Views.

Selecting an element in the quadratic formula
Selecting an element in the quadratic formula

Alternatively, click a symbol in either the text or math view to select the smallest element containing that element.

Click an empty space in one of the three views to clear the selection.

Element navigation

To change the selection, click another element in the Tree View or use the control key in conjunction with an arrow key, as follows.

Navigating elements in a formula
Key pressed Action
Control-up arrow Select parent of current selection
Control-down arrow Select first child of current selection
Control-left arrow Select sibling before current selection (e.g., previous argument of a function)
Control-right arrow Select sibling after current selection

There is no change in the current selection if there is no element in the indicated direction (e.g., no parent for Control-up, no child for Control-down).

Back to top or start of Navigation.

Viewing Properties of Formulas

FormulaForge provides a variety of means for viewing properties of the formula displayed in its Formula Views.

Properties of formula elements
Hovering the cursor over an element in the Tree View pops up a tooltip with information about that element. Selecting an element causes information about that element to appear in the Selection Pane.
References in the current formula
The References Pane displays a list of the references in the current formula.
The spreadsheet cell for the formula
The title of the Scrapbook indicates the address of the spreadsheet cell from which the current formula was obtained by clicking the Get Formula button and into which it will be written by clicking the Save Formula button.
User-defined names available for use in the formula
The Templates Pane contains a list of names that the user has defined in Excel. These names can be dragged and dropped into the Tree View when editing a formula.

Back to top or start of Viewing Properties.

Selection Pane

The Selection Pane displays the following information about the currently selected element.

Image of Selection Pane
Selection Pane
Text
The text of the element.
Value
The value of the element, as computed by Excel.
Logic regions
A list of the logical tests in all IF functions that have this element as a descendant of its second or third argument. A logical test in this list is followed by the word TRUE if the element is a descendant of the second argument, which is evaluated if the value of the logical test is TRUE, and it is followed by the word FALSE if the element is a descendant of the third argument, which is evaluated if the value of the logical test is FALSE.
Alias
The alias from the Scrapbook, if any, that matches the element. Blank if there is no matching alias.

Logic regions

A formula that contains nested IF functions can have many different values, depending on the values of the tests. The formula shown above can have one of three values, A1, B1, and C1, depending upon the values of the tests in its three IF functions.

The following formula is even harder to understand, with four different logical tests in six IF functions.

Sample formula
IF(X1 = 0, IF(Y1 = 0, "Origin", "Y axis"), IF(Y1 = 0, "X axis", IF(Y1 >= 0, IF(X1 >= 0,
"First quadrant", "Second quadrant"), IF(X1 >= 0, "Fourth quadrant", "Third quadrant"))))

This more complicated formula can have seven different values, as shown in the following table.

Possible values of sample formula
X1 = 0 Y1 = 0 Y1 >= 0 X1 >= 0 Value of formula
TRUE TRUE N/A N/A Origin
TRUE FALSE N/A N/A Y axis
FALSE TRUE N/A N/A X axis
FALSE FALSE TRUE TRUE First quadrant
FALSE FALSE TRUE FALSE Second quadrant
FALSE FALSE FALSE FALSE Third quadrant
FALSE FALSE FALSE TRUE Fourth quadrant

It can be difficult to determine whether formulas as complicated as these produce the intended values in all cases. Without the aid of FormulaForge, the best the user can do is to enter carefully chosen values in the referenced cells (A1, B1, and C1 in the first example, and X1 and Y1 in the second) and to check that the formula has the correct value in each case.

Such testing, however, does not provide conclusive proof that the formula is correct. For example, testing the second formula with values chosen from among 0, -1, and 1 would not reveal an error if the logical test for Y1 >= 0 had been written incorrectly as Y1 = 1. With the aid of FormulaForge, the user can check that this formula is correct by selecting each of the seven string constants in the Tree View and examining its logic region. For example, the logic region for First quadrant is

Definition of logic region for sample formula
X1 = 0 FALSE
Y1 = 0 FALSE
Y1 >= 0 TRUE
X1 >= 0 TRUE

which is indeed correct.

Likewise, for the first formula, the user need only check four logic regions, one for the second and third arguments of the two nested IF functions, to determine that the value of the formula is the minimum of the values of A1, B1, and C1.

Back to top or start of Selection Pane.

References Pane

The References Pane contains a list of all references that occur in the formula shown in the three Formula Views.

References pane
Sample use of References Pane

There are four columns in the list.

Sheet
If the reference is to a sheet other than the one from which the formula in the Formula Views was obtained, the name of that sheet. Otherwise blank.
Reference
The reference.
Value
The value of the reference, as computed by Excel in the cell from which the formula in the Formula Views was obtained.
Formula
If the reference is to a cell containing a formula, the text of that formula. Blank otherwise.

Synchronization with Formula Views

Rows containing references that occur in the current selection are highlighted. Thus, in the example, the rows containing the references A1 and C1 are highlighted because those references occur in the selected element A1 + C1.

Back to top or start of References Pane.

Editing

FormulaForge supports both textual and structural editing for elements in a formula.

  • Its text editor allows users to edit the text of a selected element and to detect errors in the edited text.
  • Its drag and drop editing capabilities allow users to
    • rearrange elements in a formula,
    • insert formula fragments from the Scrapbook, and
    • insert templates for operators, functions, references, and names.

    FormulaForge guarantees that changes made using drag and drop are structurally correct.

Back to top or start of Editing.

Text Editing

FormulaForge’s text editor allows users to edit the text of a selected element and to detect errors in the edited text.

Initiating a text edit

Double click an element in the Tree View to create a text edit window containing the text of that element. FormulaForge allows only one element to be edited at a time. It insists that an active edit be finished or canceled before starting another; it also disables drag and drop editing while a text edit is in progress.

Tree view for 2*B1/C1
Double clicked element B1/C1
Text edit window for B1/C1
Text edit window for B1/C1

Making changes

Simply type (or cut and paste) in the edit window to change the text. Use the arrow keys to move around in the text.

Checking the changes

Click the Check button in the edit window to have FormulaForge analyze the edited text and display a list of errors. Click a line in the list of errors to have FormulaForge highlight the location of the error.

Text editor error analysis
Sample text editor error messages

Saving the changes

Click the Finish button to have FormulaForge update the element selected for editing in the Tree View and close the edit window. FormulaForge analyzes the edited text, as it does in response to clicking the Check button, before updating the element. If there are errors, the user must fix them or cancel the edit.

FormulaForge inserts parentheses around the edited element, if necessary, to ensure the correct order of evaluation. Thus, if the user changes A1 in 2*A1 to B1 + C1, FormulaForge inserts parentheses to produce the formula 2*(B1 + C1).

Canceling an edit

Click the Cancel button to close the edit window without updating the element selected for editing. If the contents of the edit window have been changed, FormulaForge will prompt the user to take one of three actions:

  • Click ‘Yes’ to finish the edit and close the editor
  • Click ‘No’ to discard the changes and close the editor.
  • Click ‘Cancel’ to keep the editor open.

Back to top or start of Text Editing.

Scrapbook

The Scrapbook contains fragments of formulas, called scraps, that can be used when editing a formula or to abbreviate the display of a long formula.

The title of the Scrapbook indicates the cell that was active when the Get Formula button was last clicked. It is into this cell that the edited formula will be written when the Save Formula button is clicked.

To find the names of the worksheet and workbook associated with the cell in the title of the Scrapbook, hover the cursor over the title. A tooltip will appear with the desired information.

List of scraps

The Scrapbook’s list of scraps contains three columns with the following contents.

Alias
Users can enter an identifier, called an alias, for the scrap in this column. This identifier must be different from all other aliases, from references to cells, and from all user-defined names in the Excel spreadsheet.
Apply
Checking the box in this column causes the alias to be substituted for the scrap wherever it appears in the Formula Views (in purple) and in the logic regions section of the Selection Pane (in black). Unchecking this box causes the alias to be removed from wherever it had been substituted. Alias substitutions are for display purposes only and are removed from formulas written back into the spreadsheet when the Save Formula button is clicked.The Apply box cannot be checked if the Alias column is blank. If there are duplicate scraps, checking this box for one of the duplicates removes the check from the boxes for the other duplicates.

Scrap
The definition of the scrap. To see more of a definition that is too long to fit in this column, place the cursor in the definition by clicking within it and then using the arrow keys to scroll right and left.

Clicking any row in the list selects that row. Clicking the title of any column in the list sorts the list according to the values in that column. Clicking the title again reverses the sort order.

A scrap in the Scrapbook can be dragged and dropped into the Tree View to modify the definition of the formula in that view (and in the other two Formula Views as well).

A vertical scrollbar appears if the list of scraps is too long to display in the allotted area.

Buttons

Beneath the list of scraps are three buttons, which enable users to manage the list and/or the selected row.

Add
Clicking the Add button adds a scrap to the Scrapbook for the element that is currently selected in the Formula Views. Initially the new scrap has no alias, and its Apply box is not checked. If there is already a scrap for the currently selected element, FormulaForge asks if the user wants to create a duplicate. Drag and drop provides another way to add scraps to the Scrapbook.
Delete
To delete a scrap, select it by clicking its entry in the Scrapbook and then click the Delete button.
View
To view and/or edit the definition of a scrap, select it by clicking its row in the Scrapbook and then click the View button. The definition of the scrap will appear in the three Formula Views. Whenever that definition is changed by an edit, its alias (if any) is removed from wherever it had been substituted and the Scrap column is updated with the new definition. If the Apply box is checked for that scrap, the scrap’s alias is substituted for all occurrences of the scrap’s new definition.

Back to top or start of Scrapbook.

Templates

Templates are items that can be dragged and dropped into the Tree View to modify or insert elements in a formula.

Types of templates

Binary operators
Templates for binary operatorsIcons for the usual arithmetic operators (+, -, × for *, /, and ^), for ordering relations (<, for <=, =, for <>, for >=, and >), for string concatenation (&), for range union ( for a comma), and for range intersection ( for a space).
Unary operators
Templates for unary operatorsIcons for unary operators (+, -, and %).
Stub
Template for a stubAn icon for inserting a placeholder in a formula. Each stub has a name, which is indicated in red in the Math and Tree Views, and is surrounded by accent marks in the Text View, to distinguish it from names defined in Excel. The name of the placeholder stub is Stub (or `Stub` in the Text View).
Functions
Templates for functionsAn alphabetical list of the functions available in Excel, plus a drop-down menu that allows users to view all functions or just those in one of Excel’s function categories (e.g., Logical functions and Math and trigonometry functions).To find a function in the list, use the scrollbar to the right or type the first few letters of the function identifier.

Names
Templates for Excel namesA list of names defined in the spreadsheet containing the formula in the Formula Views.
Selected range
Template for a rangeA constantly updated display of the range that is currently selected in the spreadsheet.

Stubs for operands and arguments in templates

When the template for an operator or function is dragged into the Tree View, FormulaForge creates a <a
href=”#Glossary_stub”>stub for each of its required children to complete the template.

Each stub created for an operator template has Stub as its name. Thus the template for the multiplication operator corresponds to the expression Stub * Stub.

Each stub created for an argument in a function template is given the name that Excel uses to describe that argument. Thus the template for the IF function contains three stubs named logical_test, value_if_true, and value_if_false.

The value shown for each stub in the Tree View or Selection Pane is the Excel error value #VALUE;. When the user saves an edited formula into an Excel spreadsheet (by clicking the Save Formula button), FormulaForge replaces the accent marks surrounding each stub by underscores to transform it into an undefined Excel name.

Back to top or start of Templates.

Drag and Drop

Drag and drop enables structural editing in the Tree View. It can be uses to move or copy elements from one location in the Tree View to another or into the Scrapbook. It can also be used to drag new elements into the Tree View from the Scrapbook and from the References Pane.

Initiating a drag and drop

To initiate a drag and drop, click one of the following objects with the left mouse button, hold the button down, and begin to move the object.

  • An element in the Tree View.
  • An item in the Templates Pane. Templates for operators and functions represent elements with stubs for their operands and arguments. Templates for binary operators (other than the ordering relations) are expandable in that they can have two or more stubs. Templates for functions that accept variable numbers of arguments are also expandable.
  • A scrap in the Scrapbook.
  • A reference in the References Pane.

Tracking a dragged element

As an element is dragged, the cursor changes shape to indicate when a drop is allowed.

  • An arrow with an empty rectangle  Drag and drop cursor for move  indicates that a drop is allowed and that it will move the dragged element to the location indicated by the cursor. A move deletes the dragged element from its original location. Moves are not allowed out of the Templates or References panes.
  • An arrow with a rectangle and a plus sign   Drag and drop cursor for copy  indicates that a drop is allowed and that it will copy the dragged element to the location indicated by the cursor.
  • A circle with a slash   Drop not allowed cursor  indicates that a drop is not allowed.

Dropping a dragged element

To drop an element in an allowed location, simply release the left mouse button.

An element dropped in the Scrapbook creates a new scrap. Users must confirm drops that would create duplicate scraps.

An element dropped in the Tree View modifies the formula by changing its structure.

Back to top or start of Drag and Drop.

Structural Editing

Structural editing in the Tree View allows users to modify formulas, without having to worry about parentheses, in ways that are guaranteed to be structurally correct. Drag and drop operations play an important role. They allow users to

For each of these operations, when a dragged element approaches a location in the Tree View that allows the operation, FormulaForge highlights the location to show that the element can be dropped there.

Back to top or start of Structural Editing.

Adding and Deleting Elements

Some operators and functions accept variable numbers of arguments. For example, the binary operators for arithmetic (plus, minus, times, divides) accept two or more operands, the SUM and AND functions accept an unlimited number of arguments, and the INDEX function accepts from one to four arguments. Drag and drop makes it possible to add operands and arguments to, or delete them from, such operators and functions.

Adding elements

As a dragged element passes below an element for an operator that will accept an additional operand, or an element for a function that will accept an additional argument, vertical lines appear to indicate that the dragged element can be dropped to the left of, in between, or to the right of the existing operands or arguments. Dropping the dragged element on one of these vertical lines inserts it a new operand or argument.

Adding a new operand
Adding a new operand

Unary and binary relation operators do not accept additional operands, so vertical lines will not appear when a dragged element passes below them. Neither will vertical lines appear when a dragged element passes below most functions, because most accept a fixed number of arguments. Functions like SUM and AND, which accept an unlimited number of arguments, or INDEX, which have optional arguments, may be able to accept more arguments.

FormulaForge automatically wraps parentheses around the new operand when they are needed to ensure the correct order of evaluation.

Adding a new parenthesized operand
Adding a new parenthesized operand
Deleting elements

Drag and drop makes it possible to delete operands from operators or arguments from functions by moving elements to the Scrapbook.

Deleting an operand
Deleting an operand

If deleting an operand would leave an operator with too few operands, FormulaForge replaces the deleted operand by a stub. Similarly, if deleting an argument would leave a function with too few arguments, FormulaForge replaces the deleted argument with a stub.

There are two other ways to delete an operand or argument.

  • Select it and then press control-DEL.
  • Right click it and select Delete from the pop-up menu.

Back to top or start of Adding and Deleting Elements.

Moving Elements

Drag and drop makes it possible to rearrange an operator’s operands or a function’s arguments. In general, it makes it possible to copy or move an operand from one location in the Tree View to another.

Rearranging operands and arguments

Dragging an operand or argument to the right or left causes vertical lines to appear as it passes over locations where it can be dropped. Releasing the left mouse button when one of these lines is visible drops the element there.

Moving an operand
Moving an operand
Copying an operand or argument from one location to another

Elements can be dragged from one location in the Tree View to another. If the control key is depressed at the time of the drop, the Tree View will contain two copies of the dragged element, one at its original location and one at the drop location.

Copying an operand to another location
Copying an operand to another location
Moving an operand or argument from one location to another

If the control key is not depressed at the end of such a drag and drop, the Tree View will contain a single copy of the dragged element at the drop location. The dragged element will be deleted from its original location, or it will be replaced by a stub if deleting it would leave an operator with too few operands or a function with too few arguments.

Moving an operand to another location
Moving an operand to another location

Back to top or start of Moving Elements.

Replacing Elements

Drag and drop makes it easy to replace one element by another.

Dragging and dropping an element onto a target element that has no children replaces the target element with the dragged element.

Dropping a copy of an element on another element
Replacing a childless element by a copy of another element
Dropping template on childless element
Replacing a childless element by a template

Dragging and dropping an element with no children that are stubs onto a target element replaces the target element with the dropped element.

Dropping B1 on -B1
Replacing an element by a childless element

Similarly, this kind of drag and drop can be used to retain a single operand for an operator (or a single argument for a function) and to delete the other operands (or arguments): simply replace the operator by the operand (or the function by the argument). The mechanisms for deleting operands do not have the desired effect, because they will not delete the operator.

Dropping A1 on A1 + Stub
Using replacement to delete an operator and retain a single operand

As described immediately below, dragging and dropping an element with children that are stubs onto a target element with children changes the target element, while retaining its children.

Back to top or start of Replacing Elements.

Changing Elements

Drag and drop makes it easy to change an operator or a function while retaining the operator’s operands or the function’s arguments.

Dropping a template for an operator on a target element with children replaces the operator or function associated with the target element by the operator. Dropping a template for a function on a target element with children behaves similarly.

Dropping + on *
Changing an operator

For this to work, the template must have (or be expandable to have) at least as children as the target element.

Back to top or start of Changing Elements.

Inserting Elements

Drag and drop makes it possible to move elements lower in the Tree View by inserting a new element above the top element, or by inserting a new element between an existing element and its parent.

Inserting an element above the top element

Dropping a template for a unary operator above the top element in the Tree View inserts the operator in front of that element’s expression, for example, transforming A1 into -A1.

Dropping unary minus above formula
Inserting a unary operator before a formula

After the drop, there is a new element at the top of the Tree View with the old top element as its only child. Dropping the template for a unary function above the top element in the Tree View has a similar effect, for example, transforming A1 into ABS(A1).

If necessary, FormulaForge automatically wraps parentheses around the former top element to ensure the correct order of evaluation after the drop. Thus, for example, when dropping a unary minus above a sum, FormulaForge wraps the sum in parentheses to ensure that the sum is computed before the minus sign is applied.

Dropping unary minus above a sum
Automatic parenthesization when inserting a unary operator

A function with no arguments and the template for a stub cannot be dropped above the top element.

Dragging constant above a formula
Illegal drag and drop of zero-ary function

Dropping a template for a binary operator above the top element in the Tree View creates a new top element with the old top element as its first child and a stub as its second, for example, transforming A1 into A1 + Stub.

Dragging binary plus above formula
Automatic creation of stubs when inserting a binary operator

Dropping the template for a function with two or more arguments above the top element has a similar effect, creating a new top element with the old top element as its first child and stubs as its remaining children.

Inserting an element between an element and its parent

Just as a template or function can be dropped above the top element, so can it be dropped between any child and its parent.

Dropping unary minus above A1 in SIN(A1)
Inserting an operator between a child and its parent

As above, FormulaForge automatically inserts parentheses to ensure the correct order of evaluation after a drop.

Dropping binary plus above A1 in A1/2
Automatic parenthesization to ensure correct order of evaluation.

Parentheses may be needed around the child, as illustrated earlier when dropping a unary minus above a child that is a sum. They may also be needed around the dropped element, as illustrated here when dropping a binary addition above the numerator in a fraction. The parentheses ensure that the sum is computed in the numerator before it is divided by the denominator.

Back to top or start of Inserting Elements.

Installation

FormulaForge is currently in beta release and is available only to beta testers who have received a user name and password.

Downloading FormulaForge

Go to formulaforge.com/getformulaforge. Supply your beta user name and password when asked for them. Then click the link labeled Click here on the download page to download a file named InstallFormulaForge.zip.

Installing FormulaForge

Double click the downloaded .zip file to extract its contents. This will create a directory named InstallFormulaForge, which will contain three files and one directory.

  • setup.exe
  • FormulaForge.vsto
  • GettingStarted.pdf
  • Application Files (a directory)

You can move the InstallFormulaForge directory anywhere you like, but you shouldn’t change its contents.

Double click setup.exe and follow the instructions. If your Microsoft Windows installation does not have either the Microsoft .NET Framework 4 Client Profile or the Microsoft Visual Studio 2010 Tools for Office Runtime packages, they will be installed for you along with FormulaForge. These are standard Microsoft software packages that support third party applications. You may have to reboot your machine to complete their installation.

You can delete the InstallFormulaForge directory after installation is complete.

Should you need it, the GettingStarted guide provides more information about the installation procedure.

Running FormulaForge

Start Microsoft Excel, select the Formulas ribbon, and check the box labeled Show in the FormulaForge group to bring up the FormulaForge window.

Uninstalling FormulaForge

To uninstall FormulaForge, select Programs > Uninstall a program from the Windows 7 Control Panel, select FormulaForge in the list of installed applications, and click the Uninstall button just above the list. For Windows XP, select Add or Remove Programs from the Control Panel, select FormulaForge in the list of installed applications, and click Change/Remove in the highlighted area.

Back to top or start of Installation.

Limitations

The current beta release of FormulaForge has the following limitations.

Math View

The Math View uses the web-based typesetting service provided by MathJax.org. Hence the Math View is available only if that service is available.

Array expressions

FormulaForge may display incorrect values for expressions that occur in array formulas or in functions like SUMPRODUCT, which treat their arguments as array formulas.

Notations in formulas

FormulaForge does not recognize R1C1 notations for references.

FormulaView treats identifiers such as ABCD2 and A1234567 as references to cells, rather than as user-defined names, even though they exceed Excel’s limits on the maximum numbers of rows and columns in a spreadsheet.

FormulaForge does not recognize structured references, such as Inventory[InStock], to columns in tables.

User-defined functions

FormulaForge does not include user-defined functions in the list in the Templates Pane of functions available for drag-and-drop editing.

Excel interaction

FormulaForge cannot display formulas in protected worksheets.

Back to top or start of Limitations.

Glossary

Alias
An identifier that can be used within FormulaForge as a name or an abbreviation for an expression. An alias is local to FormulaForge; it has no impact on Excel’s operation.
Ancestor
An element in a tree is an ancestor of another if it is its parent or an ancestor of its parent. For example, in the Tree View, an element representing IF(A1 = 0, B2, C3) is an ancestor of an element representing A1.
Atomic expression
An expression, such as 5, "abc", and A1, that cannot be decomposed into smaller expressions.
Bottom element
An element in a tree that has no children. Bottom elements represent atomic expressions.
Child
An element in a tree that has a parent. In the Tree View, a child appears below its parent. For example, elements representing the expressions A1 and B1 appear as children of an element representing the expression A1 + B1.
Compound expression
An expression, such as A1 + 5 and IF(A1 = 0, B2, C3), that can be decomposed into smaller expressions.
Descendant
One element in a tree is a descendant of another if that other element is one of its ancestors. For example, in the Tree View, an element representing the expression A1 is a descendant of an element representing the expression IF(A1 = 0, B2, C3).
Element
An instance of an expression. For example, the expression A1 + B1 may occur more than once in a formula. Each occurrence is an element of the formula.
Ellipsis
Two or three dots used to abbreviate an expression or a string, as in IF(..., A1, B1) and "This ..".
Expression
A string that can represent a formula in Excel. An expression can be an atomic expression, which cannot be decomposed into smaller expressions, or a compound expression, which can.
Formula
An expression, such as A1 + 2, obtained from a cell in Excel whose contents begin with an equal sign (i.e., from a cell with =A1 + 2 as its contents).
Formula Views
FormulaForge’s Text View, Math View, and Tree View.
Hover
An event that occurs when a user moves or hovers the cursor over some element of a graphical user interface. See tooltip.
Logic Region
The Logic Region for an element in a formula is the collection of logical tests (i.e., first arguments) in all IF functions whose second or third arguments (i.e., its values if its first argument is TRUE or FALSE) are ancestors of the given element.
Math View
A representation of a formula, similar to those found in typeset mathematics and engineering textbooks, that employs standard mathematical symbols and uses standard typographic conventions (e.g, for numerators and denominators in fractions).
Name
An identifier in Excel that represents a cell, range of cells, formula, or constant value.
Pane
A rectangular area in the FormulaForge window that can be resized and displayed or hidden.
Panel
A column of panes in the FormulaForge window. The FormulaForge window has three panels: left, center, and right.
Parent
An element in a tree that has a child. In the Tree View, the parent appears above each of its children.
Reference
In Excel, an identifier for the location of a cell (e.g., A1, $B$35) or a range of cells (e.g, C1:E10, $A:$B, $100).
Selection
The element currently selected by the user. A selection is highlighted in blue in the Math View, Text View, and Tree View.
Sibling
An element in a tree with the same parent as another element. Any two children of a single element in a tree are siblings of each other. For example, in the Tree View, an element representingA1 + B1 has two children, representing A1 and B1, which are siblings of one another.
Stub
A named placeholder for an element in a formula. Stubs (e.g., Stub, number, and value_if_true) appear in red in the Formula Views and surrounded by accent marks (e.g., `Stub`) in the Text Editor to distinguish them from Excel names.
Tooltip
A box of information that appears when the cursor hovers over an element (such as a button) of a graphical user interface.Tooltips provide information about elements in the Tree View and about the cells from which formulas were obtained in the Scrapbook.

Top element
The unique element in a tree that is an ancestor of every other element in the tree. In the Tree View, the top element represents an entire formula.
Text View
A representation of a formula as a string of characters that is recognized, when preceded by an equals sign, as input to Excel.
Tree
A nonempty set of elements, some of which are children of other elements. Each tree has the following properties:

  • Each element is the child of at most one other element, called its parent.
  • The children of each element are ordered from left to right.
  • There is exactly one element in a tree, called its top element, that has no parent. This top element is an ancestor of every other element in the tree.
Tree View
A hierarchical representation of a formula or an expression as a tree.

Back to top or start of Glossary.

© 2012-2013 Stage One Software