Help

Re: Multiple IF(FIND(OR statements

Solved
Jump to Solution
841 0
cancel
Showing results for 
Search instead for 
Did you mean: 
apisano
5 - Automation Enthusiast
5 - Automation Enthusiast
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")
      ))
1 Solution

Accepted Solutions

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

See Solution in Thread

7 Replies 7

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,

……

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

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

 

 

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

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