# Using Formulas

## Using Formulas

Formulas provide the ability to analyze your data in powerful ways.

### Using the Formula Editor

The formula editor is located at the top of a worksheet within a workbook. Select a column on a worksheet and apply a formula with supported Datameer functions and operators.

Formulas can be added manually or using Datameer's Formula Builder. An auto-complete feature gives you the ability to visualize and complete formulas efficiently.

**As of Datameer X 7.2**

The formula editor has multi-line functionality. Add more than one line when writing a formula so that it is easier to both read and write long and/or nested formulas.

Add additional lines by pressing **Enter **on your keyboard while in the editor**. **Submit your formulas by clicking the **checkbox** on the right of the editor or using the keyboard shortcut **⌘-Enter** (mac) and **Ctrl-Enter** (win).

The multi-line editor can be expanded or collapsed by clicking and holding the edge of the formula bar and dragging your mouse.

#### Referencing worksheets and columns in the formula editor

A formula can only reference columns from it's own worksheet and one other worksheet in the workbook. Referencing columns from different worksheets is possible after a join sheet has first been created.

Syntax for referencing a worksheet and column | ||

Syntax | Example | |
---|---|---|

Worksheet | # | #apache_log |

Column | ! | !remoteUser |

Worksheet and column | #apache_log!remoteUser |

#### Writing a function in the formula editor

Datameer's supported functions.

Enter a function name into the formula editor (case insensitive) with variables separated by a semicolon within parentheses.

Example: | Result explained |
---|---|

GE(#apache_log!status;400) | The Greater Than or Equal To function is being used comparing the "status" column from the "apache_log" worksheet |

#### Writing nested functions in the formula editor

From the formula editor, you can enter functions and operators within functions.

Example | Results explained |
---|---|

IF(EQUALS(#apache_log!status;400);"Bad Request";"other") | The IF function is being used with the Equals function as one of the variables. |

### Creating formulas using the formula builder

You can create formulas on worksheets that are editable. When you click the data area of a column that has a formula associated with it, the formula displays above the workbook in the *Fx* bar. You can also create a new sheet and create formulas that reference fields from other sheets. Click the * Fx * button on the formula line to display the formula builder. (

**As of Datameer X 7.2**, the formula builder is located in the worksheet inspector.)

To create a formula using the Formula Builder:

- Click the
**Fx**button on the formula line to access the formula builder. - Select the categories in the left column to choose the type of function.
- Choose a function from the list on the right.
- Enter the argument or arguments shown for that function. You can click a sheet and select a column.
- Click the
**Plus**icon to enter additional arguments (if supported). - Click
**OK**.

The resulting function displays next to the fx icon. To learn more, see using the Formula Builder.

To use operators in a formula:

- Click the data area in a column and the current formula if one exists displays above the workbook.
- Create or edit the formula using operators such as < or > or <= and press Enter.

To use regular expressions in a formula:

- Click the data area in a column and the current formula if one exists displays above the workbook.
- Create or edit the formula using regular expressions press Enter.

See Using Regular Expressions to see some examples. Datameer X offers an API to extend the built-in formulas. See the Developer's Guide to learn more.

### Editing formulas

To edit formulas:

- Click the data area in a column and the current formula displays above the workbook.
- Edit the formula and press Enter.