Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,233 Replies
5940 Topics
I
Iv_BakoNew Participant
posted in Formulas
The formula if help

Hello! There is a column (category) with 3 categories (A, B, C) And a column (total) You need to create a formula: If {category} = “A”, {total}*0.5, If {category} = “B”, {total}*0.1, If {category} = “C”, {total}*1) But it doesn’t work for me. thanks

44
Justin_Barrett
H
Hello_FewlNew Participant
posted in Formulas
Nested 'If' formula between multiple multi-reference fields

Hi everyone! I don’t think this topic has been covered but if it has, I apologise! I’m trying to create an ‘If’ stated formula that connects two multi-reference fields. The aim is to automate the following: If I select a specific field on one row, I have to add the connecting, multiple fields to that in another column (which are a lot). The logic would read like this: If ‘anxiety’ is selected on the ‘feel statement’ column, then ‘I feel nervous/worried about most of the things I do’ and ‘I feel that something bad is about to happen’ and ‘I feel a lot of uncertainty in my daily life’ are selected on the ‘feel statement_softr’ column. A field on one column (feel statement) is the diagnosis for the statements on another column (feel statement_softr). So for example: Anxiety = I feel nervous/worried about most of the things I do I feel that something bad is about to happen I feel a lot of uncertainty in my daily life I feel panic and breathless at times I’d need to add these connection b

154
N
R
Rose_PemberNew Participant
posted in Formulas
Split() formula -- remedy? Need to split cell string at semicolons

Hello, I have a form response that pulls data in this format (bucket number, bucket weight;) ##, ##.#; ##, ##.# for example, 15, 32.5; 18, 33.4; 6, 35.1 I need to split the data so that I have the second string numbers added up to a total weight. I understand I need to split (in excel) the text at the semi colons, and then extract the last 3 characters to compound. Any ideas how to structure or accomplish this in airtable? thanks so much. Rose

1391
kuovonne
A
Asanka_NapagodaNew Participant
asked in Formulas
Assign values (numbers) for each component of Multiselect Field & calculate sum of each combination

Hello, I am looking for a method/formula to calculate the total time allocated for products (for manufacturing) which have different components with pre-defined times. For instance, A product called “GS60” can be manufactured from one or mix of “RCBO+GPO”, “SA Meter”, “Modem” and so on. I have pre-defined time for each component such as “RCBO+GPO = 1h”, "SA Meter = 1:5h etc. Once I select the components from multiselect field, it should be calculated the total time for each product in “Total Time” field. Thanks

112
A
A
April_KastnerKnown Participant
posted in Formulas
Formula that includes a rollup value is not working

I have a rollup field that is pulling the sum of multiple numbers, and in the next field, I want to use that result in a formula, but am getting an error. Before it was a rollup and I just manually entered the number, the formula field worked fine, so I’m assuming it has something to do with changing it to a rollup. This is my formula, total COG is the result of the rollup field: IF(COG>0,({Profit}/{Total COG})) Can rollups not be used in formulas or do I just need to change something about my wording for it to work?

605
A
J
jpj747Participating Frequently
posted in Formulas
Returning the last item from a rollup field

Hi I need to extract the last record from a rollup field where multiple records exist. Help!

112
kuovonne
A
April_KastnerKnown Participant
asked in Formulas
Switch with nested if formula

Hi all! I’m looking for a solution in a switch formula, with nested if functions. I want to calculate a percentage & addition based fee that changes based on the category input. Also with possible caveats on how that fee is calculated based on the price of the item. I’m hoping this is possible. Example: If Category=A, multiply by 12% If Category=B, multiply by 9%, then add .50 If Category=C, AND the price is <$15, ADD $2, OR if Category=C, AND the price is >$14.99, multiply by 20% Right now I have a separate table linked to include all this info, but I would love to set up a formula to calculate all of this within the main table. Thanks for the help!

1194
A
I
Iris_MaubonNew Participant
asked in Formulas
IF/AND Formula not working

Hi Airtable community ! I’m new to formulas, loving it but struggling a bit as well… I’m trying to automate a change in my records, from a phase of the process to the next, at a specific date using IF/AND. I created a fromnow field called “Calculation” and when it gets to 0 days, I would like to change my Phase en cours to the next phase. It looks like this : IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Envol 2” ), “Accélération 3”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Accélération 3” ), "Accélération 4”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Accélération 4” ), “Propulsion 5”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Propulsion 5” ), “Propulsion 6”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Propulsion 6” ), “Coaching terminé”, ) ) ) ) ) It’s unfortunately not working and I can’t figure out why :frowning: (it says “Sorry, there was a problem creating this field. Invalid formula. Please check your formula text.”) Thanks

152
I
B
Bianca_CadieuxNew Participant
asked in Formulas
Multiselect and Substitute formula

Hi, Begginer here with formulas! I imported a lot of data into my database. In a column box, for example, I have up to 10 different data, all separated by semicolons. My goal is to multiselect them, but I was told that I had to substitute the semicolons for something else for it to work. I have tried the basic substitute formula, but clearly I am missing some information or understanding. Thank you very much for your help!

106
B
R
Ryan_ThorpeNew Participant
asked in Formulas
Switch Formula Not Returning a Number - HELP

Objective: I want to return a number if a cell has a certain text in it and then multiply that number by another number to return a total value For example: If Content Type Column says TikTok then return number 30. However when I use the Switch function like this: SWITCH(({Select}), “UGC - TikTok”, “30”, “UGC - Review”, “25”, “UGC - How To”, “20” ) It shows a number but isn’t recognised as a number, therefore I am unable to multiply it against another column/variable to measure total cost. E.g. 30 * 5 (no of videos) = $150 [Content type cost] * [No of videos] = #ERROR! How to solve?

82
D
B
Bebe_PHONEKEONew Participant
posted in Formulas
Excluded sunday in my workdays

Hello guys, I’m new to airtable. Just want to know how to customize workday ? because my office is working from monday to saturday and the WORKDAY formulas does not work for me, so please let me know if anyone have a solution, thanks

63
S
M
Meera_MalhotraNew Participant
posted in Formulas
IF, AND, OR Formula Help

Hi Everyone, I have a field called “GL value” that is a number between 0 and about 100. I’d like to have a separate field that does the following: If GL value is equal to or over 20 = :x: High GL If GL value is between 11 and 19 = :orange_circle: Moderate GL If GL value is 10 or less.= :white_check_mark: Low GL Can someone help me write this? This is where I’ve got to: IF(OR({GL Value}>=20), " :x: High GL”), IF(AND({GL Value}>=11, {GL Value}<=19), " :orange_circle: Moderate GL”), IF({GL Value}<=10, " :white_check_mark: Low GL”) Thanks so much!

31
Justin_Barrett
L
leandro_medeiroInspiring
asked in Formulas
Comparative date formula

Dear ones, I need help. I’m using a filter on another system that searches the result based on a certain date. If I inform the filter as follows. Date = “2021-06-25”, The expectation is that I would return all the rows that appear on this date, however filtering this way I have no return. If I use the Date filter >=“2021-06-25”. it works, but it brings me back not only the lines corresponding to that date but all the other dates ahead.

677
L
I
Ivan_BorzenkoNew Participant
posted in Formulas
How to create a renaming formula

Hello yall! Got a quick quesiton here! My goal is to connect airtable with hubspot through zapier but when there import of info occurs there is a weird inflow of info due to internal problems between connecting the two that spits out code instead of text. My goal is to translate such code as lets say “100011010” into blue for example where each value of x that gets imported automatically has another column that can spit out y values. so similar to vlook in excel. Please let me know how it can be done

31
C
Karlstens
KarlstensBrainy
asked in Formulas
Stacked Switch Statement for Seasons

Hello everyone, Tinkering with my garden automations and I have seasons on my mind. There’s a couple of items I’m working on and thinking about, and figured I may as well post my questions here to help advance my Airtable skills. Firstly, spending time with a Switch statement, is it possible to stack cases? For example; SWITCH(Month{Date} ,1,"Summer" ,2,"Autumn" ,3,'Autumn' ,4,'Autumn' ,5,'Winter' ,6,'Winter' ,7,'Winter' ,8,'Spring' ,9,'Spring' ,10,'Spring' ,11,'Summer' ,12,'Summer') SWITCH(Month{Date} ,[1,2,12],"Summer" ,[3,4,5],"Autumn" ,[6,7,8],'Winter' ,[9,10,11],'Spring') And what are peoples thoughts on having rolling seasons within a recurring calendar? Not too sure about this one though, trying to map it out still, so perhaps it’s more of a base design question. :thinking: DATETIME_PARSE(DATETIME_FORMAT({Recurring Date},'MM/DD')&"/"&YEAR(NOW()))

204
Justin_Barrett
C
CSEDNew Participant
posted in Formulas
Finding Common Multiple Select Options Across Two Fields

Hello All! I am using a base to pair students based on shared interests they selected in a multiple select field. I am then trying to tell them what interests they had in common. I am easily able to do this when they selected all of the same options using IF({Are you interested in being paired based on any of the following interests?}={Are you interested in being paired based on any of the following interests?(from People)},{Are you interested in being paired based on any of the following interests?}) The problem I then run into is finding the interests they do have in common when there are additional topics they chose beyond the common ones. Here’s a picture of my fields to better help explain what I’m talking about. Thanks for any help or thoughts!

51
Z
N
New_AirtableuseNew Participant
posted in Formulas
Need help with Airtable formulae

Column A: Active (Checkbox) Column B: Expiry Date (Date Field) Column C: Date Created (Date Field) Column D: Package (Single Select) Contains two packages 2 Week Subscription, 1 Month Subscription I would like for: If the customer has a 2 week subscription enabled then the Expiry Date becomes Date Created + 14 Days If the customer has 1 month subscription enabled then the expiry date becomes date created + 31 Days If the customer’s subscription is Expired (After the expiry date) the Active checkbox must be deselected. If the customer subscription is not expired (Before and on the expiry date) the active checkbox must be deselected. Can Airtable do this?

95
Kamille_Parks11
K
kxr1mNew Participant
posted in Formulas
Delete text following after a given keyword

hi dear airtable community, i have a colum named “size” and it contains for example the following text “45 1/3 EU - 11 US - 250€”. now i am trying to find a function that will delete everything after “EU”, so it looks like “45 1/3 EU”. i already saw a bunch of formulas in here but somehow none worked. downloaded the scripting app by airtable and added codes like “LEFT({size}, SEARCH(“EU”, {size})-1)” but then i get an error message saying " ERROR ReferenceError: LEFT is not defined at main on line 1" and i dont know what it actually means. any help is highly appreciated, thanks for your time and help! Kind regards Karim :slightly_smiling_face:

22
Justin_Barrett
S
Sunaryo_PSNew Participant
posted in Formulas
Combine element

I would like to Combine this become Below Result : 65 Kodi (Seri warna) , 10 Kodi (Pilih Warna) If I simply using Concatenate For This it simply Show like This I am Using This Formula CONCATENATE({Jumlah Pesan (from Pesan Ke Sup)},{Satuan Pesan},{VarianPesanan (from Pesan Ke Sup)}) Is There Any Can Help , TQ

11
Justin_Barrett
H
Harrold_RuizNew Participant
posted in Formulas
Switch formula 2 conditions

Any way to rewrite this formula? Instead of looking at State for “BC”, using {City} SWITCH( {State}, “CT”, “New England”, “ME”, “New England”, “MA”, “New England”, “NH”, “New England”, “RI”, “New England”, “VT”, “New England”, “NJ”, “Mid-Atlantic”, “NY”, “Mid-Atlantic”, “PA”, “Mid-Atlantic”, “IL”, “Midwest”, “IN”, “Midwest”, “MI”, “Midwest”, “OH”, “Midwest”, “WI”, “Midwest”, “IA”, “Midwest”, “KS”, “Midwest”, “MN”, “Midwest”, “MO”, “Midwest”, “NE”, “Midwest”, “ND”, “Midwest”, “SD”, “Midwest”, “TX”, “Southwest”, “OK”, “Southwest”, “LA”, “Southwest”, “AR”, “Southwest”, “DE”, “South Atlantic”, “FL”, “South Atlantic”, “GA”, “South Atlantic”, “MD”, “South Atlantic”, “NC”, “South Atlantic”, “SC”, “South Atlantic”, “WV”, “South Atlantic”, “VA”, “South Atlantic”, “DC”, “South Atlantic”, “KY”, “Southeast”, “AL”, “Southeast”, “MS”, “Southeast”, “TN”, “Southeast”, “CA”, “Pacific”, “AK”, “Pacific”, “HI”, “Pacific”, “OR”, “Pacific”, “WA”, “Pacific”, “AZ”, “Mountain”, “CO”, “Mountain”, “NM”

171
Justin_Barrett
B
Boris_MaximovNew Participant
posted in Formulas
Return the first digit

How to get the first digit of a number? Like “4” from “413”

357
Justin_Barrett
B
Boris_MaximovNew Participant
posted in Formulas
Comparing item with others in the column

I’d like the numbers in a numeric column to be unique. Is there a way to check with a formula whether the new entry already exists in the column, no matter how many items there are? The result can appear in the column itself if possible, or in an extra field like for errors. I mean if there is a field with 1 2 3 …and I add a fourth item, which is 1 again – it should not be possible or return a warning

51
kuovonne
K
kxr1mNew Participant
asked in Formulas
Mid() + find() function needed

hi dear community, i need help finding the correct formula, but at least i know that the mid and find function have to be included. i want to extract the text between both “-”, so for example “11 US” is visible in the “us size field”. any help is greatly appreciated, if there are any questions regarding my concern, please let me know. kind regards karim and thanks in advance! :slightly_smiling_face:

277
Justin_Barrett
C
Christopher_MesNew Participant
asked in Formulas
Convert Formula result to a currency amount

so this is my current Formula - IF({Sold Platform}=“GOAT”,{Unit Sold Price}-({Unit Sold Price}*.095)-5,"") I am trying to have this result in a $dollar amount. I’ve looked through the community pages but I’m struggling to get the desired result. Can anyone help?

172
C
A
Ana_AmorimParticipating Frequently
asked in Formulas
Financial Formulas: is Airtable considering to launch financial formulas in the future?

Hi, guys! I couldn’t find a Roadmap for Airtable (I suppose it doesn’t exist) and I’d like to know if Airtable has plans to add financial formulas to the current list of formulas. I have a table where I collect some financial indicators and I’d like to do some basic calculations like standard deviation, variance, etc. I can’t do that in Airtable and I have to use Excel for that. I know Airtable isn’t a spreadsheet, but I hope developers could add more functionalities….

70
ScottWorld

Badge winners

Show all badges
Terms & Conditions