Formulas

I’m trying to figure out if AirTable can solve equations with unknown variables. For instance, a break-even analysis based on data in the table. (Revenue per unit ⨉ Units sold) − (Fixed costs + Cost per unit ⨉ Units sold) = 0 (200 ⨉ Units Sold) − (1000 + 100 ⨉ Units sold) = 0 Where Units sold is the unknown variable (“x”), and I would like to know the value of “x” where the equation returns 0 (break-even). A normal equation solver would give: (200x)−(1000+100x) = 0 x = 10 So, could I get “10” to display using an AirTable Formula? Thanks!!

Is it possible using a formula or automation to compare two multi-select fields and show which items are missing from the 2nd? I'd like to have a 3rd field that would show just what was missing from the completed list. 

Hello! I'm creating an Airtable base to manage book club meetings. I have a column for the meeting dates (DD/MM/YYYY) which take place once per month. I'd like to have a "Reading Status" column that uses a formula to create the following labels:"All Done" for meetings where the date has already passed"Up Next" for the upcoming meeting"Not Yet Read" for future meetings that are not the next closest meetingI am new to formulas and struggling with how to set this up. Any help would be much appreciated. Thank you!

Hi All, I want to create a bibliography. I have all the pieces in different columns and want to use the concatenate formula to pull them all together. However the Title of the book needs to be in Italics, how do I carry over the italics from the cells, or adjust the formula to make only the title Italic? Author Surname, Initial(s). (Year) Title. Place of publication: Publisher. This is my formula: CONCATENATE({Author(s) Max 3},"(",Year,")","."," “,Title,” “, City,”:"," ",Publisher) Any ideas?

How do I add a column of numbers quickly without creating a formula?

Hello,I'm pulling in purchase order data for my company, and have a "Vendor Management Team Partner" associated with each purchase order record.I'm also reconfiguring these POs in a separate table, such that the email address of the PO requester is the primary key.If a requester has multiple POs, they'll have multiple values in the VM Team Partner column, so I'm also adding a Rollup for unique values.I'm then automating the copying and pasting of the unique values in the Rollup field into a Linked field for the VM Team Partner's email info.However, in only some of records, the Link field will display a comma at the end of the values, whereas the Rollup field will not.How is this happening when there is no comma in the Lookup field? I now have to add values in my "VM Email" table for both "John Smith" and "John Smith," which is an unexpected nuisance.Can someone help me understand how this is happening and how to rem

Hi,I hope you can help me, I've searched everywhere and I can't find an answer to my problem.I work for a training organization. I have a table with students. A table with training session dates. And a table with speakers.In the dates table, I make a link with the speakers (each speaker can participate on one or more dates)In the students table, I want to get the names of the speakers of each session, but in a deduplicated way (I want to use these names to print a certificate)But no matter what I do, I can't remove the duplicates in the names of the speakers.Airtable considers that the 2 or 3 speakers on that each training session date are different arrays.I tried the « arrayflatten » and « arrayjoin » fonctions (and a combination of these) but nothing works.Do you have any idea to ge me solve this ?Thanks in advance

Hi, I’m trying to add a formula where I divide one column by a number. Like: Column A: 5000 Column B: result Formula: Column A(5000) / 365 Any idea? Been trying to find a solution to make this work…really don’t want to go back to excel… :sob: :frowning: :sob:

Hi everyone, I am new to the Airtable and I'm excited to be part of this community. May I apologise in advance if I don't use the correct technical terminology.  I design surveys, impact assessments and evaluation tools for a criminal justice charity. I have created a survey using 'Forms' and in one section I have 10 questions that require word responses. For example one of my questions is 'When I am confronted with a problem i can usually find a solution'The multiple choice response can be 'Not at all true' [=1]. Barely true [=2]. Moderately True [=3]. Exactly True [=4]I need to create a formula for a separate column that will tally up all the numerical values and give me the overall score. Any advice on how I write this formula will be greatly appreciated. Cheers Justin    

Hello,Struggling to work out where I'm going wrong. I'm trying to use a formula to auto update the "next check date" of equipment based on the date it was last checked and a single selection box.Eg. Harness checked 1/1/24Check frequency MonthlyNext check date 1/2/24My current formula is as follows: IF(  {Check Frequency} = "Annualy",  DATEADD(    {Check date}, 1, "year"    ),    IF(      {Check Frequency} = "6Monthly",      DATEADD(        {Check date}, 6, "months"        ),        IF(          {Check Frequency} = "Quareterly",          DATEADD(&

Hi! I have data coming in from a webhook to Make. I am trying to create a formula in Make to search Airtable for number of times an email appears in a field on a table and limit the number of submissions to Airtable. For example, I want to limit the rows to 5 for this user based on email. When the user submits data via the webhook, I want Make to do a search on Airtable and count the number of time the email appears in the table, if email appears less than 5, allow data to be submitted. If more than 5, give an error or prevent data submission. Here is the formula I used. 8.FIELD__KPZIT488X is the value from the webhook for email."{{8.FIELD__KPZIT488X}}"={Email}I have tried to do this with forms, but no luck. Trying to see if it can be done with formulas on the backend.

Hello, I am setting a legal case tracking base and I am trying to do something where:1: When I input the date of incident, it automatically calculates two years from that date2: I would like to have a table that also has a countdown until we reach the Statute of Limitations. Ideally, it would look something like: Date of Incident (03-11-24), Statute of Limitations (03-11-2026), Days remaining: 365I attempted the Datetime_Diff between the two, but it just gives me the number of days between the Date of injury and SOL, so I think I can omit the Date of Injury and just have a running countdown so that every day when I log in, the day is one less remaining in the column that is counting down. 

Is there a way to append data in a field? Let's say I have a long text field called "Campaigns" which will contain the different campaigns that a customer have agreed to through our emails. Is there a way to preserve the previous text and basically add the new text to the field? 

I want to create a "sheet" timetable that creates new dates using a formula based on one date that is inputted. I know there's probably a better way to say that so please disregard my ignorance. Here's what I want to happen...I input the "Class Start" date and all other dates are created using formulas. Here is our current sheet.Is this possible in airtable? I imagine there is a work around. 

Hello! I have a large set of dates that are a partial year and month, and would like to convert this to a final date of the month format. Examples:Data I HaveDesired Format24-Apr2024-04-3024-Aug2024-08-3023-Dec2023-12-31I've tried concat using LEFT( & RIGHT(I'd like to try SUBSTITUTE, but not sure of the formatting. I'm also open to any suggestionsThanks for your time! 

why does a 'day date' formula, based on a 'start time' return a day as the day before, when the start time is before 8am? Here is my formula column which is based on my start day/time fieldDATETIME_FORMAT(Start,"D-ddd") 

Hi all,I have formulated columns Apple, Orange, Banana, and the formula produces "Y" or "N" values. I want to create another column that will return the name of the column if their value is "Y". Is there any way to do this?I have tried nested IF formula, like IF(Apple="Y", "Apple", Orange="Y", "Orange"...), but the problem is they just return one value, but I want all the columns as long as their value is "Y".Thanks in advance!

I would like to limit the number of rows created in a table. I am planning to have a limited data entry period with 100 users where each user can only put in 10 rows of data. How would I achieve this with Airtable?

I'm setting up an interface that will serve as a dashboard to show my time overall client financial info. I used rollups so all the data would be in one table which will allow people viewing the dashboard to only use on filter to filter by client. The only problem I'm running in to is I would like to have the interface say "The client was last invoiced on RECENT DATE" I tried using a max() formula in a rollup column. The problem I'm having is that it's returning 2/29 as the most recent date when I have dates from this week, first week of March. How can I make sure it's actually showing the most recent date?

If there are options in a drop down menu (example Quiz or Test), start dates are entered by user, can I add a new column with a formula that detects the word 'quiz' or 'test' in the previous column and add an end date automatically based on the word? A quiz is 3 days after start date and a test is 6 days after start date for example. Appreciate any insight.

Hi !     Coleagues, I have a project in AirTable in a single table with 174 rows and 11 (eleven) categories that I can group these rows.     Each of these 174 tasks has its own "Start Date" and "End Date" column, with date format in it.     I want to know the earllier "Start Date" and the latest "end Date" inside each of these modules, when filtered by them.     (The objective is to show a timeline filtered by these "MODULES", using these fields with this formuila that identifies the earliest and the lates dates... I'm open to other approachs).   I achieve the first step with these two formulas. They returns the date in each of these fields for its row:1 - For the earliest "Start date" it is:MIN( VALUE(DATETIME_FORMAT({Start date}, 'MM/DD/YYYY')) )2 - For the latest "End date" it is:MAX( VALUE(DATETIME_FORM

Can anyone tell me where I'm going wrong with this formula? SWITCH( {HS-LA %},"15%", ({GBP Amount}-{HS-LA Commission}),"10%", ({GBP Amount}-{HS-LA Commission}-{HS-LA Commission}),)Or suggest another way to achieve it - I've tried a few IF options too but can't get that to work either. For context, I'm a literary agent and depending on the type of deal I do I either take 15% or 10% commission. Where I take 10% a co-agent will have also taken 10% but that's not necessary for me to track, other than that when I'm working out the author's overall income I need to make sure I know they didn't get it (hence trying to take away my commis

I am using Air Table to capture costs per garment style and there are 12 versions of the costs that are available to use based on importing tariffs and shipping modes. These 12 costs are hard keyed into the base. I would like to be able to make the choice on a tariff rate and a ship mode and have the cost autopopulate into a new cell so I can link this elsewhere in my base. Is there a formula that could be used? 

I am trying to separate the values in the "ADKAR numerical value" column so that the first number (2) is inputted into the "Awareness value" column. Then so on where I input the next number after the comma into another column, etc.Any help is greatly appreciated! 🙂  

Hello everyone,I'm trying to sum a row containing several values.In fact, in the second cell with 140120,178216,0,0... I'd like to sum these values.Do you have any ideas?Thanks in advance,