Formulas

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: https://airtable.com/XXXX?prefill_First%20Name=Mickey&prefill_Last%20Name=Mouse&prefill_Email%20Address=111%40111.com&prefill_Mobile%20Phone%20%23=(111)%20111-1111 Result when Emailed: https://airtable.com/XXXX?prefillFirst%20Name=Mickey&prefillLast%20Name=Mouse&prefillEmail

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!

Hi all I’ve got a base that records the bookings of meeting rooms. It’ll be used to schedule bookings across all the rooms available. So I’ve got something like this: {Room} - e.g Room 1 (This is a linked field) {Start Date} {End Date} I need to be able to check whether a {Room} is available when making a booking. I don’t want a room to be available as an option if it’s been booked. If there’s a clash of dates, then a room can’t be selected. What would be the best way of achieving this?

Hi all! I’m setting up a Zap from Airtable to Webflow CMS, and it’s all going great (loving it!), but I can’t seem to import multiple images in Airtable into Zapier’s action then publish that into Webflow CMS. It only seems to attach one image at a time. Is there a way to zap multiple images into Webflow’s CMS image gallery ? Thank you all :slightly_smiling_face:

I am trying to find a formula to search records for the next 7 days (or 1 week). I tried the formula DATEADD(TONOW({Birthday}), 1, 'week') or even IS_BEFORE( DATEADD(TODAY(), 1, 'weeks'), {Birthday}) to no avail as my records came out from starting the beginning of the year. I want to find records that are within the next 7 days from now as shown on the image. Anyone has a formula to this?

Hi, I am trying to clean some of my data but I’m not sure which formula to use. I have three columns (URL, Date, Data). I need to extract the strings from the data column, the date and the URL (which are always different). In the Date column, I have LEFT(Data,10) which returns the date perfectly. In my URL column, I have MID(Data,11,256) which returns the URL. I’ve set up a count of 256 characters to be sure I get all of it but I’m sure there is a more elegant way to do it. Now the main problem is that sometimes I get 2 identical URL in the data column but I only need one of them. So how can I be sure to always get the first URL and discard the second one if I get a duplicate one. Many thanks in advance!

Hi all, I am struggling to find the error in my nested if() statement. I am trying to create a ‘status’ field for project outputs based on their due dates, using a check box ‘complete’ field, and two date fields. If checkbox complete is checked, show “Complete” If not complete and the original due date has not passed, show “On Track” If not complete but original due date has passed but not the tolerance (adjusted) due date has not passed, show “Overdue within tolerance” If not complete, but both original and tolerance (two seperate date fields) have passed, show “Overdue exceeding tolerance” Currently the formula I’ve used is this: IF({Complete?} = ‘1’, “Complete”,IF(IS_BEFORE({Baseline due date (DD,MM,YYYY)}, TODAY()), “Overdue within tolerance”, IF(IS_AFTER({Tolerance (no Covid)}, TODAY(), “Overdue exceeding tolerance”, “On Track”)))). I am not sure if I should be using an AND somewhere. Currently the formula is accepted but only ‘Complete’ and ‘Overdue within tolerance’ are returned

IF({amount of discount}=0," ",IF({amount of discount}<0,{amount of discount})) I got this to do what I want - only show a value on a sales receipt if the value is <0 The problem is that the answer is considered a string answer and I cannot format it to display as currency. All fields leading into this formula are numbers (formatted currency) I want the results to be displayed as currency. Suggestions?

Hi SQUAD, Maybe this is a stupid question, but I’ve been wracking my brains trying to figure it out and couldn’t find a solution. My Scenario: Table 1 - list of 300 users with assigned devices by serial number. Table 2 - list of 1000 devices with other specs, including serial number. What I am trying to do Create a third table with all Serial Number listed on Table 1 (I can copy manually) and display on Field 2 the name of the user (record from Table1) and display on Field 3 Other Spec (record from Table2). On excel, it’s something simple that I can resolve with VLOOKUP (value, table, col_index, [range_lookup]). Any solution/idea for Airtable? Thanks so much!

Hi, I am trying to get a field to show either Priority Normal, Priority high or Priority Critical depending on how far away from today the “manufactured by” date field is. ie. if 3 weeks away priority normal if 2 weeks away priority high If 1 week away priority critical Aby help would be greatly appreciated.

Hello, I’m new writting formulas, I’ve read all the guides on airtable’s website and other posts in the forum. However I can’t get my formula to work. I only need that when the value of a cell is equal to a number a specific name appears. This is what I’ve wrote; IF (Total = “6.99”, Hernia discal, BLANK()) But airtable won’t let me keep the formula because it’s misspelled. Any advises? Thanks.

Hi. I’m trying to use DATETIME_Format() inside of a formula field to display the day in which an order was placed. These are the dates of the orders. For reference, the 18th took place on a Sunday. The formula block is a very simple DATE_FORMAT() function, showing the day. However, it seems as if the date format is incorrect and is defaulting to GMT time (adding 4 hours to the time fields, causing the time to roll over to the next day). I don’t know how to fix this, and I can’t change it because under formatting it says: Can someone please help?! Thanks

I am building a database for a music education charity which has five bands, about one hundred members and over a hundred musical instruments which we loan to members. With different tutors issuing instruments at different venues, I want to keep track of which members have what instruments, which instruments are in the store cupboard and which ones are elsewhere eg repair/service. I have various locations on a single select field so an instrument can be allocated to a location. Then, if “Existing Member” is selected, another single select field of member’s names comes into play. Each instrument on the [Instrument Stock] table has a unique ID directing to a form and profiling the Instrument ID and a time stamp. To increase compliance with logging instruments into the database, I have made some QR code labels which stick to the instruments and their cases. These open form refilled with the instrument ID and a timestamp. After about two weeks building the system, I became aware that for

I have created an awesome base to run agendas for my team. People can submit topics, I can print them a pdf of the weeks agenda, take notes on the meeting, and create action times. I also have views for each member of my team and meeting so I can run automations to remind them to submit topics two weeks out and the day before. I want the ability to be able to automatically carry over the notes & action items (each their own column) from the previous meeting to the next meeting so the agenda printed has those items in it. How can I create a formula that automatically carries over items from the previous row essentially into a column on the next row?

I’ve knocked my head against this for a while, including seeing a bunch of possibly related posts. My twist is that I’m trying to use two different data types. OK: I’m keeping track of something over time. These days, I can record the date it happens. For stuff that happened a while back, I only know the year. Therefore in a certain Table, I have one column for Dates (if known) and another column for Year (if date not known) . The Dates column links to a Table where each record is a date. The Year column is a multi-select dropdown list. What I want to do is make a column called Year Overlay that extracts the year from any date in the Dates (if known) column and joins it with the year from the Year (if date not known) column. CHAPTER Date (if known) Year (if date not known) Year Overlay (link to Dates table) (Multi-select) !! HELP!!` Ch 1 2020/10/22 2006 2006, 2020 Ch 2

I have a base that is set up to pull in a lot of data from our CRM, and then give my staff the ability to manage their event workflow with additional fields. It is set up so that there is one record created for the Parent and multiple records created for each “Dummy” event (long story). The Event Manager is linked to the Parent Event and each staff member is linked to one of the “dummy” events. I would like to write a formula that scrapes each of the linked staff records in the the dummy events and combines them into a new cell in the Parent event. It would end up looking like this: So ‘Matt’ had the “RXPO Real Game” and ‘Patrick’ and ‘Robin’ are on his event. In the field “Runners Assigned” it would list Robin and Patrick as linked records as well. Any help greatly appreciated.

Hustling bookseller back with another puzzler. What formula would I use to take data in my dad’s {Publisher MLA} field (NY: Atheneum, 1979) and separate it out into three formula fields ({PublishPlace}, {Publisher}, and {PublishYear})? Bonus for a formula that can change NY to New York too. Please and thank you. :slightly_smiling_face: