Formulas

I have a table with multiple single select columns. Each single select column generates a numerical value, through a switch formula. I want to average the numerical values for each switch formula column. When I average the columns out now, I get back NaN. Here are examples of my switch formula columns: Is there a way to do this?

Hello everyone! I need your ideas :slightly_smiling_face: I am trying to invert a string (of numbers). I had this nice easy formula in excel, but I am not sure how to transform it to Airtable as it contain an array. exemple: A1: 123456 A2: =TEXTJOIN("",1,MID(I49,{10,9,8,7,6,5,4,3,2,1},1)) result in A2: 654321 Is it possible to convert or do I need a new method? Those functions seems to be available in Airtable, but nothing worked from all I tried. As I could not find similar case in the forum I am asking it here today so others can also get an a solution! Thank you very much for your kind clairvoyance :grinning_face_with_big_eyes:

I can sort of see my mistake but not sure how to come at it a different way to correct it. What i am trying to ask is “Is the job booked in date, within the previous 7 days of today ? Is the Job booked in Today? Is the Job booked in within the next 7 days from today?” What i have thus far: IF({Job Booked In} >= 7, "PREVIOUSLY", IF({Job Booked In} <= 7, "UPCOMING", IF({Job Booked In} = TODAY(), "TODAY" ))) “Upcoming” and “Today” both work but “Previously” does not. I’m guessing it has to do with it being a negative number i am not sure what i am looking for.

Hey everyone, I am trying to set up my upcoming family tasks and expenditures over the next couple month. One feature that would be nice, is a “remainung budget for month x” formular. I set the amount I am willing to spend on my tasks every month of the year e.g. 1000 USD max. (don’t know where and how) I prioritise my tasks ( single select - High, meduim, low) set an expected expenditure / how much is it going to cost me (Single select Currency) set the month I want to make that expence (single select - January to December) ----> How much do I spent each month??? What is my available money in month X? Airtable has to combine my max amount i am willing to spend, the month the money is planned to be spend and the expected amount, to give me my available budget for this particular month. This wo

I got an issue with these two characters… The issue is with: ’ and ’ When I copy a formula from AirTable into textpad on MacOS and start editing the formula there, the ’ character gets eventually somehow replaced with ’ and when I copy the formula back into AirTable, the formula is broken because some of the ’ got replaced with '. It’s strange. I don’t have any text-expander or text correction software so not sure why it happens. Wondering if anybody else has the problem. The issue doesn’t happen when editing the formula in Airtable box, it only happens when I copy the formula outside to edit it and then copy it back to Airtable. The editing box for formulas is way too tiny to be useful when editing long formulas. Interesting t

How can I implement such a formula in Airtable? // all fields in an Airtable table const allFields = [ { key: 'type', value: 'TypeOne', } { key: 'name', value: 'Some name', }, { key: 'description', value: '', }, ] // set in a field of the table const requiredFields = [ { type: 'TypeOne', requiredFields: ['name', 'description'], }, { type: 'TypeTwo', requiredFields: ['name'], }, ] let invalid = 0 for (let i in requiredFields) { if (!allFields[requiredFields[i]]) invalid += 1 } if (invalid) return '⚠️ Some fields are not filled out!' For now, I have to do something like: IF(type = 'TypeOne', IF(OR(name = 0, description = 0), '⚠️ Some fields are not filled out!')) & IF(type = 'TypeTwo', IF(OR(name = 0), '⚠️ Some fields are not filled out!')) But I would like to set just an array of required fields but I don’t know how

I am trying to convert between WEEKDAY(TODAY() and the word version for the day of the week. I would imagine it is a common need. This works just fine. IF(WEEKDAY(TODAY())=1,“Monday”, “”) Why might this not work? IF(WEEKDAY(TODAY()) = 1,”Monday”, IF(WEEKDAY(TODAY()) = 2,”Tuesday”, ”Wednesday”)) My error: Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.

Hi there, I just need a little help to finish my workflow ! I want to put a status from the automatic field “Last modified time”. Formula is : IF(DATETIME_DIFF({Update URL}, TODAY(),‘days’) >= 13, “ :exclamation: ”, IF(DATETIME_DIFF({Update URL}, TODAY(),‘days’) >= 7, " :alarm_clock: ", IF(DATETIME_DIFF({Update URL}, TODAY(),‘days’) >= 1, “ :repeat: ”, IF(IS_SAME(TODAY(), {Update URL}), “New :rocket: ”))))) It works pretty fine when the field is “date” but won’t work when field is modified in “Last modified time”. Anyone can help please ?

In Excel I would have Column A (Date), B (Time in Seconds), and C (Percentage Increase/Decrease). I created a formula that when I’d enter in Column B (Time in Seconds) starting at the row 3 (because row 1 is the header and row 2 is the first entry), C3 would then be populated with the percentage increase or decrease. Is it posible to do something like this in Airtable?

I have a link field called Sale and a formula field called Status. What I want is for Status to say “For Sale” when the Sale field is empty, and for Status to say “Sold” when the Sale field is not empty. I swear I do try to search for answers in docs and previous forum discussions before asking here, and I’m so grateful for those of you who come through. From a previous discussion, I used this formula which helped part of the way… IF({Sale},{Sale}, “For Sale”) I just don’t know how to add the “Sold”

When I enter this formula… IF({Book Type Entry},{Book Type Entry}, “Book”, IF({Book Type Entry},“Advance Reading Copy”,“Advance Reading Copy”)) …the formula field accepts it and all seems well until I go to add more to the formula and see it’s been trimmed down to just this… IF({Book Type Entry},{Book Type Entry}, “Book”) …and when I close the customize cell popup I see that the information it was supposed to render for the second part of the formula is also missing. How can I solve this problem?

Hi, I work for a public television station and I’m creating a content database to store records of our productions, improve workflow and communication, etc. We have to submit descriptions for tv listings and the character limits have to fall in the range of 101-250 characters. I’m trying to create a formula that displays the number of characters and let’s the user know if the characters are below 101, between 101-250, or above 250. I find people need the visual warning since I can’t set a X amount of characters or character cut off. This is the best I can figure out so far, but it’s a little odd: LEN({Episodic Description}) & IF(LEN({Episodic Description})>250, " ❌ Too Long", " ✅ Good") & IF(LEN({Episodic Description})<101, " ❌ Too Short", " ✅ Good") For example, if some is under the 250 limit the Good pops up. If there isn’t a better formula I can definitely change the tag from " :white_check_mark: Good" to “Over 101” & “Under 2

I want to find if an item exists in a certain column in my table. Is there a way to implement it?

Hi all, I have been searching for this answer and haven’t came up with anything that is clear enough thus guides me in writing this formula. Let me try to set it up… I have a multi-select column (call it column A), which is a list of months. I have another column (call it column B) that uses a datetime_format formula to show the current month. I want to write a formula in a third column that returns a value (nothing specific, whether it is yes/no, a number, etc.) if the current month (column B) is selected in column A. Is this possible?

Hi there, I am using a look up field in a formula (on another field). The formula is just CONCATENATING the look up field, but I receive an #ERROR! when I try this. Is there a way around this. Cheers! Sam

IF(Paid>0,({Profit}/{Paid}*100),"") This is my current formula after playing with it for some time. I am calculating ROI, with many of my records containing no initial investment, which left the result of {profit/paid} as infinity on those lines. I want to easily calculate my average ROI, so I can’t have the infinity records. Initially, I thought I could add to the formula IF({profit/paid}=“infinity”,""), but that formula failed, so I ended up with the above. The problem is, because of that formatting, I now cannot format the formula to show results as a %. Any ideas?

Hi There. I’d appreciate getting my Emailed Prefilled Airtable Forms Link to sync up with my AirTable Data. I have 4 Fields being prefilled into an Airtable Form. First Name Last Name Email Address Mobile Phone # I wrote a formula (with the help of the amazing Airtable Community) to pre-populate filled fields into an Airtable form. The link for the form with the pre-populated section works perfectly in my AirTable Base. The problem arises when I use Airtable Automation to email the link. The emailed link is different from the link that was created in the Airtable Base. I’d appreciate any help to get the emailed link to match the formulaic link shown in my Airtable. Thank you. Result in AirTable:

I am using a database to keep track of clothing consigners. In my “seller” sheet, my first column is the ID column and I was using LEFT({First Name}, 1) & LEFT({Last Name}, 1) to assign IDs to the sellers. But now I have an issue where I have 2 sellers with the same initials - so I need the formula to count up as well (IF {ID} IS_SAME, COUNTA, LEFT({First Name}, 1) & LEFT({Last Name}, 1) So I should be getting this for IDs that continuously counts up - but it’s not working and tells me the text is wrong. HELP! AB AB2 CD CD2 CD3 FE GH GH1

Hey, we partly have a subscription model in our company and our deals are closed for a certain period of time. For example a deal is closed for 3 months and a new invoice is issued every month. How can this be easily displayed with airtable in terms of forecasting on monthly revenue bases?(Especially the automatic transfer to the next month) thanks in advance. best

Hello community I’m trying to add this function to the main column. This is the formula concatenate I use to show specific data. CONCATENATE({ :anger: Nombre Jugador},’ ‘,’|’,’ ‘, MID({ :anger: Año},3,2)) &’ '& { :star: :x: :heavy_dollar_sign: :older_man:t2: }& { :fire: }& ’ | '&{ :anger: Club 20/21} What I am looking to do is replace the star :star: emoji by :fire: when this column contains the emoji. And when doesn’t maintain the :star: emoji. Thanks in advance for your time and effort. Regards.

I am looking at attempting to combine the two functions into a single input. The second is to catch jobs that go overdue earlier. Original Question: Formula to track days past a set date, multiple outputs Justin Barret provided this code: IF( AND( NOT({Work Completed}), TODAY() >= {Due Date} ), IF( {Days Elapsed} >= 84, "12", IF( {Days Elapsed} >= 56, "8", IF( {Days Elapsed} >= 28, "4" ) ) ) & " weeks+" ) and I was able to use that to create this: IF( AND( TRUE({maintenance job}), TODAY() >= {Due Date} ), IF( {Days Elapsed} >= 14, "2 weeks+" ) ) )

Hi all! I am trying to convert a current set of data w/ formulas from Excel to Airtable. I am running into a roadblock when looking for an equivalent to Excel’s = Rank and =Rank.EQ. Any suggestions on formulas or alternative solutions?

Hello, I’m trying to organize some data from a csv upload. In the csv upload, there is a “Date” field, and I want to create a daily summary of all records per day. For ARRAYUNIQUE(values) to work, I’d have to link all of the records with that date. However there are many days from one single upload. Is there a way to get all unique days from the whole table, or a way to link all records per unique date? Thanks!

I had a group of students fill out a form in which they gave star ratings to a group of submissions. So the result is a table in which each record/row includes one student’s ratings. The column headers are the submitters’ names. What I want to do is calculate the average rating for each submission and then sort the results by that number (highest- to lowest-rated submissions). In my table, I can get the average to show in the summary bar. But then I don’t know how to create a table or view that allows me to sort those averages of each column. I’ve read over a bunch of other posts that talk about rollups and formulas for calculating averages, but I have not been able to figure out how to make it work in my case. Wondering if I need to have two linked tables somehow. Thanks for any suggestions.

Hi guys! I am looking to transform a lookup result into a Markdown format. Doing this will allow me -I think- to then export that field via Integromat into Webflow and displaying it in a bulleted format. In Webflow, I am using what is called a “Rich Text field”. My guess is if I do a formula that takes the info I am looking up and then transforms into a Markdown or even HTML-kinda format this should be possible. Has anyone done this before? Any ideas? I am attaching a photo of the lookup field in Airtable, the rich field in Webflow, and how its supposed to display in the end. Thanks!