Practice Module 4 – Excel (Part 2)
You are currently viewing a sample of the Cram Kit. Click here to unlock everything.
Time limit: 0
Practice Exercise Summary
0 of 18 Questions completed
Questions:
Information
You have already completed the practice exercise before. Hence you can not start it again.
Practice Exercise is loading…
You must sign in or sign up to start the practice exercise.
You must first complete the following:
Results
Practice Exercise complete. Results are being recorded.
Results
0 of 18 Questions answered correctly
Your time:
Time has elapsed
You have reached 0 of 0 point(s), (0)
Earned Point(s): 0 of 0, (0)
0 Essay(s) Pending (Possible Point(s): 0)
Categories
- Not categorized 0%
- Review / Skip
- Answered
- Correct
- Incorrect
-
Question 1 of 18
1. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 2 of 18
2. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 3 of 18
3. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 4 of 18
4. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 5 of 18
5. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 6 of 18
6. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 7 of 18
7. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 8 of 18
8. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 9 of 18
9. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 10 of 18
10. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 11 of 18
11. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 12 of 18
12. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 13 of 18
13. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 14 of 18
14. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 15 of 18
15. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 16 of 18
16. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 17 of 18
17. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect -
Question 18 of 18
18. Question
- Go to the Soccer Team worksheet.
- In E4, utilize a Nested IF formula to calculate the bonus for each soccer player. Make sure to use absolute cell referencing. The following details how the bonus is calculated, and can additionally be found in the table within A25:C30.
- If their goals are less than 10 (amount in B27), then apply a 0% bonus to their salary. They don’t deserve it. #messi #ronaldo
- If their goals are greater than or equal to 10 and less than 15 (B28), then apply a 10% bonus to their salary.
- If their goals are greater than or equal to 15 and less than 20 (B29), then apply a 12.5% bonus to their salary.
- If their goals are 20 or higher, then apply a 15% bonus to their salary.
- Since we are multiplying these salaries by a percentage, we need to make sure to ROUND the result. In E4, round the result to 2 decimals. Then copy the formula through E23.
- Enter a formula in F4:F23 that calculates the Salary after Bonus.
- Change the bonus for players with 20 or more goals to 17.5%.
- Go to the Clothing Orders worksheet.
- In this worksheet, we are determining whether or not each customer is eligible for a free sticker or a free coupon. These special promotions have the following requirements:
- Free Sticker: the customer orders a Sweatshirt or Long Sleeve Tee, and they also get a Hat.
- Free Coupon: the customer orders a T-Shirt and Bracelet, or a Sweatshirt and Hat, or a Sweatshirt and Bracelet.
- In cell D3, use an IF function with an AND and OR to determine if the customer gets the free sticker. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from D3 to D12.
- In cell E3, use an IF function with an AND and OR to determine if the customer gets the free coupon. If they’re eligible, display “ELIGIBLE”. If they’re not, display “-“. Copy the formula from E3 to E12.
- Save the file.
- Go to the Member Status worksheet.
- Fill in the following values to the Dues Table (H2:I7):
- For 0-1 years in the fraternity, dues are $750.
- For 1-2 years in the fraternity, dues are $725.
- For 2-3 years in the fraternity, dues are $500.
- For more than 3 years in the fraternity, dues are $275.
- Utilize a lookup function in cell E4 to determine the dues owed by brother Bennett based on his Years in Chapter value. Then, modify this value so that no dues are owed if they live in an Annex House.
- Copy this formula down through E51.
- In the SUMMARY table in H10:J22, perform the following steps:
- In I13, compute the total number of brothers who are living In House. Then, copy this formula into I14 to compute the total number of brothers living in an Annex House.
- In I16, count the total number of brothers that have been in the chapter for 0.5 years. Copy this formula down through I22.
- In J16, compute the average number of events attended for brothers who have been in the chapter for 0.5 years. Copy this formula down through J22. Format to have 1 decimal places.
- Utilize a lookup function in cell F4 to determine the Allowed Guests at parties for each of the brothers. This is determined based on their Events Attended value and the Allowed Guests Table.
- Copy this formula down through F51.
- Save your file.
CorrectIncorrect