Formulas

I want to perform a vlookup into a separate table. Similar to in excel, you can vlookup from another tab. However, it seems the only way to do that is by using the lookup field. That is pretty straightforward, however the field with the linked records must be a text field. I’d like to have that be a formula. Here is an example: Create formula that combines two columns. This creates a unique value I want to use that unique value (that uses the formula) as the value to lookup into another table to pull in a value from that table. Is this possible? I just want to execute what a simple vlookup would do in excel, but by using a formula instead of single text to be the value that lookups into the second table.

Hi I am trying to change the decimal point in some numbers, to be used in Integromat, using ceil or floor options. I tried using the decimal option in GS, but it didn’t work. Apparently, I need to extract the numerical value, by using split + get functions, then use the math functions floor or ceil. I wondered if someone can help. I managed to create an expression, with some help, apparently I have to use “String” after ”Split” were “1.value” is, but I was a bit stumped, do I place a value there or use a tag? If it’s a value and I want to change the decimal position from 24.01 to 24, how would I write it? If it’s a tag, we’re would I find it? Also, were do I place the “Temperature: Day” tag? Thank you, Mary This is what I would like to convert: This is how far I got: Thank you, Mary

How do I format the results of a numeric formula? For instance for a formula that sums a number of values from fields in my table into a new numeric field, how can I generate a “$” in front of those values automatically, and possibly format the numbers with a comma for a thousands separator?This is my working sum formula: SUM({WC Revenue},{GL Revenue},{GH Revenue},{CP Revenue},{CA Revenue},{EPLI Revenue},{D&O Revenue},{PL Revenue},{KMLI Revenue},{LI Revenue},{HFLH Revenue},{EL Revenue},{GK Revenue},{ProL Revenue},{HO Revenue},{PA Revenue},{Bond Revenue},{Payroll Revenue}) Anything I can add to this to generate the “$” and/or the thousands separator? Thanks!

Hey everyone, I’m trying to create a hotel room capacity plan so I can see if we have enough rooms of a category left or if we’re overbooked. I have one table where I create rows for each speaker who needs a hotel room from arrival to departure date. In another table I have my capacity plan that applies for each day. Now I’m trying to make a capacity plan for each day for each room to see if we’re overbooked or have spare rooms. I know how to do it in Excel but am not quite sure how to continue here. I started with another table that has all dates and booked and available rooms but don’t know how to get all booked rooms within in the date ranges. Appreciate your feedback. Isabelle

Hello. My first post here, so please be gentle :slightly_smiling_face: I have the following scenario The price for renting Video Equipment is different based on how long you rent it … 1-6 days, price A 7-27 days, price B 28 days +, price C Each of the prices is rental for ONE DAY (24 hours) So the Price is multiplied with the number of days … so far so good, The Prices are calculated in the Equipment Tab The amount for the Rental is calculated in the Rentals Tab I have managed to come up with a nesten IF statement, that prints to a STATUS field, and that works just fine … is prints something like “Singel day Price”, “Weekly Price” and “Monthly Price” It goes like … IF(DATETIME_DIFF({End},{Start},“d”)<7,“Daily Price”,IF(DATETIME_DIFF({End},{Start},“d”)<=27,“Week Price”,IF(DATETIME_DIFF({End},{Start},“d”)<=90,“Month Price”,""))) pardon my translation :slightly_smiling_face: Now I try to do the same for the actual price. In the Equipment database all the prices are created b

Hi there, help needed! I have an arrival and a departure date, different dates over a 2 week period, I then need 14 columns with if/then formulas so I can get a room count for each specific night. In Excel I had =if(departure=>[date],"-",if(arrival=<[date],1,"-")). In [date] I had each night’s date. I’ve tried the same in Airtable and it isn’t happy. Not sure how I should be specifying the [date] I am trying to analyse! Hope that make sense to someone and I can find a solution! Thanks

Hy Guys, We’re trying to do a formula in order to create a new graph within our dahsboard. However it keeps telling us that: => Y-axis field “Pourcentage_retard” is not numeric The formula that we’re using is: => IF(nb_intervention=0, “0”, inter_retard/nb_intervention) I’m wondering if a need to add a formula to switch to numeric. Thanks in advance!

I am have two tables. In table 1, I have: a (non-primary) field with 3 single select options (let’s call it field A) a field linked to records in table 2 (field B) In table 2 , I have: the table’s primary field, from field B in table 1 (field C) a field that counts the number of linked records (field D) I would like to filter the field that counts the number of linked records (field C) in table 2 so it only counts for one of the 3 single select field (field A) in table 1. I looked around the forum, and some answers seemed to deal with the same issue but I could not get any to work for my case.

I am trying to have AirTable take an ID number (from column a) and pull first and last names into columns b and c. I would like to use a second table as the reference sheet that would only hold this information (ID, First Name, Last Name). I have done something similar to this in Google Sheets by using a second sheet in the file to reference this information and then I have the following script running: function onOpen(event) { var FirstNameInsert = SpreadsheetApp.getActiveSheet().getRange(‘B2:B2000’); var LastNameInsert = SpreadsheetApp.getActiveSheet().getRange(‘C2:C2000’); var FirstNameFormula = “=vlookup(A2:A20000,‘Active Students’!A:C,2,false)”; var LastNameFormula = “=vlookup(A2:A20000,‘Active Students’!A:C,3,false)”; var FirstNameCheckCell = SpreadsheetApp.getActiveSheet().getRange(‘firstname’).getValue; var LastNameCheckCell = SpreadsheetApp.getActiveSheet().getRange(‘lastname’).getValue; if ( FirstNameCheckCell != FirstNameFormula ) { FirstNameInsert.setValue(FirstNameFormula)

I am new to Airtable and trying to use lookup to pull first and last names based on an ID number in a table (called log) that will be constantly receiving new records. I have a second table set up with all of the ID numbers, first names, and last names that is linked, and the lookup set for the first and last name fields. However, with the lookup I am only getting the names for the first occurrence of an ID and they are not loading for any new records that are created. I have the settings enabled to allow linking to multiple records. How can I get it so that the first and last name automatically load for all records.

Hallo I am trying to print the grid view and I always get the last column printed in separate pages, no matter if I specify “fit to page width” or actual size. I am printing on A4 using acrobat and a xerox printer, no difference. so its not driver specific. can you pls help ?

Greetings! I am reaching out for assistance with a formula field (admittedly not my strong suit!). I am tasked with tracking a declining balance fund used to pay for event expenditures. I have the following columns: item, estimated expense, actual expense, fiscal year. Ideally there would be two additional formula fields that would subtract each new expense from the starting balance. As expenses are added, I would be able to see the declining balance. My failed attempt: SUM(5000.00-{actual expense}). It appeared to be working for the first entry I made, but then went haywire when new entries were added to the table. Any assistance would be greatly appreciate.

Hey there, I am a total Airtable rookie and I am wondering how I create a formula for the following case: I want to create a field that has a marked checkbox when the record is active. The record is active when the date in the field ‘Move out’ is today or less than today. Thanks for your help! Best, Didi

I’ve got a column that automatically generates our suggested retail price (based on labor, materials, etc.). I want to add a column that rounds that number (up or down) to the nearest 5.99 or 9.99. So if it calculates the suggested retail is $10.25, I want there to be a second column that says $9.99. If it suggests $14.87, the other column says $15.99 etc.

Hi all, I’m currently struggling with a thorny Airtable formula issue. I think I need to be considering using a nested IF statement or a SWITCH formula, but i’m not 100% convinced that either will work. Here’s my problem statement… I need to analyse the outputs of five columns, each of which is populated by a unique, formula-generated text value. Based on different combinations of these text values, I need to display a different summary sentence. I thought that I could solve this issue by using the following nested IF statement… IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”, IF({Analysis Complete Summary}) = “Analysis Incomplete”, “Awaiting Analysis”, IF({Screen Designed Summary}) = “Design Incomplete”,“Awaiting Screen Design”, IF({Developed Summary}) = “Dev Incomplete”,“Development Incomplete”, IF({Function Configured}) = “Config Incomplete”,“Awaiting Configuration”,“Function Configured”))))) However, this formula doesn’t work. It seems to get stuck after th

Am I doing something wrong here? For decimal values inside a string, “0.5 some text” or “12.1 some text”, when I use, Value(left(0.5),4), I do not receive the decimal value. It will return the first integer of number. Is there a different want to return the decimal number found on the left most side of the string as mentioned above? J

I was wondering if someone can assist with the following. I’m trying to create an “If” formula for my “Previous Job #” column Depending on the “Type” I will be either a “capital” or “service” If service then the value is “Not applicable” and if Capital then I have an ID number to enter I understand the first portion, but I don’t get how to get it to let me enter my own values if it’s not a “service” IF(Type = “Service”, “Not Applicable”)

Ok, so I have a total column in which I have Sum()ed many column fields in it, to be used as a Rollup column on another table. it looks like this: SUM({5" }+{6" }+{DS$}+{Elb }+{IN/M }+{O/M }+{Endcaps }+{H/H }+{Wedg}+{Off$}+{B/P$}+{D/E$}+{Screw$}+{12" Gh }+{14" Gh }+{18" Gh }+{Perf }+{IN/C }+{O/C }+{GH Endcaps }+{Diverters }+{Brackets }+{MicroScreen }+{EvN Flo }+{MidWest }+{Sofit Vent$}) Now this formula works, but I had to HAND ENTER every column name in the formula. Is there a way to perhaps Right click and drag or ctrl click or something to select what columns I want to put into the formula? Similar to what I would have done in Excel? Anything to speed up the formula making in the future. It took me FOREVER to type all those in there. Thank you!

Hi, I am working to send my inventory to amazon listing, is there any way airtable works directly with the seller central? Moreover, I have to send images for each product in url form individual image link, is there any way I can separate images link when downloading the CSV format of the airtable?

I am looking for a way to use the REPLACE() formula function to replace values in a Multi-select column with different values on a new column. For example: The original column contains column Type of question with multi-select values bug-report, enhancement. Can we create a new column called Type of question (new) with the corresponding new values: bug-report-new and enhancement-new?

I am looking to create a database to automatically grade multiple choice exams. I have one sheet with the correct grades and then one sheet with the student grades. There are a couple of ways to do it with Excel - the easiest is to create an extra grading sheet that uses a Boolean countif to check the student’s answers against the correct answers cell by cell and add 1 to all the cells that are correct [COUNTIF(Student!E5, Correct!E$2)]. Is there any way to set up something similar with Airtable?

Hi again, Trying to write an IF statement for a cost catagory. This is what I have now. {12" GH} * 25.0 BUT I would like it to say: IF ({12" GH} is a multiple of 4, * 125, (Roundup ({12" GH}) * 25))) If the field is NOT a multiple of 4, I’d like it to round UP the field to the nearest whole integer. How would I write that? Thank you!

Hi, I’m trying to create a column that will calculate commission on the Sales Price of a job for me, but only if the Commission checkbox is checked. But I can’t get it to work! Here is what I have: IF({Commission?} = 1, SUM({Paid (Sold)})*.1 , 0) What did I do wrong? All it returns are $0.00 even if I have a Checkbox marked! Thank you for your help!

Hi, I’d like to create a calendar in date format ie like a wall calendar similar to what you can do in excel. How do I do this please?

How do I sum the values in a numeric field?