The Formula Builder lets you easily create formulas by first clicking on a category and then selecting the appropriate function. By clicking on a function, the Formula Builder provides a description of the selected function along with the required arguments and the types of data supported (i.e. integer, date, string, Boolean). After choosing which function to use, you then need to enter the arguments into the function. You can either select the column in the workbook, or you can type the column reference into the Formula field at the top of the workbook beside the fx symbol.
To Show Me How to create a formula , see Creating a Formula Using the Formula Builder.
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
- Click on an empty column to display the Formula Builder.
- 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 the Help link to see the online Help with examples.
- Enter the arguments as shown in the Formula Builder window. Just click the column that contains the desired data. The types of arguments required are displayed next to the arguments' names.
- 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 now shown in the column chosen in step one.
Editing a formula
- Click on a column in your workbook which 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.
To Show Me How to edit an existing formula, see Editing Formulas.
- The results are now shown in the column chosen in step one.
Tips for working with formulas
- You do not need to enter functions and arguments into the Formula bar per hand. If you are editing an existing formula, click the fx symbol to bring up the Formula Builder.
- All Datameer functions have the following syntax FUNCTION NAME ( argument1; argument2; … ). 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.
Returns the absolute value of the values in column B of sheet1.
SUM (#B; #D)
Returns the sum of the values in column B and D from the current workbook sheet.
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 compbine 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, it is also possible to use common operators, e.g. < (less than), + (plus), or && (Logical AND). To learn more, please refer to Using Operators.
- An error message will appear 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, i.e wrong path, wrong type of data type, will turn red, or the word <error>, will appear in every cell. If you move your mouse to a cell in that column, an error message will hover above the cell.