1.1 Use the VLOOKUP function in cell F3 to determine the number of days it will take to ship order 1. Use the distance in cell E3 to lookup the appropriate value on the "Delivery Information" reference table (range K4:M9). Use relative and absolute references appropriately.

1.2 Copy your formula in cell F3 and paste it down to complete the "# Days" column of the table.

1.3 Use the VLOOKUP function in cell G3 to calculate the delivery price for order 1.

1. The delivery price can be referenced on the "Delivery Information" lookup table.
2. Use appropriate relative and absolute cell references.

1.4 Copy your formula in cell G3 and paste it down to complete the "Delivery" column of the table.

1.5 Use the HLOOKUP function in cell H3 to calculate the product pricing based on the color and quantity of the billboards ordered.

1. Product pricing can be referenced on the "Product Pricing" lookup table (range P3:Q12).
2. Use appropriate relative and absolute cell references.
3. Note the third argument within the HLOOKUP function is the row_index_num. It specifies how many rows to go down within the matching column to find the right value to return as the function result. For each Order, the value specified as '#'Shipped in Column D is the desired Row_index_num for that order (i.e., you should refer to the respective Column D cell for the third HLOOKUP argument). The row_index_num argument will be a reference to a cell in column D rather than a fixed number.

1.6 Copy your formula in cell H3 and paste it down to complete the "Price" column of the table.

2: conditional

The athletic department is sponsoring a free throw contest before tonight's game to give away free T-shirts. They have recorded the hair and eye color of each participant. Each participant shoots ten free throws. The hair and eye color groups with the highest average number of free throws made will get a T-shirt.

Complete the summary tables on the Conditional Functions worksheet to determine which groups made the most free throws. Then complete the table to determine which students get a shirt. How many students will get a T-shirt?

2.1 Use the COUNTIF function in cell I4 to determine the number of students with black hair. Be sure to build a formula that can be reused by copying down.

2.2 Copy your function in cell I4 and paste it down to complete the "Count" column of the "Hair Color Summary" table.

2.3 Use the COUNTIF function in cell I11 to determine the number of students with brown eyes. Be sure to build a formula that can be reused by copying down.

2.4 Copy your formula in cell H10 and paste it down to complete the "Rate" column of the table.

2.5 Use the SUMIF function in cell J4 to determine the total number of free throws made by students with black hair. Be sure to build a formula that can be reused by copying down.

2.6Copy your function in cell J4 and paste it down to complete the "Sum" column of the "Hair Color Summary" table.

2.7Use the SUMIF function in cell J11 to determine the total number of free throws made by students with brown eyes. Be sure to build a formula that can be reused by copying down.

2.8 Copy your function in cell J11 and paste it down to complete the "Sum" column of the "Eye Color Summary" table.

2.9 Use the AVERAGEIF function in cell K4 to determine the average number of free throws made by students with black hair. Be sure to build a formula that can be reused by copying down.

2.10 Copy your function in cell K4 and paste it down to complete the "Average" column of the "Hair Color Summary" table.

2.11 Use the AVERAGEIF function in cell K11 to determine the average number of free throws made by students with brown eyes. Be sure to build a formula that can be reused by copying down.

2.12 Copy your function in cell K11 and paste it down to complete the "Average" column of the "Eye Color Summary" table.

2.13 Use the OR function in cell F3 to determine if Student 1 gets a T-shirt. Students with "Red" as a hair color had the highest average number of free throws made on the "Hair Color Summary" table and students with Eye Color as "Hazel" had the highest average number of free throws made on the "Eye Color Summary" table.

1. Students get a T-shirt if their Hair Color is "Red" or their Eye Color is "Hazel".

2..14 Copy your formula in cell F3 and paste it down to complete the "Get Shirt?" column of the data table.

2.15 Use an appropriate function in cell K16 to calculate the number of T-shirts that will be given away.