Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Multiple IF(FIND(OR statements

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
1906
7

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 14, 2022 01:38 PM

Hi everyone,

I've got a set of criteria to score the program length that states the following: "A faculty-led program for any term is 3 points. An interterm program longer than 3 weeks is 3 points. A summer program longer than 6 weeks is 3 points. An interterm program longer than 11 days, but less than three weeks is 2 points. A summer program longer than 21 days, but shorter than 41 days is 2 points. An interterm program shorter than 11 days is 1 point. A summer program shorter than 21 days is 1 point. In translating that to a nested combination of IF(FIND(OR arguments, my syntax got off, or, alternatively, resulted in all points being wiped out or everyone being assigned 3 points. I've included a few versions of my formula text below. Should I be using a FIND( statement for each IF( statement?

IF(

Find(

"interterm",{term}),

OR

({Smith faculty-led?}="Yes",

{Program Length}>=20),

"3",

IF({Program Length}>11,"2",

IF({Program Length}<11,"1"),

Find(

"summer",{term}),

OR

({Smith faculty-led?}="Yes",

{Program Length}>=41),

"3",

IF({Program Length}>21,"2",

IF({Program Length}<21,"1",

))))

IF(

Find(

"interterm",{term},

OR({Smith faculty-led?}="Yes",

{Program Length}>=20,"3"),

IF({Program Length}>11,"2"),

IF({Program Length}<11,"1")),

Find(

"summer",{term},

OR({Smith faculty-led?}="Yes",

{Program Length}>=41,"3"),

IF({Program Length}>21,"2"),

IF({Program Length}<21,"1")

))

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 20, 2022 03:11 PM

Good heavens. That gin & tonic is getting to me... Use this formula instead of the one above.

IF({Faculty-Led?}="Yes", 3, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}>21), AND(FIND("Summer", Term), {Program Length (Days)}>42)), 3, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}>11, {Program Length (Days)}<21), AND(FIND("Summer", Term), {Program Length (Days)}>21, {Program Length (Days)}<41)),2, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}<11), AND(FIND("Summer", Term), {Program Length (Days)}<21)), 1,"Oops"))))

Reply

7 Replies 7

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 14, 2022 04:36 PM

The FIND statement doesn’t hold to subsequent IFs, so - yes, you need a FIND for each IF. But you can do it without the FINDs by having the first IF be faculty-led=3 and then using a series of IF(AND instead of IF(OR. So….

IF(Smith faculty-led?}="Yes", 3,

IF(AND({term}=“interterm”, Program Length}>=21), 3,

IF(AND({term}=“interterm”, {Program Length}<11), 2,

IF(AND({term}=“interterm”, Program Length}>11), 1,

……

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 14, 2022 06:02 PM

Addendum: you can do OR(AND for the 3. 2, 1 points if you want to.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 20, 2022 08:46 AM

Hi, thanks for the reply. That doesn't quite seem to have done it. See, I think part of the issue is that the terms are specific to the year, but we're trying to future-proof this a bit. For example, we have Interterm 2023, and next year, there will be a "Term" value called Interterm 2024. Because of that, I added FIND( to each of the lines, but it's actually just not saving the formula now, even though Airtable says the field has been updated. If it helps, here's a link to a shared view of our table. It reverts to the simpler version here:

IF(

FIND("interterm",{Term}),

OR({Smith faculty-led?}="Yes",

{Program Length}>=20),

"3")

__New with find formulas__

IF(

FIND("interterm",{Term}),

OR({Smith faculty-led?}="Yes",

{Program Length}>=20),

"3",

FIND("interterm",{Term}),

IF({Program Length}>11,

"2",

FIND("interterm",{Term}),

if({Program Length}<11,

"1",

FIND("summer",{Term}),

OR({Smith faculty-led?}="Yes",

{Program Length}>=41),

"3",

FIND("summer",{Term}),

if({Program Length}>21,

"2",

FIND("summer",{Term}),

if({program length}<21,

"1"

)))))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 20, 2022 03:08 PM

I couldn't find the "Program Length" field in the table you linked to, so I created a mini-table of my own that just has the relevant fields in it. There's a form that you can use to add new records so you can test the formula.

Faculty-Led = 3, *overrides everything else*

Interterm >21 days OR Summer >42 = 3

Interterm >11 and <21 OR Summer >21 and <41 = 2

Interterm <11 OR Summer <21 = 1

There are some errors in the numbers.... If Summer is 41 or 42 days, it throws an error, as it does when Interterm is 21 or 11. I'm going off of your parameters in your original post. (I think I got them right, but it's late and might or might not be enjoying a gin & tonic.)

Here's the parsed formula:

IF({Faculty-Led?}, 3,

IF(OR(

AND(FIND("Interterm", Term), {Program Length (Days)}>21),

AND(FIND("Summer", Term), {Program Length (Days)}>42)

), 3,

IF(OR(

AND(FIND("Interterm", Term), {Program Length (Days)}>11, {Program Length (Days)}<21),

AND(FIND("Summer", Term), {Program Length (Days)}>21, {Program Length (Days)}<41)

), 2,

IF(OR(

AND(FIND("Interterm", Term), {Program Length (Days)}<11),

AND(FIND("Summer", Term), {Program Length (Days)}<21)

), 1,

"Oops"))))

Here's the whole shebang as it looks in Airtable (because we all know how Airtable feels about line breaks in its formulas):

IF({Faculty-Led?}, 3, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}>21), AND(FIND("Summer", Term), {Program Length (Days)}>42)), 3, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}>11, {Program Length (Days)}<21), AND(FIND("Summer", Term), {Program Length (Days)}>21, {Program Length (Days)}<41)),2, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}<11), AND(FIND("Summer", Term), {Program Length (Days)}<21)), 1,"Oops"))))

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 20, 2022 03:11 PM

Good heavens. That gin & tonic is getting to me... Use this formula instead of the one above.

IF({Faculty-Led?}="Yes", 3, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}>21), AND(FIND("Summer", Term), {Program Length (Days)}>42)), 3, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}>11, {Program Length (Days)}<21), AND(FIND("Summer", Term), {Program Length (Days)}>21, {Program Length (Days)}<41)),2, IF(OR(AND(FIND("Interterm", Term), {Program Length (Days)}<11), AND(FIND("Summer", Term), {Program Length (Days)}<21)), 1,"Oops"))))

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2022 07:03 AM

This appears to have done it with some minor edits like column names and substituting ">" for ">=" on the first program length formula.

I like the "oops" touch at the end, that was helpful! Enjoy your G&Ts!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2022 08:28 AM