solve only f through i . show formulas used .
12-18. (New in Second Edition) Data on the performance of basketball players in a game is given in the worksheet below. This can be downloaded at http://masteringexcel.com/TeamStats.xls Your task is to enter formulas so that the appropriate statistics are calculated automatically in G18 through G29. The values in B5:F16 all are inputs. Changing any of these cells, including names, should result in changes in your answers. The only allowable positions are Guard, Forward, and Center. You may assume there are exactly 12 players on the team. If a person plays 0 minutes, the points per minute for the player should be 0; your formulas need to account for this. In case of a tie among the players, select any of the leaders. If two or more players are tied with most points scored then one of them should be given as the Name of Player with Most Points and another of the tied players should be given as the Name of Player with Second Most Points. You may not Copy and paste the data to somewhere else on your sheet as part of your solution because then changing one of the cells in B5:F16 would not result in changes in the answers. For the same reason you may not sort the data. You may use extra columns with formulas. Use Fill where possible; do not use a formula with eleven nested IFs. Of course, your worksheet should work for any inputs. Try a large variety of input changes but submit the worksheet with the data below. Please do not use any functions not covered so far in the course or book. This is an exercise mainly in COUNTIF, SUMIF, IF, extra columns and cells. You may use VLOOKUP, but it's a bit tricky under these conditions and not necessary. Sign in Shan File Home Insert Page Layout Formulas Data Review 131 3 D E F A G B 1 2 3 Team Statistics 4. Minutes 16 34 30 Points 3 10 6 0 0 17 Number Name 1 Johnson 4 Subowsky 5 Perez 9 Irving 12 O'Neill 15 Borden 24 Jackson 27 Thomas 32 Smith 35 James 41 Sorrento 44 Thompson Position Guard Guard Forward Forward Center Forward Forward Center Guard Forward Guard Guard 0 8 2 09 31 14 26 o 24 6 14 0 3 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 62 b) c) d) e) f) Total Points Number of Guards Number of Players who Scored 10 Points or More Total Points Scored by Guards Highest Points per Minute of Any Player Name of Player with Highest Points per Minute Average Minutes Played by Forwards Name of Player with Most Points Name of Player with Second Most Points Position with the Most Total Points Number of Guards who scored at least 10 Points Total Points Scored by Guards who played at least 10 Minutes h) 1) j) k) 1) Sheet1 Ready
