Multiple IF(FIND(OR statements

[apisano]

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?

v1

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",

      ))))

v2

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")

      ))

[pressGO_design]

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,

……

[pressGO_design]

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

[apisano]

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"

   )))))

[pressGO_design]

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.

Parameters: 

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"))))

[pressGO_design · Accepted solution]

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"))))

[apisano]

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!

[pressGO_design]

Thanks! And "oops" is the unsung hero of all of my formulas. Sometimes, when I'm super-frustrated, I replace it with curse words. 😂🤣