CGS 2518 Exam Questions Correct
Verified Answers Current Update (A+
Pass)
TF: The lookup_value of a HLOOKUP function can be a contiguous
cell range. - ANSWER>>False; it has to be a singular number, text, or
reference.
TF: In a VLOOKUP formula with a TRUE lookup type, the first column
of the lookup table referenced must be in ascending order to retrieve
the correct value. - ANSWER>>True
TF: The result_vector of a LOOKUP function must be sorted in
ascending order - ANSWER>>True; because the LOOKUP function
only uses a TRUE lookup type, the column and row containing the
lookup values must be in ascending order.
TF: Reference and Lookup functions may not contain nested
functions as arguments - ANSWER>>False; they may.
TF: The default range_lookup type for the VLOOKUP and HLOOKUP
functions is TRUE - ANSWER>>True
TF: Excel matches the lookup_value "tom" with the entry "TOM" in a
lookup table - ANSWER>>True; formatting doesn't matter
TF: The row and column arguments in the INDEX function can be
numeric values, Boolean values, or text - ANSWER>>False; it has to
be a number or a reference that equals a number because it is
supposed to let the function know which cell to find.
,TF: The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the
value in cell D3 - ANSWER>>True; D3 is in the second row and third
column of the first range in the function.
TF: The formula =AVERAGE(CHOOSE(1,B12:D17,B22:D17)) averages
the value 1 with the values in cells B12 to D17 and B22 to D17. -
ANSWER>>False; the first value in the CHOOSE function indicates
that Excel should choose the first value (B12:D17) from the list of
values, so this formula is actually finding the average of the cells in
B12:D17.
TF: The formula =MATCH(40,{10,20,40,50},0) returns the value 3 -
ANSWER>>True; 40 is the third value in the list in the lookup_array.
What happens when Excel is solving a HLOOKUP formula with a
FALSE range_lookup type and does not find an exact match in the
lookup table? - ANSWER>>The text "#N/A!" is displayed in the cell
What is the difference between the LOOKUP function and the
VLOOKUP or HLOOKUP function? - ANSWER>>A LOOKUP function
looks up the greatest value that does not exceed a specified value
*anywhere* in a table or range. It can retrieve data from a lookup table
with a vertical or horizontal orientation. It also uses only a TRUE
lookup type.
Which Excel function should you use when you want to look up a
value from a two-dimensional table, where both the columns and the
rows can be varied? - ANSWER>>=INDEX()
Write a formula to choose the name of the third day of the week from
the list starting with Sunday, Monday, Tuesday,...Saturday. -
ANSWER>>=CHOOSE(3,"Sunday","Monday","Tuesday","Wednesday"
,"Thursday","Friday","Saturday")
, What function returns the relative position of an item from a list? -
ANSWER>>=MATCH()
Using the Pricing worksheet, write a formula in cell C13 to determine
the total cost of making copies for this order (275). Write the formula
so that it works when copied into cells C14:C15.
Pricing Worksheet
A/B/C/D
1: (A)/Pricing Sheet for Copies
2: (A)/Number of Copies/Price per Copy
3: (A)/1/$0.10
4: (A)/10/0.09
5: (A)/25/0.08
6: (A)/50/0.07
7: (A)/100/0.05
8: (A)/500/0.03
9: (A)/1000/0.02
10:
11:
12: Order #/Number of Copies/Total Cost/Delivery Cost
13: 1/275/C13/D13
14: 2/3/C14/D14
15: 3/2500/C15/D15 -
ANSWER>>=PRODUCT(VLOOKUP(B13,$B$3:$C$9,2,TRUE),B13)
The delivery charges used in the blank Delivery worksheet are as
follows:
For orders under $10, there is a $6 delivery fee
For orders at least $10 but less than $40, there is a $7.50 delivery fee
For orders over $40, delivery is free of charge.
Create a lookup table in the Delivery worksheet, so that you can use a
horizontal function to calculate the delivery cost for each order. -
ANSWER>>Delivery Worksheet
Verified Answers Current Update (A+
Pass)
TF: The lookup_value of a HLOOKUP function can be a contiguous
cell range. - ANSWER>>False; it has to be a singular number, text, or
reference.
TF: In a VLOOKUP formula with a TRUE lookup type, the first column
of the lookup table referenced must be in ascending order to retrieve
the correct value. - ANSWER>>True
TF: The result_vector of a LOOKUP function must be sorted in
ascending order - ANSWER>>True; because the LOOKUP function
only uses a TRUE lookup type, the column and row containing the
lookup values must be in ascending order.
TF: Reference and Lookup functions may not contain nested
functions as arguments - ANSWER>>False; they may.
TF: The default range_lookup type for the VLOOKUP and HLOOKUP
functions is TRUE - ANSWER>>True
TF: Excel matches the lookup_value "tom" with the entry "TOM" in a
lookup table - ANSWER>>True; formatting doesn't matter
TF: The row and column arguments in the INDEX function can be
numeric values, Boolean values, or text - ANSWER>>False; it has to
be a number or a reference that equals a number because it is
supposed to let the function know which cell to find.
,TF: The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the
value in cell D3 - ANSWER>>True; D3 is in the second row and third
column of the first range in the function.
TF: The formula =AVERAGE(CHOOSE(1,B12:D17,B22:D17)) averages
the value 1 with the values in cells B12 to D17 and B22 to D17. -
ANSWER>>False; the first value in the CHOOSE function indicates
that Excel should choose the first value (B12:D17) from the list of
values, so this formula is actually finding the average of the cells in
B12:D17.
TF: The formula =MATCH(40,{10,20,40,50},0) returns the value 3 -
ANSWER>>True; 40 is the third value in the list in the lookup_array.
What happens when Excel is solving a HLOOKUP formula with a
FALSE range_lookup type and does not find an exact match in the
lookup table? - ANSWER>>The text "#N/A!" is displayed in the cell
What is the difference between the LOOKUP function and the
VLOOKUP or HLOOKUP function? - ANSWER>>A LOOKUP function
looks up the greatest value that does not exceed a specified value
*anywhere* in a table or range. It can retrieve data from a lookup table
with a vertical or horizontal orientation. It also uses only a TRUE
lookup type.
Which Excel function should you use when you want to look up a
value from a two-dimensional table, where both the columns and the
rows can be varied? - ANSWER>>=INDEX()
Write a formula to choose the name of the third day of the week from
the list starting with Sunday, Monday, Tuesday,...Saturday. -
ANSWER>>=CHOOSE(3,"Sunday","Monday","Tuesday","Wednesday"
,"Thursday","Friday","Saturday")
, What function returns the relative position of an item from a list? -
ANSWER>>=MATCH()
Using the Pricing worksheet, write a formula in cell C13 to determine
the total cost of making copies for this order (275). Write the formula
so that it works when copied into cells C14:C15.
Pricing Worksheet
A/B/C/D
1: (A)/Pricing Sheet for Copies
2: (A)/Number of Copies/Price per Copy
3: (A)/1/$0.10
4: (A)/10/0.09
5: (A)/25/0.08
6: (A)/50/0.07
7: (A)/100/0.05
8: (A)/500/0.03
9: (A)/1000/0.02
10:
11:
12: Order #/Number of Copies/Total Cost/Delivery Cost
13: 1/275/C13/D13
14: 2/3/C14/D14
15: 3/2500/C15/D15 -
ANSWER>>=PRODUCT(VLOOKUP(B13,$B$3:$C$9,2,TRUE),B13)
The delivery charges used in the blank Delivery worksheet are as
follows:
For orders under $10, there is a $6 delivery fee
For orders at least $10 but less than $40, there is a $7.50 delivery fee
For orders over $40, delivery is free of charge.
Create a lookup table in the Delivery worksheet, so that you can use a
horizontal function to calculate the delivery cost for each order. -
ANSWER>>Delivery Worksheet
