Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
M
Madeline_Smith-New Participant
asked in Formulas
Summarize lookup field into categories

Hi all, I’m trying to create a field that will use the data in the field shown to categorize each record into “private only,” “public only,” and “both private and public.” It seems like there should be a way to do this, but I’m stumped! If it’s helpful, the field shown is a double lookup. It is in a “programs” table, each program is linked to funders in the “funders” table, and each funder is designated private or public. I want to see which programs receive exclusively private support, exclusively public support, or both types of support. Thanks!

140
E
N
NSG185New Participant
asked in Formulas
Formula will not calculate with a linked field value

I’m trying to create an order form that calculates price (FORMULA column) by multiplying the SQ. FT. size of an item by the SQ. FT. PRICE of the material. The SQ. FT. column is a simple calculation done based on sizes input into the order form. The SQ. FT. PRICE column is a linked field value pulled in from a different table (based on the material part number). I keep getting #ERROR! on the calculation. The Error appears to be stemming from using a linked field value. Look at the second image below…when I change the formula to use a different value from the same table it calculates without an issue. Is this an odd occurrence or are you not able to use linked fields in formulas?

42
N
K
kxr1mNew Participant
posted in Formulas
Summary of records, help needed

dear airtable community, i would like to create a formula that sums up the size of the identical item. in the screenshot you will see, that every record has only one size, even though its sometimes the identical sneaker. i want it to be: -“dunk low disrupt 2 pale ivory black” : 39 EU - 8W US [case 1: only one record with the title - nothing should be changed, just display the available size] -“dunk low light violet (venice)” : 38 EU - 7W US [case 2: multiple records with the same title, but with the identical size - only one record should include the size they all share and the others should be empty] -“dunk low two tone grey” : 38 EU - 5.5Y US / 44 EU - 10 US [case 3: multiple records with the same title, but with different sizes - only one record should include all the sizes which are seperated with a " / " and the others should be empty. if anyone of you could help me with this challenge i would highly appreciate it, i hope i described the requirements understandable, if not feel fr

00
K
T
Thibaud_DonzierKnown Participant
posted in Formulas
Formula to convert Geographic coordinates

Hi everyone, I am looking for a Formula to convert Degrees Minutes Seconds (DMS) Geographic coordinates to Decimal Degrees (DD). Right now I have one column with this format : 57°34’57.8"N 6°19’30.9"W and I will need to split in two columns to have something like that : Latitude : 57.582722 Longitude : -6.325250 in order to use this mini estension : Embed Map of Airtable Records on Your Website(Embed Map of Airtable Records on Your Website - miniExtensions) Embed Map of Airtable Records on Your Website - miniExtensions If anyone has done this, thank you in advance ! My first topic has been closed but it wasn’t resoled. Formula to convert Degrees Minutes Seconds to Decimal Degrees Formulas Hi everyone, I am looking for a Formula to convert Degrees Minutes Seconds (DMS) Geographic coordinates to Decimal Degrees (DD). Right now I have one column lwith this format : 57°34’57.8"N 6°19’30.9"W and I will need to split in two columns to have something like that :

60
T
Cole_Wedemeier
Cole_WedemeierInspiring
asked in Formulas
How to make a nested IF statement evaluate ALL nested statements

Hello! I have a formula with nested IF statements that works except that what I realize I actually need is to not have the logic stop when it finds 1 of the IF statements true, and actually continue on to validate the remaining IF statements. It very well could be that IF statements are not the right way to reach my goal, so I’ll also explain what my goal is. We have several ways that our participants in a course interact with us. These include: Critiques of work Q’s for Coaching submitted *** Last Lesson in Course** Community Engagement Milestone Reached Each of these has an Airtable field with the most recent date they’ve interacted with us in the respective category. What I want to do is create a formula that evaluates each of the 5 dates and populates the most recent of the 5. When I’m doing it with nested IF statements currently, if it finds that 1 fields date is greater than the compared fields date, it stops there and populates that date without validating if the others are a

3724
Cole_Wedemeier
R
Roehner_RyanNew Participant
asked in Formulas
Create due date from checkbox selection

I am completely new to this and appreciate any help. I would like to be able to create a due date 4 weeks from when the clients select checkbox is ticked. This is where I am at and I can’t seem to get it to work. IF( {Client Selects}=“1” (DATEADD(TODAY(4, “week”) ) ) )

72
R
M
Melissa1Inspiring
asked in Formulas
Change a comma to an addition

Hello, Is there a formula to modify the comma by an addition? I would like to have the total automatically. Thanks for your help

32
M
S
Sam_ArenNew Participant
asked in Formulas
Bullet Point list of Roll Up

Hey Airtable Community, I am trying to create a roll up formula combination that is taking a list of values some of those are blank and only outputs a bulleted list of the filled in values. For Example: Right now I have tried: IF(COUNTA(values) >= 1, "• “) & ARRAYJOIN(values,”\n• ") This still outputs if there are empty values in the roll up. I have tried to also use the arraycompact() but then I have no way to use Substitute() to split up the values into a bulleted list. Any help will be greatly appreciated! Thanks!

644
S
B
Bobby_LahiereNew Participant
asked in Formulas
Converting hours:minutes:seconds to total minutes

Is there any way to convert hh:mm:ss to total number of minutes like you can in Excel (Excel: Convert hours and minutes to minutes | CyberText Newsletter)?

823
B
S
Shiko_Ben-MenahNew Participant
posted in Formulas
List field names for non-zero values in record

I’m trying to list the field names for which non-zero values appear in each record. I would have used a combination of Match and Index in excel… What would be the solution in Airtable?

11
kuovonne
L
Lauren_PreteParticipating Frequently
posted in Formulas
IF+AND Statement w Exclusion Conditions

Hi all, This has become a bit of a brainteaser for me and was wondering if anyone much more proficient than I could have a crack! Trying to formulate a field to return COST PRICE as a NEG value when the following options are true: There is a value in COST PRICE($) column AND the REASON column value = “DEFECT” Further, I need help creating a condition that applies the above only when the following is true for a certain supplier: The SUPPLIER column value = VR The GAME RETURNED column (checkbox) = TRUE Essentially, I want to have a negative cost-price returned in a separate column if the REASON is listed DEFECT and the COST PRICE is AVAILABLE, but for a particular supplier (VR) I only need this negative cost-price value returned when the GAME RETURNED column is checked. Current formula is: IF({COST PRICE}=1, AND({REASON}=“DEFECT”), {COST PRICE}*-1) and is returning cost-price for every reason code listed, instead of just DEFECT. Also doesn’t factor in the VR supplier condition. Wonde

95
L
S
StyleFinder_RocNew Participant
posted in Formulas
Concatenate two fields into a date

Using the web clipper app, which doesn’t support the Date field. I can create a Month and Day field in the Clipper in which the user inputs the values. Can I then concatenate the Month and Day into Date type which results as an example 4/14/2022? I appreciate any help. Cheers

191
kuovonne
N
Noah_ColemanInspiring
asked in Formulas
Calculating intervals for repeating events

I have a base to keep track of tasks that need to be repeated every so often. I’ve been able to achieve this with the following formula, checking the modulus between today and the start date to see if it matches up with the repeat interval: IF(MOD(DATETIME_DIFF(TODAY(),{Start Date},'days'),{Repeat Interval (days)})=0,"Today is the day!","Not yet...") When the formula resolves to “Today is the day!”, an automation is triggered that creates an instance of the task on the to do list. The limitation to this is I have to put my repeat interval in days. How would I go about building a formula that does the same thing, but could handle different interval inputs, like every 2 weeks or every 18 months? I think I would need a number field and an interval field (e.g. 3 and "weeks"), then maybe a switch statement to handle the different cases? If anyone has any examples or could point me towards a tutorial, I would be most grateful.

91
N
1
1F408New Participant
posted in Formulas
Set up linked record for variable costs

I have a base with labor rates, which can vary depending on retail / wholesale, etc … screenshot 1 That table links to another table in the same base, which has a variety of categories. Might price category 1 at List Price, category 2 at 10% off, category 3 at cost +10%, etc … screenshot 2 Is there a way to have one “base rate” amount, and then have the variable rates calculate based on that? Right now, I just input them each manually. Would be nice to change the “base rate” and have all the variables automatically update. I realize I could do this in multiple fields, but that won’t give me the ability to have a linked record selection? Thanks in advance

30
1
S
Sebastien_LEBLANew Participant
asked in Formulas
Nested IF with OR

Hi everyone, I’m trying to set a field value based on a formula : IF( OR( {Commandes} = BLANK(), {Date_heure_RDV} = BLANK()), "", CONCATENATE({Commandes} , " - ", DATETIME_FORMAT(Date_heure_RDV, 'M/DD/YYYY H:mm')), IF( OR( {SILGAN BOL} = BLANK(), {Date_heure_RDV} = BLANK()), "", CONCATENATE({SILGAN BOL} , " - ", DATETIME_FORMAT(Date_heure_RDV, 'M/DD/YYYY H:mm')) ) ) But when saving the formula… it just keep the first part. Is it possible to do this kind of nesting? Thanks for your help

143
S
M
Mariane_MarketiInspiring
posted in Formulas
Consolidate data from line item table to get total sale per order

Hey guys, I’m not sure how to do this, but I have a table where all sales are entered in an order line item format and I have a column for the invoice number field. I can’t change that because of how that’s exported from different distributors. How do I sum up the $$ for each order line item based on invoice number so I can get a roll up and pull a report on average sale per customer? thank you!

20
M
F
Francesco_PoggiNew Participant
posted in Formulas
Formula field non editable - WHY -

Excuse me but I’m trying to create a formula field but it always turns out not editable, why? I’m looking all over for the solution but can’t find anything.

61
ScottWorld
T
Toby_WatkinsNew Participant
posted in Formulas
Calculating the difference between two dates in Number of Weeks

Hi, I am struggling with a date formula: I want to calculate the number of weeks that a record will be early or late based on three variables. The Delivery Date, The Ex Factory Date and the Freight method. The formula below is the equivalent of what i would use in excel. I calculate it in days and then divide by 7 to show the number of weeks. The formula doesn’t seem to work in Airtable and i cant work out why this is. I was wondering if anyone would be able to give me some advice on this? =IF(OR(“Freight” = “Sea”; Freight =“Air”);IF(“Ex Fact” >0;”DC Date” - “Ex Fact” - 49)/7; IF(“Ex Fact”>0;”DC Date” - “Ex Fact” -10 )/7) In words this is what i’m trying to calculate is the following - if the freight method is “Sea” we add 49 days (7 weeks) to the Ex Factory date I want to know the difference between this date and the planned DC date. If the freight method is “Air” we add 10 days to the ex factory date. We want to know the different between this date and the planned DC date. Than

40
T
R
Robbi_BehrNew Participant
posted in Formulas
Formula for Calculated Dates and Set Dates and no dates

I have set up a series of tasks. Some of the tasks have a regular due date (DUE DATE), some of the tasks have a due date that is calculated (CALCULATED DUE DATE), and some of the tasks need to be done as soon as possible (AS NEEDED). I would like to make a formula that says IF the due date is not empty, return the DUE DATE. but if the due date is empty, look to see if the CHECKBOX is checked. IF the checkbox is checked, return the words “ASAP” but if the due date is empty AND the checkbox is not checked, return the CALCULATED DUE DATE. As an added bonus, I would like the dates in the final output to be written out in the format “Monday, 3/14/22”. As an added added bonus it would be cool if those dates could be workdays. I think this is getting complicated because some values are dates and others are words but I can’t quite figure out. Attached is an image of what I want to do with what info.

82
R
P
Pornwalai_RansoNew Participant
posted in Formulas
How to sort Date and Time in Text?

I synced my data with Jotform and the Date appointment is come in Text so I can’t find the way to sort it by Date. not sure there are anyway to help me with this. Thank you.

103
kuovonne
C
Christopher_MurParticipating Frequently
posted in Formulas
IF Single Select then Change or Switch Single Select

Hi There, I have two single select columns and I would like some of them to follow one another. Say for instance if I select “ON HIRE” in one single select column I would like the other single select column to automatically select “ON HIRE” as well? can this be done, any help would be greatly appreciated.

163
V
J
John_CampbellNew Participant
posted in Formulas
Getting random form data in multiple fields to show up in a primary field?

Hi, I am relatively new to airtable. I have a form asking people for their top 3 of something (let’s say food) and they input it in separate fields under primary, secondary and tertiary. That is all in one record for one person. Now I would like to list all of those favorite foods (maybe a couple hundred) in the primary field of a different table so I can present a view of all of the favorite foods. I haven’t been able to figure it out. Any help or direction would be appreciated! Thanks!

42
J
J
James_BolducNew Participant
asked in Formulas
How to write the equivalent of Excel's PMT formula in AirTable field that produces the equivalent result in Excel?

How do I write the equivalent of Excel’s PMT formula in AirTable field that produces the equivalent result in Excel? In Excel it’s =PMT(rate,nper,pv). In Excel I adapted it as =PMT(rate/12,nper12,pv)-1** which also produced the monthly payment as a positive value. I found this in the community: ({Monthly Interest Rate}*{Loan Amount})/(1-POWER((1+{Monthly Interest Rate}),-{Number of Monthly Payments})) I adapted it to this: ({Monthly Interest Rate/12*{Loan Amount}})/(1-POWER((1+{Monthly Interest Rate}/12),-{Number of Monthly Payments))/10 It was close but not exact and the small difference becomes a much larger difference in another calculation. Is there a way to do this?

764
J
R
RJ_SouthamParticipating Frequently
asked in Formulas
Check a date against a static date

Wanting to check Date of birth against a static date, if true emoji if not blank. If(DOB<=12/31/2017, :star: ," ")

96
R
T
Test_App_CreatoNew Participant
posted in Formulas
Populate the 26 records with the Alphabet automatically

Hi Everyone, Is there a Formula to populate the Alphabet Automatically. I have created a Auto number column and created 26 records and I need to populate the Full Alphabet Automatically. Is this possible or not? Thank you

176
Kamille_Parks11

Badge winners

Show all badges
Terms & Conditions