Question 1

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below.
-Mark tests the following formula with a FALSE type in the worksheet shown above: =VLOOKUP(D14,A$3:B$8,2,FALSE). The result is ____.

Accepted Answer

The answer of  
Mark is in charge of determining...

Question 2

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount "0" in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.
-Violet now wants to send any orders for more than 30 toys to Warehouse 2. She is looking for the position where the smallest value is greater than 30. She will send the orders starting from that position on to Warehouse 2. The correct formula would be ____.

Accepted Answer

The formula =MATCH(31,A1:E1,-1) finds the position of the smallest value greater than 30 in a descending ordered list.

Question 3

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount "0" in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.
-The Customer Service department tells Violet there is a 25% discount on orders of 500 toys or more. Violet knows there is only one order that large, for 1000 toys, so which of the following formulas does she write to find the position of this order?

Accepted Answer

The correct formula to find the position of the order for 1000 toys in a range A1:E1 is =MATCH(1000,A1:E1,0). This formula uses the MATCH function to search for the exact value of 1000 within the specified range and returns its position. The third argument, 0, specifies that it should look for an exact match.

Question 4

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount "0" in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.
-Violet wants to send any orders for 30 toys or fewer to Warehouse 1 to ship out. To do this, she locates the position in the chart where the greatest value does not exceed 30. The formula is ____.

Accepted Answer

The correct formula is =MATCH(30,A1:E1,1) because it searches for the largest value that is less than or equal to 30 in a range that is assumed to be in ascending order. The third argument "1" specifies this type of search.

Question 5

A data list that categorizes values you want to retrieve is called a(n) ____________________ table.

Accepted Answer

The answer of A data list that categorizes values you...

Question 6

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below.
-Mark writes a formula to look up the unit pricing for each order. He starts by writing one for Order #1 and then plans to copy it from E12 to E13:E16. The correct formula would be ____.

Accepted Answer

The correct formula for looking up the unit pricing for each order is VLOOKUP(D12, A$7:B$8, 2, TRUE). 
- D12 is the value we are looking up, which is the quantity of robot toys in each order. 
- A$7:B$8 is the table array where we will match the quantity. 
- 2 is the column index number for the unit price column in the table array. 
- TRUE ensures that we get an approximate match, which means it will return the next smallest quantity if the exact quantity is not found in the table. 
Copying this formula from E12 to E13:E16 will give us the correct unit pricing for each order.

Question 7

When solving a(n) ____________________ formula, Excel looks up a value by testing for a criterion across a row, instead of down a column.

Accepted Answer

The answer of When solving a(n) ____________________ formula, Excel looks...

Question 8

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount "0" in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.
-The shipping manager asks Violet to send any orders for 100 toys over immediately because they are testing a new box that will hold exactly 100 toys. In order to determine the position in the chart for the order with 100 toys, she writes the formula ____.

Accepted Answer

To determine the position in the chart for the order with 100 toys, Violet should use the formula =MATCH(100,A1:E1,0) since it will search for the value "100" in the range A1:E1 and return the position of the first exact match (or an error if there is no match).

Question 9

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below.
-One of Mark's co-workers asks what would happen if he changed the formula to one having a FALSE type. As an example for discussion, Mark writes: =VLOOKUP(Z10,H$1:I10,1,FALSE). How does the FALSE type affect this formula?

Accepted Answer

When the FALSE type is used in the VLOOKUP function, it will only look for an exact match of the lookup_value in the range specified. If no exact match is found, it will return an error. This is different from using the TRUE type, which will return an approximate match if no exact match is found.

Question 10

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below.
-In the formula Mark is discussing with his co-worker, =VLOOKUP(Z10,H$1:I10,1,FALSE), the lookup_value is ____.

Accepted Answer

The lookup_value is the value being searched for in the first column of the lookup table, which in this case is cell Z10.

Question 11

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount "0" in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.
-Violet is not sure if the Sales department is filling in the word "return" in uppercase or lowercase letters. What is the correct way to write "return" in a MATCH formula?

Accepted Answer

The MATCH function is not case sensitive, so it does not matter if "return" is written in uppercase or lowercase letters.

Question 12

One effective way to verify accuracy in a worksheet is to create an order with ____ set(s) of entries, and then systematically vary each entry to test each formula and cell that contains a value.

Accepted Answer

One set of entries is sufficient to verify accuracy in a worksheet because it allows you to test each formula and cell that contains a value.

Question 13

The ____ function is designed to return the relative position (such as 1, 2, or 3) of an item in a list.

Accepted Answer

The MATCH function is designed to return the relative position of an item in a list. It takes two arguments: the value you want to locate and the range that contains the list.

Question 14

Use ____________________ when the first column of the lookup table contains the key data, and the information you want to retrieve is in one of the columns to the right of the key data column.

Accepted Answer

The answer of Use ____________________ when the first column of...

Question 15

When using the CHOOSE function, if the index_num is a fraction, it is truncated to ____ before being used.

Accepted Answer

The index_num in the CHOOSE function refers to the position of the value in the list of values. It must be an integer, which means a whole number without any decimals. If a fraction is used as the index_num, it will be truncated to the nearest integer. For example, if the index_num is 2.7, it will be truncated to 2. Therefore, the best choice for the question is A, an integer.

Question 16

When testing a worksheet, it's best to use numbers like ____.

Accepted Answer

When testing a worksheet, it's best to use numbers that are easy to understand and have a wide range of possibilities. Using numbers like 10, 100, and 1000 allows for easy verification of calculations and ensures that the worksheet can handle large numbers. B and C are too specific and do not provide a wide range of possibilities. D is too random and does not have the same level of significance as A.

Question 17

If you use a match_type of 0 (exact match), you can use the ____ to match any sequence of characters.

Accepted Answer

An asterisk (*) is used as a wildcard character to match any sequence of characters when using a match_type of 0.

Question 18

A lookup table can have only one ____________________ in each cell of its first column.

Accepted Answer

The answer of A lookup table can have only one...

Question 19

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount "0" in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.
-The Sales department has been mistakenly writing the word "return" in the quantity field for orders that are returned. Violet looks at the chart above and can see that the word "return" is not listed this time. However, to check for this word in future orders, Violet writes the formula ____.

Accepted Answer

The formula =MATCH("return",A1:E1,0) is the best choice because it searches for the exact match of the word "return" in the range A1:E1. The "0" at the end of the formula ensures that the exact match is required. Option B is incorrect because "return" needs to be in quotes. Option C is incorrect because the parameters in the formula are in the wrong order. Option D is incorrect because "0:1000" is not a specific range and will not look for "return" in the correct cells.

Question 20

In the VLOOKUP function, if a type ____________________ is specified, an exact or approximate match is returned.

Accepted Answer

The answer of In the VLOOKUP function, if a type...

Quiz 5: Retrieving Data for Computation, Analysis, and Reference

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below. -Mark tests the following formula with a FALSE type in the worksheet shown above: =VLOOKUP(D14,A$3:B$8,2,FALSE) . The result is ____.