In the provided spreadsheet, you will find the rosters for the Chicago Bears (Bears Player Worksheet) and the Green Bay Packers (Packers Players Worksheet).

1. On a separate worksheet named BearsVsPackers, calculate the average weight, age, and experience of the bears and packers players in their respective columns

2. On a separate worksheet named BearsVsPackers2, calculate the average weight, age, and experience of the bears and packers players by position in their respective columns

3. On a separate worksheet named BearsVsPackers3, calculate the number of players for each position.Note the hybrid players which should include all players which are NOT one of the other positions.

4. On a separate worksheet named BearsVsPackers4, create two random functions to create a win loss ratio for both the bears and the packers.Note: there are 16 games a season (Hint: figure out the wins and subtract to get the losses)

a. In the first, use rand between() between function and In the second, use rand() function (going to need round)

b. using the above functions, create a win/loss percentage

c. Run this simulation 10 times to see the results for the RAND function and record the result in the table below

5. On the bears and the packers spreadsheet, use the rank formula to rank players based on experience.

6.On a separate worksheet named BearsVsPackers5, put the names of the top five players from both team based on experience.

7. Copy the packers worksheet to Packers 2 (Note: you can copy the worksheet or recreate it and then copy it) and answer the following question: How old would Aaron Rodgers need to be to move the average age of the team to 22? Note: you should use goalseek and leave Aaron Rodgers age as the result

Name Acho, Sam Allen, Jared Amos, Adrian Anderson, Jonathan Ball, Alan Bass, David Bellamy, Joshua Bennett, Martellus Black, Qumain Boffeli, Conor Bostic, Jonathan Bushrod, Jermon Callahan, Bryce Carey, Ka'Deem Carter, David Clausen, Jimmy Cruz, A.J. Cutler, Jay Ducasse, Vladimir Dunn, Brandon Dysert, Zac Fabuluje, Tayo Fales, David Ferguson, Ego Forte, Matt Foster, Mason Fuller, Kyle Gafford, Thomas Glenn, Jacoby Goldman, Eddie Gould, Robbie Grasu, Hroniss Houston, Lamarr Hurst, Demontre Jefferson, Anthony Jeffery, Alshon Jenkins, Jarvis Jones, Christian Langford, Jeremy Lawrence, Rashad Lee, Khari Leno, Charles Long, Kyle Mariani, Marc Martin, Sherrod McClellin, Shea McManis, Sherrick Position LB LB FS LB CB LB WR TE CB G LB T CB RB DL QB WR QB G DL QB OT QB DL RB LB CB LS CB DL K C LB CB SS WR DL LB RB WR TE T G WR/KR S LB CB Weight 257 255 218 227 195 260 208 273 190 304 246 315 185 210 300 210 195 220 325 300 221 342 219 309 218 237 194 244 180 332 185 300 270 183 198 216 300 248 208 197 235 302 328 187 200 245 197 Age 26 33 22 23 30 24 26 28 23 24 24 31 23 22 27 27 24 32 27 22 25 24 24 24 29 26 23 32 22 21 33 24 28 24 23 25 27 24 23 23 23 23 26 28 30 26 27 Experience 5 12 0 0 8 3 1 8 0 1 3 9 0 2 3 6 0 10 6 1 2 0 1 2 8 5 2 8 0 0 11 0 6 2 0 4 5 2 0 1 0 2 3 6 5 4 6 College Texas Idaho State Penn State Texas Christian Illinois Missouri Western Louisville Texas A&M East Central (OK) Iowa Florida Towson Rice Arizona UCLA Notre Dame Brown Vanderbilt Massachusetts Louisville Miami (Ohio) Texas Christian San Jose State LSU Tulane Washington Virginia Tech Houston Central Florida Florida State Penn State Oregon Texas Oklahoma UCLA South Carolina Clemson Florida State Michigan State Northwestern Bowie State Boise State Oregon Montana Troy Boise State Northwestern McPhee, Pernell Meredith, Cameron Miller, Zach Mills, Jordan Mitchell, Terrance Montgomery, Will Moore, Tyler O'Donnell, Pat Ola, Michael Pascoe, Bear Porter, Tracy Ratliff, Jeremiah Rodgers, Jacquizz Rolle, Antrel Rosario, Dante Royal, Eddie Sinclair, Gannon Slauson, Matt Sutton, Will Timu, John Umodu, Ify Vereen, Brock Washington, Cornelius Wells, Matthew Williams, Terry Wilson, Marquess Woestmann, Kyle Young, Willie LB WR TE T CB C G P T TE CB DL RB S TE WR TE G DL LB WR S DL LB DL WR LB LB 275 200 240 320 190 304 324 220 312 265 190 303 199 206 242 185 270 320 315 247 215 202 285 215 322 199 260 251 26 22 30 24 23 32 22 24 27 29 29 34 25 32 30 29 22 29 23 23 23 23 25 24 23 22 23 29 5 0 6 3 2 10 0 2 2 7 8 11 5 11 9 8 0 7 2 0 0 2 3 0 0 3 0 6 Mississippi State Illinois State Nebraska-Omaha Louisiana Tech Oregon Virginia Tech Florida Miami (Fla.) Hampton Fresno State Indiana Auburn Oregon State Miami (Fla.) Oregon Virginia Tech Missouri State Nebraska Arizona State Washington Northern Arizona Minnesota Georgia Mississippi State East Carolina Washington State Vanderbilt North Carolina State Name Abbrederis, Jared Adams, Davante Backman, Kennard Bakhtiari, David Banjo, Chris Barclay, Don Barrington, Sam Boyd, Josh Bradford, Carl Bulaga, Bryan Burnett, Morgan Clinton-Dix, Ha Ha Cobb, Randall Crockett, John Crosby, Mason Daniels, Mike Elliott, Jayrone Fanor, Jean Gaston, Bruce Gerhart, Garth Glover-Wright, Tay Goode, Brett Goodson, Demetri Guion, Letroy Gunter, LaDarius Harris, Alonzo Hayward, Casey Henry, Mitchell Hundley, Brett Hyde, Micah Janis, Jeff Jones, Datone Kuhn, John Lacy, Eddie Lang, T.J. Linsley, Corey Masthay, Tim Matthews, Clay Montgomery, Ty Mulumba, Andy Neal, Mike Neal, Rajion Palmer, Nate Pennel, Mike Peppers, Julius Perillo, Justin Perry, Nick Phillips, Andy Pinkard, Larry Quarless, Andrew Position WR WR TE T S T/G LB DT LB T S S WR RB K DT LB S DT C CB LS CB DT CB RB CB TE QB DB WR DE FB RB G C P LB WR LB LB RB LB DT LB TE LB C/G WR TE Weight 195 215 245 310 207 305 240 310 248 314 209 208 192 217 207 310 255 205 310 310 180 255 197 322 201 237 192 252 226 197 219 285 250 234 318 301 200 255 216 260 262 220 248 332 287 250 265 303 196 252 Age 24 22 22 23 25 26 24 26 23 26 26 22 25 23 31 26 23 26 23 26 23 30 26 28 23 22 25 22 22 24 24 25 32 25 27 24 28 29 22 25 28 23 25 24 35 24 25 24 23 26 Experience 2 2 0 3 2 4 3 3 2 6 6 2 5 0 9 4 2 1 1 2 1 8 2 8 0 0 4 0 0 3 2 3 10 3 7 2 6 7 0 3 6 1 3 2 14 2 4 0 0 6 College Wisconsin Fresno State Alabama-Birmingham Colorado SMU West Virginia South Florida Mississippi State Arizona State Iowa Georgia Tech Alabama Kentucky North Dakota State Colorado Iowa Toledo Bethune-Cookman Purdue Arizona State Utah State Arkansas Baylor Florida State Miami Louisiana-Lafayette Vanderbilt Western Kentucky UCLA Iowa Saginaw Valley State UCLA Shippensburg Alabama Eastern Michigan Ohio State Kentucky Southern California Stanford Eastern Michigan Purdue Tennessee Illinois State Colorado State-Pueblo North Carolina Maine Southern California Central Michigan Old Dominion Penn State Raji, B.J. Randall, Damarious Rasco, Jermauria Richardson, Sean Ringo, Christian Ripkowski, Aaron Rodgers, Aaron Rodgers, Richard Rollins, Quinten Rotheram, Matt Ryan, Jake Shields, Sam Sitton, Josh Starks, James Taylor, Lane Thomas, Joe Thornton, Khyri Tolzien, Scott Tretter, JC Vaughters, James Vujnovich, Jeremy Walker, Josh White, Myles White, Ryan Williams, Ed DT CB LB S DT FB QB TE CB G LB CB G RB G LB DT QB C/G LB T G WR CB WR 337 196 252 216 298 246 225 257 195 325 240 184 318 218 324 227 315 213 307 254 300 328 190 197 196 24.98667 29 23 22 25 23 22 31 23 23 23 23 27 29 29 25 24 25 27 24 22 24 24 25 23 24 7 0 0 4 0 0 11 2 0 0 0 6 8 6 3 1 2 5 3 0 1 1 2 1 0 Boston College Arizona State Louisiana State Vanderbilt Louisiana-Lafayette Oklahoma California California Miami (Ohio) Pittsburgh Michigan Miami (Fla.) Central Florida Buffalo Oklahoma State South Carolina State Southern Mississippi Wisconsin Cornell Stanford Louisiana College Middle Tennessee State Louisiana Tech Auburn Fort Hays State Bears Weight Age Experience Packers Position C CB DB DE DT FB G K LB LS P QB RB S T TE WR Weight Bears Packers Age Bears Packers Experience Bears Packers Position Bears C CB DB DE DT FB G K LB LS P QB RB S T TE WR Hybrid Packers RAND Wins Losses Winner WinLoss % Bears Packers Simulations 1 2 3 4 5 6 7 8 9 10 WinLoss % RANDBETWEEN Wins Bears Packers Losses WinLoss % WinLoss % Bears #1 #2 #3 #4 #5 Packers