Formulas

Hi dear community 🙂I try to build a crowdfunding platform for real estate using Airtable.Users can choose the project to invest in and make a payment.   I am able to see all users and in what project they invested in Here are all transactions users made The struggle is to pay every user based on the amount they invested in each project because each project has a different dividend % and users can invest in 2-3 different projects in different periods. I somehow need to add to their profile what they earn every month. Any ideas on how to make it a reality? 🙂 Thank you 🙂

Good dayI am new to airtable.I want to know on how to do something like =B3-B2 and show in C3 in iartable but for full column I did try {Notes} - {Notes}in my column Notes row 1 is 111 and row 2 is 97But is shows 0 instead off 14 in my next column

I do a lot of networking for my job, and many networking groups that I visit have weekly meetings. I can't attend all of them every week, but I like to keep track of their meetings in case I can use them fill a gap in my schedule. To do this, I would like to have a formula that allows me to enter the day of the week (i.e. Wednesdays), and it calculates the next upcoming date (the 12th).I've found a formula that will calculate the next Tuesday from a given date, and I think I can retool it so it will calculate the next Tuesday from today, but I need one that allows for variation in the day of the week, so that it will calculate the next Tuesday for a group that meets Tuesdays and the next Wednesday for groups that meet Wednesdays, etc. 

Hello!I have a formula that calculates commission and I need to add an additional calculation that is tripping me up. I am starting with the following formula.SUM(IF({Commission Based On},{Commission Based On},{Purchase Price (Current/Final)})*{Commission %})+ {Commission Flat Fee}+{Admin/Office Fee}+{Other Fee}-{Concession}-{Referral Flat Fee}I added the following field to my table and need to add it to the formula.{Referral %} The first part of the current formula takes the purchase price and multiplies it by the commission % plus the commission flat fee.SUM(IF({Commission Based On},{Commission Based On},{Purchase Price (Current/Final)})*{Commission %})+ {Commission Flat Fee}But next I need it to take the result of the first part of the formula (which would be the total commission amount), multiply it by {Referral %}, add {Referral Flat Fee}, then subtract those two resulting amounts from the commission amount.Then I will add/subtract the remaining items.Any help would

In the following top portion of my pre-fill code, everything works fine except when there are duplicate records in the Asset Name field.All the other pre-filled items work fine. But when there are duplicate Asset Name values, the form displays nothing in the Asset to Update form item.This duplication is expected and I was thinking I should somehow refer to the Record ID as well so the code will grab to correct record:CONCATENATE(IF("Prefilling [Update Asset] view in [Assets Updates] table in base with id 'appZyoL5fNNYcN6dv'","https://airtable.com/shr7hLXk437tBSfOb"),CONCATENATE("?prefill_" & ENCODE_URL_COMPONENT("Asset to Update"),"=" & ENCODE_URL_COMPONENT({Asset Name } & "")),CONCATENATE("&prefill_" & ENCODE_URL_COMPONENT("Original Module"),"=" & ENCODE_URL_COMPONENT({Module COPY} & "")),What code should I use to clarify the Asset Name to pre-fill when there are duplicate Asset Names?Thanks for any ideas. Charlie

I want to use this mathematical formula to calculate the "average" diameter at breast height (DBH) of a tree with multiple stems:DBHavg = SQRT({DBH1}^2 + {DBH2}^2 + {DBH3}^2 + {DBH4}^2)This is the formula I used:SQRT(POWER({DBH 1 (cm)}, 2) + POWER({DBH 2 (cm)}, 2) + POWER({DBH 3 (cm)}, 2) + POWER({DBH 4 (cm)}, 2)) This formula returns an error for most records because most trees only have one or two stems. Only trees with numbers in all six fields will correctly calculate a value. How can I change the formula so it calculates the square root of however many DBH values are present for a particular record?

hi i need to copy the formula of the data "campagna" column of the database section in the "candidature" section so the data will appear even in this column. How can i do? I can't find a solution, thank you very much for the help. Please let me know

Hello all, This is my first time posting here! Apologies if the explanation is not the most succinct. I need help with creating a formula to work within an intricate email and list management system I've set up. I have two tables: Email --> Each record is an email being produced, tracking copy, design, and deploymentE.G. "Women's Board meeting 2 invite email" List management --> Each record corresponds to a named list of contacts from our CRM.E.G. "Women Board members" Both of these tables are linked to each otherEach record in the email table is linked to at least 1 list record, indicating the audience the email is going toEach record in the list table has 1 or more emails linked to it, indicating the past and future emails that have or will utilize this list's audience Fields to be aware of The Email table has a "Deploy date" field --> indicating when the email will dropThe List Management table has a look-up field pulling in the "Depl

Hi all,I have created a table that houses outputs from ChatGPT; namely creating assessments. I am currently prompting the output to create an assessment section by section. Each section contains a number of questions, which as per the prompt, are referred to as 'Question 1', 'Question 2', 'Question 3'. I want to extract each question and populate them as new records so that I can create marking guides and model answers for them. What would be the formula I could use or script I could run to extract each question from the string? See an example of a generated section below:-----------------------------------------------------------------------------------------------------------------------------------------Total Marks: 15Instructions: Please read each question and select the best possible answer from the options provided.Question 1 (3 marks): Which of the following laws in South Africa is the highest law?A. Labour LawB. Traffic LawC. Medical LawD. Constitution 

I need a formula for the column on the right that writes a number 1 to the first result on the left, a 2 to the second result, etc.I don't need an autonumber, this is because when the left column changes the numbers, the autonumber stays the same. Is there any way? 

I'm trying to set up an automated email to send clients after their first booking with my business. What formula can I use to identify if the record (booking) is the first one for that client (linked record from another table)?I don't want to manually check a box to trigger the automation, as that means first manually checking if it is their first booking, and that defeats the purpose.All I can think of is something like;IF Booking Count = 0 at time of Record Created, then = YESAny ideas on how this can be done? Let me know if you need more information.

This will be a long post, and a intricate task because Airtable somehow in 2023 doesn't have a "date is current month" type of filters.I want to in my interface dashboard to summarize the total sales value within the current month. And in a statistics summary show the total sales pr month.The rightmost box is is currently filtering the sales done within a month ago, which is not the correct setup.When it's april, that one should only display April sales, not anything from March.I have already a formula field that declares the date the sale was done, which is the field called "Salg".What i need the formula to do is to return "1" or "true" if it's this month.And then i will filter the summary to only calculate the sales value on the records which are a match to whatever value we create in the formula....  I appreciate any help i can get here. 

I am quite new to airtable, i am trying to create a formula that says if an end date exists THEN subtract today's date from the end date column ELSE calculate days remaining in the month.I have checked through various date formulas on the community and i do not find a fomrula that directly calculates days remaining between today and month end if there is no value in the field 'end date'.Can someone help please?

Hi guys need help with this formula if possible, basically the field 'total Pie' needs to return 3 types of values, but if I don;t add the blank condition blank fields default to 'high'How can I insert the blank statement in?IF(AND({Total PIE}=BLANK(),BLANK(), if({Total PIE}<=3, "low", if({Total PIE}>=6,"high", "medium"))

I'm putting the below formula but it keeps coming up with an error message - any ideas why when each part seems to work independently.  IF({Always On/Miniseries} = "Always On" , {Total Budget (USD)}*((13-{Number (from Start Date)})/12) ), IF({Always On/Miniseries} = "Mini Series", {Total Budget (USD)}*{Year 1 (from Mini Series Amort) (from Start Date)})

New here!Is it possible to set a different formula in each row of a column? For example, I am looking to set a due date in one of our rows (which is one of our tasks) to automatically calculate a date 2 months before a start date of a program. The second row, is another task and has another due date (7 days before the start date). The start date is the same for both. Is this possible? Thanks!

Hello all,I'm struggling to edit the text in a field referencing an attachment field. I want it to extract a label code from the attachment name. The text looks like…GAR0027 Southern Vines Cab Sauv 15 F.pdf (https://dl.airtable.com/.attachments/c3a3a85e047021c86d494d90be7dcb64/08f0ad8f/GAR0027SouthernVinesCabSauv15F.pdf), GAR0028 Southern Vines Cab Sauv 15 B.pdf (https://dl.airtable.com/.attachments/034388155febbaac0b80a222437f97f5/3a723729/GAR0028SouthernVinesCabSauv15B.pdf), GAR0027 Southern Vines Cab Sauv 15 F.pdf (https://dl.airtable.com/.attachments/34bf7b45992c17c945927f49bac0365f/f6f2829a/GAR0027SouthernVinesCabSauv15F.pdf), GAR0028 Southern Vines Cab Sauv 15 B.pdf (https://dl.airtable.com/.attachments/6f3bc3598e135c082f1e093c73f0ba11/58802a97/GAR0028SouthernVinesCabSauv15B.pdf), GAR0027 Southern Vines Cab Sauv 15 F.pdf (https://dl.airtable.com/.attachments/8db9deebbb0460d8bb3fa6d367f5bbb5/6e8c7d49/GAR0027SouthernVinesCabSauv15F.pdf), GAR0028 Southern Vines Cab Sauv 15 B.pdf (ht

I'm thinking I can use a 'formula' field to do this task. Can anyone help with the 'Formula' field... or is there a better suggestion. Thx.IFFirst 4 characters of a record in {Unit & Week} = First 4 characters of a record in {Unit Name} AND {2023 Fee Paid} is Checked, insert the amount in {2023 Maint. Fees} field.Association Table{Unit & Week} is not the primary field{2023 Fee Paid} is a CheckboxMaint Fees Table{Unit Name}{2023 Maint. Fees}

My custom personal CRM is composed of 3 tables: People, Organizations, Interactions.  People belong to one or more organization.  Interactions are individual events that could include multiple people.  Organizations has a linked field to People.What I want to do is show when the last time I interacted with each organization on the Organization table.  I figured out how to show the last time I interacted with a person using a Rollup with the Aggregation formula of MAX(values).  Now how do I go through and aggregate each persons last interaction to show the latest for the organization?Thanks, Jon

Hoping someone can assist me in creating a formula. Basically I want to take a field that has a numeric value in it and convert it to CM or if there is no number in that field output the text that is in it. e.g.Field A contains value: 60 or contains value "not applicable".Using a formula field I have {Field A} & " in, " & ROUND(VALUE({Field A}) * 2.54) & ' cm'. The output would be "60 in, 152, cm" if it just contained numbers. If that field instead contained text and no numbers I want to just output the text. It won't always be "not applicable" that's just an example. So I figure I might have to use the search() function with regex in the formula with an IF statement. So basically "if "field a" does not contain a number just output text". Just not really sure how to do it! Any help would be great. 

Hello everyone,I'm fairly new to Airtable and I've been impressed with the level of questions the community has solved.  This is the first time I'm posting a question with the hope someone can help me.  Here is my situation:In Table A, I have 3 single-select fields called Trade Choice 1, Trade Choice 2 and Trade Choice 3. Each record (applicant) has a Province of Residence.  I have a lookup field to Table B that lists each trade with the specific provinces where the trade is a Red Seal trade in a multiple-select field called Red Seal Province.I want to add a verification step to confirm when the choices of jobs are entered that they are a Red Seal Trade in the province of residence of the applicant. I would ultimately use condition colour to highlight any choices where the trades don't exist as a Red Seal in the province of residence.How do I write an IF formula that would say:  IF(Red Seal Provinces from Trade Choice 1, Trade Choice 2 

I have a campaign work back schedule. I need to calculate four date fields based on the Launch Date:Assign Due DateDeveloper Due DateQA Due DateApproval Due DateLaunch DateThe number of days for each due date varies based on the combination of two single-select fields:{Channel}: Email, Push, Banner{Send Size}: Large, SmallThe logic is:If Email (Channel) and Large (Send size) with Launch Date 6/1/23{Assign Due Date} = 6/1, -12 days {Developer Due Date} = 6/1, -7 days{QA Due Date} = 6/1, -6 days{Approval Due Date} = 6/1, -7 days {Launch Date} = 6/1If Push (Channel) and Small (Send size) with Launch Date 6/1/23{Assign Due Date} = 6/1, -5 days {Developer Due Date} = 6/1, -2 days{QA Due Date} = 6/1, -1 days{Approval Due Date} = DELETE (or show NA){Launch Date} = 6/1I'm new to airtable so I hope this isn't a noob question. 

Hello! Can someone help me create a formula that finds the first wednesday after the 10th of the month? I have 1 date field that I manually give a date value (eg. 1/4/23), and then I have a formula field that should find the first wednesday after the 10th of the month. Is that possible?

Looking for some help. I have a table were I utilize "last modified time" to be inputed based on the Specific field ("Checkbox").This sequence works well for what I am doing, which is; creating time logs for when we "Start" and "Stop" work on a specific task. I then have a formula set up "Datetime_Diff" where it grabs the difference between the two in minutes (set up as integer) so that I can log billable hrs towards a project (Time on Task).   Here is my problem... I have another table "billing" that I want to utilize a "rollup" field for, to grab the SUM of that data in my "Time on Task" field and have it contribute to a specific job number. I am utilizing this exact same method for and "expense" field but the field the rollup is connected too on that method is a "currency" field (which I cannot use on my time tacking). I am sure there is a solution but I am not an expert.. yet! Any help would be wonderfully appreciated! 

Hello everyone! I have this formula:  IF(AND(DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10, DATETIME_FORMAT({Dato for udløb}, "D")<10), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))=3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))<3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),3-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))>3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),10-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"))))) Currently when the field 'Trækdato (FarPay)' is empty, it will give an '#ERROR!'. I wa