How to do formulas?


#1

Hi everyone,
I have a reading log setup in Google Sheets that has a few “sheets” (tables on Airtable) that group information together from all the different sheets (organized by years) so I can see the differences per year. These formulas count how many books per genre, what’s the longest book read, how many total pages read, what’s the most read author and so on and so forth. I can’t get any of the Google Sheets formulas to work in Airtable.
Is there a manual on how to do formulas, especially formulas between tables?
Any help will be greatly appreciated as I feel as if I have no idea what I’m doing here.
Thanks in advance.


#2

Yes, @Francesca_Bensi, there is:

And, importantly, it will be helpful for you if you don’t think of Airtable in the same way you think of Google Sheets. While Airtable’s advertising may be a little misleading, a base in Airtable is not a spreadsheet, and it does not behave like a spreadsheet either. The kinds of formulas you use in Google Sheets are formulas that reference particular cells/rows/columns by reference to where those cells/rows/columns exist relative to other cells/rows/columns.

In Airtable, what look like cells/rows/columns (in AT they are called cells/records/fields, respectively) DO NOT have any spatial relationship to each other - they do not exist in a position relative to other cells/rows/columns. An Airtable base is a database, and behaves far more like a database than it does a spreadsheet. And thus, the kinds of formulas you use, and the ways you use them, correspond far more to databases than to spreadsheets.

Using Airtable will take a little adjusting in the way you think about capturing your data, but once you get your mind around it, I would argue that Airtable will serve you far better for what you are trying to accomplish than your Google Sheets were.

If you have further, more specific questions, feel free to ask. There are a lot of people here willing to help out.


#3

Thanks Jeremy! I think what you are saying makes sense in terms of not thinking of it as cells. I think what I’m trying to accomplish won’t work with formulas, maybe you can tell me?

I have setup the database with a single select for genre, a multiple choice for trope, the date finished reading is a date field, the number of pages or hours of audiobook are also separate columns with the number or time field type. I also set up a different table for each year. If I wanted to create a table that combines the data and gives me a count of how many of each genre per year for example on Google I was calling that column (spacial) and equaling each time the cell in that column matched the genre value count as one, then adding them all up for that sheet. So I think what you’re saying with this being a database won’t be able to apply quite like that here. Is there a way I can see that breakdown from the database without doing a formula? Sorry I hope that was clear. I just want to replicate seeing what gets read the most, and maybe even analyze if a certain genre gets an average higher rating than others.


#4

Absolutely!

Step 1:
Create a field in your first “year” table that is a formula field called “Year”, and has this formula -

YEAR({Date Finished Reading})

(replace {Date Finished Reading} with the name of your actual date field inside {} if I did not get the name right)

This will pull just the year out of your date you enter. You can hide this field later if you want.

Step 2:
Transfer all your records from the other “Year” tables into that first table (ie, combine all your records in one table). Rename that one table to “Books” or something like that - it does not need to be a year-dependent table, because now all of your records are year-dependent records.

Step 3:
Use the Group menu to group by the “Year” field (order from Z -> A if you want the most recent reads at the top):


(as you see from the screenshot, you could take this a step further and break them down into months as well - ask later if you want help with that)

This will allow you to see create summaries by year - the summary bar lets you Sum, Avg, etc the values of any records within the group (Year group) on the fields below - so you could see the Sum pages read by year, for example, and at the very bottom of the page, you can see Sum pages read for ALL years.

Step 4:
Rename that view to “Grouped by Year” or some-such:
image

Step 5:
Make a new grid view to Group by Genre:


(follow grouping steps above, but choose your “Genre” field to group on)

In that “View” you can see your Sum or Avg, etc values by Genre group.

Step 6:
Play around with nesting groups - Group first by year, then by Genre, then by Type (book vs audio) etc…

In Airtable, you want to keep your data as unified as possible - don’t split up records that look essentially the same into different tables (ie, Year). Make the “Year” an attribute within a record. Then, utilize “Views” to filter/group/sort your data as you want to see it.

Happy to answer more questions - keep 'em coming as needed!


#5

Thank you so much! This has given me quite a few ideas to play with.