Using Datasheets

Learn how to use datasheets for calculations that depend on large amounts of data.

Sometimes you need to calculate something that can be easily put in a calculation. But sometimes the outcome to a formula is based on larger datasets. Don't worry though, for these use cases we've also got you covered!

How do Datasheets work?

Datasheets are similar to Excel-sheets and Google-sheets. The difference is that datasheets are simplified by design. You can create a datasheet by expanding the formula editor and clicking on the "Add datasheet" button.

Adding datasheets

For the datasheet itself, you can copy/paste your data or add values manually. When you have setup your datasheet, you can use the complete datasheet (matrix) or specific columns in your calculations.

How to use Datasheets?

The strongest use case for datasheets is to find an item in a dataset. This use case can be facilitated with multiple functions (INDEX, VLOOKUP, etc.). A simple example of retrieving an item in a datasheet would look like this:

INDEX(DA_A, 3)

The INDEX-function will pick the 4th item (3 + 1, since datasheets start from 0) from column A in Datasheet A, which is referenced as DA.

For example Say your result depends on two inputs, in this example two option questions. For this, you can build a datasheet, with one question representing the x-axis (column numbers) and the one question the y-axis (row numbers). You can do this with the INDEX-function.

Datasheets example

Be aware that the INDEX starts from 0. When either the row indicator or the column indicator is 0, the formula will return the row/column label.


A second strong use case for datasheets is when you want to find a value that is matching one or multiple conditions.

FINDIFS(DA_C, DA_A, 1, DA_B, 2)

Or

FINDIFS(DA_C, DA_A, QA, DA_B, QB)

The FINDIFS-function finds the value in column C of datasheet A DA on the row where column A matches the static value 1 or the value returned by QA and column B matches 2/QB. This is well explained in the example below.

FINDIFS

What functions can be used with datasheets?

These popular functions can be used with datasheets:

Note: there are other functions that accept columns and matrices as input. The functions above are the most common ones.

Example

Below an example to show how to use a datasheet properly. Let's assume we have the following datasheet (DA):

A B C
1 1 50
1 2 100
2 1 125
2 2 150

Column A stands for bedrooms, column B bathrooms and column C is the price of cleaning. You have two questions (QA: Bedrooms, QB: Bathrooms) and want to calculate the cost of cleaning. Basically you want to find the item in column C under conditions A and B. So 1 bedroom and 2 bathrooms would be $100 and 2 bedrooms and 1 bathroom would be $125.

The formula that finds the proper value is the following:

FINDIFS(DA_C, DA_A, QA, DA_B, QB)

Formula breakdown:

  • DA_C : column where to find the item, in this case datasheet A, column C
  • DA_A : column where to match the first condition, in this case datasheet A, column A
  • QA : first condition, which in this case is set by the value of QA instead of a static value
  • DA_B : column where to match the second condition, in this case datasheet A, column B
  • QB : second condition

Please visit the formula documentation to see more examples on how to apply datasheets.