Question

In cell L2, enter a formula using the IF and AND functions, as well as structured references to determine if Adam Moriarty can be a bunk leader. a. The logical test in the IF function should determine if the staff member’s Service Years is greater than 4 AND the staff member’s Leadership Training status is "Yes". Remember to use a structured reference to the Service Years and the Leadership Training columns. b. The function should return the text Yes if a staff member meets both of those criteria. c. The function should return the text No if a staff member meets none or only one of those criteria.

Answer 1

Enter the following formula in L2

=IF(AND([Service Year]>4,[Leadership Training]="Yes"),"Yes","No")

Step-by-step explanation:

Analyzing the formula:

`= \to` Begin the formula with an equal to sign

IF
`\to` This indicates that the formula uses an "IF" function

AND
`\to` This indicates that there are at least 2 conditions in the function; all of which must be true for the function to return true.

The conditions in the formula are:

[Service Year]>4; and [Leadership Training]="Yes"

Notice that the column names (service year and leadership training) are called and not the cell names (e.g. K2); this is what is referred to as structured referencing.

Having said that, both conditions must be true for the function to return true (because we use the AND clause to join both functions)

The possible results in the formula are:

"Yes","No"

If both conditions are true, then the value of L2 will be "Yes"

If otherwise, then the value of L2 will be "No"