Game Comp Att Yds TD Int Row Game Comp Att Yds TD Int 1 37 54 450 2 1 2 24 36 273 4 0 3 19 38 238 1 1 4 28 39 285 0 0 5 21 37 226 0 1 6 30 40 371 4 0 7 31 42 338 1 0 8 21 30 281 0 1 9 25 35 284 3 1 10 19 37 232 0 2 11 22 39 185 2 1 12 19 39 273 1 0 13 21 32 224 1 3 14 34 49 356 3 0 15 27 35 300 2 0 16 29 44 265 2 0 Total 407 626 4581 26 11 Total Rating( ) 65 02 Rating( ) Talbe 1 Table 2 Aspeadsheet of Matt Ryan's game by game results A simulated season for Matt Ryan Note Comp Passes completed Att Passes attempted Yds Yards gained by passing Int Interceptions thrown Rating( ) Percentage of passes completed In this Practice, we will find out the true interval of Ryan's percentage of passes completed (Rating ) in the 2020 season by using the margin of error (p 97 99, 112) Q1 First, fill in the Row column in Table 2 by using the Excel command RANDBETWEEN We want to choose a random integer from 2 to 17 So, if you complete the command correctly, every time you run it, the cell will select a random number from 2 to 17 This is just a number but does not mean a row number However, this random number will help to simulate Ryan's actual stats as the guideline of row number Hint You can use the auto fill function, but all cells will be the same Q2 Next, fill in the simulated Game column (from I2 to I17) in Table 2 by using the Excel command INDIRECT The original Game in Table 1 is from A2 to A17 For using the command INDIRECT , you need a reference letter for a column and a cell location for a random number of a row As the example of the textbook, the command INDIRECT( B $H2) means finding the number on the location of B column and some row based on the random number in cell H2 Hint You can use the auto fill function The cell location will be changed, but the reference letter will be maintained Q3 Please fill in the other columns in Table 2 bu using the Excel command INDIRECT Hint The reference letter in the command should be matched with correct column in Table 1 Q4 Please fill in the total and rating( ) in Table 1 by using the Excel command ( SUM) and formula Q5 Now, we are ready to simulate the 2020 season of Ryan's game by game results To run the simulation, we will use Calculation Options in the top menu, Formulas Calculation Options Automatic, as the screenshot below Every time you click the 'Automatic' button (or you can simply double click any cell and click another cell), you can see all numbers of Table 2 will be calculated again First, please fill in the Simulated Rating row in Table 3 manually by clicking the 'Automatic' button ten times Next, using the numbers in the Simulated Rating row, calculated standard deviation (SD) in cell B47 You must use STDEV S Excel command Lastly, the margin of error is twice the standard deviation Fill in cell B48 by using the Excel formula 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th Simulated Rating ( ) Standard Deviation (SD) of the 10 simulations Margin of Error (SD 2) Table 3 A simulated of Ryan's percentage of passes completed and the margin of error Q6 As you can see in Table 1, the actual value of Ryan's percentage of passes completed (Rating) is 65 02 in the 2020 season Finally, according to the simulations, what is the variability in Ryan's percentage of passes completed (Rating) values by the interval Hint The answer should be an interval (A, B)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 10, 2024

Game Comp Att Yds TD Int Row Game Comp Att Yds TD Int 1 37 54 450 2 1 2 24 36 273 4 0

Game	Comp	Att	Yds	TD	Int		Row	Game	Comp	Att	Yds	TD	Int
1	37	54	450	2	1
2	24	36	273	4	0
3	19	38	238	1	1
4	28	39	285	0	0
5	21	37	226	0	1
6	30	40	371	4	0
7	31	42	338	1	0
8	21	30	281	0	1
9	25	35	284	3	1
10	19	37	232	0	2
11	22	39	185	2	1
12	19	39	273	1	0
13	21	32	224	1	3
14	34	49	356	3	0
15	27	35	300	2	0
16	29	44	265	2	0
Total	407	626	4581	26	11			Total
Rating(%)		65.02						Rating(%)

Talbe 1							Table 2
Aspeadsheet of Matt Ryan's game-by-game results							A simulated season for Matt Ryan

Note. Comp = Passes completed; Att = Passes attempted; Yds = Yards gained by passing; Int = Interceptions thrown
	Rating(%) = Percentage of passes completed

In this Practice, we will find out the true interval of Ryan's percentage of passes completed (Rating %) in the 2020 season by using the margin of error. (p. 97-99, 112)

Q1. First, fill in the "Row" column in Table 2 by using the Excel command "=RANDBETWEEN". We want to choose a random integer from 2 to 17. So, if you complete the command correctly, every time you run it, the cell will select a random number from 2 to 17. This is just a number but does not mean a row number. However, this random number will help to simulate Ryan's actual stats as the guideline of row number. Hint. You can use the auto-fill function, but all cells will be the same.

Q2. Next, fill in the simulated "Game" column (from I2 to I17) in Table 2 by using the Excel command "=INDIRECT". The original "Game" in Table 1 is from A2 to A17. For using the command "=INDIRECT", you need a "reference letter" for a column and a "cell location" for a random number of a row. As the example of the textbook, the command "=INDIRECT("B"&$H2)" means "finding the number on the location of B column and some row based on the random number in cell H2." Hint. You can use the auto-fill function. The "cell location" will be changed, but the "reference letter" will be maintained.

Q3. Please fill in the other columns in Table 2 bu using the Excel command "=INDIRECT". Hint. The "reference letter" in the command should be matched with correct column in Table 1.

Q4. Please fill in the total and rating(%) in Table 1 by using the Excel command (=SUM) and formula.

Q5. Now, we are ready to simulate the 2020 season of Ryan's game-by-game results. To run the simulation, we will use "Calculation Options" in the top menu, Formulas - Calculation Options - Automatic, as the screenshot below. Every time you click the 'Automatic' button (or you can simply double-click any cell and click another cell), you can see all numbers of Table 2 will be calculated again. First, please fill in the "Simulated Rating" row in Table 3 manually by clicking the 'Automatic' button ten times. Next, using the numbers in the "Simulated Rating" row, calculated standard deviation (SD) in cell B47. You must use "=STDEV.S" Excel command. Lastly, the margin of error is twice the standard deviation. Fill in cell B48 by using the Excel formula.







	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th
Simulated Rating (%)
Standard Deviation (SD) of the 10 simulations
Margin of Error (SD*2)

Table 3
A simulated of Ryan's percentage of passes completed and the margin of error

Q6. As you can see in Table 1, the actual value of Ryan's percentage of passes completed (Rating) is 65.02% in the 2020 season. Finally, according to the simulations, what is the variability in Ryan's percentage of passes completed (Rating) values by the interval? Hint. The answer should be an interval (A, B)