Working with formulas can take your Airtable base from useful to absolutely extraordinary. If you’re not familiar, formulas reference parts of your table, take an action with that data, then display the all-new, updated information. One of the most common uses for formulas is applying math to a field.
That sounds complex but let’s think of some practical examples. You might want to add together the total remaining inventory you’ve cataloged in your base, see an average sales price for items you’ve sold, or maybe you want to budget a percentage of your income for candles.
Whatever your desire, it’s possible with Airtable—and remarkably straightforward. In fact, with Airtable you can use Airtable to execute these numeric functions:
- Return an absolute value
- Return the average of the numbers
- Count the number of numeric values
- Return the largest or smallest of the given numbers
- Add together all the numbers
And that’s just the beginning. You can read about every numeric function available in Airtable formulas here.
Videos more your speed? Watch this step-by-step tutorial for setting up your first numerical formula.
Let’s say you’ve created a budget field in your Airtable base for paid advertising on social media. You have a set budget, but you also want to track overhead—in other words, you won’t always spend the exact budget, but you need to stay relatively close to the allotted dollar amount.
After discussions with your CMO, you’ve targeted a 15% flexibility—in other words, you can spend 15% more or 15% less than your requested budget. And the team needs to know what 15% of every paid social campaign is—at a glance. You also want that 15% to automatically adjust if and when your budget changes.
This is just one of many, many possibilities—but it’s also a great way to wrap your head around how to use Airtable as a calculator.
How to create your formula field
We already have a budget field—and now we need to create an overhead field.
- While viewing your base in Grid view, scroll to the right until you see a “+” on the field header. Click that and add a field name. (We’ll call this one “Overhead” and select the formula field.)
- We know we want to take the value of our Budget record and display 15% of that number—that’s our formula. To do this, we want to multiply our budget by 15% and then display that number.
- You can do so by typing “Budget” or clicking it from the dropdown. Next, type “*” for multiplied by and “0.15.” This will multiply the budget by 0.15, which will display 15%.
- Since we’re displaying a dollar amount (our flexibility in the budget), select “currency” from the dropdown list. Next, select the precision—do you want it to include pennies? Be rounded to the nearest ten dollars or ten thousand?
Best of all, as you update the budget, the “Overhead” pane will update automatically as your budget changes.
---
As far as formulas go, this is just the tip of the iceberg. If you’re interested in learning more, check out the top 10 time-saving Airtable formulas.