Formulas

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 possible solution? I’m out of i

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?

I want to count the number of attendees on course. I’ve uplodad a pic of the formula. Any ideas what I’ve done wrong?

I’m trying to create an inventory of our IT hardware, using computer names and the users associated with those names. I have access to the model number of the computers, but I want to be able to convert that model number into an easier identity that laymen would understand at a glance. Here’s an example: Name, Computer Name, Model #, Model Name Michael, PC01, 10HY002AUS, ThinkCentre M700 Tiny This last column is what I need to generate, based on the “Model #” - I know I can do this with a VLOOKUP in Excel, is it possible to do in AirTable?

How to create a new field from searching the value of two fields from the same table in another linked table. For example I want to get the serial number of a PC knowing its name and location (in the same table) but the information needed is in another extended table linked to the table where I want to put the result.

I am attempting to calculate a cost per validation in a table using a Formula of two rollup Fields but I want the results to show either the resolution of the problem {column A}/{column B} or “0” if NaN or the sum of Field 1 if Infinity. Examples of problems: if Field 1 rollup is $1800.00 Field 2 rollup is 0 I need the formula in Field 3 {column A}/{column B} to show $1800.00 not Infinity if Field 1 rollup is $0.00 Field 2 rollup is 0 I need the formula in Field 3 {column A}/{column B} to show $1800.00 not NaN if Field 1 rollup is $1800.00 Field 2 rollup is 2 I need the formula in Field 3 {column A}/{column B} to show $900 Getting the formula to give me the last result is easy enough - but I can’t seem to figure out how to use nested if formula to give me either 0 or Feild 1 amount. Is anyone else able to help me build this formula? Please and thank you so much if so!

I use a formula filterByFormula=Lower(E_Mail)=Lower("{user_email}". If there is a special Character of # or & or + in the e-mail address, for example debby+maier@gmail.com the formula does not find correspondig records in airtable. Special Characters like !$%’*-/=? within the E-Mail address are found. Can someone give me a workaround or solution. Thank you for your help

I am working on a database of the individuals in our feeding programme. In December we host an annual Christmas party for the kids between 5 and 16. How do I write a formula to identify these children? I have tried variations: IF({Current Age}>4 AND {Current Age}<17), ‘invite’) or IF( AND({Current Age}>4 {Current Age}<17), ‘invite’) and I just keep getting error messages. Clearly something is wrong with my format but I don’t know how to fix it. Please help :slightly_smiling_face:

I have data being automatically being inputted from Stripe to the column on the right. I need to -2 from the number in the right column and then *87 however I don’t want it to go minus(it does this when there’s no data in the right column). I want 0 to be lowest value it goes. Could somebody help? ( new to all this…!) Cheers! :grinning_face_with_big_eyes:

Hi there, I was able to grab a formula from this amazing community to show status of due items, which is working great – but I need to add in one layer and my attempts so far have only produced errors. Not sure if it’s possible or what I’m doing wrong. The current formula: IF({Due Date}="",“Not Set”, IF(AND(DATETIME_DIFF({Due Date},TODAY(),‘hours’) >=12,DATETIME_DIFF({Due Date},TODAY(),‘hours’) <=48),“Due Soon”,IF(IS_BEFORE({Due Date},TODAY()),“Past Due :no_entry: ”, IF(IS_SAME({Due Date},TODAY()),"Due Today :eight_spoked_asterisk: ",“Upcoming”)))) What I need to add to the above is : If Due Date is before today AND “Status” column = Done :heavy_check_mark: , then value should show as “Completed” Appreciate any help!! This community is amazing, and I’ve learned so much from you!

How do I insert a blank in a concatenation formula?

Hi all! Thanks for taking the time to read this, I am migrating from Google Sheets and really need an inventive solution to this problem. I’d love to know your thoughts. My company run Zoom workshops and we are required to generate registers with time in/out. Zoom gives me a CSV file with this data, but it often enters the same client accross multiple rows. I then need to apply deductions for any period of time where the client was offline during the session. Time in Time out Deductions 1 ␣ 09:28 ␣ 09:37 ␣ 00:00 2 ␣ 09:34 ␣ 10:16 ␣ 00:00 3 ␣ 09:44 ␣ 10:16 ␣ 00:00 4 ␣ 10:18 ␣ 10:37 ␣ 00:02 5 ␣ 10:19 ␣ 10:37 ␣ 00:00 6 ␣ 10:38 ␣ 10:44 ␣ 00:01 7 ␣ 10:40 ␣ 10:44 ␣ 00:00 I can use a roll up to identify the MAX and MIN times, but cannot work out the deductions. To achieve this in sheets I did {Time out 2} - {Time in 1}. This had to be applied once the data was appropriately sorted using a macro. Clearly this does not work in Airtable - any other smart ideas?

I’m trying to calculate a formula using the if function but not sure where to start. My formula correctly calculates the wholesale cost of a case of 12 bottles of imported wine from the ex cellar price. However, still wines under 14.10% Alc are taxed at 25% of the value (ex cellar x 1.25), still wines 14.1% and higher are not taxed, and to make things even more exciting, sparkling wines are never taxed. I want to make one formula that incorporates whether the wine is taxed or not. can anyone help? in my formula: ex cellar = price of 1 bottle of wine in EUR, 1.15 = est exchange rate 1 EUR= USD, 12 = number of bottles in a case, 18 = cost of shipping in USD to import the wine, 0.6 = target 40% margin on final price. everything works fine, just not sure how to incorporate the tariff on some wines but not others.

Hi @all, I am trying to move everything that has the “finished” checkbox checked to another table. Is this possible? I am using integromat’s automation system. here is what I’ve tried:

I’m trying to figure this out and can’t for the life of me. Basically, this was a template in the AirTable Universe and when it’s a meal from leftovers I want it to say not just “Leftovers”, but which leftovers: ie “Leftovers (Beef Tacos)”. But I cannot for the life of me figure out how to do it. Here’s the formula that is currently in the table: I tried to do nesting IF statements and could not get it to work. I saw on a different post here that some people have conditional roll up fields, but I don’t seem to have that option (there’s no toggle on my roll up options to add conditions). Help is appreciated.

Hi All, I have a formula which finds a single instance of a string in a field but I would like it to find multiple instances of the string in the field, how should I change the formula? MID( {Field Name}, FIND( ‘beginning of string’, {Field Name} )+6, FIND( ‘end of string’, {Field Name}, FIND( ‘beginning of string’, {Field Name} ) )-(FIND( ‘beginning of string’, {Field Name} )+2) )

Hi, Thank you in advance for advise. I have 3 department in my company. I am creating 1 table per department. Table 1 = Product A Table 2 = Product B etc… I want to have a quick overview on all Sum from each table. I created a Table 4. I want that a cell displays the SUM of column A in Table 1 ; How to do it? Thanks

I would like the # Field “T” to tally up all the checkboxes across the record. Is there formula for that?

I have 5 fields with different time formats which can be date, hours, minutes, seconds… ex: Field 1: 4 days Field 2: 2 hours Field 3: 2,5 days Field 4: 15 mins Field 5: 25 seconds Field 6 = sum (field 1 + field 2+ field 3+field 4+ field 5 ) with ‘days’ format Which formula I can use in this situation ?

Hi there, Newbie here, so apologies if this has been asked. Scenario: I have 2 tabs (1. PROJECTS 2. TASKLIST), and I’ve linked each individual task items to a project on the TASKLIST tab manually. Lets assume the link between task-projects from TASKLIST tab is the main source of truth. Question - is there a better way to summarize the total count of tasks per project on the PROJECTS tab? Currently, I have to create two fields in PROJECTS tab. (1) task - to link all tasks connected to the project (2) total task count - use the count formula to add the count in task field. With this approach, I have to manually add each tasks to the task field on the PROJECTS tab, which seems repetitive since data is already on the TASKLIST tab, and also I might accidentally make a mistake if this is manual. Is there a way to lookup TASKLIST tab, and see the relation between task vs project on that tab, and do the count automatically in PROJECT tab? Thank you, Vickie

Hey guys! I’m looking to add an if formula to the formula below. What I need is that if the “fixture model #”, the “bulb model #” or the “miscellaneous” fields are empty, the output of the concatenate funtion is “N/A” CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous}) Thanks in advance!

Table “Companies” is a list of companies. Table “Posts” lists blog posts, and has a Date field. They are linked related between Companies’ “Posts” field and Posts’ “Company” field. ie. Posts relate to companies. On the Companies table, I am able to successfully show a “Total Posts” count of the number of corresponding posts, by adding a Count field for the Posts table. However, how can I add another column to Companies which only counts those Posts whose Date fields fall within the last seven days? tl&dr - How do I count the number of linked records whose date falls within the last seven days?

Hi all I’m trying to find a way to record, within a table, an ongoing “history” of when a checkbox has been checked/unchecked. The first stage is simple enough, e.g. a formula field that outputs today’s date when the box is initially selected - IF({Yes/No}=“1”, DATETIME_FORMAT(TODAY(), “DD/MM/YYYY”), “”) but what I then want is an additional field that keeps a kind of tally of when the box has been checked/unchecked, i.e. “Approved 03/07/2020; removed 10/07/2020; approved 13/07/2020…” etc. I think it will probably involve using a “last modified” field but I can’t work out how to make the “tallying” part of the formula, rather than just updating each time there is a change. Can anyone advise if this is possible to do? Thank you!

Hello all! I want to have countdown (showing how many days left) based on the stage (a single select field) of a record. Each stage should take a certain amount of time to complete, so the goal is to see how many days left I have to complete a stage. Then once I change it to the next stage, that countdown is reset to the parameters of the next stage. I have parameters are mostly set by formula-based date fields that live in the table. So it would look something like this: If {Stage}=Stage 1, calculate difference between {date 1} & 14 days from {date 1} If {Stage}=Stage 2, calculate difference between {date 2} & 14 days from {date 2} If {Stage}=Stage 3, calculate difference between {date 3} & 28 days from {date 3} If {Stage}=Stage Complete, “Complete” I tried doing a DATETIME_DIFF inside of the If function, but have been struggling with errors. Seems pretty straight-forward, but I can’t figure it out… Any help would be great! Thanks in advance.

I have been using the really helpful formula in this airtable article to have a status of done, complete, and in-progress for my project management base: https://support.airtable.com/hc/en-us/articles/221104988-Conditional-formulas-with-emoji My formula currently looks like this: IF({Done} = 1, “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Due Date}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :thumbs_up: In-progress​:+1:”)) However, I would really like to divide the In-progress into tasks that are currently in-progress (keeping status. as is) and tasks that are upcoming (start date is after today, basically). This is what I want: :white_check_mark: Done :white_check_mark: : IF({Done} = 1, “ :white_check_mark: Done :white_check_mark: ” :thumbs_up: In-progress :thumbs_up: : Start Date=before today and Due Date=After today :alarm_clock: OVERDUE :alarm_clock: : Start Date = before today and Due Date=before today :clock2: Upcoming :clock2: : Start Date= after