Sometimes you need to calculate something that can easily be put in a calculation. But sometimes, the outcome of 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.

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:

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.

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.

What functions can be used with datasheets?

These popular functions can be used with datasheets:

• CEILING
• FLOOR
• COUNTIF
• COUNTIFS
• FINDIFS (Works the same as SUMIFS but returns only the first match)
• INDEX
• SUMIF

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

Example

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

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: