Formulas

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?

Hey Airtable Community, I have a “Forecast” table in Airtable that I’m using to track various line item costs of a particular project. Each row has the following basic fields: Spend Description, Planned Spend, Start Date, End Date, Q1, Q2, Q3, Q4. I’d like to be able to use a formula to calculate planned spend by quarter… essentially place line item costs into the Q1, Q2, Q3 and Q4 fields. I’m new to formulas overall, particularly ones involving dates. I thought I could work around my own limitations by creating a “Spend Month” Multi-Select field, selecting the spend months for each line item, (for example January, February, March) and using the below formula to calculate quarterly spend. In a field called “Q1” I’m using the formula: IF(OR({Spend Month}=“January”,{Spend Month}=“February”,{Spend Month}=“March”),{Planned Spend}) However, I’m running into several issues. The most pressing issue is that there are some costs that span multiple quarters (Spend Month = January, February, Mar

After solving an earlier query about setting my formula and linking a client and rates table I now need to create a column which averages out my earnings from clients. Should this be in my clients table or do I need to create a separate table for this? I now have records with the numbers of sessions for each client and total earnings for each client - all in my clients table - so where and how should my average income per client calculation go please? Thanks in advance Liz

I am trying to calculate the number of months to an 18th birthday. I start with the Date of Birth and add 18 years to that. I format that date so that I get DD/MM/YYYY and not the American MM/DD/YYYY. That works fine. Now I use the following formula in the next column: DATETIME_DIFF({18th Birthday},TODAY(),‘months’). I get a result (3 months instead of 5) which indicates that it is using (In think) the American date and reading the month and year backwards. Is there anyway around this?

Is there a way to use hard coded dates in a date calculation e.g. Date Diff(1/1/2019,Today()). I can get the right calculation if both dates are in fields but the moment put in a hard coded date it doesn’t work. Any ideas?

I’m trying to generate a URL for each record by inserting record details (source, signup email address, and referrer email address) so that I can pass and track our signup forms. Does anyone know how I would do this properly with a formula? examplesite.typeformdotcom/to/nk19Yv?source=xxxxx&signup_email_address=xxxxx&referrer_email_address=xxxxx

Trying to set 1st field as a formula with IF statement. I currently have: Bill & " - " & DATETIME_FORMAT({Due Date}, “ll”) I have another table for bills paid on irregular date (non due date). Without the Due Date being filled in I get an Error in the formula. I’d like to have the formula show IF Due Date Empty, date paid Thank you.

I am building a table of all elected offices in my county. I have the term of office, 2, 4, 6 years (so far no others) but I want to have a calculated field for the next year an office is up for election. So, today, I want the record for PRESIDENT so show the next election is 2020. On 1/1/2021, I want it to show 2024. Can anyone advise me on this one? Thanks

I’m sure that I’m making this more difficult than it needs to be, and I keep ALMOST finding what I’m looking for by searching this forum, but then not quite getting there. I’m hoping someone can help me work this out. I have a base that I use to track commissions paid to sales agents. Sales agent commission rates change over time - they can adjust up or down, based on a sales volume formula. The sales volume formula is complex enough that I’m not even worried about integrating that element. But the tricky part is that when a new sale is entered by the sales agent, I want the commission rate to be automatically generated at whatever their current rate is. So … I have an Agents table, which includes a host of information about each agent, including their commission rate. I set up another table, called Commission Rate History. It’s linked to the Agents table. Commission Rate History includes the following fields: Agent Commission Rate Change Date I want the Agents table to have a field ca

I’ve been searching throughout the forums but couldn’t find the answer to this question. I currently have an “Interactions” table where I capture the date of the interaction and link it to a contact record in the “Contacts” table. I also have a field in the Interactions table where I select the type of interaction e.g. Meeting, Phone Call, Email, Task. What I would like to do in the “Contacts” table is create a view to capture servicing items. One of those is to capture the last date I held a meeting with a specific contact. Is there a way I could create a field in the “Contacts” table to automatically pull the last date I had a meeting with that contact from the interactions table? I can easily pull the last interaction date with the contact using a rollup field with the formula Max(Values) but I would like to specify interactions that are of the Meeting type only so it doesn’t pull the other types such as Phone Calls, Emails etc.

Not sure how to title this, too early to think of the proper name - but this is what I’m trying to accomplish and I can get one percent to work but the others fill in with Error or NaN. When Gross Profit percent is: 0%: Formula is: 0% gross profit: 1-15% formula reads: 1-15% 16-25%… 26-45%… and so on up to 100% Trying to group all gross profits based on the percent for graphing purposes.

Hi all, When I try to use the formula If({LINKED FIELD} !=BLANK(),Duration,BLANK()) Explanation: the duration of a certain activity is put in a seperate (new) column. It only will fill if the LINKED FIELD is empty. For some reason it just doesn’t work. the != combination seems to be ignored. The duration is filled regardless of the content of the LINKED FIELD. What am I doing wrong? Regards, André

Hi all, I have a lookup field that lists values for the same person (e.g. 0,0,1,0,1,0,0,0). I want to pull out the non-zero values into columns by themselves. Some unique aspects are that: the number of values varies by person/case the position of the non-zero values varies by person/case some people/cases will have only 1 non-zero value, others will have 2 or 3 The data table looks something like this: |name|score| |—|---| |Amanda|0,0,0,0,1,0,0| |Jeffrey|| |Joanna|1,0,0,0| |Julianna|0,0,1,0,0| |Kelsie|1,0,0,0,| |Leandro|0,0,0,1,0,1| |Victoria|1,0,0,0,0,1,0,0,0| what’s I’d like is: |name|score 1|score 2| |Amanda|1|| |Jeffrey||| |Joanna|1|| |Julianna|1|| |Kelsie|1|| |Leandro|1|1| |Victoria|1|1| Thank you! Jenny

I have a multi select menu for each job, and so each job may or may contain the GH option, by itself, or with other options. I am trying to calculate Commission on jobs that CONTAIN GH. The way I have it now, it only returns commission based on jobs that are ONLY GH. Any help would be appreciated. Thank you! IF(Type = “GH”, SUM({Paid (Sold)}*.1))

I want to calculate a value from one table multiplying it with a record value from another table. Can this be done please? Specifically, a number of consultations provided in one table, multiplied by a rate given in a different table.

Howdy! I’m putting the final touches on the asset management system that I am building, and I’m just a little stuck with one element that I want to incorporate. What I’m looking to do involves two tables: The Assets table, and the Sign Out Log. On the Assets table, I have a field showing the current status of an asset. When someone fills out the Sign Out Log, the status changes to “Checked Out.” When they complete the Sign In Log, the status changes to “Available.” I’d like to be able to pull the name of the person who most recently has that asset, IF the checked out column on the Assets table reads as Checked Out. I’m not sure if I should use a standard formula or some kind of rollup feature. Anyone have any guidance as to how I should proceed? Thank you!!

I use air table for my test prep company. We help people pass a general contractor’s exams in many different states. I’m using air tables to keep track of all the questions. Sometimes I’ll have a question that is used in multiple books such as: “What is the maximum allowable foundation pressure for sandy gravel?” That can be found in 2 different books. It’s in the Residential Code 2006 AND in the Builder Code 2015. (see image below) The problem is, if I group by book, I only see that question in the group that shows both of those books. I need it to show in the Residential Code 2006, AND in the Builder Code 2015 when I look at the questions for those individual groups. How do I make it appear in each of those individual groups?

Hello! I am starting to create an Asset Management System using AirTable (sidenote: It’s so much easier and cleaner looking here than anywhere else I’ve tried to build this!), and I’m a little stuck while trying to create a nested IF function for a Status column. I have two columns, “Operational Status” and “Check Out Status”, to show whether an asset is working or not, and whether an operator in the field has checked it out for use. I’d like to have a third column that shows the overall status of each asset, that combines the statuses of the other two columns. The conditions I’d like to have met are: IF {Operational Status}=“In Service”, AND(IF({Check Out Status}=“Available”, “Available”)) If {Check Out Status}=“Checked Out”, “Checked Out” IF{Operational Status}=“Out For Repair”, “Unavailable” IF{Operational Status}=“Retired”, “Retired” I feel like I’m close to figuring this out, but it’s stumped me since last night! Does anyone have any suggestions as to input this formula?

Hi all, I’m looking to create a formula to show a :rotating_light: when a record’s status is “PENDING” and it has been more than 10 days since it was updated. I’ve tried doing this a few ways using both a column with the Last Modified Date field, and “Today” in the formula itself, and then also one where I created a new column that shows the difference between the Last Modified Date Today. Please help!

I need to count the number of fractional years between dates. I’m currently using this formula to count the number of days between dates: DATETIME_DIFF({To},{From},‘days’) and this formula works great for the number of days between dates but what I really need is fractional years between dates, such as 1.1, 1.5, etc, such as the YEARFRAC function does in Excel. If I change ‘days’ to ‘year’ I get whole number results like 0, 1 or 2. I need to show fractions of a year. Suggestions anyone?