Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,230 Replies
5940 Topics
R
Rodrigo_FloresNew Participant
posted in Formulas
Operate Multiple Field Values

Hi everyone. please help me with this. I have some lookup fields, they have many values on them. I want to add a formula to multiply each of those values by a percentage (%), at the time I make this formula the total shows 1.0 instead of the multiple comma-separated values with their values reflecting the discount made by the percentage field. Am I missing something in formulas to operate those values? Thanks in advance

41
N
M
Mike_D_RAGNew Participant
posted in Formulas
Formula to Pull data to Create Pie Chart from survey questions on multiple choice answers?

Here is an example of what I have. Each student has 5 choices in the answers. 15 questions, 30 students and 5 choices per question. I need to create a global pie chart for the percentages answered to each question. In the example: How often do you consider someone else’s Pain? The answers: Very Often, Often, Sometimes, Rarely, Not at all I need the poercentage of how many students answered Not at all or often etc

41
Alexey_Gusev
C
Claire_ChildNew Participant
posted in Formulas
Creating a gradebook

Hi there! I’m trying to create a gradebook for a faculty member that I support. She gives students the option of dropping their midterm score if they did poorly and having their final exam make up a bigger portion of their final grade. I have a checkbox field to track that, so I want the formula for the final exam to say: if the checkbox is blank, average the midterm, final, and participation, and if it’s checked, average the final and participation. I keep getting an #ERROR value. Here’s my formula currently: IF({Reduce midterm?}=0, SUM(({Midterm exam numerical}.3),({Final exam numerical}.5),({Participation numerical}.2)), IF({Reduce midterm?}=1, SUM(({Final exam numerical}.8),({Participation numerical}*.2)))) Thanks for any help!

132
C
K
Karim_YounanNew Participant
posted in Formulas
If checkbox V --> calculate price of 2 column

Hi, I’m trying to make a formula if a checkbox is tiped: If checkbox is tiped than calculate the price by multiplying a value in column A by another value in columbn B and show the result in column C. I could have something but the result shown was “error” when the checkbox was untiped… Thanks a lot for your help !

42
K
S
Shira_LoaParticipating Frequently
asked in Formulas
Formula to calculate percentage of day worked on a project per workday

Hi community, I’ve built a labor tracker where we will track hours worked per Person/Project/Day. Based upon an 8 hour day, I want to calculate the percent of their day worked on that particular project. For example, if Veronica works on Project P-035.1 for 4 hrs, she submits that through a form by entering her name (linked field), the date she worked, the project (linked field) and the hours she spent on that project that day. In the case of working 4 hrs, the percentage worked on that project for the day should be 50%. I’m hitting a total blank on how to do that with a formula. (right now the % field is just a manual thing, because I don’t know what the formula should be) After that, I’m hoping to figure out how to calculate percentage worked on the project for the Month, because that’s how our budget predictions are created, and I want to compare and contrast predictions with actuals. Any help would be greatly appreciated!

172
S
C
christophe_spenNew Participant
posted in Formulas
Breaking down a row value into weeks

I don’t know if this is possible to do - but if so would be of huge value to me. My Challenge: I have records in Airtable that contain ‘Media Bookings’ for advertising campaigns As an example: Campaign: Launch Site: Youtube booked Impressions: 1000000 Media Cost: $15000 Start Date: 01/10/2022 End Date: 31/12/2022 I need to easily convert this information into ‘Week commencing’ detail so that I can prorata the impressions and cost based on weeks of the campaign e.g. W/C 03rd October, 10th October Value of doing this: this would help significantly with monitoring pacing of campaigns, and suporting accurate invoicing of costs to clients Is there a way of doing this within Airtable… thank you so much for your help!!!

10
C
W
Wendy_YelsikInspiring
asked in Formulas
If or Switch Functions with Dates

I need to compare two date fields to get a specific result. I am not an expert at Airtable, so my explanation isn’t very good :). My base is for tracking training. I need to mark a month “excused” if a person’s hire date is after the meeting date and then mark the month as completed or missed if their hire date is before the meeting date. The hire date is 9/1/2022, and the meeting date is 10/1/2022. That person is required to complete the monthly program. Any suggestions on a formula? - Wendy in Michigan.

71
Kamille_Parks11
B
Bill_FrenchInspiring
posted in Formulas
No More Static URLs for Attachments? (the rest of the story)

Continuing the discussion from No More Static URLs for Attachments?: No More Static URLs for Attachments? But I’ve now lost hope that this WON’T happen if we complain enough… I head back from the support team who said they would share my concerns with the product team… I do believe in miracles… Ha ha! I guess you got your wish (for a while anyway).

22
B
T
Tomas_Diaz_VelaNew Participant
posted in Formulas
Update a field with the content of other fields separated by a symbol but not if they are empty

Hi, I am trying to fill in a field with the content of 5 other fields separated by an underscore. I manage to get Field1_Field2_Field3_Field4_Field5 but if one or more of the fields are empty I get a consecutive underscores like this: Field1__Field3_ Is there a way I can tell airtable not to include the underscores that would be followed by empty fields?

10
T
C
corbKnown Participant
posted in Formulas
Comparing NOW() to a certain time earlier today

I need to be able to check and see if a certain time has passed in a formula field, and I don’t think I’m doing this right. My pseudocode is: If now is past 10:30am today, then 1, otherwise 0 What would be the best way to go about this?

95
C
M
Max_HoogewegNew Participant
posted in Formulas
Formula for Cumulative sum for the last 30 days

Hello, I’m trying to find a way to get a cumulative sum from the last 30 days that needs to be updated daily. This is all I have at the moment. (PS: The numbers on the left are IDs.)

202
M
X
XavierNew Participant
posted in Formulas
Formula for detecting duplicates

Hello, I’m trying to write a formula to detect if there is a duplicate record, ignoring a few columns. I’m not very good with formulas, but I imagine it’d be two if statements, which if correct I’d like it to highlight the row red? There’s probably a more intuitive way. I think with the if statements it might not pick up duplicate numbers that are on the same row? Thanks!

943
TheTimeSavingCo
Ricardo11
Ricardo11Inspiring
posted in Formulas
How to format time in a lookup array of durations?

Hey there, I’m looking up a duration formatted formula field from multiple records. The result of the lookup looks like this “4:24, 6:00, 6:31”. Is there a way to reference and DATETIME_FORMAT this in a formula field and get a similar output format? I cannot figure it out and get a zero value or the underlying seconds.

00
Ricardo11
J
James_SauveNew Participant
posted in Formulas
Calculate the difference between four dates

Hello, I need some help. I’m not a formula expert but I’m trying to determine the amount of time between 4 dates. In a nutshell, I know how to already determine the time between 2 dates *ONLY but I need to figure out how to get the formula to compute for multiple dates as listed in the formula and pictures below. Any assistance you can provide would be great! This is for managing a team of volunteers who sometimes come in and come out (pause and restart based on their availability). Thanks, James

21
TheTimeSavingCo
A
All_OptionsNew Participant
posted in Formulas
When the value of one field changes, set another field to have a set value

I think this is pretty simple? I would like to set a formula on a checkbox so that, when the value of another field changes, the checkbox is checked. OR, when field “Money” changes value, a separate field returns a value of “Print”. Then I have an extension set to print records with the “Print” value. Would appreciate help. Thank you!

441
TheTimeSavingCo
J
Jon_WrightInspiring
asked in Formulas
IF formula based on range/array of numbers across columns

I have 4 columns in my table, each containing numbers. In the Formula column called ‘Above Threshold’ I’m trying to create a formula that says if any of the values across the 4 columns is >100 output ‘Above’ and if <=100 output ‘Below’. So something like this IF(({Total users (last 30d)},{Total users (prev 30d)},{Total users (last 90d-120d)},{Total users (last 120d-150d)},)>100, "Above", "Below") That formula doesn’t work, so is there a way of doing it without using nested IF statements?

71
J
K
Kc_chenNew Participant
posted in Formulas
Can there be a formula in the IF?

Hello Everyone, I am a gallery assistant and trying to convince my director to move to Airtable! However, I meet some issues, hope anyone can kindly help me. So there will be two groups of product prices, one is (A+B), and the other one will be the amount we type in. Can the formula do something like If(Checkbox=1, “{A+B}”, “manual enter”) or something like this? Sorry really bad at this. Hope anyone can give me some ideas about this

82
K
S
Sched_kodeParticipating Frequently
posted in Formulas
Urgent Concatenate Formula

Hello kindly help out i have a table with the following field invitation link (field) : http://example.com Url (encode url component): test%20year how can i add the above such that it reflect url Parameter like http://example.com"?test%20year" thank you

83
J
M
MK_NguyenNew Participant
posted in Formulas
Help with formula that calculates amount of days from when a record was marked with a specific category in a field

What is the formula I input if i’m looking to create a new field that calculates days since a Record ID was tagged with a specific label in a specific column/field?

213
N
Z
Zak_KennedyParticipating Frequently
asked in Formulas
IF AND Statement

I am trying to create and if and statement for one field being empty and the other being filled. My fields Problem Category Closed Date Closed date always needs to be filled for me to provide a return to my field Charge Type. If the problem category is blank then I need to return “Marketable”. If the problem category is filled then I need to return “Problem”. I have tried multiple If And statements always getting an error or undesirable result. The latest one I have gotten is as follows If (And( Problem Category = BLANK(), Closed Date != BLANK()), “Marketable”, “Problem”)

4500
Z
F
funkedelic_bobNew Participant
posted in Formulas
Trouble converting an excel filter formula into Airtable

Hi all, I have a sheet of stock data that was too large for excel, so I decided to move it all over to Airtable. But I’m having trouble with one of the formulas I was using. In excel I’m using the FILTER formula that looks across the column headers (dates) to return the price that relates to a date I enter in another cell. For example, you can see in the image below, I entered the date of 10/11/12 in B2, and with my filter formula in K2 =FILTER(L2:O2,L1:O1=B2) it returns the price that is in N2 (as N1 matches the date I entered in B2). I see in Airtable there is a formula field type, but I don’t know how to replicate what I’m trying to do in excel… Any help would be much appreciated!

102
F
T
Timeless_Photo_New Participant
posted in Formulas
Utilizing IF functions to dynamically calculate amount

I have a payroll base that my workers fill out a form and then they submit a role (Main photo, Main video, 2nd shooter) I have a set rate for each role in each wedding that is literally different for each one. How can I structure a IF statement to dynamically pull the right amount to calculate how much they are owed from the job?

53
Zack_S
J
Jake_BoggsNew Participant
posted in Formulas
Automation, formula, or something else?

Hi, going off of my previous topic about gardening in “How can I use a formula for the below data?” I am wondering if there is a way to mark a row to show that I missed the target date for the plantings. Say like my target date is 11/20/22 but I didn’t get around to it till the 23rd. Is there a way to make it automatically color or trigger that row saying that I went over that date? Thanks.

11
Kamille_Parks11
J
Jake_BoggsNew Participant
asked in Formulas
How can I use a formula for the below data?

Hi, so I am trying to make a formula that fits my gardening schedule for the upcoming spring season. I have the pro plan if that makes a difference in what I am limited to do. Here’s an live example of the problem I am having so far. I will plant my cilantro seeds on 3/22/23. It takes 4 weeks till harvest. So I have 4/12/2023 as the ending date. I will need two other harvest date’s after that. The problem is that I need formulas for each plant which is a total of 40. They all have different dates and different weeks till harvest. Would I need different formulas for an auto-fill each time I put seeds into the ground or am I able to create conditions for it to all work? Thanks.

152
J
T
Theo_DjerkallisNew Participant
asked in Formulas
Exclude public holidays from counting date - date

Heyy everyone, I wonder if you can help, I am not the best at Airtable so rely mostly on learning as I go and your threads. I currently have several columns which count the time(days) between Date 1 and Date 2 but now I am looking to make it exclude a list of public holidays, currently, it just measures working days. So this is the formula currently - IF(OR({DATE 1} = BLANK(),{Date 1}<{Date 2}), BLANK(), MAX(WORKDAY_DIFF({Date 2},{Confirmed Date 1})-1,0)) But I can not seem to figure out how to make this formula not count the below, I’d have to implement this into several columns with similar formulas ! 01/01/2021 02/04/2021 05/05/2021 31/05/2021 30/08/2021 27/12/2021 28/12/2021 03/01/2022 15/04/2022 18/04/2022 02/05/2022 02/06/2022 03/06/2022 29/08/2022 19/09/2022 26/12/2022 27/12/2022 02/01/2023 07/04/2023 10/04/2023 01/05/2023 29/05/2023 28/08/2023 25/12/2023 26/12/2023 Any help is welcomed :slightly_smiling_face: Thanks

792
Databaser

Badge winners

Show all badges
Terms & Conditions