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:
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:
- FINDIFS (Works the same as SUMIFS but returns only the first match)
Note that there are other formulas that accept columns and matrices as input. These are just the most important onces.
Let's create 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.
Let's create a formula that finds the proper value:
FINDIFS(DA_C, DA_A, QA, DA_B, QB)