Formulas

HELLO! What fx do I have to build in “copy”, so that the value of the cell below is the summation with the value above? [quote=“Serge_Lacasse, post:1, topic:29840, full:true”]

Hello everyone, and @Jeremy_Oglesby in particular. I desperately need some assistance on this one. What I would really like is to be able to set up a product list with an automated parts inventory list. The moment I hit [PRODUCT SOLD], the designated parts to that particular product then should be subtracted from the parts inventory list automatically. To be able to do this I grouped records under a product type in a many to many junction table and told Airtable how many parts each product consists of. But… I cannot get this to work. Here you’ll find a copy of the base. The base is still somewhat of a mess. I translated the base as well as I could from Dutch to English in order for you folks to be able to peek in, please do: Airtable Airtable | Everyone's app platform Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free. Bu

I’m struggling with formatting my formula values. I’m trying to design a proposal in Page Designer. We list the price of all items, even when we don’t think we will use them so the client knows what they would cost if they are used. On the proposal, I want the subtotal value to be blank or * instead of $0.00. Easy enough, but then I want it to still format as a dollar value for the other items. I have column “Qty” where I manually enter the quantity of the item to go on the proposal. “Subtotal” multiplies the “Qty” and “Price”. “Qty for Proposal” and “Subtotal for Proposal” are the fields that are used for Page Designer. Is there a way to have my cake and eat it too? Thanks.

This has been asked a few times but I can’t seem to find the solution that works. I have a formula "CONCATENATE({For Formula},{Market},{Product Management},{Product Category},{Gender})" but its output adds unwanted quote marks like so: What I really want is to have it be separated by “/” but I’ve exhausted the time I have trying to fix that. How can I get this to output something with consistent quote marks - or comma separation - or preferably the / Thanks!

I have two columns that currently have the same fields. Column 1 is a synced column. Column 2 is a copy of Column 1, and we are asking collaborators to go into the base, and add or remove items from Column 2. In Column 3, I want a formula to show me all the items that were added or removed in Column 2 - mostly so we can track those changes and make sure nothing was removed or added incorrectly. Any thoughts on how to do that? Thank you!!!

I have a decently large data set, and I am trying to mine from it information to see what has value. On that I am trying to create a new sheet that has a list of keywords. I would like to then have a column next to each keyword that displays how many times that word appears in my main sheet (or a single column). I have seen several topics that cover something similar but nothing that seems to fit the bill. Can anyone help? Thanks!

Is it possible to put the Enter key in the formula? I tried the Enter key code (^p, it is Enter key code in the Word) , but it didn’t work. I want to implement the following display: EU Community Legislation: RED 2014/53/EU, RoHS Directive 2011/65/EU (10), RoHS Directive (EU) 2015/863; Product Standards: EN 301 489-1 V2.2.3, EN 301 489-17 V3.2.4, EN 300 328 V2.2.2, EN 301 893 V2.1.1, EN 62311:2008, EN 62368-1:2014/A11:2017; Adapter Standars: NA

How can I create a formula field that references an existing text field, but removes hard returns between lines of type?

Hi there! I’m setting up an editing task list for a wedding photo + video business. I have a lookup field set up for the wedding date, and would like to have one field that returns the deadline for each deliverable (various films, galleries, etc.), based on differing #s of days from the wedding date. E.g. preview photo gallery due in 21 days, versus a film due in 60 days, etc. If I use the formula shown below, it seems like I have to have separate fields for each of my different delivery timings (+21 days for some, +60 days for others, etc.), which really muddies up my table. This also doesn’t work well in cases where my deadline needs to deviate from the rule (for example, a scenario where we need to deliver in 14 days rather than our usual 21 days) I’d like to be able to enter the number of days post-wedding that the item is to be delivered into one field (e.g. 21, 60, 90, etc.), then use a formula in another field to spit out the deadline based on that specific number of days after

How do we get this formula right? Now it always returns 0

Hi! I’m stuck on a formula for tracking attendance. IF({Attended 10/23}<>1, “New”, IF(AND({Attended 10/23}=1, {Attended 12/8}=1), “Yes”,“No”) ) I have had 2 classes, and I want to know how many people who came to the first one (on 10/23) also came to the second (on 12/8). If they came to 10/23 but not 12/8, I’d like it to spit out “No” (because I am calling that column “Returned”). However, if they came to just the second meeting, I’d like to fill in “New” because I will ultimately want to filter out those who didn’t attend both sessions when getting my return attendance rate. This way, I should have: *Who came to my first class *Who came to my second class *Who my new students were in class 2 *Who came to the first and returned for the second Ideally, I’d like to see % attended across all sessions by student. Open to other ways of getting these metrics and am grateful for the help!

I am trying to come up with a formula that when I select an item from my multiple select field it uses its separate value to add to the total quote total. Any advice would be greatly appreciated.

Hi, I have a base where we key in the ID numbers of each person in a column - Males have odd numbers and females have even numbers. Would like to create a column that automatically tags them as either Male or female based on this data but I cant seem to figure our what formula to use. Could someone help me? It would be of great help. Examples: XXXXXX-XX-5557 = Male XXXXXX-XX-5556 = Female.

I am attempting to get all of the entries between two different dates. One date is the creation date of an entry and the other is today’s date. After doing a little bit of searching I figured it would be something like filterByFormula=AND(IS_AFTER({Start Date},IS_BEFORE(NOW())) But when I try using this via the API and filterByFormula it says invalid formula. I am not sure where it’s going wrong as I am super new to all of this and was hoping someone would have an idea or two. I also tried to include the date after the is_after and is_before but it came back as invalid. AND(IS_AFTER({Start Date}, {{INSERTDATE}}), IS_BEFORE({Start Date}, {{now}}) Oh and I should specify the Start Date in the formula is being pulled directly from the API response with the creation date in it.

Greetings - I’m using arrayunique(value) for rollups to scan shopify orders and copy over information to my customer, including phone number and delivery day. Problem is when I receive 2 or more different values, I need the most recent one only. How would I compare date values and return the most recent non-blank value?

Hi there, I am looking to link a row from one tab to another once a box is checked. I am looking to pull certain data from the row only and move it to a new tab. Is that possible? Thank you in advance!

I am looking for some help please to bring a formula from Excel into Airtable in order to calculate Auction fees and commission based on certain variables. First, I will explain how this example auction works: The Hammer Price is the final amount someone bids on an item. The auction adds a “Buyers Premium” based on the final amount to be paid by the buyer. Here is how it is calculated. X Auction charges a Buyer’s Premium calculated on the Hammer Price as follows: a rate of twenty-five percent (25%) of the Hammer Price of the Lot up to and including $25,000; plus twenty percent (20%) on the part of the Hammer Price over $25,000 and up to and including $5,000,000; plus fifteen percent (15%) on the part of the Hammer Price over $5,000,000. The eventual buyer pays the Hammer Price + the Buyer’s Premium. In addition to the above charges, Auction X also charges the seller a fixed Selling Commission of 10% on the Hammer Price. This is amount is subtracted from the Hammer Price before the proc

Hi all, What am I doing wrong here? SWITCH(Status, ‘Am Pending’, ‘Pending’, ‘Vendor Pending’, ‘Pending’, ‘Vendor Research’, ‘Pending’ ‘Board Pending’, ‘Pending’) I would like to return the same value which is Pending. Thanks in advance!

Thanks for taking a look in this thread. I am looking to create a zapier step that checks for a row in a table which contains 1) a certain ID (exists as string), and 2) to see if a certain string exists as part of a rollup. How would I write the formula for this? This is what I have so far… it’s good for finding rows that have only one item in the rollup, but if I need to find a certain item within a rollup, it says none exist. AND({ID}=‘abcdefg123’, {Rollup}=‘xyz999999’) Thanks again! Happy holidays!

Hello Community, I would need help with the case below, please: So, I have 2 sets of 2 columns, The first one (Column A and Column B) contain names that refer to a code. In the second set I have the same codes that should refer to the same name of the first set, but they could be misspelled. The codes are exactly the same in column B and D, but column C and D don’t contain all the codes and the names contained in Column A and B. What I need to do is to compare Column A and C to find misspelled names of the rows with matching codes (column D and B). Is there a way to do it in Airtable please? Thanks!

Hi I’m looking for a formula to convert a field from First Name Last Name to Last, First. “Steve Smith” to Smith, Steve". I see a lot of people have ways to go the other way… thanks!

Hello, I use this formula to define the week number according to the month and it resets again starting the new month. LPSD is my Licence Period start date VALUE(DATETIME_FORMAT(LPSD,‘w’))- VALUE(DATETIME_FORMAT(DATETIME_PARSE(‘01’&DATETIME_FORMAT(LPSD,‘MM’)&YEAR(LPSD),‘DDMMYYYY’),‘w’))+1 it’s giving me a wrong week no: week -47. when it’s week 5 is there a slight modify can solve this? thanks

Hi there :slightly_smiling_face: I have a multiple select column (column A, not the primary key column) that includes values like: Test A Test B Test, C I use look up in another column to refer to column A. The output is: Test A Test B “Test, C” How can I display comma-containing records properly (without the quotation marks)? I found some references in this forum using roll-up instead of look-up, but that was not helpful for my case. Should I somehow parse it as a string first?

TLDR: Concatenated text shown in the formula field depicts consecutive spaces as a single space. The real, complete, result of the formula only shows up in the expanded cell view (or, as I learned, when you copy and paste out of the ‘abridged’ formula field) Description of Issue/Discovery This was going to be a question because I have been struggling to figure out how to get a CONCATENATE formula to allow me to include multiple consecutive spaces (" ") in the result. For example: {Label-Lot}&" | “&IF({Delivered Amount}=BLANK(),” “&” “&” “&{Unit},{Delivered Amount}&” "&{Unit}) {Label-Lot}&" | “&IF({Delivered Amount}=BLANK(),” “&{Unit},{Delivered Amount}&” "&{Unit}) When the {Delivered Amount} field has a value, I get the result that I want, eg: Lot ALOF19 | 16 oz But, when the {Delivered Amount} field is blank, both of these formulas (and many other attempted workarounds) give a result that shows only 1 space instead of the 3 that I n

CAN SOMEONE HELP ME FIX THIS FORMULAE: IF {GST Rate}= (g) THEN SUM({Total inc GST}/11, IF {GST Rate}IS BLANK() THEN SUM {{Total inc GST}/0