Formulas

I am building a CRM database. There is a table in my database called Deals that contains a multiple select field called Deal Stage. One of the options in that field is called Closed Won. Those are deals for which we have a signed term sheet and are going to provide a loan. I want to create a new table called Loans where I list details for each of the loans we have made to that subset of Deals for which we Closed Won. For the primary field in the Loan table, I would like to create a formula that looks up the name of the deals in the Deals table and only finds those ones that have the status Closed Won in the Deal Stage field. I would be so extremely grateful for help me with this. Thank you!!!

Hello wonderful Airtable community! I was wondering if anyone knew a way to take values in a lookup field and then provide the percentage of duplicate values? (e.g. for numerical values: “5,6,6,6,7” the output would be 20% for 5, 20% for 7, and 60% for 6) (and e.g. for string values: “Dentist, Doctor, Surgeon, Dentist, Doctor” the output would be 40% for dentist, 40% for doctor, and 20% for Surgeon) OR a way to see this in a chart if that’s easier Edit: figured it out. In the shared view for the base (so that the filter doesnt stay locked), just have to filter in the View I needed, and the chart will update accordingly.

Hi All, I am trying to create a Email Link formula where “EmailAllStaff” is a rollup and “NamesAllStaff” is a lookup. this should be a strait forward thing to do but i am somhow stuck :slightly_smiling_face: i must be missing something obvious thx! :m:

Hey I want to track the dates of when we shipped our items. So if I write Shipped, into one field, another field should be populated with the current date. I tried this: LEFT(IF({ :writing_hand: Shipping status*} = “3. Shipped”, LAST_MODIFIED_TIME({ :writing_hand: Shipping status*}),""),10) But sadly this is not really what I am after since it would alter the date if someone changed the Shipping status field due to whatever reason. Also writing an if formula with if (shipping status = SHipped), Today (), “”) is also not working since it will update the date on the next day. I need an if formula with Today, that will not change after calculating it once. Thank you

I have multiple checkboxes in my base, showing only 1 checkbox per view. Can someone please help with putting together the correct formula to update my “Status” field based on the checkboxes. The closest I can find is support when 1 box is checked at a time, however, once this step is complete the checkbox for that view is hidden and the new view reveals step 2 - showing only step 2 checkbox. So as you carry on through the task management process multiple checkboxes will get checked and I don’t want the “status” field to show all fields, just the one on the current view. Typing that to explain it is harder than I thought. So in a nutshell- think of the checkboxes as status dropdowns - and manually changing the status drop down during each phase of your project. That’s what I need to accomplish but with checkboxes. Thank you

HI all! I could really use your help please. I have a column that I call “Sponsor” that is a value of a number like 12345 (this is imported from a spreadsheet) but instead of showing 12345 (cause I can’t remember who that is) I’d like it to show a name like Sally Brown. There are probably 10 unique “Sponsor” numbers that I need to correlate with 10 different names.

I’ve got a start date in one column and I’d like to add hours from another column to have it output a date and hour for me. For instance, I start a job June 26 12:00 and I want to add 40 hours to that date. I’d like it to output “June 27 4:00” for me. I tried the following: DATEADD({Ramp Start Time},{Ramp Length Hours}) “Ramp start time” is a date field and “ramp length hours” is a duration field. Currently I’ve got "ramp start time as “6/27/2020 10:00” and “ramp length hours” at 40 and it’s giving me “6/27/2020 10:02” What am I missing?

I am using Stacker and AirTable for a simple app. It shows a list of orders that users enter. I want to show summary data, but here are the problems: if I share the base, everyone will see all email addresses for all users (for shared bases, you can’t hide fields as public users can toggle your hidden fields) if I share the view of a table, it doesn’t show the blocks that display the summary data I want to show Stacker doesn’t have the ability to summarize data (IE: average, min/max etc) I thought about having a ‘stats’ table but I’d have to link every order record to each field somehow, and then I could pull that data into Stacker. Any ideas?

How do you sum a column of data. I try the formulas that SUM({GROUP}), but the result is not the sum of all the data in a column.

Hi there, I’m relatively new to Airtable and admittedly, not great at formulas. Here is what I’m trying to achieve. I work for a small book publisher that is a subsidiary of a non-profit organization, and we run ads on Amazon. Our books are sold to Amazon at a discount. To complicate things further, our partner non-profit receives 65% of the sale and the publisher receives 35% of the sale. I’m trying to track how much we are spending on Amazon ads as compared to the actual profit the publsiher receives to make sure there is enough ROI per ad. So sometimes, we can be losing money on ads, even when Amazon shows the ad as profitable. Therefore, I need a way to see negative currency values in my table. How would I adjust the following formulas to report negative currency? Profit Per Book = {Conversion Cost Max}/CPC Orders*{Profit Per Book} Thank you!

Hi there! I’m a new Airtable user. I have a list of URLs. I need to get their document titles. In Google Sheets, my formula would be: =IMPORTXML("https://nickgray.net/bdc/","//title") where “https://nickgray.net/bdc/” is the URL I want to query. How can I do this in Airtable?

Hi everyone! I have a nested formula that supplies me with tag suggestions. The tags depend on info from 8 different columns. I’ve gotten it to do almost everything I want it to, it suggests tags really well. The problem is the ELSE part of the formula. If it finds no tags I would like it to say “« :no_entry_sign: No Tag »\n”. I can get it to do that just fine by using a second column, but I would like it all in the one if possible. Here’s what I’m working with. It’s way longer, but this is the gist of it structure wise. IF( OR( {Column 1}, {Column 2}, {Column 3}, {Column 4}, {Column 5}, {Column 6}, {Column 7}, {Column 8} ), IF( OR( FIND("light", LOWER({Column 1})), FIND("vanilla", LOWER({Column 8})), AND({Column 2} >= "2020-11-01",{Column 2} <= "2020-11-16") ), "« Tag 1 »\n", "" )&''&IF( OR( FIND("bi", LOWER({Column 1})), FIND("barn", LOWER({Column 1})), FIND("bi", LOWER({Column 8})), FIND

Would you help me find what’s wrong with my ARRAYJOIN ? ARRAYJOIN([{Field 205}, {Field 206}, {Field 3}, {Field 4}, {Field 207}, {Field 6}],"; ") This is the error message i get : Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.

Hi All, Hoping someone can figure out what I’m doing wrong here. I’m trying to get the number of weeks (preferably with one decimal point) by subtracting two dates from each other. In addition, there is an IF function, as described below: IF {date 1} < TODAY, then do the difference between {date1} & {date 2} in weeks format. If not, then do difference between TODAY() & date 2. I have this, but it’s not working: IF({Contract Signed Date}<TODAY(), DATETIME_DIFF({date 1},{date 2},‘days’), DATETIME_DIFF(TODAY(),{date 2},‘days’)/7 Thanks, Dave

Hi All, I am very new to airtable and trying to decide if this tool is right for our team. I think that it is, I am just having an issue trying to calculate the percentage. I have a count of subtasks as well as a count of the completed subtasks (linked record to another table,‘subtasks’). I wanted to use these two fields to calculate the percentage of completion. I added a new field and called it percentage complete and used the below formula: {Number of Completed Subtasks}/Number of subtasks and formatted as percentage. Since some items do not have any subtasks at all (0) I am getting the NaN error for percentage. How can I rectify this so that the NaN error, essentially 0/0 returns an actual percentage to help me with the bigger picture? I have added a screenshot for reference: any help is appreciated, thanks!

It’s 2020 halfway through. Are we getting trig functions in formula field? I need to have the ability to instantly calculating values when some fields are populated (no script block manual triggering).

Hi, I’m new here and trying to create a simple product catalog for a client, I’m looking at the template " Product Catalog" but I don’t know how to send an order form to the final client (where can I see how to create one), how the final client can chose the product, quantity, etc. I viewed most of the info and videos online but I didn’t find that yet…I already checked the product catalog page but still don’t know how to link the product catalog with an order form from the final client… please help! thanks

Hi there, I was wondering if someone could help me create a formula so that if I have a Text field, it can automatically add a “( )” around the first 2 or 3 digits? I know I could use the phone number field option but I rather not. EX: 1301234901 --> (130)1234901 OR (21)301234901 So far this I’ve gotten the following but they’re applied in separate fields. EX: “Field 1- Number”, Formula: LEFT({Number}, 3) As you’ll see by using this formula, it only isolates the 3 characters. Ex: “Field 1- Number”, Formula: IF(LEFT({text number}, 3),"(") This only gives me the ‘parenthesis’ in return. All help is appreciated :grinning:

I am not sure how to add in an “or” situation to this formula. Here is my formula: IF({Task Start Date},IF({Task Status}=‘Completed’,{Description}&" | “&‘COMPLETE’&” | “&DATETIME_FORMAT({Status Updated},‘MM/DD’),{Description}&” | “&DATETIME_FORMAT({Task Start Date},‘MM/DD’)),{Description}&” |") This only works if my Task Start Date is completed, but I need the Description to show regardless of the date column being filled. Does this need an if/or statement? Thanks!

Hi there, I am a relatively new Airtable user with a basic understanding of formulas which has held me in good stead until this week. I have a table linked to a form. It’s a form designed to use for a customer to hire various types of product for us. The form populates all the required information into a table where it shows all the relative information to us about what product they are hiring. The products are theatrical props and theatrical costumes. In the table, there are fields for “collection date” and “return date” for each above category and currently I have a field called “number of days hired” for each category that is reading those dates and working out the amount of days that product is hired by the current formula: DATETIME_DIFF({Date for costume return}, {Date for costume collection}, “days”) This field then multiples with the cost amount in a further field to tally the total cost. My issue is that when one of the date fields i

So I have a column titled “Days Since Update” which is the days since a certain date to today and then “Field 10” is based on an If Formula (if the status is completed it gives a 0). I now want a formula column which gives the smallest of these two values. I’ve tried Min formula however this ignores logical values (values in field 10). Does anyone have any suggestions? All I really want is that when the status is completed a value doesn’t come up in Days Since Update! Is there a way I can merge the Min and If formula together to achieve this? image|281x500

To create a more efficient process in pricing materials I’m working through different ideas and it’s just becoming messy. I’m trying to figure out if a formula would provide the best option or possibly a drop-down based on conditions. Example : I’d post a picture of my base, but it’s not quite finished, which is why I’m reaching out. If I need to purchase paint and paint supplies to bid materials needed correctly I’m trying to find the best option to fill in QTY TO ORDER gallon of paint is $30.00. Coverage area is 200 SF paintbrush is $2.00 needs replaced after 100 SF All materialS, coverage/price breakdown, is listed in a materials table. In the Bidding table I’d like to: COVERAGE AREA MATERIAL QTY TO ORDER PRICE Manual input SF, LF, tile count, etc. Manual input based on linked field

I have a category called frequency and it has four options. I want when i select one option another option automatically imputed in another category .

I was wondering if there is a way to do a percentile calculation in Airtable. Basically I’m trying to determine salary percentiles (in my case let’s say 75th percentile). So say I have a column of salaries in a “Positions” table with 25 rows (mine has way more, but for the sake of simplicity). I figured out that I can do the linked record trick to get all the values pulled into a single cell in another table, call it “Summary”. So i would use a lookup field to pull all 25 salaries from “Positions” into an array in the other table. I can then do a count of the number of values in that array. Then in theory I would multiply that count times my percentile (0.75 in my example), and round the result up to an integer, n. Then it’s a matter of finding that nth value in the array. BUT the array isn’t in order, so without being able to sort the array in ascending order, I can’t actually do the percentile. Anyone have any experience with this type of problem and a

Hi! I need a formula to calculate the average of the previous 4 weeks %. Screenshot attached. Does anyone know what I would need to put in for that? I’m assuming the July overall column needs to be a formula, right?