Formulas

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?

Hi all, I have a table where I want to have a “Cleared?” formula box that looks up if the current record also exists in a view, “Outstanding items” In plain language, if an employee in “Pre-service” view also exists in the “Outstanding Items” view, place an :x: . Otherwise, if they don’t exist in the Outstanding Items view, place a :white_check_mark: I am thinking the RECORD_ID() formula might work (catching an exception) but I can’t quite wrap my head around how to use it with a specific view.

Hi Am fine with IF(FIND) formula when I want different outcomes but not sure how to set up when I want all outcomes to be the same. I know the outcome from AND/OR will be 1 or 0 which I can then work from but need help with how to write the actual formula! Below is my template formula I use to remember the IF(FIND) formula and adapt to whatever I need - if it helps to show me what I need to add where please use IF(FIND(“search#word”, {search#field}), “the#result”) Any help really appreciated have been searching but not finding yet. Thank you

Hello all. I want to do a simple sum on a group by. In my example, I am building a recipe calculator and want to find out the proportion of cost for each ingredient out of the total cost. My formula would be : cost/sum(cost group by recipe) In sql this is call a window function but I am not sure if there is the equivalent in Airtable. I have a screenshot, but seems like I cannot upload it. Must be because I’m a new in the community. Anyway let’s say i have recipe_a ingredient_a $3 recipe_a ingredient_b $6 recipe_a ingredient_c $4 recipe_a ingredient_b $2 I want a new column that would output that ingredient_b stands for 1/3 of the cost of recipe_a A workaround is to do a pivot table but it seems like a lot of trouble for something simple (plus not a very scalable solution) If you have any input, I’ll gladly take some. Thank you Stay safe !

Hi everyone, I have a table where user X can endorse user Y on a particular skill Z. The final table will be something like this: John | Carol | Painting Carol | John | Drawing Fred | John | Drawing Bill | John | Writing Then I will have a separate table with user profiles, and I would like John’s profile to display “Drawing (+2), Writing (+1)”. The way I would achieve this using a pure SQL mentality would be to group by name, skill, do a count, and then JOIN it with my user profiles table. The way I’m trying to achieve it with Airtable is to create a new column on every row with the value {Name} & “(+” & something & “)”. This would essentially be doing a row-level PARTITION and GROUP BY COUNT, and would allow me then to link to these records from the Profiles table and select the unique. In this case, we would have repeated Drawing (+2), Drawing (+2) and Writing (+1) for John, and the desired resu

My goal is to have a DOB field concatenate with a name and calculated age field. Currently the concatenate works fine with CONCATENATE(Child_First,", “,Gender,”, ",Age) = Rohnan, Boy, 10M When I add DOB field I get Rohnan, Boy, 10M, 2020-12-15T00:00:00.000Z How do I get rid of the T00:00:00.000Z at the end? When I use the Date_time format I get gibberish. CONCATENATE(Child_First,", “,Gender,”, “,Age,”, ",DATETIME_FORMAT(DOB,‘dd-mmm-yyyy’)) = Rohnan Boy 10M Tu-000-yyyy What I want to see is: Rohnan Boy 10M 15-Dec-2020

Hello, I’m writing with a question on how to set up a REGEX Extract. I assume it should be relatively straightforward but as a beginner I’m finding myself out of my depth. Any help would be appreciated! Goal: Extract a SKU number from a Webflow Ecommerce string. String Example: count: 1 height: 0 length: 0 productId: 618573ee195d39c0e1ba4199 productName: Test Board productSlug: test-board rowTotal: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantId: 618573ee82825fd59dc4b5bc variantImage: {‘fileId’: ‘6181a0f79b29435921241715’, ‘url’: ‘https://uploads-ssl.webflow.com/618058264546c2bae56f2188/6181a0f79b29435921241715_Screen%20Shot%202021-10-14%20at%208.46.20%20PM.png’, ‘alt’: None} variantName: Test Board variantPrice: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantS

Hi! I’m looking for help writing a formula. I need something that will automatically check off boxes in separate fields for 2/9 through 2/21 if individuals will be present based on the entered arrival/ departure dates. “if x date is between the range of arrival date and departure date, insert checkbox, otherwise blank” For example if the arrival date is 2/12/21 and departure is 2/20/21, I’d want it to check off all of the dates in between, but not 2/9-11 or anything after 2/20

I’m trying to put together a form for screening, however I cannot figure out a way to create a formula that would enable me to give an automatic output based on the form response. In short, if someone writes a symptom I want the last column to output a false, or a fail message. I have tried doing this but haven’t been able to. Here you can find a look into the form: And a formula I put together to try to get an automatic output:

I would like to create a formula that will: Sum the {Amt} fields only from records that have the identical data in another field {ticket/voucher #}. For example: Records 1, 7 and 14 have the identical data in {Ticket/Voucher #}. The formula would sum the amounts only from those records 1717.40 980.00 250.00 and put the total 487.40 in {Formula field}. The same would go for records 10, 12 and 15 and 11, 16 and 13. See link to image of my table. https://hctny-my.sharepoint.com/:i:/g/personal/office_highclasstravel_com/EesBZqFYxQpIvxnKzbAVLu8Bs6pO4BEkdNQWgzHui4Gj_g?e=4zSeTF

I’m using Eazyform app to get time slots separate from the date/time field. I am having trouble then figuring out what formula to use to calculate total hours for employee scheduling. Additionally I would like to be able to view in a Calendar view.

This ought to be easy. I want to display records on a calendar by a single date field, but the date field for display is calculated: It’s the DueDate if there is one entered, otherwise it’s CreatedDate. IF( DueDate=BLANK(), CreatedDate, DueDate ) Dates in, dates out, right? Nope. A formula field using the above formula cannot be used in the calendar, apparently because the result of the formula is not recognized as a date. Now the following DOES work: DATETIME_PARSE( DATETIME_FORMAT( IF(DueDate=BLANK(),CreatedDate,DueDate),"MM/DD/YYYY" ),"MM/DD/YYYY" ) Seriously? Is there not an easier way to do this?

I’m using a button with “Gmail” & ENCODE_URL_COMPONENT({Email}) & “&su=” & ENCODE_URL_COMPONENT({Subject}) & “&body=” & ENCODE_URL_COMPONENT({Body}) to send an email through Gmail it it works great, but how do I attach a file from an attachment field on the record? I really like the control of a button. I’d rather not use an automation. Thanks in advance!

Hi, I’m using Airtable to manage a conference. I have a table which has conference proposals with the authors’ contact info. Then I have another table with people that have subscribed to the conference. I’m trying to send emails to those that sent proposals in but haven’t subscribed. So I’m trying to accomplish that by comparing the emails from the Subscribed table with the ones in the Proposals table. These are the steps I followed so far: I linked the 2 tables and added a lookup field to the Proposals table to look for the emails of the Registered participants. In the Proposals table, the contact info is spreaded out in different columns, so I used CONCATENATE() to join all the emails with a separator. I realize that the lookup field returns an array while the CONCATENATE() function returns a string. Here is the tricky part. I’ve been trying to compare both groups of emails and it hasn’t worked. I need something that loo

I want to use a rollup to get an average of the existing numbers, but not all the fields in the column I want an average from have an existing number and return NaN. I want the rollup to ignore NaN.

Hi all, We have a table with records per customer, with multiple variables and their scores coming from typeform (auto-sync). The goal is to get out the 3 highest scoring variables and send the label of those variables to the customer. I can’t seem to find a way to get inside 3 fields the 3 column-names with the highest value of that record. Another solution I was thinking of, was when the auto-sync worked, to store the variables in different rows in another table but also with this I see no reason than to get the max 3 variables out of this table linked to the original one. Help would be much appreciated, thanks!

Here is what my formula looks like. I can not get Airtable to accept it. Can you say why? Current formula IF( ({Sales Rep}!="" ), AND( NOW()>={Followup Date}), “Delay Notification”) Proposed formula IF( OR( AND( {Sales Rep}!=BLANK(), {Followup Date}, NOW()>={Followup Date} ), AND( {Sales Rep}!=BLANK(), {Followup Date}=BLANK() ) ), “Delay Notification”, “Send Notification” )

Hey fellow AT users, I’m trying to set up a formula that calculates the renewal date of subscriptions based on if they are Monthly or Annual. I currently have 3 columns that I’m working with: Frequency, Billing Start Date, and Billing Renewal Date (which is where my formula is). I was testing the following formula: IF( {Billing Start Date}, IF( IS_BEFORE({Billing Start Date}, TODAY()), DATEADD({Billing Start Date}, 1, SWITCH({Frequency}, "/mo.", 'month', "/yr.", 'year')) ) ) The issue is that while it DOES show the next date based on the Frequency (i.e /mo. or /yr.), it’s not current. For example, a Billing Start Date of August 29, 2020, that’s set to renew monthly, is showing as September 28, 2020 instead of October 28, 2021 (since it’s currently 2021 and September is past). Does anyone know how I can get this to work? Do I need

The formula is simple in that I want field A multiplied by field B to = field C What happens is because field B is rounded due to the limit I put in the field because I just want two places after the decimal, it is calculating based upon the original math and not what the cell shows. For example. 51 x 68.33 = 3,484.83 but the cell shows 3,848.70 bc of the original amount being 68.3275 How do I get the field B to use the actual data in the cell instead of the original so that if someone not in the know can see that 51 * 68.33 = 3,484.83 Thank you in advance!

Hi Airtable Community! I’m developing a base to manage a bike polo championship. So far so good, I could implement all the function that are required, but I’m stumbling on a formula that I can’t get to work. On this table I want to get the final score for both Teams. As for shown with the example on the last column which it was achieved with a Count field and a filter with the name of the Team ‘Test1’. That is not a solution for me, because what I want is to filter the count based on the name of the column ‘Team 1’ and not on a particular team name. I’ve tried to combine IF () with FIND(), IF(FIND({Team 1},{Which team scored?}),1,0), but then I get only a 1 or a 0. Does anyone have any idea on how I can then count the frequency of the string in the column ‘Team 1’ and than count it or Sum it? I’ve tried to add SUM() or COUNTA() in the formula