Formulas

Hi I have a lookup field that is displaying values from a multiple select field, which I have converted into a comma-separated format. Let’s say the values in that field (field A) are: Cat, Dog, Antelope, Lion I have another field (field B) that has the possibility of having at least some of the same values as the first field. It might look like: Lizard, Elephant, Cat, Eagle The order of the variables is, well, variable because the user may enter them in whatever order they wish. What I need to do is evaluate whether any of the terms in field A match any of the terms in field B. In this case, it would return true because ‘Cat’ appears in both. Thanks!

Is it possible to sort records numerically in a roll up field?

Hi, I am trying to generate a “Date” based on the month input available in my airtable. I tried using the If function, however, the result is not being recognised as a date. A little more information on my use case. I’m creating a database from a form that asks people to enter their preferred month, these months are provided as dropdowns. So, for example, if my table has October as a Month - I want to be able to create an additional column that tells me the first date of the months, i.e October 1, 2021, should be the value. Any ways to do this?

I’m working on a table where I have different currencies (in separate columns) and need to convert them all to one currency. I have a column marking the currency used and so the conditional if statement uses this to have a different calculation for each statement. However, for some reason, the formula doesn’t work but it does not return an error, it simply returns a blank Cell, so I have no idea what I’m doing wrong. Please HELP. IF({Currency} = “MXN”, {Amount in MXN}/{FX}, IF({Currency} = “EUR”, {Amount in EUR}*{FX}, IF({Currency} = “USD”,{Amount in USD}))) I know about the Currency converter app, but due to the Terms and conditions/privacy settings, I can’t use it.

Its probably a simple formula…but here goes… I have a record in airtable that looks like this: The A, B, C represent the responses to survey What I want to do is COUNT the number of each letter - so for example A = 3 B = 1 C = 2 E = 1 I have got as far as: IF({Col1}=‘A’,1,0) and that returns the 1 (in this example) What does the formula look like to check in Col2 - Col7 contains ‘A’ and then give me the total of 3? I hope that explains what I am trying to do clearly :grimacing: :nerd_face: Thanks in advance.

Consulta: Si tengo un campo donde ingreso la fecha de nacimiento de una persona, cómo debo formular otro campo para que me devuelva la edad de esa persona y que se actualice automáticamente cada vez que consulte la tabla. Gracias a todos!

Hi, Is there in Airtable a (cumulative) normal distribution formula or work arround besides writing the full formula ? ie : Excel norm.dist function equivalant with cumulative option set as true. Thanks

I have 21 lookup fields that are used by different team members, which may or may not be filled in for a given Airtable record (i.e., one record might have lookup items tagged in 3 of the fields, another record 5 of the fields, yet another just 1, and so on). I’d like to summarize, with a formula field, all of the lookup items tagged for each record across these 21 fields. When I just use an & operator, I’ll end up with something like this, if the record has items tagged in more than one of these 21 fields: Tag 1A, Tag 1BTag2A, Tag 2B, Tag 2CTag 3A …so I want to add a comma and space separator between the tags from each field, but only when values are actually present in any of the fields so as to not have redundant commas. I tried doing something like this: {Field 1} & IF(AND({Field 1}, OR({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7}, {Field 8}, {Field 9}, {Field 10}, {Field 11}, {Field 12},

Hi community, I need your help. There is something i don’t understand with the formula bellow : IF( SEARCH("Jungle", {Order Line Items}), "Jungle", IF( SEARCH("Baby Plantes", {Order Line Items}), "Baby plante", IF( SEARCH("Pets Friendly", {Order Line Items}), "Pet's Friendly" ) ) ) I want to write a 4th condition but it never work. What is the importance of the space between the condition and the “)” ? How can i built easly this type of formula ? Thank’s a lot, Matthieu

I have 3 columns that have data, and for any given record, only 1 of those columns will have data. I’d like a 4th column to populate the data that’s in whichever of the 3 columns has data. Examples: Column A = Blank, Column B = Blank, Column C = ABC, then Column D = ABC Column A = XYZ, Column B = Blank, Column C = Blank, then Column D = XYZ Column A = Blank, Column B = MNO, Column C = Blank, then Column D = MNO I cannot for the life of me figure out an IF/AND or a nested IF statement(s) that will make this work. Is this at all possible? Here’s a snipit of my table where I’d like Interaction ID to handle what Column D in my previous explanation would have. I appreciate any insight. Thank you!

Hello. I am trying to create a set of tables where our employees can fill a form to update the status of an order, and the latest status gets updated to a table which contains all orders. I have a table which contains details about orders, of which one of the fields is ‘status’. I have created another table with a form view, which has a single selection dropdown menu with different statuses of the order such as ‘Department 1’, ‘Department 2’, and ‘Department 3’. When an employee fills this form, they select the Order ID from a linked field (which is linked to the orders table), type in their name, and select the current status from the drop down. I would like only the latest entry from this Form Entries table to be visible in the status field of the ‘Orders’ table for that respective order. Any suggestions on how. to make this work? I would greatly appreciate any help you could provide. Thank you!

The setup: I have one table which is used to schedule marketing emails (each record contains a single date). I have another table which is used to schedule multiday promotions (each record contains a start date and and end date). What I’m looking for: For each record in my email-scheduling table, I would like a field that links to the records of all the promotions from the promotion-scheduling table that are ongoing during the day the marketing email is scheduled. Any help would be appreciated. Thank you.

I’m trying to combine multiple values from a multiple fields. For example. Column A: Type A, Type B, Type C (multiple values in a single cell) Column B: Option1, Option2 (multiple values in a single cell) Result: Type A - Option1, Type A - Option 2, Type B - Option1, Type B - Option 2, Type C - Option1, Type C - Option 2 Is this possible? Please help. Thanks!

I am trying to concatenate two columns, a percentage (based on a formula) in one column and its associated product in the other column. When I concatenate, the percent is converted to a decimal. I know to multiply this result by 100 and add ‘%’ to get the result I want. And it works for most values, but some of the decimals turn out like this: 15.920000000000002%. How do I get all resulting decimals to show as XX.XX%?

I have several tasks where a date field is filled for whatever date that task is completed. I tried to build a formula where if no date field is blank, it sets the date to TODAY() so that I can know when everything is done. It seems like this formula keeps re-updating to whatever today is everyday. If all the tasks were done Tuesday, it still keeps changing to whatever today is in that IF(***, TODAY(), BLANK()) statement. Am I doing something to cause the table to think these values are changing or do I need to use an automation trigger to accomplish this?

Hello, I’m trying to create a formula to return a value of “early deadline” if a field I have named {Application Date} contains a date less than or equal to 12/15/21. I’d also like to return a value of “Regular Deadline” if the date in the Application Date field is after 12/15/21. This is a variation of the the formulas I’ve tried: IF ({Application Deadline} <= (12/15/21),“Early Deadline” I’m wondering if I need to add anything to qualify the 12/15/21 date. The date functions I’m finding look like they compare dates in two fields on the table, where my issue is that I only have one date on the table and am comparing it to another date that is not on the table. When I try to save my formula I get an invalid formula error. Thanks in advance!!

Hi There, Apologies as I am very new to airtable. Essentially, I have a column which contains a series of numbers which indicates how many businesses make it through a particular process. This number automatically updates based on my input in other areas of the table. What I would like to be able to do is have the success rate automatically update in the next row. I can do it on excel, but with airtable being a database I am not sure how to go about doing this. In excel it looks like this : How can I do this in airtable? The point of this is because I would like to then create a chart which keeps updating with this information, showing how many as a percentage make it through each stage. Thanks L

Hello! I am trying to find a formula that will extract a customers name and address from a body of text and create new single line text fields with this data. The data is coming into airtable via an email automation, and the body of the text data coming in is always consistent in format, with four pieces of data that change, the name, address, market, and URL for the customer. There are other keywords within the body that could be used to identify the before and after for the desired text - but the left, right, and mid formula’s will not work because each body will have varying lengths based on the customers info.

Greetings All. I am new to Airtable, but getting around pretty well. I have a Base with multiple tables. I am trying to automate Prospect tracking as much as possible. I have a situation where when I enter a Zip Code I want it to populate a District, Regions, POC Name, POC Email, and POC Phone. I was able to do that with Zips I coded with a linked table and associated lookups. However, I had to restructure the table and index by District rather than zip. I just cant figure out how to do a sort of VLOOKUP function based on Zips in Airtable because the zips are no longer the index on the table. So, I figured I could use formulas instead. This is the full text of my formula: “If the value from the “Zip Code” column in the “Prospects” table appears in the “Zips” column on the “List of Districts” table, then enter the value in the “District” column of the same “List of Districts” table.” My issue is I cannot write the formula to save my life!! Is this possible? I hav

Hello community, I’m trying to average geographic coordinates. First I split the coordinate into lat & lon fields, for example: 41.3310413 and 19.7828039 In Google Sheets I’m using average() with correct result In Airtable the rollup with average() fails with NaN Does anyone know another way of averaging coordinates? PS: Also tried to remove the dot with SUBSTITUTE(Lat,".","",1) but am getting an error.

I came up with the following formula. IF({Registration date}=BLANK(), ‘’, SWITCH({PLAN}, ‘A’, SWITCH({TYPE}, ‘1’,DATEADD({Registration date},30, “days”), ‘2’,DATEADD({Registration date},30, “days”), ‘3’,DATEADD({Registration date},30, “days”) ), ‘B’, SWITCH({TYPE}, ‘2’,DATEADD({Registration date},30, “days”)), ‘3’,DATEADD({Registration date},30, “days”), ‘5’,DATEADD({Registration date},30, “days”) ) ) ) Depending on the PLAN and TYPE, we want to return the value of {Registration date} plus the number of days. And if {Registration date} is blank, I want the value of the formula to be blank as well (to avoid #ERROR). However, the returned value is not recognized as a date and is displayed as follows 2021-12-17t00:00:0.000z If I remove the IF statement at the beginning, it is recognized as a date format. Is there any way to resolve this?

Hello, I am still very new to Airtable so maybe this is easier than I think but would still like help. In general, I am looking to write a formula so that when a field (that is a single select option) is selected to “deleted” it will then clear many fields (that are multiple select) of all of their data. So in the picture included below, when the field Deleted is selected to “Deleted” I want all of the data in the other rows to be cleared and back to like nothing had been selected. How would I go about this? Thanks

Hi Currently, I have 3-columns Due date (date field) Overdue (formula) IF(IS_BEFORE({Due Date},TODAY()),“ :rotating_light: OVERDUE :rotating_light: ”,"") Due Now (formula) IF(IS_SAME({Due Date},TODAY()),“ :gift: NOW :gift: ”,"") I would love to see both results/formulas in the OVERDUE column. Then it is not necessary for both columns. That said, I am not finding the right way of merging these two formulas in one field/column. Any help will be much appreciated. Thank you

I am trying to combine two fields in a base. Both of these fields are Linked Records. Is there a way to combine the two fields and retain the records. Currently I am getting text if I use CONCENTATE or ‘&’ Here is a demo base with the example https://airtable.com/shr1OM2ygBXu8ygke Thanks for the help!

Hi, I’m making a database of coffee beans. Roasters often sell in different units of measurement like grams, pounds, kilograms and ounces. I have a column for weight (number) and another for unit of measurement (single select). I also have price. Is there a way I can use a formula to calculate price per pound without requiring extra work from contributors?