CS&E 1111/1112 pre Lab 5: Nested if’s
Purpose:
The purpose of this lab is to use Nested If’s to help create spreadsheets that enable decision making. This lab will also reinforce the use of Excel to incorporate multiple worksheets in a workbook and utilize tools to help manage large worksheets such as freezing panes and splitting the screen.
Lab 5: Pre-Lab Assignment –complete before coming to lab:
Part 1 - SIMnet
Go to Carmen then to Modules then to McGraw Hill Campus then to SIMnet for your assignment for
Prelab 5.
Part 2 – nested if– soccer players evaluation problem
As the new manager of Chelsea Football Club your first priority is to evaluate each player and distribute the season end bonuses according to your evaluation of players’ quality in the last season. Your predecessor has already setup a workbook named Soccer.xlsx and your assignment is to complete this worksheet as directed.
· Open the starting file Soccer.xlsx from Carmen and save to your personal directory lab 5 folder.
· Write a formula to be put in cell D2, which can be copied down the column, to determine this player’s squad status. A player is considered a regular member if they play at least 25 games. Players who have appeared in less than 25 games are considered rotational players only. The formula should return either the text Regular or Rotation, to indicate the player’s squad status. Copy the formula down the column.
· Write a formula in cell E2, which can be copied down the column, to determine the player’s performance during this last season. The formula should return one of the following:
o If a player had less than 6 goals last season, they are given a performance of Poor.
o If a player scored at least 6 goals but less than 8, they are given a performance rating of Satisfactory.
o A player with 8 goals or more will receive a performance rating of Excellent.
· An alternate method of rating is to assign a numerical score to each player based on the player’s status, number of goals and number of games played. Write a formula in cell F2, which can be copied down the column, to determine the player’s quality score based on the following criteria:
o A regular player’s quality score is their number of goals multiplied by 1.5 plus the number of games played.
o A rotation player’s quality score is their the number of goals multiplied by 2 plus the number of games played.
· You have decided to award end of season bonuses based on a player’s Quality Score. Players with a score of less than 20 receive no bonus. Players with at least a score of 20 but less than 40 will receive a $10,000 bonus. Players with a score of at least 40 but less than 50 will receive a $20,000 bonus and players with at least a score of 50 will receive a $50,000 bonus. Write a formula in cell G2 to determine this player’s bonus. Save your completed workbook as Soccer.xlsx.
Part 3 – using multiple worksheets – top movie picks
To celebrate the anniversary of the Hollywood-Movie Fan Club, your club has asked you to give a presentation analyzing the top 250 movies recommended by the International Movie Database (IMDB). For your convenience IMDB offers a downloadable workbook named Movies.xlsx. Analyze the data provided as described in the following steps.
· Open the starting file Movies.xlsx from Carmen and save to your personal directory lab 5 folder.
· Select the Sheet1 worksheet, if not already visible. Use the Freeze Panes tool to make the titles always show on left and headings show in row 1.
· Write a formula in cell F2, which can be copied down the column, to determine (True/False) if the movie is most memorable. A movie is identified as most memorable, if it satisfies one of the following criteria:
o A rating of higher than 8.5.
o Produced after 1970.
· Write a formula in cell G2, which can be copied down the column, to determine the financial value of this movie based on the following criteria:
o If a movie is produced after 1990, its financial value is calculated as the number of votes multiplied by 10.
o If a movie is produced before or in 1990, its financial value is calculated as the number of votes mutiplied by 2.
· Write a formula in cell B253 to calculate the average rating of all the movies rounded result to the nearest tenth.
· Write a formula in cell D254 to calculate the most number of votes received by any single movie.
· Write a formula in cell E255 to calculate the year in which the oldest movie was produced.
· Rename your worksheet as data and give it a blue tab.
· Create a new worksheet named analysis with a red tab.
· Arrange a new window so that both the data and analysis worksheets are visible on your monitor.
· On the analysis worksheet setup a table as seen in Figure 1 to summarize the number of movies and financial value by memorable category.
o Include a heading above this table named Summary that is merged and centered above the table and highlighted in yellow.
o Write a formula in the #movies column and TRUE row to determine the number of memorable (TRUE) movies. Copy the formula down to determine the number of non-memorable (FALSE) movies.
o Write a formula in the Total Financial Value column and TRUE row to determine the total financial value of all memorable movies. Copy the formula down to determine the total financial value of all non-memorable movies.
· Save your completed workbook as Movies.xlsx. Be sure the panes are still frozen and both worksheets are visible on screen when saving.
Prelab 5 - Zip your files before submitting to Carmen.
· Soccer.xlsx
· Movies.xlsx
Page 2