Issue with Embedded IF Statement

Katy H

I am working on a training tracker and am struggling with a formula that, while long, seems fairly straightforward. The column is a checkbox. And should be marked off with a check if the row is deemed a "required training".

I am having an issue with the first part of the formula (The bolded portion below). Basically, the formula starts out by saying "If the training is a mandatory training, the training is a supervisor specific training, and the employee is a supervisor; mark the checkbox in the required column."

The issue I am having is that with the formula as written it always marks the supervisor training as required. Perhaps I am missing a better way to do this.

=IF(AND([Training Requirements]@row = "Required", [Supervisor Training?]@row = "Yes", [Supervisor?]@row = "Yes"), 1, IF(OR(AND([Training Requirements]@row = "Required", CONTAINS([Primary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Primary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Primary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Secondary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Primary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Tertiary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Secondary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Primary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Secondary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Secondary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Secondary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Tertiary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Tertiary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Primary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Tertiary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Secondary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), AND([Training Requirements]@row = "Required", CONTAINS([Tertiary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Tertiary Department]@row, [Departments Required to Complete Training (HELPER)]@row))), 1, 0))

Jeff Reisman

@Katy H

Getting back to "why is the box always checked for supervisor training?"

=IF(AND([Training Requirements]@row = "Required", [Supervisor Training?]@row = "Yes", [Supervisor?]@row = "Yes"), 1, IF...

The way your IF is written, these are the first three things it considers. So if the training is required, it's supervisor training, and the user is a supervisor, that box is getting checked. The IF stops right there, because the first logical statement was true. Once it finds that the logical statement is true, it applies the true condition, which in this case is "set this checkbox to 1 (checked)". It never even considers the negative condition, which in your case is that huge pile of ANDs 😁

So what other data might be on a row for a supervisor training that would make it so that the box should not be checked? You need to work out all the logic for when a supervisor row should or should not be checked, and account for that in your IF.

Jeff Reisman

@Katy H

First off, let me say "bravo!" That is the longest and most complex formula I've ever seen to check a box. I scanned over it 7 times already and can't begin to make heads or tails of what you're doing, or how you know your issue is with the first IF.

So you say "the formula as written it always marks the supervisor training as required." What are the different variations of criteria you have for supervisor training? I only see two IF statements in this haystack of a formula, with so many criteria in the second IF, it's impossible to judge based on the info you've given why the box would always be checked when it's supervisor training.

Katy H

Jeff,

Haha, it is a bit of a beast of a formula. The first part is the only part that deals with supervisor training, which I imagine is a part of the issue. For context this formula was written before trying to identify supervisor specific training, and honestly may be too much for just a single training (so far) that falls into this category.

The required checkbox is dependent on a few things:

• Location-based required training, aka which facility or facilities does this employee work at (up to three locations possible): Primary Location, Secondary Location, Tertiary Location are the columns that parse this information out.
• Department-based required training, aka which department or departments does this employee work in (up to three departments possible): Primary Department, Secondary Department, Tertiary Location are the columns that parse this out.
• Is the training a "required" training, this information is pulled into a column from a master training list.
• Is the training supervisor specific (from master training list) and is the employee a supervisor (from master employee list)

I feel like the formula is so unwieldy I am struggling to write it properly.

I hope this helps provide context. The rest of the formula works if you completely ignore the supervisor aspect of the formula.

The length of the formula is the fact that any employee could work at any combination of locations and departments (although rare) so I had to account for location A:department A, location A:department B, location A: department C.... etc etc.

Jeff Reisman

Ok, started making some sense of this. Your second IF has an OR statement, where the choices inside the OR are NINE different AND statements, each with THREE criteria, and super-long column names.

The criteria for the first IF, and the criteria for all the ANDs inside the second IF, all include the fact that [Training Requirements]@row must equal "Required" for the logical statement to be true.

So my first question is - do you have ANY rows where [Training Requirements]@row does NOT equal "Required"?

Second question: You say the check box should be checked if this is deemed 'required training'; Doesn't the [Training Requirements]@row being "Required" tell you that?

Third question: Do you have any rows that DON'T meet one of the 9 sets of criteria below? If so, what would that data look like?

Here's a hint also: since all the sets of criteria in the second IF include [Training Requirements]@row = "Required", just list this first inside an opening AND, followed by the OR containing the other sets of criteria. Then you don't have to include it 8 additional times.

IF(AND([Training Requirements]@row = "Required", OR(

AND(CONTAINS([Primary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Primary Department]@row, [Departments Required to Complete Training (HELPER)]@row)),

AND(CONTAINS([Primary Location]@row, [Locations Required to Complete Training (HELPER)]@row), CONTAINS([Secondary Department]@row, [Departments Required to Complete Training (HELPER)]@row)), ... cont'd

Katy H

https://community.smartsheet.com/discussion/comment/327275#Comment_327275

Thank you Jeff. To answer your questions:

1. "do you have ANY rows where [Training Requirements]@row does NOT equal "Required"?" Yes, we have several recommended trainings that if taken the first time will then notify others
2. You say the check box should be checked if this is deemed 'required training'; Doesn't the [Training Requirements]@row being "Required" tell you that? So it is complicated. The formula I am building considers everything in the requirement. The [Training Requirements]@row indicates if the training is required but it may not be required for everyone in the company, it is only required if they work in the department or site that needs that training, for example, forklift certification. This is a "required" training, but only people that work in the warehouse at the facility with forklifts need this training.
3. "Do you have any rows that DON'T meet one of the 9 sets of criteria below? If so, what would that data look like?" The only exception that doesn't meet the 9 sets of criteria, are the supervisor specific trainings.

"Here's a hint also: since all the sets of criteria in the second IF include [Training Requirements]@row = "Required", just list this first inside an opening AND, followed by the OR containing the other sets of criteria. Then you don't have to include it 8 additional times." OH THANK YOU!! I hadn't even considered that. I just added it in after writing the initial formula and often that is what causes my confusion.

Jeff Reisman

@Katy H

Getting back to "why is the box always checked for supervisor training?"

=IF(AND([Training Requirements]@row = "Required", [Supervisor Training?]@row = "Yes", [Supervisor?]@row = "Yes"), 1, IF...

The way your IF is written, these are the first three things it considers. So if the training is required, it's supervisor training, and the user is a supervisor, that box is getting checked. The IF stops right there, because the first logical statement was true. Once it finds that the logical statement is true, it applies the true condition, which in this case is "set this checkbox to 1 (checked)". It never even considers the negative condition, which in your case is that huge pile of ANDs 😁

So what other data might be on a row for a supervisor training that would make it so that the box should not be checked? You need to work out all the logic for when a supervisor row should or should not be checked, and account for that in your IF.