Formulas

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’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 th

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?

i am using the main Table (client List) where I add my client then I use lookup (in client list) to find the Invoice number in another Table Called “Master Inventory” My question is How to find the count? with the following conditions Count the number of people in “SOLD TO” TAB number of people in “SOLD TO” - “Store Inventory” if nothing is available in “SOLD TO” return the original “Store Inventory” Can you guys help me

Hi there, I have an IF clause complex that unfortunately stretches my abilities beyond the possible. Here is the challenge: I have a column with a variety of about 15 industries, the column name is {Primary Industry} and examples include Healthcare, Justice, Public Sector, Dealerships, Fitness, Utilities, and Hospitality (there are more than 7 but lets focus on these four for the purpose of my problem). I now have a new column called {Account ownership}. In this column I like to assign the ownership for the different accounts according to the Primary Industry they belong. I.e. all accounts with a Primary Industry “Dealerships” and “Healthcare” belong to Sven, those with “Justice” and “Public Sector” to Toni, and those with “Utilities”, “Hospitality”, and “Fitness” to Sandy. This is how I tried to solve it: IF( {Primary Industry}NOT(), IF( {Primary Industry}=‘Dealerships’), “Sven”, IF( {Primary Industry}=‘Healthcare’), “S

Hi there, I do have two date columns {Created Date} and {Last Substantial Contact}. I like to check first if the date in the {Created Date} field is older than 365 days. If it is not then I like the formula to output “covered”, if it is older than 365 days I like to check if {Last Substantial Contact} is less than 365 days. If this is the case then the field should also read covered. If {Last Substantial Contac} is older than 365 then the field should give the output “uncovered”. This is how I tried to implement the above logic: IF(OR(DATETIME_DIFF(TODAY(),{Created Date},‘days’)<365), (DATETIME_DIFF(TODAY(),{Last Substantial Contact},‘days’)<365), “Covered”, "Uncovered”)) I tried several variations but always get “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” Does anybody see where the error in my formula is? Thanks, Marc

I have some tables one of which is contributors and another is contributions. The contributions primary field is the amount of the contributions. One of the contributions field is the Contributor linked field (linked to contributors). I am attempting to sum the contributions from each contributor but it is not working out. Is there a way to sum the contributions attributed to each contributor?

Hi, I am looking for a way to build a formula that goes like this If the date created+timestamp <= to 4PM that day then subtract 10 hours if not subtract nothing. I am racking my head how to do this. Any help would be awesome!

I have a system of daily count and need to check previous day count and today count to get how much used . Is there anyway i cud perform functions between rows .atleast auto copy or subtract for two rows ?

I am attempting to write an equation but I cannot get Airtable to do what I want. What I am asking is that if “Total Time Worked” is greater than or equal to 7 hours and 45 minutes to round it up to 8 hours. However if “Total Time Worked” is less than 7 hours and 45 minutes do nothing and leave as is. I am trying to calculate this for payout purposes at work and making sure that anyone working at least 7:45 gets a full 8 hours but anyone working 7:44 hours or less simply gets paid for the time they worked. This is what I wrote: IF({Total Time Worked} >= 7.75, ROUNDUP(7.75,0)) What am I doing wrong here? Any help would be greatly appreciated. Problem here is once this equation is entered what Airtable is now doing is round all hours above 7.5 to 8.

I’m trying to display the days in which a certain volunteer has come, as a percentage of the days that have passed this year. I’d like that to be shown in a block, i.e. 33.1% of days Volunteer X has been present, (178 days this year, X has been here for 59 of them). How can I create such a block? How can I create a column which counts the days of the year that have passed?

I’m trying to create a formula that I don’t think should be too complex but I can’t seem to figure it out. I have an Agenda table with a list of agenda items, and a To Do column that links to a To Do table. In the To Do table, team members can check off it that thing has been done or not. I would like to write a formula in the Agenda tab that will essentially remove the To Do items once they have been checked off. I’m fine with creating a new To Do column and hiding the original one if that’s necessary, but I can’t seem to figure this out. Any help would be appreciated!

I want a form where the user can select a range of dates. This is for group interviews. So I send every candidate the form, they select a javascript calendar saying they are available Monday-Tuesday 1-2pm etc, and then I can pick a date such that all the form applicants are available. How do I do this?

Hello! I’m trying to write a “Deadline Alert” formula to indicate that a particular task is :rotating_light: PAST DUE​:rotating_light:or :negative_squared_cross_mark: which means on track / not overdue. The conditions for a past due task are as follows: (1) The field “Task Status” (Single Select Field) must NOT be set to “Complete” or “Not Started”---- completed or not started tasks can’t be past due :winking_face: (2) The field “Approval Deadline” must be TRUE aka not blank (3) The field “Days Past Due” (which already has a formula in it to calculate days past the approval deadline using DATETIME_DIFF) must be <1 (a negative number). Below is my poor attempt that has not been working. I’m new to formulas and greatly appreciate the help! IF(AND( {Approval Deadline}=TRUE, {Days Past Due}=<1, {Task Status}!=(“Complete”,“Not Started”) ), ” :rotating_light: PAST DUE​:rotating_light:", “ :negative_squared_cross_mark: ”)

Hey AirTable community! I want to to autopopulate a field (specifically a single select field, but this query could apply more broadly to other field types as well) with “No” if “Yes” is not selected. I have a massive data set with “Yes” sprinkled throughout that field type. I know I could filter to show blanks and manually select “No” and click+drag, but I will be continually adding new records and I want the preset for this field to be “No.” Any solutions??

I seem to be in an odd setup … new Chromebook using Chrome OS. I’ve installed an app, but it says its no longer supported, and to upgrade to install the latest version. Not sure what upgrade means in that sense, but I did that … and it installed but now its saying the same thing. Is the app supported on Chrome OS? It’s in the Play Store.