Formulas

Hi, I’m using the Recipe Organizer template. I would like to add another view that calculates the total number for each ingredients. For example: Recipe 1 - Big Salad - consists of:6 Cucumbers and 8 Tomatoes. Recipe 2 - Small Salads - consists of:3 Cucumbers and 4 Tomatoes. The user needs to prepare 10 Big Salads and 4 Small Salads. The output should be 72 Cucumbers and 96 Tomatoes. Is it possible? Thank you, Sam

Hello fellow community members, I have a sheet (Sheet1) which has multiple columns - say ConfigValue1, ConfigValue2. Then I have a column called Result that calculates a result using those values and values (Logic1, Logic2) from Sheet2. In Sheet1:Result, I want Sheet1:ConfigValue1 x Sheet2:Logic1 + Sheet1.ConfigValue2 x Sheet2:Logic2. Note that the logic could be a bit more complicated. I could put the logic right into Sheet1, but the issue is that we want to have a separate interface for entering logic. Thanks!

Hi everyone, Looking for some help on this case … The user enters any one value (ISBN Code or Author Name) in the form and gets a result (Book Details corresponding to the author or ISBN Code) displayed in view link. I have created a MASTER CONTENT table A, with columns naming ‘ISBN Code’, ‘book name’, ‘pages’, ‘author’, with "ISBN code’ as the primary field. From another table B, I have created a USER SEARCH form, in which "ISBN code’ column is linked with table A. IN the form, the user enters the ‘ISBN code’ and in return in VIEW of the table, A gets other values corresponding to ISBN Code like Book name/Author Name etc. from Table A using Look Up. Now I want to create an option in the form in which a User has the option to either fill the ‘code’ or ‘author’ & he can similarly view all the details from table A? How to pull and Display values by entering either - in the same filed or two separate fields in the Form? Thanks for taking time and look forward to some comments.

Hi, I’m trying to create a formula that flags (with a 1 or 0) when a certain 5 digit number code is present in either a linked record field, or a field of arrays (whatever is easiest). For example, I was attempting: ``IF( OR( FIND(‘68100’,{Combined Codes & Tags (all)}), FIND(‘41100’,{Combined Codes & Tags (all)}), ), 1,0)`` But this only works when I have two possible options of codes. I’m actually looking for the presence of 5 different codes. What is the best way to do this?

I am creating a new table, say for example with daily price of gold, I have three fields, Date, Price and Change in Price I want the field “Change is Price” to be the difference in price since yesterday, to be calculated automatically. In spreadsheet, I could do it as =b2-b1 Is it possible to do this in Airtable, Please help. Thanks in Advance.

Hi Community, I set up a form to prefill using the URL. The field it autofilled was a drop down select look up from another table. I am 99% sure I tested the form an it worked fine. I have now tried to do a similar lookup and cannot get it to prefill. So I went back to the place I’d originally done it and now that is not working either! Has something changed in airtable? Or is there another explanation? Please help if you can! Best, Nathan

I was wondering if there was a formula I could use for day of the month in which I can enter the day and not have it always being current day each time? I need it connected to a time percentage. The reason is we have metrics we put in, but I can’t put it in every day. If someone looks at my tab, they will see the current date and assume that the numbers are updated. We also run campaigns that may only last a few days out of the month. I just need the first day of the month to be a manual entry attached to the formula for time. I apologize if this is a silly question.

IS Airtable down? Can not connect !!!

Is there a way to set up a Zap that will send an email on a date, to a person, based on a Date or Formula (date) Field?

I have a base that we use for activity management. I want to be able to count the number of times a date field has been updated. We are trying to see how many times a “Due Date” has been changed. Thank you Sally

Ok so I’ll try to get this all straight. Obviously the goal is limit the number of times records have to be linked, I’m hoping someone can offer a different perspective. The Background: Everyday we send out an email record from our table REPORT. Report is basically the combination of the BREAKS table, which can have 1-4 every day. BREAKS is composed of mags from the MAGS table, each break usually has between 1-10 mags. Each Mag has NOTES from the NOTES table. So thus far its been going great, you make a note, use a lookup to tag the MAG table, adds note to each mags, then the Mags is tagged to the Break table where all the mags with the all notes get rolled up, which in turn the breaks are all tagged to a single day and at the end of the day all that data rolls up into a single email report which is in HTML and uses lookup/rollup tables from all the above to fill in proper data. The Problem: Mags started sharing the same notes, issues that occurred on both mags, we can tag both mags bu

Hi all, I love the visual usability of Airtable, but am restricted by a total lack of understanding formulas (or Excel!). I’ve read all the Airtable guides, but can’t figure out my specific request and the formula needed, or whether it’s a formula I need or something else! Apologies if stupid question, but: I’ve made a table of content for social media and other media, listing content title, type, platform to post on, due date. Currently, it looks like this: Name . Content Type. Status . Due Date . Gifs for Facebook . Creative Idea 31/8/2019 (Imagine details filled out further, I can’t link to or insert a screenshot) I am trying to work out a way to group these under three groups - either ‘This Week’, ‘This Month’ or ‘This Year’. I’d want the entries to be automatically populated into these groups dependent on the due date I manually enter. Is there a formula for this, where should I add it

Hello! I was hoping someone could create a formula for me. I am new to the formula area and what I want to do is FAR beyond my brain power. Wanted to know if this type of formula was possible… Fields: “Date” (which is a date field) “Day of Week” Field (which is a formula using DATEFORMAT to get the day of week) “Complete” (which is a Check box) The formula I am hoping for would replace my “Day of week” section: INCLUDES: • Dateformat to show date of week • IF statement that says “Next Week” if the date is next week • IF statement that says “Completed” if the Complete box is checked I am creating a task list and I want this all in one field, so I can group. I want the tasks to be organized by the day of the week, for it to say “Next Week” if the date falls in the next week, and for it to say “Completed” if the box is checked. Is this possible? Your help is MOST appreciated! Thank you!!

I have asked my guests to enter mobile number and name in a form. Now in my base, the mobile number will be a unique value that will be used for linking to another table. But I can’t link a column with a formula (concatenate) & if I convert phone-number or a text column into the linked column - then a new guest will not be able to enter data in the form but will have the option to choose someone else’s number only. Any suggestions?

Hi all, I am creating a Landing Page testing and analytics tool. Basically, on a table called ‘Components’ I have taken all components you would expect in a landing page and divided them into records, organized by their type (for example, “Hero Unit”, “Benefit”, “CTA”, etc…) Then, on a separate table called “tests”, I link components into records, with five sections linked to components (labelled ‘Top Section’, ‘Section 1’, ‘Section 2’, etc…). I then compile components into various sections (sometimes randomly, sometimes planned). These basically run as tests through a CMS that allows me to create lots of iterations of landing page. I gain some basic analytics (visitors vs. conversions) that I then want to be back traced to the original component (as analytics will only be for the whole page, not for sections of it). In this way, I’m hoping every test I run (and have conversion numbers for) will help me understand which components are most greatly responsible for creating conversions.

Hi Would really appreciate some help creating a formula. I am trying to run (modernize) a non profit social society’s events calendar / booking system using airtable. I have 3 tables in the same base. Table 1 (Master) - This has a sign up form linked to it for people (both members and guests) to can sign up for the events (single or multiple). The selection of events is linked to the (Events) table. The sign up form has a check box for users to select if they are Members. Table 2. (Events) - this is the calendar table with date and event name. (separate columns) Table 3. (Members) - List of members with their names and membership numbers (and other membership data) (all in separate columns) If a member signs up for specific event, I need a formula to look into the (Members) table and compare their names and return their membership number into the master table. Appreciate any help people can offer? Craig

Hi, I’m trying to get one field to return a value based on the number in a different field. I’ve tried both uploading and linking to a screenshot but it’s not allowing me to do that. This is the Nested IF formula I wrote: IF({Number of ELLs}<21,“0-20”,IF({Number of ELLs}>20,“21-50”,IF({Number of ELLs}>50,“51-75”,IF({Number of ELLs}>75,“76-100”,IF({Number of ELLs}>100,“101-300”,IF({Number of ELLs}>300,“301-500”,IF({Number of ELLs}>500,“501-1000”,IF({Number of ELLs}>1000,“1001-1500”,IF({Number of ELLs}>1501,“1500+”))))))))) The formula won’t return any values other than “0-20” and “21-50”. Does anyone have any insight on why this might happen?

I’m struggling to get the desired results using the Substitute formula. Maybe I need to try the Replace formula. But thought I’d ask here in the meantime. I have a list of tasks. Each task starts with a client code and project code. How can I write a formula to update the client code and project code for each new client/project? For example, if the one task is “CLI-PRJ-Send thank you to client.” I would like “CLI” replaced with “4CR” I would like “PRJ” replaced with “Blog” So the new task would be “4CR-Blog-Send thank you to client.” Thanks in advance for help on the topic!

Hi I need to get results in days : hours bettwen to dates. I have {date start} , {date stop} and when {date stop} fincion should count to Now() Funcion IF({date stop}="",DATETIME_DIFF(NOW(),{date start},‘hours’)/24,DATETIME_DIFF(date stop},{date start},‘hours’)/24) It shows results in decimal how many days but now in DD:HH I tryed: DATETIME_FORMAT( IF({date stop}="",DATETIME_DIFF(NOW(),{date start},‘hours’)/24,DATETIME_DIFF({date stop},{date start},‘hours’)/24),‘DD:HH’) but I get ERROR Any advice ?

Hello all! Tried searching, but I couldn’t find anything that answered my question. I believe my formula is correct, and it doesn’t throw an error, however, every time I input it from Notepad ++ it limits it. Basically I’m trying to assign point values if certain criteria are selected from a multi-select form. I was able to convert it to a stringify data. So there are nine options. Option 1, Option 2, and Option 3 are selected. 15 points Option 1, Option 4, and Option 7 are selected. 10 points Option 6 and Option 7 are selected. 5 points Option 9 is selected 0 points. Etc. I’m okay with doing multiple, nested IF formulas, but again it limits it? Any reason why? Thank you!

I’m sorry if im not clear enough i’ll try my best to explain what i want to achieve. I have a table called Courses that contains a list of courses in row (like Math, Physics… etc) Each Course have a date (like 1/1/2019) and Students can register to a class (through form). a record will appear like (separating column with ‘|’ symbol): student1@email | Math | 1/1/2019 but students can register to multiples class: student2@email | Math Physics | 8/8/2018 1/1/2019 I wish to list students that attended the Math class on a specific date like: student1@email | Math | 1/1/2019 student2@email | Math | 1/1/2019 and not student1@email | Math | 1/1/2019 student2@email | Math Physics | 8/8/2018 1/1/2019 Using a filter with “not Physics” will remove the entry of student2 If i click on date entry 1/1/2019 i get all students that attended the class in a window, but i don’t know how to export this into a new grid view. I wish you understand my problem and also that it can help other similar issues. Tha

I have an Airtable with thousands of rows, all of them with unique links. Each link has an associated author who has a Username and Email. In a view, I’m grouping by Username and then I see multiple entries for that same Email. That’s fine, but my goal is to see only one entry for that Email, for that Username, in this specific view. How can I go about achieving that? Thanks!

Hi all! I’ve run into an odd issue that I’m hoping someone out there has found a workaround for :slightly_smiling_face: I have a table consisting of recurring tasks, where each task is assigned a value ($). A zap creates assignments based on those tasks and values in a separate table. I need to be able to override the value of the task in the new table; which is pretty straightforward… but I’m running into issues if I want to override the price to be $0.00. Airtable seems to treat Currency fields (and probably number fields too, haven’t tested it) the same whether they are BLANK() or have a value of $0.00. Here is the formula I’m using (in a rollup field): IF({Override Task Price}, {Override Task Price}, SUM(values)/{Step Count}) Has anyone found a way to force Airtable to recognize ANY entry in a Currency field, even if it’s $0.00?

I have a rollup field (or lookup field) that usually contains an array of 8 numbers. I would like to have a formula that gives the sum of the smallest three numbers. Right now, it seems that it is impossible without an external application (Zapier). Are there any array formulas that would help me to achieve that? Note that these are numeric values and don’t necessarily have the same “length”.

Hi All, I am trying to go around this nested formula and hope someone could help me try understand why is not working. I have three {columns} that indicate if a condition is either 1 or 0. So: column 1 can be 1 or 0 column 2 can be 1 or 0 column 3 can be 1or 0 I have a fourth formula column where I want to indicate the following: IF column 1,2,3 are all 1 then show “status A” IF column 1,2 are 1 and 3 is 0, then show “status B”, IF column 1,2,3, are 0, then show " status C" I have tried this formula but it wont go through: IF( OR( FIND(“1”, {column1} ), FIND(“1”, {column2} ), FIND(“1”, {column3} ), ), “status A”, IF( OR( FIND(“1”, {column1} ), FIND(“1”, {column2} ), FIND(“0”, {column3} ), ), “Status B”, IF( OR( FIND(“0”, {column1} ), FIND(“0”, {column2} ), FIND(“0”, {column3} ), ), “Status C”, ) Thanks for whoever takes time to help out with this.