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

- Recognizing formulas
- Debugging formulas
- Synthesizing formulas
- Dealing with problematic parentheses
- Clarifying complicated logic

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.

Mathematical definition of 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.

Invoking FormulaForge from Excel’s context menu

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

.

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

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.

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.

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.

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

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

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

Expression | Representation |
---|---|

Product: `x*y` |
$x\; \cdot \; y$ |

Simple fraction: `x/y` , `(x)/y` , `x/(y)` , `(x)/(y)` |
$x\; y$ |

Fraction with fraction in numerator: `x/y/z` |
$x\; y\cdot \; z$ |

Simple power: `x^y` |
$x\; y$ |

Power of a power: `x^y^z` |
$x\; y\; \cdot \; z$ |

Less than or equal to: `x<=y` |
$x\; \le \; y$ |

Not equal to: `x<>y` |
$x\; \ne \; y$ |

Greater than or equal to: `x>=y` |
$x\; \ge \; y$ |

Range union: `x, y` |
$x\; \cup \; y$ |

Range intersection: `x y` |
$x\; \cap \; y$ |

`ABS(x)` |
$|\; x\; |$ |

`EXP(x)` |
$e\; x$ |

`INT(x)` |
$\lfloor \; x\; \rfloor $ |

`LOG10(x)` |
$log10(\; x\; )$ |

`PI()` |
$\pi $ |

`SQRT(x)` |
$x$ |

#### Typesetting technology for the Math View

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`

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.

`-A1^2`

`(-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

Collapsed element

Back to top or start of Tree View.

### Navigating the Formula Views

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

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.

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.

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.

`IF(X1 = 0, IF(Y1 = 0, "Origin", "Y axis"), IF(Y1 = 0, "X axis", IF(Y1 >= 0, IF(X1 >= 0,` |

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

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

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

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.

Double clicked element

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

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
- Icons 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
- Icons for unary operators (
`+`

,`-`

, and`%`

). - Stub
- An 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
- An 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
- A list of names defined in the spreadsheet containing the formula in the Formula Views.
- Selected range
- A 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 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 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 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

- add and delete an element,
- move an element,
- replace an element,
- change an element, and
- insert an element between a parent and its child.

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

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

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

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

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

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

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.

Replacing a childless element by a copy of another 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.

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.

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.

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`

.

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.

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.

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`

.

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.

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.

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 representing
`A1 + 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