Formulas

Hi there, So I feel like the Airtable formula support is severely lacking in a couple of ways, but here’s a really painfully obvious one: Let’s say you have a single base, single table, and there are two columns: Current Members and Valid Members. The Current Members column contains a comma-separated list of current members, like Bob, Sally. The Valid Members column contains a comma-separated list of ALL allowed members, like Bob, Susan, Tim, Jim Now, I want to create a third, formula-powered column called Are Current Members Valid? and that will return 1 if all Current Members are present in the Valid Members column; 0 will get set otherwise. In order to implement this correctly, I sort of need to: Collect each Current Members item as an array – wh

Good morning everyone, I’m stuck on a simple problem. do I need to list items from a segment or else list a fixed item “exit” as would the formula? the first step I’m using the following formula: IF(OR({Company}= ‘@company’,{Type}= “Knowing the Services”,{stock}= “YES”) the second part of the formula in a dumb translation would be : or type = continue how would the assembly of this formula be? The intention is that the formula lists the products that are in the column “know our services” and also list the product “continue”

Hey, do you know how can i get in a field, only the hour format Hour/minutes like 09:34, without the date ?? thank you

Hello everyone! Thanks in advance for your time. I made a table to log tasks, where I put one column for level of complexity (S-small, M-medium, L-large) and another column for volume of work (S-small, M-mediu, L-large). Another third column sums it into SM, SL, SS, MM, LL etc and also sorts it by a custom order. How can I make a formula for one more column for URGENCE LEVEL, where I can put a formula that says like IF Hierarchy = SS, SM or SL then show the word “HIGH”, then IF Hierarchy = MM or ML, show the word “MODERATE” and IF Hierarchy = LL, then show “LOW”. Idk, it seems simple, but I advance I am a creative that managed into adulthood without learning many formulas, god only knows how. So after failing the whole afternoon I came to look for your wisdom. Thank you guys for reading me! :grinning_face_with_big_eyes:

Hello :slightly_smiling_face: New topic new question : I want to do a DATEDIFF formula really basic). I did : DATETIME_DIFF({FIN PE},{DATE ENTREE},‘days’) But it’s really weird it work for some lines and don’t for the others. No links between the lines there are working with … The two fields use in the formula are two dates with european format. I don’t understand where i’m going wrong … please help

I am building a competitive pricing sheet and have a pricing column that is formulated to give me pricing based on a specific margin, however, I then need a column that would round that pricing to the .95. Does anyone have something like this?

Hi there Airtable community! I’m having trouble with an IF statement. I have a lookup column with two values, Turned or H/L. If the value is Turned, I want my formula to add the values in each of the shoe sizes listed. I’m getting an ERROR every time I try it though. Is it because the value (H/L) is a lookup value? The formula saves correctly, but the resulting value is #ERROR! Screenshot attached too… thanks for your help :slightly_smiling_face: IF( SEARCH(“H/L”, {Toe Puff}), SUM({2},{2.5},{3},{3.5},{4},{4.5},{5},{5.5},{6},{6.5},{7},{7.5},{8},{8.5},{9},{9.5},{10},{10.5},{11},{11.5},{12},{12.5},{13},{14}) )

I know that these types of questions have been asked several times here, but I haven’t been able to replicate suggestions from other threads to get the desired empty cell for my formula. Thanks in advance for any assistance! My formula is: DATEADD({Associated Event Date}, {Days before/after event date} * 1, ‘days’) some of my tasks do not have a value for the {Days before/after event date} field. In these cases, I would like for the cell to show up empty rather than show #ERROR. Please help :grinning_face_with_big_eyes:

Hi there, Hope you’re doing well ! Can someone help me to formula-translate “has none of” please ? :slightly_smiling_face: What i need to do is to make a webhook call to filter some results and only take the ones which does not contains “SFR or Orange” in “which_network”. Thanks a lot

Hello :slightly_smiling_face: I am a beginner in the creation of Airtable formulas. Is it possible to create a formula IF(AND(value1,value2,value3) with at least 3 conditions ? I only succeeded it with 2 conditions. If it’s not possible, do you know how i can make it with others formulas ? Tx u very much for your help/time

I’m tracking bicycle tire air pressure fills and my current setup include 3 main columns: Begin Date, End Date, and Duration. Duration is a DATETIME_DIFF function, calculating the days between air refills. The problem is, I have to copy each End Date to the subsequent row’s Begin Date every time. This is quick on the desktop, but on iOS it’s extra steps. Ideally, I would have just one date column and the duration column would calculate the difference from the previous record. For example, in Google Sheets, the function would be able to easily calculate the difference between cells A1 and A2. I haven’t figured out how to do this in Airtable. This article mentions how to "find the difference between a record’s date and today’s date. Airtable Support | Calculate the difference between two dates I’m looking for a replacement f

Hello Everyone! Would greatly appreciate any help on this matter. I have a base with two rows: people can enter their name and a date. Is there a formula I can use in the third row which would check how many entries are recorded for the same date, and could show a message along the line of: “Too late, there are already 8 entries with this date”. Thank You!

Hey all! I am trying to use Airtable as an excel spreadsheet to track my budget. Is there a way to create a formula that use the previous records ‘current balance’ amount for the formula so it will update each record (transaction). I really appreciate any help you can offer!

Hello! I am an absolute newbie to databases, spreadsheets, the whole nine yards. I am working on a DATETIME_DIFF formula that I want to either show a fixed duration (based on two dates) if the status in Column X is A, B, or C, and show a duration using the TODAY formula if the status in Column X is not one of A, B, or C. Ideally, for those entries with A, B, or C in Column X (where Column X is a single-select field), the DATETIME_DIFF formula will pull the end date from Column Y, and for those entries without A, B, or C in Column X, the DATETIME_DIFF formula will pull the end date from Column Z. So for entries with end-dates (i.e. the ones that are tagged A, B, or C in Column X), it will always show the number (e.g. 38 years from start date to end date), but for ones that are ongoing, it will update to today’s date (i.e., this year it will say 25 years from start date til today, next year it will say 26 years, etc.). I tried this: IF(Posture = OR(Commuted

I want to create a table similar to this one in AirTable. What I can’t figure out is if there is a way to calculate between rows (records). For example, looking at this spreadsheet, the calculation in “O7” is =IFERROR(L7/L6,0) Is there a way to mimic the same kind of calculation (formula) in AirTable?

Hi everyone, I am using a formula to determine how much profit/loss is being made given the minimum number of people attend an event. The issue I am encountering is that when the minimum number of people attending the event means that the profit/loss is exactly zero then it is returning an empty cell rather than a 0.00. You will see in the below formula that I am also using the ROUND function as it was returning 6 decimal points when I only wanted 2. I was unable to use the formatting function as it is not always numerical value given the IF conditions at the start. Really stuck on this one and any help would be greatly appreciated! IF({Banded Pricing}=“No”,“N/A”,ROUND(((({Traveller Price}-{Variable PP Price}-{TM Pays Cash}-{TM Commission})*{Minimum #})-({Banded Fixed Cost at Min}/{Unique XR})),2))

Hello, I would like to use a parameter in a formula. Some context: I have a table A with different rates for each rows. (rate1, rate2, rate3). I have a table B with weighs for each of the rate (rate1_weigh = 1, rate2_weigh = 2, rate3_weigh = 5) I want to calculate the overall rate for each rows using weighs for each rate. Something like: (rate1_weigh * rate1 + rate2_weigh * rate2 + rate3_weigh * rate3) / (rate1_weigh + rate2_weigh +rate3_weigh) How could I call the weigh that I have in table B in the formula I write in table A? Thanks a lot for your help, Regards

I am currently working on putting together a price list from our various venders who offer similar products. One of my tables list the “Generic Product” ID while I have another table with a list of all products we purchase. I was able to use a rollup easy enough to tell me what the lowest price for that “Generic Product” is but would like for that to also pull the information from that table to tell me which supplier offers the lowest price. Is there a simple way for me to accomplish this? I have been missing around with some ideas but figured I would ask the community.

Hi! I am new to Airtable - and it shows a lot of promise but I have a hard time wrapping my head around it. I need to check my orders against a backorder list. I can get both as excel files and thus read them into Airtable. Then I need a crosssection of the columns. Something like: For every_line in ColA (for every_line in ColB check if ColA_item = ColB_item → return ColB_item) Does this make any sense? :slightly_smiling_face:

Hi everyone! So I have a table with everyone names, slack id and DM links. I have a table where a supervisor can add small tasks with fixed deadline to a single person or a few picking from the first table. So far I managed to make a channel where using a few lookup tables it sends a meaningful notification. Trying to improve: Best option send a DM to the person chosen. No idea how to make it work. (easy with a static field but I would need it dynamically) Secondary option is to tag the people listed. There is a neat guide using the ID but it fails as soon as I have more than one person listed in the field of people to notificate. Any idea?

Thanks in advance everyone for the help!!! I am really having some issue finding the solution to this one. I have a base with several tables and many different fields. For 1 of these fields, in 1 of the tables, I want it to be determined by 3 other fields in the same table. Basically, I want to use 3 conditions (for example: Name, Service, Language) and for the field to be autopopulated with a rate to be found in another table. Is there any way to do this?

Is it possible to combine “IF” and “SWITCH” formulas into one? i am trying this formula and i am keep getting an error: IF ({Send to FBA},SWITCH({Product Type}, ‘Individual’, {Arrive to Prep Center}*1.5, ‘Return’,{Arrive to Prep Center}*2.5) The “Send to FBA” is a checkbox field When i do this formula alone it works great: SWITCH({Product Type}, ‘Individual’, {Arrive to Prep Center}1.5, ‘Return’,{Arrive to Prep Center}2.5) But i want this formula to work only if “Send to FBA” is checkbox!

I have the roadmap of a project with start and end date fields for each task with a dependency between tasks, I need that when the end date of some tasks that has dependency changes, it automatically changes the dates of the rest of the tasks

Hi, I try to calculate the time duration between two date fields with DATETIME_DIFF formula, but doesn’t work at all. It shows totally false results. Here it is: This is the formula I use: This is the formatting for duration: Any idea what I should do?

Hello, I’m Spanish and I can speak a little English. I’m new in Airtable, sorry if my question is too easy for you. I have a shoop with several makers (“Créateur”) I would like to sum “Prix” for each “Créateur” in the field “Total pour créateur” I’ve made a formule with SWITCH but its does’nt work {Prix} + SWITCH( { Créateur }, “Sophie”, SUM, “Mathilde”, SUM, “LMC”, SUM ) Can you explain how to find the solution ? Thanks Date____________.Créateur_________Prix_________Total pour créateur 5-3-21___________Sophie___________80_______________80 5-3-21 __________Mathilde __________60_______________60 5-3-21 __________ LMC ____________50_______________50 5-3-21__________Sophie ___________20 ______________ 100