Using Datasheets

Learn how to use datasheets to make calculate stuff with use huge amounts of data

Sometimes you want to calculate something which is pretty logical; that can be easily put in a calculation. But sometimes an outcome to a formula is based on huge arbitrary datasets. No worry, we've got you covered!

How do Datasheets work?

Datasheets are a bit similar to Excel, although it's simplified by design. You can create a datasheet via the formula editor and start adding (or copy/pasting) your dataset. You can use the columns or the complete datasheet (matrix) in your calculations.

How to use Datasheets?

You can use datasheets mainly to find an item in a set. In the easiest form this would look like this:

INDEX(DA_A, 3)

This will pick the 4th (3 + 1) item from column A in DA (Datasheet A).

You can also use datasheets to find a value when matching one or multiple conditions:

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

This will find the value in column C on the same row as column A matches 1 and column B matches 2.

Which formulas can be used with datasheets?

These popular formulas can be used with datasheets:

Note that there are other formulas that accept columns and matrices as input. These are just the most important onces.

Example

Let's create 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.

Let's create a formula that finds the proper value:

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

ConvertCalculator is a powerful calculator form builder for your website