K201 Study guide for exam 3

What is the basic structure of an IF statement in Excel?

=IF(condition, value_if_true, value_if_false)

When would you use the AND function in an IF statement?

Use AND when all conditions must be true. Example: =IF(AND(A1>50, B1>50), 'Pass', 'Fail')

What does the OR function do in an IF statement?

Use OR when only one of the conditions needs to be true. Example: =IF(OR(A1>50, B1>50), 'Pass', 'Fail')

How does a combined IF statement with AND and OR work?

Example: =IF(AND(A1>30, OR(B1>=50, C1>=50)), 'Y', 'N'). This means A must be true and at least one of B or C must also be true.

What are the benefits of using the IFS function?

Use IFS for multiple outcomes, cleaner than nested IFs and it stops at the first TRUE condition.

Describe the XLOOKUP function.

=XLOOKUP(lookup_value, lookup_array, return_array). Use it when you want to find something and return related information.

What is the advantage of using INDEX + MATCH over VLOOKUP?

INDEX + MATCH is more flexible than VLOOKUP and can sometimes be required in exams.

What is a 2D lookup in Excel?

=INDEX(A1:G100, MATCH(value, column, 0), MATCH(header, row, 0). Use it when you need both row and column matches.

What formulas can you use to find the maximum, minimum, average, and total in a range?

=MAX(A1:A100), =MIN(A1:A100), =AVERAGE(A1:A100), =SUM(A1:A100). Use these for finding extremes or totals.

What common errors should you know in Excel?

N/A (Not found), #REF! (Broken reference), #DIV/0! (Dividing by zero).

Outline the steps for building logic in Excel formulas.

1. Write conditions. 2. Ask if all required: use AND; just one needed: use OR. 3. Wrap in IF statement.

What is a nested IF statement? When would you use it?

=IF(A1>=90,'A', IF(A1>=80,'B', IF(A1>=70,'C','F'))). Use when multiple outputs are needed without using IFS.

How do you decide which function to use in Excel?

Use IF for 2 outcomes, IFS for multiple outcomes, AND when all conditions must be true, OR for one condition, XLOOKUP for table search, INDEX + MATCH for complex lookups, and MAX / MIN for highest/lowest.

What are some common test traps to avoid?

Using AND instead of OR, wrong order in IFS, forgetting TRUE default, mixing up row/column in INDEX, using * instead of comma.

What are some final memory tricks for Excel functions?

AND = strict, OR = flexible, IFS = ladder (top to down), INDEX/MATCH = coordinates, XLOOKUP = search + return.