Formulas

Hey all! I am trying to use Airtable as an excel spreadsheet to track my budget. Is there a way to create a formula that use the previous records ‘current balance’ amount for the formula so it will update each record (transaction). I really appreciate any help you can offer!

Hello! I am an absolute newbie to databases, spreadsheets, the whole nine yards. I am working on a DATETIME_DIFF formula that I want to either show a fixed duration (based on two dates) if the status in Column X is A, B, or C, and show a duration using the TODAY formula if the status in Column X is not one of A, B, or C. Ideally, for those entries with A, B, or C in Column X (where Column X is a single-select field), the DATETIME_DIFF formula will pull the end date from Column Y, and for those entries without A, B, or C in Column X, the DATETIME_DIFF formula will pull the end date from Column Z. So for entries with end-dates (i.e. the ones that are tagged A, B, or C in Column X), it will always show the number (e.g. 38 years from start date to end date), but for ones that are ongoing, it will update to today’s date (i.e., this year it will say 25 years from start date til today, next year it will say 26 years, etc.). I tried this: IF(Posture = OR(Commuted, Paroled, Deceased), DATETIME_

I want to create a table similar to this one in AirTable. What I can’t figure out is if there is a way to calculate between rows (records). For example, looking at this spreadsheet, the calculation in “O7” is =IFERROR(L7/L6,0) Is there a way to mimic the same kind of calculation (formula) in AirTable?

Hi everyone, I am using a formula to determine how much profit/loss is being made given the minimum number of people attend an event. The issue I am encountering is that when the minimum number of people attending the event means that the profit/loss is exactly zero then it is returning an empty cell rather than a 0.00. You will see in the below formula that I am also using the ROUND function as it was returning 6 decimal points when I only wanted 2. I was unable to use the formatting function as it is not always numerical value given the IF conditions at the start. Really stuck on this one and any help would be greatly appreciated! IF({Banded Pricing}=“No”,“N/A”,ROUND(((({Traveller Price}-{Variable PP Price}-{TM Pays Cash}-{TM Commission})*{Minimum #})-({Banded Fixed Cost at Min}/{Unique XR})),2))

Hello, I would like to use a parameter in a formula. Some context: I have a table A with different rates for each rows. (rate1, rate2, rate3). I have a table B with weighs for each of the rate (rate1_weigh = 1, rate2_weigh = 2, rate3_weigh = 5) I want to calculate the overall rate for each rows using weighs for each rate. Something like: (rate1_weigh * rate1 + rate2_weigh * rate2 + rate3_weigh * rate3) / (rate1_weigh + rate2_weigh +rate3_weigh) How could I call the weigh that I have in table B in the formula I write in table A? Thanks a lot for your help, Regards

I am currently working on putting together a price list from our various venders who offer similar products. One of my tables list the “Generic Product” ID while I have another table with a list of all products we purchase. I was able to use a rollup easy enough to tell me what the lowest price for that “Generic Product” is but would like for that to also pull the information from that table to tell me which supplier offers the lowest price. Is there a simple way for me to accomplish this? I have been missing around with some ideas but figured I would ask the community.

Hi! I am new to Airtable - and it shows a lot of promise but I have a hard time wrapping my head around it. I need to check my orders against a backorder list. I can get both as excel files and thus read them into Airtable. Then I need a crosssection of the columns. Something like: For every_line in ColA (for every_line in ColB check if ColA_item = ColB_item → return ColB_item) Does this make any sense? :slightly_smiling_face:

Hi everyone! So I have a table with everyone names, slack id and DM links. I have a table where a supervisor can add small tasks with fixed deadline to a single person or a few picking from the first table. So far I managed to make a channel where using a few lookup tables it sends a meaningful notification. Trying to improve: Best option send a DM to the person chosen. No idea how to make it work. (easy with a static field but I would need it dynamically) Secondary option is to tag the people listed. There is a neat guide using the ID but it fails as soon as I have more than one person listed in the field of people to notificate. Any idea?

Thanks in advance everyone for the help!!! I am really having some issue finding the solution to this one. I have a base with several tables and many different fields. For 1 of these fields, in 1 of the tables, I want it to be determined by 3 other fields in the same table. Basically, I want to use 3 conditions (for example: Name, Service, Language) and for the field to be autopopulated with a rate to be found in another table. Is there any way to do this?

Is it possible to combine “IF” and “SWITCH” formulas into one? i am trying this formula and i am keep getting an error: IF ({Send to FBA},SWITCH({Product Type}, ‘Individual’, {Arrive to Prep Center}*1.5, ‘Return’,{Arrive to Prep Center}*2.5) The “Send to FBA” is a checkbox field When i do this formula alone it works great: SWITCH({Product Type}, ‘Individual’, {Arrive to Prep Center}1.5, ‘Return’,{Arrive to Prep Center}2.5) But i want this formula to work only if “Send to FBA” is checkbox!

I have the roadmap of a project with start and end date fields for each task with a dependency between tasks, I need that when the end date of some tasks that has dependency changes, it automatically changes the dates of the rest of the tasks

Hi, I try to calculate the time duration between two date fields with DATETIME_DIFF formula, but doesn’t work at all. It shows totally false results. Here it is: This is the formula I use: This is the formatting for duration: Any idea what I should do?

Hello, I’m Spanish and I can speak a little English. I’m new in Airtable, sorry if my question is too easy for you. I have a shoop with several makers (“Créateur”) I would like to sum “Prix” for each “Créateur” in the field “Total pour créateur” I’ve made a formule with SWITCH but its does’nt work {Prix} + SWITCH( { Créateur }, “Sophie”, SUM, “Mathilde”, SUM, “LMC”, SUM ) Can you explain how to find the solution ? Thanks Date____________.Créateur_________Prix_________Total pour créateur 5-3-21___________Sophie___________80_______________80 5-3-21 __________Mathilde __________60_______________60 5-3-21 __________ LMC ____________50_______________50 5-3-21__________Sophie ___________20 ______________ 100

I am new and teaching myself databases and AirTable so please be gentle. :grinning_face_with_big_eyes: I am trying to extract the numbers from the {Recipe} column and add them all together to figure out the total number of drops for each blend. Is it possible to do this with my current setup? I have figured out how to extract the numbers but it just smooshes the numbers to become a 2-6+ digit number. I hope this makes sense.

Hello, would anyone know a formula to add a date with a time and bring the result of date and time updated? example: Start date column: 6/7/2021 1:45 am Duration column: 0:45 Result column: 7/6/2021 2:30 pm. Thanks in advance ! :blush:

Hi, I’m wondering how to count all the unique values in a field/column? In my simplified example below, I’ve filtered for orders shipped in the spring, and I want to know how many unique fruits are in the linked field, “Content (from Fruit table),” so that I know how many unique types of fruit I shipped all spring. Thanks for any help you can give.

I have Table 01: Product Columm Price option : $1 , $2 , $3 Table 02 : Customer Column Want to buy at : $1, $2, $3 I want to match these condition together and display its at another sheet and i can track back which customer is which. Example : Display sheet will be display: Product A price $1 have : Mr.A , Mr.B, Mr.C, Mr.D want to buy Product B price $2 have : Mr.A, Mr.B want to buy

I am using a WORKDAY formula to calculate the end date of an event based on the duration. The formula I’m using is: WORKDAY({*Start date},({*Days}-1) However, this does not return a date field that can be clicked and dragged in Calendar and Gantt views. Is there a way that I can make that happen? I don’t mind having another field that converts that returned value to the correct format.

I have a table called Things and another called Options The Options table has a list of attribute columns and each has an associated Cost Modifier Name Type (Color, Size, Shape, Special) Cost Modifier The Things table has Lookup values that point to views of certain Types of Options Color Option (Single Select) Size Option (Single Select) Shape Option (Single Select) Special Options (Multi Select) What I want to do is to aggregate the unique options from (Color, Size, Special) and use the resulting value to create a Rollup Sum of all Costs. Example: Options (Name | Type | Cost) Red | Color | 3 Black | Color | 1 Large | Size | 2 X-Large | Size | 3 Hoodie | Shape | 5 V-neck | Shape | 3 T-Shirt | Shape | 2 Distressed | Special | 2 Glow in the Dark | Special | 1 Glittered | Special | 2 Things (Name | Color | Size | Shape | Special | f(Color + Size + Shape + Special) | sum(lookup(Unique Selected)[Cost]) Princess Hoodie | Red | Large | Hoodie | Red, Distressed, Glittered | [Red, Hoodie,

Hi everyone, I’m adding a button to my table, it’s a URL that includes a dynamic field agency_cardId from that table. It’s not accepting the syntax, any thoughts, please? “https://d1s1fe8elvctzs.xxxxx.net/?cardId= agency_cardId &source=agency&token=123456789.123456789.123456789M#/”

I have a link that may change regularly. I have put it in a tab with a single cell, and want it to propagate across all my tabs using a lookup, without having to explicitly have it look up ever single time. Is there a way to pull a cell from another tab into a formula for a column? (Basically, it’s a meeting ID that needs to be on every row in each tab - pulled from a single source)

Hi all, Here is a screen of what I want to do automatically: For an example, I have a application with the name “gazelle-evsclient”. This application can be have multiple version (ex: 5.13.1, 5.13.0, …). I want to create a column (ex: Need to be upgrade) based maybe on a formula who say: “If the actual version is not the latest, so I display the latest version for this application”. It is possible to do that ? Thx for your help ! :slightly_smiling_face:

ColA has 3 different transaction numbers. The first always starts with TEX followed by numbers. The second is always just numbers. The third is always blank. I need a formula that will look at ColA If ColA has TEX, in ColB put Text Me If ColA does not have TEX, in ColB put Call Me If ColA is blank, in ColB put Do Not Contact

Hi everyone! I have a table with several courses, and event dates for each course. I’m trying to assign the week of the course each event falls in. So, if Course A starts 11/1/20200 and ends 12/31/2020, all events between 11/1 and 11/7 would be assigned Week 1; all events between 11/8 and 11/14 would be assigned Week 2 (and so on) until 12/31/2020. Is there something in Airtable that can do that? Currently I’m writing a formula (If course = X and date is after 11/1/2020 and before 11/8/2020, then Week 1; if course = X and date is after 11/8/2020 and before 11/15/2020, then Week 2…etc, etc) Any help on how to make this simpler would be appreciated! Thanks! Jeffrey

Hello! I’m new on airtable and would love some help creating an IF formula. Context: I’m trying to create a unique vendor code for SKUs. Ive added an autogenerate number field called “Vendor Number” and added a “vendor code” field where I would like to create the IF formula. I want to to add “00” at the beginning of the vendor number if the vendor number is less than 10, add “0” if the vendor number is less than 100 and show only the “vendor number” if it is greater than 100. Example: Vendor Number: 7 Vendor Code: 007 Vendor Number: 19 Vendor Code: 019 Vendor Number: 122 Vendor Code: 122 Any help would be greatly appreciated! Thanks