Skip to content

Using Functions and Formulas

The Row64 evaluation language consists of two components: Functions and Formulas. Functions can be applied on Dataframes, while Formulas work in both Dataframes and Spreadsheets.

Functions

Functions are allowed in Dataframes. At the top of each Dataframe is an editor where you can enter in function commands.

  • Each new function command must begin on a new line.
  • Functions commands are executed in the order they are listed in the editor.
  • Functions commands cannot be nested.

Below is an example of a Dataframe editor with functions. The Dataframe editor is a basic code editor and provides some syntax highlighting to assist with distinguishing different parts of the function commands.

  • Text in bold black are the function commands.
  • Arguments are in gray.
  • Formula-type arguments are in green.
  • Column-type arguments are always inputted using in A1 notation.
  • Comments begin with a pound sign (#) and display in light blue text.

Some Functions, such as ADDCOLUMN, take a formula as an input argument. Formula input arguments are displayed in green.

When you are ready to execute your set of functions, click the RUN button at the top. When your functions have finished executing, the output editor will indicate that the evaluation has completed. The resulting table is displayed in the dataframe.

Formulas

Formulas can be used in Spreadsheet cells and within specific Dataframe functions. The formula language component has extensive Excel formula compatibility and includes additional complex formulas for advanced users.

Using Formulas in Spreadsheets

Spreadsheet formulas are a highly flexible tool for building data summaries that synthesize data within the project. Spreadsheet formulas can be nested and can include sheet link references to other spreadsheet or dataframe tabs.

To input a formula into a spreadsheet cell, simply type = into your selected cell and then type in your formula. Press the Enter key or Tab key to exit the cell and evaluate the formula.

A feature of spreadsheet formulas is the ability to link to dataframe cells. This allows for seamless integration between function evaluation and formula evaluation. In the example below, you can see that, on left pane, we have a spreadsheet that includes a formula that links to a dataframe column in the right pane.

NOTE: For spreadsheet formulas that support inputting link references, you can simply click and drag and cells that you want to input, and the spreadsheet formula will auto-update with the sheet link reference. To select multiple references, hold down the CTRL key before you click and drag. This works for both spreadsheet and dataframe links.

Using Formulas in Dataframes

A select number of dataframe functions also support formula inputs as arguments. Formula evaluation for these dataframe functions differs from the formula evaluation in spreadsheets. While spreadsheet formulas fill the value of a single cell, dataframe formulas apply to an entire column.

The following example shows how you can use a formula in the ADDCOLUMN function to fill values in a new column. The new Column G contains the sum of the values in Columns B and D subtracting the value in Column C. This is indicated by the formula: SUM(B1,D1)-C1.

As with spreadsheet formulas, the dataframe formula notation also takes A1 notation. However, in dataframes, the cell reference automatically increments to evaluate each row. In this case, Row 1 evaluates SUM(B1,D1)-C1, then Row 2 evaluates SUM(B2,D2)-C2, and so on.

In addition to within row evaluation, the Dataframe formula system also allows across row evaluations. The next example shows a 3 period moving average, where evaluation happens across rows. In Column G, Row 3, you can see that this shows the average of B1, B2, and B3. The next Row 4 shows the average of B2, B3, and B4.

Dataframe formulas also allow you to perform operations on the entire column. In the example below, the result in Column G shows the percent of the total value of the column. To apply a reference to the total column, use the A1 column notation. In this example, we take the sum of the entirety of Column B, which is evaluated by SUM(B:B) in the formula.

The dataframe formula system has some differences from spreadsheet formulas. These include the following:

  • Link references must be within the same sheet and do not include the sheet name. You cannot include a reference link to another spreadsheet or dataframe.
  • Link references cannot reference the generating column. In the above examples, we added a new Column G, so the formula cannot include any references to cells in Column G.
  • Error values in TEXT columns are displayed as text.
  • Error values in NUMERIC columns are represented as NULL.