Formulas

Trying to figure out a formula to run a conditional average: Within the table, each record is a single asset. One field tells me what asset it is (unique ID), one field shows me where its physically located, another shows me the type of asset (may assets, although all unique are the same TYPE, etc.). I’ve got another field that contains a formula that shows me the average use each asset receive within a given time sample - that field/formula work perfectly. What I’m trying to do is average that use for the assets, but ONLY where the location and type are the same. Example - Location #3 has 4 assets. 3 are Type 1, the last is Type 2. The use formula is returning values of .25, .50 and .75 for the three Type 1 assets. I’d like this formula to average those 3 values together to return .5, BUT ONLY for Location #3, and only for Type 1 assets. The Type 2 asset should remain blank because its the only Typ

Hi everyone, Please advise how to create a formula for a field (let’s call it “CityCount”), which would automatically display the number of records in this table which have the same value in the field “City” as a current record? Here is an example: there are 3 records in the table with City=London, thus each of the record should have CityCount=3. If I add one more record with City=London, then each of such records would get updated with field CityCount=4. Any help or ideas appreciated!

Hi, I have some problem in calculating CO2 calculation. Calculation result values as below. Excel : round( 8.110000 * 0.000039 * 56.100000 * 1.000, 6) = 17.925857 Airtable : round( 8.110000 * 0.000039 * 56.100000 * 1.000, 6) = 17.743869 Each data in Airtable and excel is in the field (decimal 1.000000) It would be appreciated to help me. I think excel value would be right calculation result. Regards JP

From a look up field a have a row of values which are in my case dog breed subscriptions. I cropt this row by using the Array unique functions so it only shows unique vallues. Some rows have just one unique value of some have for example 3. I want to seperate always the last value in a row with an & or and instead of a comma. For example Bernese Mountain Dog, Rhodesian Ridgeback, Labradoodle → Bernese Mountain Dog, Rhodesian Ridgeback & Labradoodle Rhodesian Ridgeback, Labradoodle → Rhodesian Ridgeback & Labradoodle Does anyone now a solution for this problem?

Someone already asked above question, though. I couldn’t find the answer. I know how to use “prefill function” for text but I want to use it for attachments file. is it possible?

Hi, I have a rollup field for ‘Last Contact Date’ to fetch the most recent Communiques (which is my unique name for Contact Points) date and display it in my Contacts table. So far so good. Now - here is the challenge. For the sake of convenience I just thought it’d be cool to display the related text for that particular field in another field - like “Last Contact Notes” - this would give me a quick refresher on that contact if I was going to call them again. Just an idea. And wondering if it’s possible? I made a video to clarify: And… somet

Either I am crazy or I cannot find a drop-down to create a formula in any of my columns. This is my first time using the app I created a table from scratch.

Here’s one for you. We have a purchase order for a job, with a total amount in one table. We have multiple work shifts each be a varied amount depending on hours and rates of pay in another table. I’d like to be able to summarise all shifts and subtract them from the total purchase order to check we are under budget. Is this possible?

We are trying to add a formula that if one room is available even though others are not it should still says “Available”. If one room says Becoming Available but there’s no room that is available then it should say “Becoming Available”. If all rooms are rented then it should say “Rented”

Hi community, I am looking for a solution where I can compare two tables and update the main table with both status and also the new data entries from the second table. Appreciate if anyone can shed a light on how this could be achieved. Basically we have a data scraping code that writes the entire dataset to a table (let’s call this secondary table) on regular basis. And we have a main table used in production. What we want to achieve is to use the secondary table to update the main one. If the dataset does already exist in the main table and also exist during the latest script run on the secondary table (there is timestamp), then the “status” field in the main should be updated to “available”. If the dataset does not exist in the main table but show up during the latest script run on the secondary table, then the “status” field in the main table should be “new” If the dataset do exist in the main table but does not

Hi I am new to Airtable… big learning curve. I am using it for Shopify with the Shopify App Airpower for bulk uploading including images! (Amazing !). However I am changing themes and need to add to the text in my multiple select field. example. I use tags… so in a single field my current tage are formatted with a underscore… I have 20 tags…: color_blue, color_green, size_7", size_9", shape_tube, shape_slant, skirted_vinyl I need to keep the existing “tags” for now (during the transiton of themes) but for my new theme I need all the same tag but WITHOUT the first workd and underscore example: blue, green, 7", 9", tube, slant, vinyl… how can I creat an automation? I am not proficient in formulas other than excel. Please help! Thank you!

Hi I have two tables Transactions - with a date Periods of time - with a Title, Start date and End date. ie “Year 1” runs from 1st April 2019 to 31st May 2020, “Year 2” runs from 1st June 2020 to 31st May 2021. I’d like to have a field in the transactions table, which looks up from the periods of time table, which year a transaction took place. So if the transaction was on the 1st July 2020, it would be in the Year 2 fiscal period. If it took place on the 1st May 2019, it was in the Year 1 fiscal period. I know I could write that with a long IF statement, but that is cumbersome! If my fiscal periods were whole years, (ie 1st Jan to 31st Dec) it would be so easy! Any ideas or suggestions very welcome. Thanks, Andrew

I have a complex formula about 2 letter size pages long. The formula didn’t work and I spent 2 hours of my life trying to figure out why it didn’t. Looking at the formula, after procrastinating a bit I went on crunching through it but I was expecting to be rewarded by a nice catch worth my time going on through this fishing expedition. Instead I was taken by surprise of how sometimes complex problems require simple solutions. Here is the over-simplified formula. The issue was I wasn’t negating the formula on the LEFT correctly. To negate the formula on the LEFT, I need to negate also function “OR” and turn it into “AND”. I guess this is a 101 basic of Math but it has been so many years I don’t remmeber. I guess I am getting old now. The first column on the left is PRICE and the field can be empty, zero or non-zero. The formula is check if the field PRICE is empty or zero and show “YES”, otherwise show “NO”. The first picture shows the formula wasn’t conve

Hi everybody how are you? I am trying to create a formula that divides by 2 the amount of the field “Quantitat” only if in the “Tipus” field it says “sacas” and places it in the field “Tones”. Is it possible? I am using the following formula: “IF (Tipus =” sacas “,” Quantitat "/ 2) is it correct? Thank you very much for the responses, greetings.

Hello, I’m Phil And I’m working for a video production company. We create video for others companies, all the production follow up is made with airtable. I’m working on a formula that can increment a cell (0,1,2,3) if specific date cells are filled let’s illustrate my use case I need to log the delivery date of video production (SLA are calculated using those dates) if {v1} is empty = work in progress = 0 if {v1} contains a date = done = 1 if {v2} contains a date = correction done = 2 if {v3} contains a date = re-correction done = 3 then I’d like to use that “increment calculation” to calculate my production costs using another formula {unit_cost}*{increment_calculation} something like that. Tell me what’s your advices :slightly_smiling_face: thanks

I am using a simple formula: IF(TDay > TODAY(), 1, 0) It works fine in a table where TDay is a field containing Date values. However when I use the formula in another table where TDay is a field containing Date values that are pulled from another table via a lookup then the formula does not work (it either provides all 0s or all 1s and does not perform the evaluation function). Any thoughts on what I’m doing wrong? Thanks in advance.

Hi everyone, I can’t find anything about this in the documentation or in the Community posts: is it possible, in a formula, to refer to field option IDs instead of option values? Let’s take a basic single select field named “Choice” with 2 values: “1” and “0”. It’s likely that these values could be changed to “Yes” and “No” or “Acknowledge” and “Refuse” in the future. To avoid any issue with the formula when that happens, I’d like to refer to the option IDs matching the value “1” (something like “selQHcj58xhh44C1J”) instead of the values: instead of IF(Choice="1","blah","") I’d have liked to use IF(Choice="selQHcj58xhh44C1J","blah","") - but it’s not working. Any way this would be possible in a formula (not in a script)? Best, Ludo

Hi, having trouble getting rid of the “NaN” when trying to figure out the average difference in dates per sales manager. In one table (named “Tracking Log)” have a date field for “Transport Order Date” and a date field for “Commitment Date”. I used the following formula to look determine the difference in dates by number of days for each sale they made in a field I named “Time to Order Transport (days)”. IF( AND( {Transport Order Date}, {Commitment Date} ),DATETIME_DIFF({Transport Order Date},{Commitment Date},‘days’),BLANK()) Each manager is doing dozens of these transactions each month so I now want to see the Average days per manager for this date difference. I have another table (named “Sales Manager”) where I did a look-up of the field from Tracking log named “Time to Order Transport (days)”. I then created a new a rollup field, as well as a formula field using “Average”, however I have some new managers who have just started and have

Thanks in advance to anyone who even scans this query! :blush: I have a rollup field that is flattening an array of dates - that’s working great! What I want to do is produce this same information showing only the month. For example, I have this list (a flattened list of dates from 3 different records showing only unique dates): 13/09/2021, 20/09/2021, 27/09/2021, 04/10/2021, 06/10/2021, 13/10/2021, 20/10/2021, 13/12/2021, 29/11/2021, 22/11/2021, 15/11/2021, 08/11/2021, 25/10/2021, 20/12/2021, 09/12/2021, 01/10/2021, 08/10/2021, 18/10/2021, 22/10/2021, 29/10/2021, 05/11/2021, 12/11/2021, 19/11/2021, 26/11/2021, 10/12/2021, 17/12/2021, 03/12/2021 I want to create a formula that converts this list of dates into (just the month of each date as an array): 09,09,09,10,10,10,10,12,11,11,11,11,10,12,12,10 etc… If I extract the month before producing the full array of dates, of course, it only ever gives me 1 instance of each month which is not what I want

Hi, I am trying to figure out why I keep getting this error message: The Impact Value is the average of the Confidentiality, Integrity and Availability column. (Confidentiality+Integrity+Availability)/3 The Risk Value should be multiplying the Impact Value with the Likelihood Value. But I am getting an error message with this basic formula {Impact Value}*{Likelihood Value} Please help.

I have 4 sets of formulas. SUBSTITUTE({Status}, “Floater Membership”, “Available”) SUBSTITUTE({Status}, “Pending Lease”, “Available”) SUBSTITUTE({Status},“Pending Payment”,“Available”) SUBSTITUTE({Status}, “Floater Membership”, “Available”) We wanted to merge them into one. I tried to do it with this formula SUBSTITUTE(SUBSTITUTE({Status}, “Pending Transfer”, “Available”), “Pending Lease”, “Available”), “Pending Payment”, “Available”), “Floater Membership”, “Available”) but it is not working. What is your workaround with this?

Hello ! I’m new to airtable and struggling to write a formula that allows me to extract dates from a field. I have tried many different formulas found online but nothing matches my case. I have a SPRINT column that hosts in each field a set of dates: 2022-01-03, 2022-01-10, 2022-01-17… (can go up to 53 dates). I would like to extract from any of the fields in the SPRINT column the FIRST date (2022-01-03) and send it to another field in a new column named START. Then, extract from any of the fields in the SPRINT column the LAST date (2022-01-17) and send it to another field in a new column named END. Do you think that is possible and if so could you help me find out the correct formula ? Thanks !

Hello, I am new to Airtable and am recreating an unwieldy spreadsheet into a (hopefully) elegant database! We harvest crops according to a varying ‘Grow Cycle’. The cycle is bookended from the ‘Seeding Date’ to the ‘Cleandown Date’. We record information like ‘Harvest Yield’ for each Grow Cycle. We also conduct daily checks of water and electricity meters to record our water and electricity consumption. In Excel using the SUMIFS function we can automatically sum all the water and electricity records between each Seeding Date and Cleandown Date to calculate the water and electricity consumption during each Grow Cycle. This is useful for calculating electricity and water intensity. I cannot work out how to replicate this in Air Table. Ideally I would like to use a Form so that Daily Checks Data can uploaded to the Data Base each day. I would then like to use another Form to upload Grow Cycle Data (e.g. Seed Date, Cleandown Date) and use a function to automatically sum al

Hi there! :grinning: Thanks in advance for taking the time to help me out. I’m new to airtable and I’m currently evaluating if I can use it for my small business to do the budgeting for my projects. I actually think what I’m looking for is too much of a spreadsheet functionality which isn’t supported by airtable as a database tool. But I would love to be wrong because it would make my life so much easier if it was possible: I’m trying to calculate the costs of each of my departments and usually just have a long list of items and staff. Some fields I need to be the percentage of the total costs (and update automatically if the total sum changes). For instance I would like to have a field with contingency costs of 5% of the total sum. Am I right assuming that there is no easy formula that can do that? And I would have to follow a complicated workaround with rollup fields and a linked table instead, because you can’t use summary

Hello! Here’s what I’m hoping to accomplish: If {Inventory Arrived?} = ‘Inventory Arrived’ and HAS a date in {When did Inventory Land?} use that date. If {Inventory Arrived?} = ‘Inventory Arrived’ and DOESN’T have a date, use {Updated Estimated Delivery Date} date If {Inventory Arrived?} = ‘Inventory Not Arrived’, use {Updated Estimated Delivery Date} Thanks for your help!