Formulas

I use the software to keep track of all my craft projects as well as all my supplies. I am trying to calculate the price of the supplies used for my projects ("cost per item * # of items" kind of thing), but one of my fields has multiple values. When I try the below formula, it always returns a 1.0. {Price/sq in (from Vinyl)} * {Area of Vinyl Used (sq in)} Is there a way to have each value in the "price/sq in" field be multiplied by the "Area Used" field (which only has 1 value)?

Hello, I am new to Airtable and formulas. My current formula is this IF({Full/Part Time}="Part-time", 0.5, IF({Full/Part Time}="Full-time", 1.0, 0)) I want to add a condition that if another field has two selected clients, the part time value of 0.5 will be doubled. The Full/Part Time column is a single select field. The formula isn't coming up with any results. Any idea what I did wrong, or what's missing in my formula? Thank you!

I have a table for payment processing. When new records get created with a certain status, I would like to find the biggest check number in the table and add 1. Not sure if this would be using automation or formulas but thought I would start here. Thank you!

Hello Airtable community! I inherited a pretty robust Airtable system from a predecessor and have been working to revamp it, but my formula skills are not as good as the person who came before me, and I've been struggling to get IF AND statements to work. The logic seems sound, but they keep returning empty cells. One of the ones I'm struggling with concerns a formula that should return 1 of 2 options if a cell has a specific entry. I can get a single IF statement to return the correct result, but as soon as I try to include an AND, it fails. Here's what I have so far: In my table, the formula field is 'Ship From' with the two options being 'Direct from Printer' or 'X Distributor'. I want the formula to return 'Direct from Printer' if another field - 'Role' - is one of 3 options: Marketing/Sales, Licensor, or Editor in Chief. All other Roles should return 'X Distributor.' So I put in: IF ( AND ( {Role} = "Marketing/Sales" , IF ( AND ( {Role} = "Licensor" , IF(AND({Role}="Editor in Chie

Hi there! I am trying to remove NaN values from a formula field where both positive and negative values exist. This is in an effort to clean up two extensions: One chart indicating the number of each value's occurrence and one number where I am trying to get the average of the values, but with NaNs included I receive an infinity. Here is my current formula: DATETIME_DIFF ( {Due Date} , {Date Completed} , 'days' ) + IF ( {Date Completed} , {Due Date} = "NaN" , BLANK ()) Thank you in advance!

Hi, I'm trying to create a table to track income and the records feed from different tables using automations which are assigned a deal type as they're created. If they're type A the commission is 15% of the payment amount, if they're type B the commission is 10% of the payment amount and if they're type C then the commission field needs to return 0. I don't know if it makes a difference, but the payment amount field is a formula. I've tried SWITCH ( {Type of deal} , "A" , {Payment amount} * 0.15 , "B" , {Payment amount} * 0.10 , "C" , 0 ) But I just keep getting ERROR in the results . I tried looking here at nested IF formula and came up with: IF ( AND ( {type of deal} = ”A” , {Payment amount} * 0.15 ,)) IF ( AND ( {type of deal} = “B” , {Payment amount} * 0.10 ,)) IF ( AND ( {type of deal} = “C” , {Payment amount} *0 )) But there's clearly something bigger wrong there that I can't see because it says it's not a valid formula. Any suggestions would be hugely appreciated, thank you.

Hi there, I'm looking for a formula to automatically calculate the Venue and Producer split of the Budget and Budget Committed as per the "Deal." However, at present, the "Deal" field is a text entry, and I wondered what the best way to achieve this is? Many thanks, Tom

Hi There, I know I should already know this but I always get nervous when dates are involved. I want to be able to highlight either a field or a row when a task in the row passes the deadline. I don't need to know any other information like how many days over it is, I just want it to be highlighted to bring attention to it. I have a [Date Due] column and a [Status] column. The idea is that the status column would be set to "On Time" or "Overdue" to allow filtering Can anyone tell me what formula I can use to trigger the change in the status column? Thank you for your time. S

I’ve been using Airtable for a while now, and while it’s a fantastic tool for organizing data and streamlining workflows, I’ve been contemplating how I can enhance my note-taking system using Airtable formulas. My idea revolves around integrating a simple online notepad feature within Airtable to streamline my workflow without overcomplicating things. Airtable’s versatility and customizability make it perfect for managing complex data sets, but I feel like an integrated online notepad could improve the way I interact with my notes directly inside Airtable. I’m thinking of using a set of formulas to create a simple, text-based interface where I can store short snippets of information, thoughts, or even checklists. This would eliminate the need to flip between different apps or browsers to jot down quick thoughts, making the entire process smoother. An online notepad typically refers to a digital tool or platform that allows users to write, edit, and save notes directly within a web brow

Hey there, Thank you for your time helping me ! So i'm a mushroom farmer. I cultivate different batchs simultaneously. Each time I harvest one, I create a record for it, with the date of harvest, the batch harvested and the weight harvested. There is a tab dedicated to it. I also have another tab which contains more infos about each batch, and also a sum rollup of the harvests of each batch. My main metric to know if a batch is good is the yield, I compare the total weight of the batch to the amount harvested. For example, I harvested 10 times a batch, the total is 300kg of fresh mushrooms on a batch of 1000kg of mushroom substrate, hence 30% yield. But I'd like more complex infos, for instance I'd like to know on which specific date of harvest I crossed the 10% yield mark, the 20% ... So i could know for each batch after how many days I crossed the 10%/20% mark I've tried some combos of rollup and formulas but nothing works so much, any ideas ?

I'm new to Airtable formulas, so I would appreciate your input. I created a formula that I absolutely KNOW was working. But suddenly the field where the calculation was says #ERROR! now although it said 100% before. I can't figure out what has changed because I didn't change the fields used in the formula. I would appreciate your help. The field name is Sold v Booked % The formula is: IF ( {# of Winners Booked} , IF ( {Number of Winners/GCs Sold} , {# of Winners Booked} / {Number of Winners/GCs Sold} )) The # of Winners Booked field is a Lookup field from another table. The Number of Winners/GCs Sold field is a Number field. I wasn't sure if the above formula would work because the # of Winners Booked field had 1, 1 in it as it's looking up multiple records. So I was thrilled when the Sold v Booked % field showed "100%" which was correct since it was 2 that sold and 2 that booked.. Then the field data changed to #ERROR! instead of 100% - and I don't know why. I didn't change the type o

Hey everyone, I’ve been spinning my wheels on a simple issue for the last few days and hoping someone can put me out of my misery. I have the above dataset and hoping to split it up automatically using a formula. The data comes in via Zapier and the delimiter is always a ,. I need a solution that can run automatically. Few keynotes on the data; Could be anything from 1-3 records. They won’t always be the same The length of them will also vary FWIW I have only two records now and have formulas in place based on searching around. They are; Product ID A IF( FIND(",", {Product ID purchased}), LEFT({Product ID purchased}, FIND(",", {Product ID purchased})-1 ), {Product ID purchased} ) ``` Product ID B RIGHT({Product ID purchased}, LEN({Product ID purchased}) - LEN({Product ID A}) - 1 ), FIND( ",", RIGHT({Product ID purchased}, LEN({Product ID purchased}) - LEN({Product ID A}) - 1 ) & "," )-1 ) ``` [quote="Ryan_de_Metz, post:1, topic:44313, full:true"] Hey everyone, I've been spinning my whe

I am trying to create a formula that will take the latest date from a linked field (Appointments) and enter the latest date in a new field called "Last Appointment." I tried to generate a formula using the AI which gave me this formula: DATESTR(ARRAYSLICE({Appointments}, -1, 1)) but it keeps coming back with an ERROR. Any help would be appreciated.

Hi, we receive form emails in our airtable base. what's the best regex to use to extract information? Email looks like this: *Your Name* Jane Doe *Organization* Best Company *Your Job Title* Sales Manager Thank you!

I'm having a really hard time with making a formula field only calculate a value if the input field is not empty, including if it's 0. For some reason Airtable seems to treat 0 and empty value that same. Here is my current formula: IF({Monthly Actual} = BLANK(),BLANK(),{Monthly Actual} - {Monthly Goal}) In the attached screenshot, I want the fields with monthly actual = 0 to have a difference calculated, and I want the empty monthly actual to not have a difference calculated. The latter works, but the former doesn't. I've tried ChatGPT, Perplexity...etc. all types of AI and can't figure it out!

Hello Airtable Community! I'm facing a challenge with constructing a filterByFormula and came across a similar issue discussed here: https://community.airtable.com/t5/formulas/formula-with-multiple-values/td-p/174768. I have a column named Klasse, which can contain multiple values. I want to check if my search terms ('A', 'A1', or 'A2') match any of the entries in the Klasse column. Using the method suggested in the linked discussion, I crafted the following formula: OR(FIND('A', {Klasse}) > 0, FIND('A1', {Klasse}) > 0, FIND('A2', {Klasse}) > 0) This formula is intended to verify if 'A', 'A1', or 'A2' exists in any of the Klasse entries. However, I encounter an issue where FIND('A', {Klasse}) incorrectly matches entries containing 'A' as part of a longer string (e.g., 'AM146'). I am looking for a way to ensure that only exact matches are identified. Could you provide some guidance on how to refine this formula to achieve exact matches only?

I have a table of books with the following columns Book Title year published Amazon Link Bookshop.org Link I want to create a field that returns Book Title (Year Published) Amazon Bookshop, where the "Amazon" is linked abd "Bookshop.org" is linked. Is it possible to use a formula to return text with links? I tried creating columns for buttons, but when I include the button in the formula, the output is just the URL. For each author, I want to roll up a list of their books with the corresponding links.

Hi there. I'm trying to create a formula that returns a numerical value (price) based on a dropdown field. So, if "Option Zebra" is selected in the dropdown, the formula returns a value of 250. If "Option Lion", then the formula returns a value of 300, etc. Would some amazing wizkid be able to help me with that?

Hi all! I am trying to find a method to tell me the Field Name of the last modified field, using a Formula. Is this possible without external scripting? I'd also settle for Field ID, although not most ideal 🙃 Was hoping for a function like LAST_MODIFIED_TIME( FIELDNAME() ) or something. Basically, I have logs coming into a Logs table with 100(ish) columns, and I want to know at a glance what type of logs that particular one is 🙂 Thanks in advance!Adam

I need a formula to verify a single-line text field only contains productId's separated by commas with <=30 comma separated productId's. OK: <=30 comma separated 8-character productIds Not OK: >30 productId's; <8 or >8 character productId's, not comma separated example (with 5 comma separated productId's) correct: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9 incorrect:7 productId's: B0D5FHXP,B0D86NWH,BCVPRWY7,DRKM1T5G,GK3DHGH9,H4VPMWY7,MRKM1Y6F 5 productId's with missing commas: B0D5FHXP B0D86NWH BCVPRWY7 DRKM1T5G GK3DHGH9 7 productId's with semi-colons: B0D5FHXP;B0D86NWH;BCVPRWY7;DRKM1T5G;GK3DHGH9

Hi there, I have a team of account executives and I'm trying to rollup the amount of revenue each person manages during a 12-month period. Revenue is generated on a per project basis. Each project has a start and end date and a monthly retainer fee, which are recorded in the project's record in my project table. I have a formula field ("Project Value") that calculates the total project value. I maintain a second team member table and I link account executives to the projects they manage using a linked record field. Some projects are managed by two account executives (and are linked to two account executive records as a result). The problem is that when I rollup the amount of revenue each person manages during a 12-month period, for projects that have two account executives attached, the rollup attributes 100% of the project revenue to both of the employees linked (rather than 50% to each). Can anyone point me towards a solution? Many thanks.

Trying to create a condition via formula which will mean that a client won't be able to be quoted via airtable extension with the following: Formula: IF ( Type= "Client" , IF ( {On Hold} = "On Hold" > "Good" , "✅ Good to Quote" , "❌On Hold - Do not Quote" )) We are hoping to then enable the On hold note to propagate through to an extension we use to send quotes onto clients There is a field with single text which we use to fill out the client and then a options single select for on hold/good

Hi, I have a formula that almost works. I'm trying to make my primary field a formula where it looks at "Column A" and looks at "Column B". If a there's a value in "Column A", enter that value as the primary field. If there's a value in "Column B", enter that value in the primary filed. If there's a value in both "Column A" and "Column B", combine those two values and place a "/" between the two fields. The formula I have looks like this: IF ( AND ({ Column A} != BLANK (), {Column B} != BLANK ()), CONCATENATE ({ Column A} , "/" , {Column B} ), IF ( Column A != BLANK (), {Column A} , {Column B} )) This does work. However this formula is adding the "/" character to the beginning of "Column A" and at the end of "Column B" when there's only one value in one of the fields. The results look something like this: If there's a value in "Column A" but not in "Column B": 12345/ 75326/ 23457/ If there's a value in "Column B" but not in "Column A": /93664 /30483 /54214 I tried replacing IF(Column A

Hello, I'm learning how to pre-fill a form. I really like it but it would be even better if I could prefill a form entry with multiple values/entries from another linked record. Currently, it is only pre-filling the first value of cell. The linked record in this formula is the Residents Column. I need them to stay selectable values, not text. I can't expect the users to enter the text values exactly as they are written in the linked records. "https://airtable.com/---/form?" & "prefill_Job=" & ENCODE_URL_COMPONENT ( {Job} ) & "&prefill_Order=" & ENCODE_URL_COMPONENT ( {Order} ) & "&prefill_Teams=" & ENCODE_URL_COMPONENT ( {Teams} ) & "&prefill_Residents=" & ENCODE_URL_COMPONENT ( {Residents} ) & "&prefill_Description=" & ENCODE_URL_COMPONENT ( {Description} )

I am using a formula to calculate a date in one field, and then trying to use another formula field to determine whether a contract is current, future, or expired using that date and another manually entered date field. The field I'm using to determine if the range returns the wrong results if I use the calculated expiration date field, but if I use a manually entered field with that same date, the calculated range returns correctly. ExpDate: DATETIME_FORMAT ( DATEADD ( DATEADD ( LIC__EffDate , {Term (Mo)} , "months" ), - 1 , 'DAYS' ), 'M/DD/YYYY' ) ContractRange: IF ( ExpDate > TODAY (), IF ( LIC__EffDate > TODAY (), "Future" , "Current" ), "Expired" ) ContractRange copy: IF ( LIC_ ExpDate > TODAY (), IF ( LIC__EffDate > TODAY (), "Future" , "Current" ), "Expired" ) Is there something wrong in my ExpDate formula, or is there an issue with using a calculated date in a date range? Or am I just doing something else totally wrong?