Question

8. To be a Bunk Leader, a staff member must have over 4 service years and have completed leadership training. 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.

Answer 1

Answer:

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

Explanation:

To make use of structured references, a table has to be defined (created). Assume that the table definition is as follows;

Staff --- Service Year --- Leadership Training --- Status

In cell L2, type the above formula

Analyzing the formula:

=; An Excel formula must start with an = sign

IF; This signifies  that the formula is an "IF" function

AND; This signifies that there are more than one condition in thee formula and all conditions must be true

[Service Year]>4; The question requires that the formula uses a structured referencing. Structured referencing is such that the columns of a table are called instead of cell names;

Here, reference is being made to [Service Year] because Service Year is a column name and its value must be greater than 4

[Leadership Training]="Yes"; The above analysis is applicable here too because Leadership Training is column name and it must be "Yes" for it to return a true value.

"Yes"; Return Yes if both conditions are true

"No"; Otherwise, return No.