# Using the Formula Builder

After creating a workbook and importing a data source, you are now ready to start analyzing the data present in your workbook.

The **Formula** **Builder** lets you create formulas by first clicking on a category and then selecting the appropriate function. When you click a function, the **Formula** **Builder** provides a description of the selected function along with the required arguments and the types of data supported (such as integer, date, string, Boolean). After choosing which function to use, you then need to enter the arguments into the function. You can enter the column from a worksheet in your workbook by adding it into a value field in the inspector, or you can type the column reference into the **Formula** field at the top of the workbook beside the *fx* symbol.

If you are familiar with Excel, you are probably interested in the differences between a Datameer workbook and Excel - Tips for Excel Users highlights these differences.

## Creating a Formula With the Formula Builder in 7.1

Click the Fx button in the formula bar and Datameer's Formula Builder pops up.

- Select a category from the column on the left and select a function from the list on the right.
- Read the description for the selected function at the bottom of the
**Formula****Builder**, or you can click**Help**to see the online help with examples. - Enter the arguments as shown in the
**Formula****Builder**. To do so, click the column that contains the desired data. The types of arguments required are displayed next to the arguments' names. You can also enter a null argument by typing in*null*, all lower case. - If the function supports multiple elements for a single argument, a
**+ (plus)**button is available. Click to add additional elements. - Click
**OK**to finish entering the formula. The results are shown in the column selected from step one.

The **Formula Builder** can store up to 5000 characters. If you build a larger query outside of Datameer then paste the query in, the builder accepts it. However, if you edit the query, the builder only displays the first 5000 characters.

## Creating a Formula With the Formula Builder as of 7.2

**As of Datameer 7.2**

- Access the formula builder by opening a workbook, click within the column you would like build a formula, and open the
**Fx**tab in the workbook inspector. - Select a category from the column on the left and select a function from the list on the right.
- Read the description for the selected function at the bottom of the
**Formula****Builder.**Click**Online Documentation and Samples**for additional information. - Enter the arguments as shown in the
**Formula****Builder**. To do so, type the column that contains the desired data. The types of arguments required are displayed next to the arguments' names. You can also enter a null argument by typing in*null*, all lower case. - If the function supports multiple elements for a single argument, a
**+ (plus)**button is available. Click to add additional elements. - Click
**Add to Formula**to finish entering the formula. The results are shown in the column selected from step one.

## Editing a Formula

- Click a column in your workbook that already contains a formula.
- The formula is displayed in the
**Formula****Bar**- beside the*fx*symbol at the top of the workbook sheet.

- Edit the formula as appropriate and press enter.
- The results are now shown in the column selected in step one.

### Tips for working with formulas

- You don't need to enter functions and arguments into the
**Formula**bar by hand. If you are editing an existing formula, click the*fx*symbol to open the**Formula****Builder**.

All Datameer functions have the following syntax: Function Name(argument

_{1}; argument_{2}; …). You can refer to a column in the current sheet, to a column form a different sheet, or manually type in a number to be used as an argument.Function

Example

Result

ABS

ABS(#sheet1!B)

Returns the absolute value of the values in column B of sheet1.

SUM

SUM(#B; #D)

Returns the sum of the values in column B and D from the current workbook sheet.

GT

GT(#C; 5)

Returns true if the value in column C of the current sheet is greater than 5 and false if it is less than 5.

- You can also combine functions in the
**Formula**bar. If you need the current time in milliseconds, then enter*TIMESTAMP(NOW())*into the formula bar. The current time in milliseconds is returned.

- Instead of using a function from the
**Formula****Builder**, you can use common operators, such as < (less than), + (plus), or && (Logical AND). To learn more, refer to Using Operators.

- An error message appears if there is a syntax error in the formula or if you try to use the wrong kind of data specified by a function.

- A column that contains an error, such as a wrong path or wrong type of data type, turns red, or shows the word <error> in every cell. If you move your mouse to a cell in that column, an error message appears above the cell.

#### Quote characters

Quote characters signify text constants in our formula expression language. Datameer allows for both single and double quotation marks to be used as quote characters.

In this example, both single or double quotes produce the same outcome.

=SUBSTITUTEALL("text";"replacement") =SUBSTITUTEALL('text';'replacement')

Having two different quote characters can be useful to avoid having to use escape characters.

In the following example, all formulas have the same outcome, as both single and double quotation marks can be used as quote characters, users can create formulas that are easily readable.

=SUBSTITUTEALL("\"";"'") =SUBSTITUTEALL('"';'\'') =SUBSTITUTEALL('"';"'")