Formulas

Hello, I have a field called “Quantity” that needs to count how many parts are being replaced for each machine. I have a field marked “PartNumber” where I can link the parts from a PartsList (table of a partnumber for each record along with a description). Is there any way to calculate that I have 2 of “M10506” and only 1 of “M34563”? We are coming from paper copies of Service Reports and I have no idea how to do this. I would gladly compensate anyone who would spend more time with me to help iron out this database. Thank you for the help. -Nathan

Hello, I am trying to get repeating tasks to work based on their regularity (Daily, Weekly, Monthly) with the IF and DATEADD formula used together. Here is what I have tried: Can anyone suggest where I am going wrong?

Hi friends, I’m trying to score ideas and calculate a weighted score, but I’m not sure how to execute this in Airtable (it would take me seconds in excel). I have a list of ideas, and a score for each across several variables (see 1st image below). I have a 2nd table with the weights (see 2nd image below). I would like to write a formula in the Total Score column in the 1st table (3rd image) that multiplies each of the score variables with their corresponding values so that The total score reflects the weighted result. So conceptually, I want to calculate like this – how the heck to I do this in Airtable? Total Score = Sum { (Var1 * Var1_weight) + (Var2 * Var2_weight) + (Var3 * Var3_weight) } … etc. I really appreciate any guidance you can provide.

Hi guys, I’m very new to Airtable. It is a marvelous tool but it’s a bit different than excel which i use my whole life. It left me confused with some formulas that seem easy in Excel. Background I’m trying to make an automated contracts form. I constructed a few databases A freelancer’s database with all data like address, fields of expertise etc. A project database. Each row contains a project name, member of a team , budget per member, few columns from Freelancer database - field of expertise etc. A contract database with project start and finish date, project scope. And the thing i have the most problem with is the contract number. Problem: Contract number is formatted like this: number of contract/ project name / yearI need to count the order of occurrence of duplicates to make multiple contracts in the same project numbered in ascending order. In excel it would be like this *Names are from airtable template After that i will try to export all of this data as a PDF file with c

Hello! I am using the airtable API with a formula to basically filter a set of records from a table. I am filtering on a multiple-select-field, combined with a logical OR, so something like this: OR((FIND(LOWER("First"),LOWER({Select})) >= 1),(FIND(LOWER("Second"),LOWER({Select})) >= 1)) Now I will have records that have both “First” and “Second” selected in the “Select” column. I would like to sort these more accurate matches to the top of my results. Is there any way to do this? Thanks in advance! Tom

Have a field InvoicedDate. I want to get the last two digits of the year of the InvoiceDate value. So for example, if InvoiceDate contains “2/15/2021” I want to get the result “21”. This is what I’ve tried: RIGHT(YEAR(InvoicedDate),2) Looks okay to me, but it returns #ERROR. I suspect it’s a typing problem, that is, YEAR() returns a number, but RIGHT() can only be used with a string. But I can’t see how to coerce the number to be a string. I’ve reread the functions reference but can’t find a NUMSTR() function or anything like that. What am I missing? William

While creating a record at work, e.g. Q#1640, I kept making new records (#1641-1645) clumsily and deleting them. Unfortunately now the business is missing those rows. There’s no field to leave a comment. Is it possible to add, paste, or restore rows #1641-1645, and have the table working normally?

Hi there - Whenever I try to do a basic division formula between the values in two columns I get an #ERROR! message. I can’t figure out why. Below is what I’m inputting. Formula for column: {Measured Coil Temp.}/{Max Rated Temp.} Would really appreciate some help. Thanks in advance!

Hi All, I am really new to formulas and I am trying to do along string IF statement. Can anyone help me identify where this is going wrong? I looked and read through other topics and just can’t get it to work. I essentially want this to go all the way from 1 to 100 with greater than or equal to all the way up. IF({Security Price (from Search Criteria)}>85,“1”) ({Security Price (from Search Criteria)}>=70,<=84,”2”) ))

I have a column that was a name of a product, which is customer-facing, and I have another column that I use for myself. I would like to create a formula where if (Column B contains the word Blue) output BLUE to column B If (Column B contains the word Whale) output WHALE in column B If neither is true output ERROR I’ve tried so many options, nested ifs, and substitutes … I only have 5 products, so I also tried using a switch statement … but it’s not working … does it only work with integers?

Hi, I am working on a project where invntory can be rented for a certain period. I need a formula to count how many records that have requested a rent for the same day. When I know this, I can set the status as ‘unavailable’ or ‘available’ if too have requested the product for the same day. Thank you!

In the documentation for the NOW() function it states that: If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies. I have a Zapier process that polls a view, does this count as a sync dependency? The formula i’m using calculates whether the value in a date field is greater than the current datetime, and if it is it will appear in the View that Zapier is watching. It’s important that the formula is calculated 24/7 and not just when the base is open.

Hi All, I would like to know if it is possible for me to calculate how many due dates were missed in a month. I have a column for Due Date (The last day to complete), the status of the task (To Do, Created) Count the number of times the status was changed from “to do” to “created” post due date of the task. Thanks in Advance.

Hi all. I am kinda new to formulas and was hoping there was a way to make a formula that can take into account data from other tables? What I am trying to do here is assign certain data a simplified number. Example: If the number in column A from table 1 is between 1,000 and 1,999 then it is assigned ‘1’ in table 2. If the number in column A from table 1 is between 2,000 and 2,999 then it is assigned ‘2’ in table 2. So on and so forth.

Hello, I’m new to writing formulas and am having trouble figuring out how to nail this one down. I want two things to happen, the date and the status fields both to be updated if the task has not been completed on time. Any pointers? Thanks! IF the DEADLINE (February 15, 2021 format) has past AND the STATUS (single drop down select) is not COMPLETE, THEN change DEADLINE to today AND change STATUS to OVERDUE I’m thinking this could be solved in one nested formula but where would I even enter it if that’s correct?

Hi everyone, Thanks for looking at my question. Here’s what I’m trying to do: I have multiple Tables in a Base; “Primary DB”, “DB2”, “DB3”, etc… Inside of the “Primary DB” there are two Fields called “Company Name” and “Other DB”. The “Company Name” field is populated with … company names. In all the other Tables I have one Field called “Company Name”. I would like to create a formula in the Primary DB table / Other DB field that checks the values of the "“DB2” table / “Company Name” field, and if the values match, to fill in the Table name (“DB2” in this example). And then for “DB3” and so on. Is this possible? Many thanks, Kai

Can you spot an error with this formula? I’ve tried multiple variations and keep getting, “Sorry there was a problem saving this field. Invalid formula…” SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Status}, “Destroyed”,”Sold” ), “Gifted”,”Sold” ), “Stored/Loan”,”Sold” )

Hello! New Airtable user here! I’ve been racking my brain for days trying to figure this out. I am trying to create a formula based on a selection. What I am trying to accomplish is: If the rate is HOURLY, calculate date/time difference for # of hours If the rate is DAILY, calculate date/time difference for # of days if the rate is TOTAL, calculate the rate X 1 Any help or suggestions would be awesome!

I love Airtable’s ability to create views and then sorting, filtering, and grouping to see things like average, median, range, etc. I am using Airtable for a pricing database across multiple vendors (basically, see how much vendors are charging other companies with similar characteristics (e.g. by revenue, employee size, a combination of the two, etc.) I am user Stacker.app as a front-end. As pricing data is entered and updated, you can slice and dice with different views. However, that takes effort by my users. I’ve tried the pivot table app and while that’s ok, you cannot run multilevel analysis like you can with the views (e.g. show me average price for companies with $5-10m in revenue and 100-250 employees and compare by vendors X, Y, and Z). Also, embedding these apps opens up the whole base which I definitely dont want. Ideally, I’d like to just be able to create a table or view (maybe using a Gallery) that I can then embed through Stacker. Anybody know if there’s a way to

Hi there ! I have been struggling a lot for i guess a simple formula … I have a table with contact informations (name, product of interest) Then a second table for tasks (see attached) I want that when a product A is selected, to calculate the task completion (as you can see on the 100% cell) the formula I used the following formula : IF({Product of interest}=“A”,SUM({A-1},{A-2}),"")/2 But, if a customer select product A and B, I want the task completion to be calculated with the checkbox “B-1” and B-2" as well … And same in the case a customer pick product A, B and C, I want it to be calculated with C-1 and C2… I have been looking with IF formula, Switch formula or even Substitute, but i can’t find a correct solution ;( Any good way to do this ? thanks in advance !

Hello, I need help trying to figure out this formula. I have spent the last three days looking through the community and I believe this is the closest article I can find, (Formula Help! IF AND + checkboxes) but I still can’t seem to get this working. I originally created an IF statement based on blank fields. The purpose was based on keeping track of our workflow. Now, we have the need to added other “flags” that I will need to change the formula to "Done! " without completing all the steps from the original IF statement. The first flag is a “Canceled” Checkbox. If the checkbox is checked I need the status to change to “Done !” regardless of other fields are still blank. The second is new additions of two extra fields added to the Single Select field in the Shipping Method. If the option of “Picked Up” or “Dropped Off” is selected, I would need the Status to change to “Done”, without having to select a date for the “Delivered” Column. What I am trying to accomplish is when either o

I am importing a financial summary from my bank which comes in CSV, and the amount of expenses comes in negative numbers. How can I convert a whole field with negative sign before the number to positive/delete the negative symbol? And how can I switch the dot and comma symbol from US to international? e.g 1,234.56 to 1.234,56 Thank you!

Hello, in the past i used extract url from img with the following function MID({Media},FIND("(",{Media})+1,LEN({Media})-(FIND("(",{Media})+1)) But this time, the FIND function isnt counting well for some records. i don’t know why. here is the results .

Hi there, I recognize the power of regex expressions, but I’m just pathetic at composing them, so I’m reaching out for help from someone who actually understands the syntax. I have a table in which all entries contain a URL with this structure: https://scholarship.ode.state.oh.us/Student/Home/Details/555555?stdntID=444444 From record to record, only two components of the URL change: The number immediately following Details/ (i.e. 555555) The number immediately following stdntID= (i.e. 444444) I need to write two formulas, one for each of the components named above, to extract these unique numbers. If anyone could help in this regard, goodness what a kindness it would be. Thank you for taking the time to read and consider.

In the MP IMU% field I have this formula: IF({MP ICOGS},SUM({MP IGP} / {MP ICOGS})) My question is how do I change my formula to return a value in the MP IMU% field that is not a negative percentage? The true answer is 281.34% - I’m beyond stumped??? :woozy_face: