Excel Assignment on Logic and Reference Functions
This assignment has a total of 100 points.
Complete the homework placing all answers in the cells indicated.
Do not insert rows or columns. The grader relies on the current worksheet structure.
Boolean
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 below highlights 30 baseball scenarios. Complete the tasks below to determine if the umpire should declare an infield fly.
Task # | Points | Task Description |
1 | 9 | Use the AND function with appropriate arguments in cell H11 to determine if there is a force out at third base. There is a force out at third base if "Runner on 1st" and "Runner on 2nd" are both "Yes". |
2 | 1 | Copy your function in cell H11 and paste it down to complete the "Force @ Third" column of the table. |
3 | 7 | Use the OR function with appropriate arguments in cell I11 to determine if there is a "Fly Ball". There is a "Fly Ball" if a "Catchable Fly Ball [is] Hit to" the "Infield" (cell E11 is "Yes") or "Shallow Outfield" (cell F11 is "Yes"). |
4 | 1 | Copy your function in cell I11 and paste it down to complete the "Fly Ball" column of the table. |
5 | 7 | Use the NOT function in cell J11 to determine if there are "Not 2 Outs". Use the "Outs" column in your determination. |
6 | 1 | Copy your function in cell J11 and paste it down to complete the "Not 2 Outs" column of the table. |
7 | 3 | Use the AND function in cell K11 to determine if all of the conditions are met for an infield fly to be declared. These conditions are: there must be a force out at third (H11 = TRUE), there must be a catchable fly ball it to the infield or shallow outfield (I11 = TRUE), and there must not be 2 outs (J11 = TRUE). |
8 | 1 | Copy your function in cell K11 and paste it down to complete the "Infield Fly" colun of the table. |
Total: | 30 |
IF
Beverly sells donuts at the local bakery. Donuts cost $.50 each unless customers buy a dozen or more. When customers buy at least a dozen they cost $.40 each. Beverly earns a commission based on her sales performance. Because the bakery makes so much money on beverages, she earns 10% commission (based on the total sale) when a customer buys a drink. She also earns commission on her sales. She earns 2% on each sale that totals more than $10. She earns 1% commission on each sale that totals more than $5 (but less than $10). She does not earn commission on each sale that totals $5 or less (though she will earn the beverage commission). Complete the table below to help Beverly calculate her commission based on the 50 customers she helped this morning.
Task # | Points | Task Description |
1 | 5 | Use an IF function in cell E18 to calculate the price to charge per donut for order 1. A customer is charged $.50 per donut unless they buy a dozen or more. In this case, they are charged $.40 a donut. Refer to the appropriate price in cells C11 and C12 for your "value_if_true" and "value_if_false" arguments. Use absolute and relative references when appropriate. |
2 | 1 | Copy your formula in cell E18 and paste it down to complete the "Price/Donut" column of the table. |
3 | 5 | Use an IF function in cell H18 to calculate the commission rate for the total sale for order 1. The commission rate is 2% for all sales that total more than $10 and 1% for all sales that total more than $5. No commission is paid on sales that total $5 or less. Reference the threshold levels (cells F13 and F14) in your logical tests for your IF function and the commission rates (cells G13 and G14) as your "value_if_true" and "value_if_false" arguments. Use appropriate relative and absolute references. |
4 | 1 | Copy your formula in cell H18 and paste it down to complete the "Rate" column of the table. |
5 | 5 | Use an IF function in cell J18 to calculate the commission earned for beverages for order 1. The beverage commission is 10% of the total sale if the customer buys a beverage. If the beverage sale amount (cell D18) is greater than 0 the beverage commission equals the sales total (cell G18) multiplied by the beverage commission rate (cell G12). Otherwise the beverage commission is 0. Use appropriate relative and absolute cell references. |
6 | 1 | Copy your formula in cell J18 and paste it down to complete the "Beverage" commission column of the table. |
7 | 2 | Beverly always treats herself to a donut if her daily commission is more than $40. Use an IF function in cell K13 to determine if she should buy herself a donut. Cell K13 should contain the value "Yes" if her total commission in cell K11 is more than $40. Otherwise cell K13 should contain the value "No" |
Total: | 20 |
Lookup
Task # | Points | Task Description |
1 | 6 | Use the VLOOKUP function in cell F9 to determine the number of days it will take to ship order 1. Use the distance in cell E9 to lookup the appropriate value on the "Delivery Information" reference table (range K10:M15). Make sure to use relative and absolute references appropriately. |
2 | 1 | Copy your formula in cell F9 and paste it down to complete the "# Days" column of the table. |
3 | 5 | Use the VLOOKUP function in cell G9 to calculate the delivery price for order 1. The delivery price can be referenced on the "Delivery Information" lookup table. Use appropriate relative and absolute cell references. |
4 | 1 | Copy your formula in cell G9 and paste it down to complete the "Delivery" column of the table. |
Built-tough Boards sells outside bulletin boards used to display community information on the outside of buildings. They sell blue and red bulletin boards which they will deliver regionally up to 1000 miles. While they offer a discount for purchasing in bulk, they require customers to buy at least two bulletin boards at a time. The table below lists 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.
5 | 6 | Use the HLOOKUP function in cell H9 to calculate the product pricing based on the color and quantity of the billboards ordered. Product pricing can be referenced on the "Product Pricing" lookup table (range O9:Q18). Be sure to use appropriate relative and absolute cell references. |
6 | 1 | Copy your formula in cell H9 and paste it down to complete the "Price" column of the table. |
Total: | 20 |
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 below 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?
Task # | Points | Task Description |
1 | 3 | Use the COUNTIF function in cell I10 to determine the number of students with black hair. Be sure to build a formula can be reused by copying down. |
2 | 1 | Copy your function in cell I10 and paste it down to complete the "Count" column of the "Hair Color Summary" table. |
3 | 3 | Use the COUNTIF function in cell I17 to determine the number of students with brown eyes. Be sure to build a formula can be reused by copying down. |
4 | 1 | Copy your function in cell I17 and paste it down to complete the "Count" column of the "Eye Color Summary" table. |
5 | 3 | Use the SUMIF function in cell J10 to determine the total number of free throws made by students with black hair. Be sure to build a formula can be reused by copying down. |
6 | 1 | Copy your function in cell J10 and paste it down to complete the "Sum" column of the "Hair Color Summary" table. |
7 | 3 | Use the SUMIF function in cell J17 to determine the total number of free throws made by students with brown eyes. Be sure to build a formula can be reused by copying down. |
8 | 1 | Copy your function in cell J17 and paste it down to complete the "Sum" column of the "Eye Color Summary" table. |
9 | 3 | Use the AVERAGEIF function in cell K10 to determine the average number of free throws made by students with black hair. Be sure to build a formula can be reused by copying down. |
10 | 1 | Copy your function in cell K10 and paste it down to complete the "Average" column of the "Hair Color Summary" table. |
11 | 3 | Use the AVERAGEIF function in cell K17 to determine the average number of free throws made by students with brown eyes. Be sure to build a formula can be reused by copying down. |
12 | 1 | Copy your function in cell K17 and paste it down to complete the "Average" column of the "Eye Color Summary" table. |
13 | 3 | Students with "Red" hair had the highest average number of free throws made on the "Hair Color Summary" table and students with "Hazel" eyes had the highest average number of free throws made on the "Eye Color Summary" table. Use the OR function in cell F9 to determine if Student 1 gets a t-shirt. Students get a t-shirt if they have either "Red" hair or "Hazel" eyes. Design your function to return TRUE if an individual student gets a t-shirt and FALSE if they do not. |
14 | 1 | Copy your formula in cell F9 and paste it down to complete the "Get Shirt?" column of the data table. |
15 | 2 | Use an appropriate function or formula in cell K22 to calculate the number of t-shirts that will be given away. |
Total: | 30 |
