Chapter 5 conceptual
As shown in the Grades worksheet, final grades are determined using the following grading scheme: • Students earning over 900 points receive an A. • Students earning less than 900 points but at least 800 points receive a B. • Students earning less than 800 points but at least 700 points receive a C. • Students earning less than 700 points but at least 600 points receive a D. •Students earning less than 600 points receive an F. Write a formula in cell C3 in the Scores worksheet that determines the final grade for the first student based on the grading scheme. Use the appropriate Reference and Lookup function and write the formula so that it can be copied down the column.
HLOOKUP(B2,Grades!A$5:E$7,2,TRUE)
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?
#N/A
The delivery charges used in the delivery worksheet are as follows: a. For orders under $10, there is a $6 fee. b. For orders at least $10 but less than $40 there is a &.50 fee. c. For orders over $40 the delivery is free.
...
Write a formula to choose the name of the third day of the week from the list starting with Sunday, Monday, Tuesday....Saturday.
=CHOOSE(3,"Sunday","Monday","Tuesday","Wednesday", "Thursday","Friday","Saturday")
Write a formula in cell D13 in the pricing worksheet to look up the correct delivery cost using the lookup table you created in question 17. Write the formula so that it works when copied down the column
=VLOOKUP(A2,$A3:$B3,3,True)
Reference and Lookup functions may not contain nested functions as arguments.
False
The default range_lookup type for the VLOOKUP and HLOOKUP fu nctions is FALSE.
False
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.
False
The lookup_value of a HLOOKUP function can be a contiguous cell range.
False
The result_vector of a LOOKUP function must be sorted in ascending order.
False
The row and column arguments in the INDEX function can be num eric values, Boolean values, or text.
False
What function returns the relative position of an item from a list?
MATCH
Write a formula in cell C13 in the Pricing worksheet to determine the total cost of making copies for this order (275 copies). Write the formula so that it works when copied into cells C14:C15.
NPER
Explain the difference between the lookup table in cells A3:E4 of the Grades worksheet and the lookup table in cells A5:E7 in the same worksheet.
The lookup table in A3:E4 of the Grades worksheet is sorted in descending order, and the lookup table in cells A5:E7 is sorted in ascending order, which allows you to use the HLOOKUP function with 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 rows can be varied?
To find a value in a two-‐dimensional table, use the INDEX function
Excel matches the lookup_value "tom" with the entry "tom" in a l ookup table.
True
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.
True
The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the value in cell D3.
True
The formula =MATCH(40,{10,20,40,50},0) returns the value 3.
True
What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function?
VLOOKUP searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. HLOOKUP is similar but it searches a horizontal lookup table. The LOOKUP function looks for a matching value in a range of cells (vertical or horizontal) that you specify and returns a value. LOOKUP is limited to a single row or column.
