Formulas

Hi all, i am trying to reproduce an exel formula that calculates the Minimum Sample Size based on some values in Airtable. the formula in excel is like this: =IF(AB2;ROUNDUP((1,644853625*(2I2(1-I2))^0,5+0,8416212327*(I2*(1-I2)+(I2+(I2AC2))(1-I2-(I2AC2)))^0,5)^2/((I2AC2)^2);0)*K2;0,1) Reference: AB2 = value from call (TRUE/FALSE) I2 = 10% (value from cell) AC2 = 20% (value from cell) K2 = 2 (value from sell) I am stuck with the use of “^” (power) any suggestions? Thanks in advance. Regards Maichel

Hi there! I’m looking to calculate the growth rate of attendees for events that I run. Specifically, I’d like to understand how many attendees are first timers. To spell out how this formula could work… I see x variables Attendee Attendee attended event date list Event Event dates I’d like a sum of how many attendees attended this event if it was their first time (had not attended events prior. if ( {Attendee} has NOT(BEFORE({attended event date list}, {Event dates})) This is not really working… One of the problems is that the rollup of events attended is just a list of dates, it’s not running through the formula one by one with the attendees. Any advice on how this could work? I have two linked databases with Attendees and events.

How can I count the number of occurrences of a character in a paragraph? Example: I want to check the number of occurrences of commas in a paragraph: “182-DISPLU6-Profile 3-disneytest04@gmail.com, 161-DISPLU6-Profile 1-disneytest01@gmail.com, 162-DISPLU6-Profile 2-disneytest02@gmail.com” I want the return result is 2

I had a formula that was working until this week and has now broken for some reason. I looked into the basics (did the field names for the formula change, synced fields change, anything like that) but literally nothing about the components of the formula broke, so I’m unsure why it’s now resulting in #ERROR rather than the result. Formula below, it’s honestly incredibly basic. Any ideas for simple things I could be missing? DATETIME_PARSE(ARRAYJOIN({Release Date_static}), ‘MM-DD-YYYY’) Where {Release Date_static} is a lookup of a single line text field with a date in ‘YYYY-MM-DD’ format.

Hi! Anyone have any solutions on how to create a formula that would allow me to input a list of say 10 people and then create all possible pair combinations without creating duplicates? Example: Name 1 Name 2 Name 3 Name 4 Name 5 Then I would want to create 10 groupings where each name is paired with each other at least once without duplicating. I’ve tried using autonumber, but can’t seem to figure out the formula to create this.

Hello everyone, I receive in a single text field this kind of data: +1 * 1+0 * 2+0 * 3+0 * 4+0 * 5+0 * 6+0 * 7+0 * 8+0 * 9+0 * 10 or +1 * 4+1 * 5+0 * 6+0 * 7 This is the number of kilos that needs to be sent in the appropriate package. I wonder how I could retrieve the 1*1 from the first line, or, for the second line, 1 * 4 and 1 * 5 I was thinking about some regex expression but my knowledge is poor in that field. Anyone would have an advice? Thanks in advance

How can I replace decimal number with two decimal places to hourly like number. 2.42 > 2:42h

Hi Trying to get a prepopulated airtable form URL to prefill a certain field, per this link. The problem is that the form is generating a new record, not updating the old one. Do I need to use a separate sheet for the submissions, is this causing the issue? The formula used is CONCATENATE('https://airtable.com/shrxxxxxxxx?’, ‘prefill_StatusC=’,{Record ID}) Record ID = formula field = RECORD_ID() StatusC = Single select field which is also included in the form

Hey guys, So I have a freight-orientated base, where I record all loads driven by particular collaborators (drivers). So that means I have a list of facilities and warehouses where those drivers are delivering particular loads to. So, I would like to give drivers the opportunity to rate those facilities, to write comments for that particular facility/warehouse and choose some predefined options from “Multiple select” fields. Also, I would like drivers to have the option to fill their loading/unloading time for that particular facility/warehouse. And all that I mentioned above I want to show as average numbers in my base, so later on drivers can see average rating for that facility, and their previous ratings. Is that even doable? Thank you

Let’s say I have a multi-select column called “Colors.” And I have a record with the Colors value: Red, Yellow, Blue I’d like to create a new column called “Tags” with the string: “Color: Red, Color: Yellow, Color: Blue” Is it possible to access the column name in a formula?

Getting into formula’s a bit more as a novice lately, but struggling to get this one right. Anyone able to help me out on this one? What I want is to change the Multiple Select Field as per below. small instead of s average instead of a tall instead of t Was thinking in the direction of wrapping the exiting formula in a Switch( ) to change the select options small, average and tall to s, a and t for the the output.

Anyone can help me make a formula to write out the below numbers in a Single Line text field (A) that are styled like below, to full digits in a Formula Field (B)? Field A 1 10 100 1k 10k 100k 1.1k 10.1k 100.1k 1M 1.1M Etc. So far I’ve come up to this formula. But it’s very lacking. SUBSTITUTE( Views, REGEX_EXTRACT({Views}, "\.\d+k"), "00") It outputs 100 when its 1.7k and doesn’t work at all for entries like 1 to 999 and 1M upwards.

I am looking to take an integer and add the number to a start date. I need to have weekends excluded from the date add. Please see the formula I am currently using that isn’t generating the result I need: IF({Workflow}=“Manufacturing”, DATEADD( DATEADD({Start Date}, {Estimated Production Days}, ‘days’), SWITCH( WEEKDAY( DATEADD({Start Date}, {Estimated Production Days}, ‘days’) ), 0, -1, 1, -2, 0 ), ‘days’ ))

I have a formula that groups together transactions within each month. Formula is: DATETIME_FORMAT({Pickup Date}, ‘MMMM’) I need to sort the months in chronological order instead of alphabetical. Is there a concatenation or formula that will return this result?

I have a “Next Year End” date field for each of our clients and then I have fields for each of the 4 quarters to populate dates in various other tables. I am trying to create a formula that will take automatically choose the last day of the month when I use this formula: DATEADD({Next Year End}, 6, “month”) - it basically brings me to October 30th, not October 31st. Does anyone know what I’m missing? I’ve been looking everywhere in here…It doesn’t seem like this should be so difficult unless I’m totally not understanding :slightly_smiling_face: Thanks in advance!

I am reconfiguring my budget spreadsheet and am having trouble with an IF formula. I want to set up the base so that I can select if something is revenue or an expense, enter the dollar amount, and then in the next column over (Debits and Credits), it will automatically add a (-) negative to any item that is marked as an expense. Here is the formula I was using: IF({Trans Type}=“Expense”,{Transaction Amount}*-1)&IF({Trans Type}=“Revenue”,{Transaction Amount}*1) The formula is messing things up somehow because it won’t recognize the output as a number, and therefore will not let me format it as currency. Instead, it says: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date. I want this column to be formatted as currency and show a running total. Can someone help?

Hi, I am creating a client job tracker and would like to autogenerate 4 digit numbers, eg. 0001, 0002. Can someome please help as I can only get a single digit or decimals so far. Thanks Daz

Hi, I have a list of cities and states, and I want to extract only first state initials. How can I do that? Thanks

Hi there, I’m trying to figure out a way to take 2 date inputs (shift start and shift end) to output a duration and I’ve done this using “DATETIME_DIFF({Shift End}, {Shift Start})”. However the system we use for our company relies on daytime hours and night hours being paid differently, so ideally we want the end duration to be split between day and night where the crossover is 7AM/7PM. For example if someone started their shift at 0500 and finished at 2300 they’d work 12 day hours and 6 night hours. Would this be possible to configure within Airtable’s capabilities? Thank you in advance.

I’m trying to use a formula to calculate the number of years between two dates, namely the start and end dates of a job/position – while accounting conditionally for when ether of those fields are blank. Thought myself rather clever with this formula, but sadly it’s not working. Can anyone help me figure out what’s wrong? IF( {Position Start}=BLANK(), "", DATETIME_DIFF( {Position End}, IF( {Position End}=BLANK(), TODAY(), {Position End}), 'years' ) )

I’ve been fighting this and researching long enough. here is what I currently have that doesn’t work: IF({Referral Name}}, “Field is Not Empty”),{Commission}{Referral %},"")* Explained: If Referral Name field is not blank or empty, Calculate Commission field multiplied by Referral % Otherwise, leave return a blank field. Help! :slightly_smiling_face:

Hi Airtable! I am trying to figure out how to create a formula for a string. I would like to remove everything after the first set of numbers, with no spaces: 1234 56.jpeg 1234a 56.jpeg to: 1234 1234a Can someone help me? Formulas are not my strong suit. Mary

Hi! First of all, I am french so I hope you’ll understand my question. :hugs: I’d like to calculate how long has an employee been with the company, so : since entry date (fr: date d’embauche) until now/today. I already have a formula that calculates the number of years (fr: années) and another formula that calculates the number of months (fr: mois) but I’d like to have these informations in one formula. Is that possible ? Thanks !

I thought this would be fairly simple but then it turned out to be a little more complicated, and I’m not sure if this is possible to do without Javascript. I have a work start date, I know how long the work is going to take (in hours) and I want to calculate an estimated End date. But different days of the week has different shift hours fx. Mon-Thurs has 10 hours shifts, Friday 9 hours and Saturday 5 hours. Is it possible to calculate an end date, with these informations? I’ve tried to use play around with WEEKDAY() and DATEADD() but nothing sofar has come close to any solution. Or is this only possible in JavaScript?

Hi there! I have the following categories in my table: Task Due Date, Weeks From Launch (Formula Field) and Launch Date. I want to write a formula in the Weeks From Launch category where if the Task Due Date is blank, then record cell stays blank, otherwise it uses the Launch Date and Task Due Date fields to calculate weeks from launch. My current formula (returning an error message): IF({TASK DUE DATE} = “”, “”, DATETIME_DIFF({TASK DUE DATE}, {LAUNCH DATE (from PROJECT)}, ‘WEEKS’) Any suggestions? Thank you!