PROJECT 2 excel answer

  

1. Boolean Functions

An infield fly in baseball is called to prevent the defense from recording an easy double play. When an infield fly occurs, the batter is automatically out once the ball is touched by a fielder or hits the ground, and the baserunners must go back to their bases (though they may tag up if they wish).

An infield fly occurs when the following conditions are met: (1) there is a force out at third base (this means that there are runners on first base and second base), (2) there are not two outs, and (3) the batter hits a catchable fly ball to the infield or the shallow outfield. The table on the Boolean Functions worksheet highlights 30 baseball scenarios.

Complete the tasks to determine if the umpire should declare an infield fly.

1.1

Use the AND function with appropriate arguments in cell H4 to determine if there is a force out at third base.

a. There is a force out at third base if “Runner on 1st” and “Runner on 2nd” are both “Yes”.

1.2

Copy your function in cell H4 and paste it down to complete the “Force at Third” column of the table.

1.3

Use the OR function with appropriate arguments in cell I4 to determine if there is a “Fly Ball”.

a. There is a “Fly Ball” if a “Catchable Fly Ball is Hit to” the “Infield” (cell E4 is “Yes”) or “Shallow Outfield” (cell F4 is “Yes”).

1.4

Copy your function in cell I4 and paste it down to complete the “Fly Ball” column of the table.

1.5

Use the NOT function in cell J4 to determine if there are “Not 2 Outs”. Use the “Outs” column in your determination.

1.6

Copy your function in cell J4 and paste it down to complete the “Not 2 Outs” column of the table.

1.7

Use the AND function in cell K4 to determine if all of the conditions are met for an infield fly to be declared. These conditions are:

a. There must be a force out at third (the value in H4 is TRUE).

b. There must be a catchable fly ball hit to the infield or shallow outfield (the value in I4 is TRUE).

c. There must not be two outs (the value in J4 is TRUE).

1.8

Copy your function in cell K4 and paste it down to complete the “Infield Fly” column of the table.

IF Function

Beverly sells donuts for $.50 each at the local bakery.

If a customer buys at least a dozen donuts, the cost is reduced $.40 each.

Beverly earns a commission based on the number of donuts she sells to a customer.

· If a sale totals more than $10, she earns 2% on that sale total.

· If a sale totals more than $5 (but is $10 or less), she earns 1% commission on that sale total.

· If a sale totals $5 or less, she does not earn a commission.

Because the bakery makes so much money on beverages, she also earns a beverage commission in addition to her donut commission. Beverly earns a 10% commission on the total sale of any order where a customer buys a beverage.

Complete the table on the IF Function worksheet to help Beverly calculate her commission based on the 50 customers she helped this morning.

2.1

Use an IF function in cell E10 to calculate the price to charge per donut for order 1.

a. Customer are charged $.50 per donut unless they buy a dozen or more. In this case, they are charged $.40 a donut.

b. Refer to the appropriate price in cells C3 and C4 for your “value_if_true” and “value_if_false” arguments.

c. Use absolute and relative references when appropriate.

2.2

Copy your formula in cell E10 and paste it down to complete the “Price/Donut” column of the table.

2.3

Use an IF function in cell H10 to calculate the commission rate for the total sale for order 1.

a. The commission rate is 2% for all sales that total more than $10. It is 1% for all sales that total more than $5, but are less than or equal to $10.

b. No commission is paid on sales that total $5 or less.

c. Reference the threshold levels (cells F5 and F6) in your logical tests for your IF function and the commission rates (cells G5 and G6) as your “value_if_true” and “value_if_false” arguments.

d. Use appropriate relative and absolute references.

2.4

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

2.5

Use an IF function in cell J10 to calculate the commission earned for beverages for order 1.

a. The beverage commission is 10% of the total sale if the customer buys a beverage.

b. If the beverage sale amount (cell D10) is greater than 0, the beverage commission equals the sales total (cell G10) multiplied by the beverage commission rate (cell G4).

c. Otherwise, the beverage commission is 0.

d. Use appropriate relative and absolute cell references.

2.6

Copy your formula in cell J10 and paste it down to complete the “Beverage” commission column of the table.

2.7

Beverly treats herself to a donut if her daily commission is more than her set Donut Threshold.

a. Use an IF function in cell K5 to determine if she should buy herself a donut.

b. Cell K5 should contain the value “Yes” if her total commission in cell K3 is more than her “Donut Threshold”.

c. Otherwise, cell K5 should contain the value “No”.

d. Be sure to reference the “Donut Threshold” value in cell K6 in your formula.

3 Lookup Functions

Built-tough Boards sells outside bulletin boards used to display community information on the outside of buildings. The company sells blue and red bulletin boards, which they will deliver regionally up to 1,000 miles. While Built-tough Boards offers a discount for purchasing in bulk, it requires customers to buy at least two bulletin boards at a time.

The table on the Lookup Functions worksheet shows 25 recent sales. Each order lists the color of the boards ordered, the number to be shipped with the order, and the distance of the delivery in miles.

Complete the table by using the VLOOKUP and HLOOKUP functions in Excel to calculate the number of days it will take for delivery, the delivery price, and the price of the signs for each order.

3.1

Use the VLOOKUP function in cell F3 to determine the number of days it will take to ship order 1.

a. Use the distance in cell E3 to lookup the appropriate value on the “Delivery Information” reference table (range K4:M9).

b. Use relative and absolute references appropriately.

3.2

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

3.3

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

a. The delivery price can be referenced on the “Delivery Information” lookup table.

b. Use appropriate relative and absolute cell references.

3.4

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

3.5

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

a. Product pricing can be referenced on the “Product Pricing” lookup table (range P3:Q12).

b. Use appropriate relative and absolute cell references.

c. 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.

3.6

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

4.Conditional Functions

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?

4.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.

4.2

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

4.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.

4.4

Copy your function in cell I11 and paste it down to complete the “Count” column of the “Eye Color Summary” table.

4.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.

4.6

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

4.7

Use 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.

4.8

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

4.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.

4.10

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

4.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.

4.12

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

4.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.

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

4.14

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

4.15

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