Question 1(3 points)

Saved

If cell c5 has this formula "=$K2" and you copy the cell to d6, cell d6 will have this formula "=$K3".

Question 1 options:

1) True2) False

Question 2(3 points)

Saved

A named range can be created from selection.

Question 2 options:

1) True2) False

Question 3(3 points)

Saved

Ctrl+Shift+Esc is the shortcut key to make an array formula in desktop version of Excel.

Question 3 options:

1) True2) False

Question 4(3 points)

Saved

The following statement gives you the 3rd largest value returned by the INDEX function.

=LARGE(INDEX(F7:M14, 8, 0), 3)

Question 4 options:

1) True2) False

Question 5(3 points)

Saved

In Office 365 version of Excel (i.e., web version), you will receive a #SPILL! error if a formula returns multiple values, AND there is not enough space for Excel to show all returned values.

=INDEX(distances, 8, 0)

Question 5 options:

1) True2) False

Question 6(3 points)

The following statement counts salaries between 50000 and 100000.

=CountIFs(Salary, ">50000", "<100000")

Question 6 options:

1) True2) False

Question 7(3 points)

COUNTIF does not let you defined more than one criteria.

Question 7 options:

1) True2) False

Question 8(3 points)

It is possible to use wildcards in the criteria part of COUNTIF.

Question 8 options:

1) True2) False

Question 9(3 points)

The criteria ranges in COUNTIFS can each reference a different range (e.g., B5:B10, A1:A6, etc.).

Question 9 options:

1) True2) False

Question 10(3 points)

Cell reference and named range are among the possible values that can go into the first part of INDIRECT function.

Question 10 options:

1) True2) False

Question 11(3 points)

If an OFFSET function returns multiple values, you can use an aggregate function to turn those values into a single value.

Question 11 options:

1) True2) False

Question 12(3 points)

The following statements all return multiple values back.

=OFFSET(B5, 3, 5, 3, 7)

=OFFSET(B5:D10, 3, 5)

=OFFSET(H25, -4, 6, 5, 3)

Question 12 options:

1) True2) False

Question 13(3 points)

The first column of the table array for VLOOKUP is analogous to a lookup vector that does not need to be sorted.

Question 13 options:

1) True2) False

Question 14(3 points)

A lookup vector is data with multiple columns and rows from which the lookup value is searched.

Question 14 options:

1) True2) False

Question 15(3 points)

LOOKUP( ) does not offer any capability to perform only exact match.

Question 15 options:

1) True2) False

Question 16(3 points)

xlookup can be configure to show a custom error message when lookup fails.

Question 16 options:

1) True2) False

Question 17(3 points)

When using MATCH and INDEX together to perform lookups, the INDEX function should be defined inside the MATCH function.

Question 17 options:

1) True2) False

Question 18(3 points)

If you wish to search for something that ends with "food", you could do the following in the MATCH function.

=match("food", B4:B20, 0)

Question 18 options:

1) True2) False

Question 19(3 points)

MATCH-INDEX allows you to work with bigger data more efficiently than VLOOKUP.

Question 19 options:

1) True2) False

Question 20(3 points)

It is possible to use wildcards in the lookup value for the MATCH function.

Question 20 options:

1) True2) False