Formulas

I used a simple formula for column C (it is A-B), it shows up as -0 in one case and 0 for another? Why should zero have a negative sign? In my case although all 3 columns are setup to round to 2 digits, I am certain that there is no third decimal digit in either column A or B. Reason for asking is that it messes my colour codes where I’ve setup anything less than zero as red.

Hi everyone! First time posting. Appreciate your help! I need your help in writing a formula that operates like this: (Assume CATEGORY is a single select field.) If CATEGORY = A, B, C, or D, then X If CATEGORY = E, F, G, or H, then Y If CATEGORY = I, J, K, OR L, then Z I think a SWITCH formula would do the trick, but I’d like the result (X, Y or Z) to come from a single select field. What I want to avoid is having to type out X, Y or Z multiple times. For example, here’s what I have now, which works but is not efficient: SWITCH( {CATEGORY}, “A”, “X”, “B”, “X”, “C”, “X”, “D”, “X”, “E”, “Y”, “F”, “Y” ETC. ) Thoughts? Thanks!

We have done a pantry inventory, and want to make a shopping list sheet. We would like things to auto add to the shopping list when they reach a certain level of stock. So for instance, If we have 12 soy milk in pantry, we want soy milk to add to the list when we reach 6, and we want to buy 12 at a time. I am looking for a way to get a formula to do this. Is that even possible? My column headings are Item - Use by - Location - Stock - Minimum stock - Amount to buy I just need the Item and amount to buy to copy to shopping list when the minimum stock is reached. Am I dreaming?

IF(AND({Total Pages}<“50”,{Total Pages} >“43”,"$3.12"),IF(AND({Total Pages}<“12”,{Total Pages}>“1”,"$1.67")) What am I doing wrong? I’m trying to calculate postage by ranges…stating if Total pages is less than 50 and greater than 43, then $3.12, if total pages is less than 12 and greater than 1, then $1.67 and so on.

I think this can be done, might just need to add a new field! On table ‘seed packets’, I have a manual number field to log how many seeds in the original packet. On table ‘sowing log’ it calulates how many seeds I have sown by N numbers in N numbers of stations (manually logged, auto answer (I didn’t set it up so language may be wrong). On the ‘seed packets’ table I appreciate I shall need to create a new field for the answer. I want it to take off the number of seeds sown from the original packet number I am familer with formulas in excel but not here so please, if someone could give me a simple starter I’d be most grateful; or correct me if I am heading down the wrong path

Can someone help my brain wrap around this type of formula? I’m trying to get different amounts to populate based on what is selected in a different field. For example) Field 1 is State Trying to get Field 2 to calculate: if Field 1 is Missouri, then Field 2 should equal $2.00, OR If Field 1 is Illinois, then Field 2 should equal $1.00

Hi, I’m very new to Airtable. I am crafting a budget base for freelance business. I have an Income table and I am trying to only calculate tax for untaxed income. I have a multiple choice selection with options “FREELANCE” and “PAYROLL” then I have a field for calculating the tax (amount*.25) I want to be able to only calculate tax for the records that are selected FREELANCE… IF multiple select = FREELANCE then (amount*.25) IF multiple select = PAYROLL then 0 I have no idea how to go about doing this, all my attempts have failed.

I’m not quite sure how to go about creating a formula that checks an expiration date (eg date field called “Expiration Date”) against today’s date. I know this formula checks field Expiration Date against another date field called Date, but I need it to always check against the current date, not against another date field. IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),“ :warning: Expired”,“Ok”)

I don’t think I’m thinking about this the right way… I have two tables: Events: hostId, eventname, city 20, event1, helsinki 20, event2, stockholm 21, eventN, toronto Hosts: hostID, hostname, totalEvents 20, biff, count of all rows from event table where hostID = 20 21, biffette, count of all rows from event table where hostID = 21 No matter what I’ve tried to link, rollup, or count, the field in the Hosts table always shows 1. I can easily generate this using a Pivot, but I want to use that summary data in other systems that I connect via Zapier. For example, when a new event is scheduled I want to send a notification to another system to say "congrats to {host} on scheduling their {countOfEvents} event!

I’m using a single select field to note the different statuses in a production workflow. I’d like to create a field to mark the date that the record was set to a particular status, “Ready to Publish,” and keep that date persistent even when the status moves on to another stage. I started with this formula, which does return the date something is set to the correct status: IF(Status=“Ready to Publish”, TODAY(), “”) But then goes blank when the status updates to the next stage. How can I make the date persistent after the status changes? Thanks for any help.

OK, all you smart people out there. (I know there are a lot of you!) I need a formula for my database and I need some help, (because I’m horrible with math and algebra.) I have different churches with different services and studies through the week. The services are stored in the database with an unspecified date/day/time. (e.g. Bible study is every Tuesday night at 7:00pm) On an Airtable calendar I want to display an actual date to the user. (i.e. Tuesday, March 10th.) In order to do this I have to calculate what the next actual future date will be from whatever is the current date. It has to dynamically adjust with any given day the user might be looking at the calendar. So… Get the current date (e.g. March 4th) Get the stored generic day (i.e. Tuesday) This is where I get stuck !

Is it possible to override a cell with a formula? For the most part, one of my columns should be a formula, but other times I want to hard-code it. This has kept me from using AirTable to track my bookings/commissions because it’s not as flexible as Excel in this way. Would love to be able to use the system for what I need. Thanks!

Hello, I have a date field and a checkbox field. Can I write a formula that says if there is a date in the date field, check the box in the other field? Eg “Date Commission Paid” has a date, so “Commission Paid” is ticked?

SWITCH(PageID, "18479402361", "NKH", "8532248414", “WWP”, "8271670852", “LLS”, "342294162453301", “PP”, "27817332304", “AFSP”, "55701226138", “AHA”, "15687409793", “WWF”, "12692786774", “ALS, "184404096774", “LUPUS”, "105229062888132", “SSS”, "25372476424", “BFAS”, "12596759677", “RAICES”, "14084625156", “SGK”, “13793312555", “MAW”, "1562713090633117", “OUR”, "20876805343", “MOD”, "100438444321", “CCA”, "8492293163", “EDF”, "111541054636", “BTM”, "10331123781", “SO”, "371566686229476", “ADAA”, "342091725962792", “M22”, "8047221596", “STC”, "11791104453", “NRDC”, "81517275796", “UNICEF”, "512423278811960", “DS”, "122531741163825", “ALF”, "22902828717", “BB”, "332378771195", “CF”, "97646191545", “HA”, "44224524016", “AKF”, "332077995968", “LCRF”, "84618074710", “CCC”, "95155519857", “BLISS”, "24470472733", “TP”, "7271534916", “FFB”, "N/A") Here is my switch formula currently. I have genuinely been looking at this for 45 minutes now and I cannot seem to figure

Hello, I’m creating a spreadsheet which has multiple tables for different events (event 1, 2, 3, 4) we put on, and within those tables is dollar amounts. I then have a separate table with rows for each event, and I want the table to calculate the total dollar amounts across all sheets? Is this possible?

I am wondering if I can take the column that’s titled “Client Lifetime Value” which is a numbers column… If the “Client Lifetime Value” is set at $37 I want it to print out “$37 Package Purchased” but if the value is $127 I want it to say “$127 Package Purchased”. I would like these to show up in a Single Select column at the end of it so that the options are colored and easily distinguished. We use the single select options to ensure that the filtering for the rest of the spreadsheet works correctly.

Hello, I’m trying to do a formula that calculates commision due. I have the commission field, and I also have a ‘commision paid’ checkbox field. Basically I want the commission owing field to calculate the commission if the paid field isn’t ticked yes. Help please!

Hi guys. Trying to figure out the best way to overcome the limitations of the Page Designer and hoping a formula might be the answer. I’m collecting a lot of data in a table called “Rooms”. Each room record has linked products, materials, and concepts, and then I use a bunch of lookups to retrieve the information I’m hoping to display on my Page Designer. The problem is how the Lookup field combines my information. Here is my Product Name Lookup field for one room as an example: Aqua 2 Complete Shower Rail, Lapiana Towel Bar, Lapiana Towel Ring, Lapiana Robe Hook, Lapiana Paper Holder, Square Tubular Pull Handles for Back to Back Mounting, Square Trim for 1/2" Valve, Caso Single Hole Faucet It obviously collects the information in a list, but that’s not what I want. I would like to be able to hand arrange each name in my page design so that each text string can be under their respective images. For that, each name has to be in i

What am I doing wrong with this one? Please help! If ({Date Due} IS_BEFORE{Date to work on it}), “ :nauseated_face: ”))

Hello, dear Airtable community. I want to generate a unique payment ID, based on the sum, date, cashier, and the type of transaction. Everything has been good by far, until I decided that I want to try using emojis. So, I assumed it should be a mix of CONCATENATE and IF, because I want the emoji to appear only in one field = meaning that IF it says ‘cash’ then show ‘ :money_with_wings: ’, IF it says ‘card’ then show ‘ :credit_card: ’

Hi, I’d like to auto-assign due dates to our writers. However, there are some conditions: I don’t want due dates to ever occur on weekends Some writers work 3 days a week (M,W,F) and some 5 days a week. Is there a formula I can use to do this and all the while include the start date for the first assigned task?

Is it possible to recreate this formula in Airtable? Where: i = IRR L = Loan amount F = Points and all other lender fees P = Monthly payment n = Month when the balance is paid in full Bn = Balance in month n This equation can be solved for i only through a series of successive approximations, which must be done by computer. Many calculators will also do it provided that all the values of P are the same. I’m trying to solve for i All items except for i would be supplied via columns inside Airtable

I’m a professional writer, and I have a table where I put all the projects I turn in. On the table, I list how long a project took me (duration) and and how many words it was (#) (along with other factors). What’s the easiest way to create a formula that will give me the time it takes per 100 words? I keep messing up each time I try.

I’ve tinkered with a few ways of doing this, but wanted to get some ideas about the most efficient way. I have this full address string: Street address: 1600 Pennsylvania Ave NW # 15 City: Washington State: DC ZIP Code: 20500-0003 I want to extract the State (in this case, DC). I’ve tried LEFT, RIGHT, MID, etc but nothing seems to zero in on it. Appreciate the help :slightly_smiling_face:

Is there a way to utilize a concatenate formula + Link to Another Record to have a lookup work automatically? I currently have to copy/paste the concatenate result from the formula into the Link to Another Record to get the result via lookup.