DATASET() - function

Building a dataset in a spreadsheet editor like Excel is relatively straightforward. Spreadsheets allow you to place unique functions in each cell and easily reference other cells—regardless of their location in the spreadsheet. Additionally, a spreadsheet offers one-click functionality to extrapolate functions across rows and columns, which is incredibly useful!

See the basic example below for a spreadsheet with 3 columns and 10 rows.

The spreadsheet can be divided into two sections:

1. Base-row
2. Repeating-row

This spreadsheet setup works perfectly when the number of rows in the dataset is fixed. However, when the number of rows is dynamic, creating the dataset in Excel becomes significantly more complex. Exactly this is where we make the difference.

Building a dataset in Convert_

In Convert, we build datasets via functions. While this method is more challenging than building a spreadsheet dataset due to the lack of an intuitive and clear interface, it offers a significant advantage: expanding the number of rows in the dataset doesn't increase complexity.

DATASET() - function

To make creating datasets in Convert more accessible, we have introduced the DATASET function. The DATASET function has the following arguments:

• #Rows: Determines the number of rows in your dataset. You can input either a fixed number or refer to a variable.
• Per column you have the following 4 arguments:
  • Reference: A string identifier you can use in formulas within your DATASET function to reference cells in the "previous row";
  • Value: The function for the cell, similar to what you'd input in a formula element;
  • Reference_n: A string identifier to reference the cell in the "current row";
  • Value_n: The function for the repeating row. This formula can include references defined in the DATASET function. In such cases, wrap the formula in the function() wrapper. Ensure that any reference you use in your function is available (defined before you define your function).

*Datasets with many rows will be extensive since the values for Reference, Value, Reference_n, Value_n must be defined per column.

Example

Data-set helper

Creating data sets remains an advanced feature with inherent complexity. However, we strive to make it as accessible as possible. Please visit our data-set helper by clicking the image below. This tool allows you to set the number of rows and columns, and copy cells from Excel for the initial and second rows. Based on your inputs, it generates the syntax for the data-set function as a formula output. You can copy this dataset to your calculator to view your finished dataset or check for any errors in your original inputs.