Question

1 Approved Answer

Posted on Sep 10, 2024

Spend time studying the NFL scoring data set, so that you are comfortable with the content and format of the information in each spreadsheet column.

Modify the attached spreadsheet by building formulas to answer the questions below. You will only need to use the IF, SUMIF, COUNTIF and COUNTIFS functions along with the math operators (+ - * /) and the comparison operators ( = etc.) to answer these questions.

Place your formulas in cells I44858 to I44864 of the spreadsheet. The formulas are not difficult but require some thought to make sure that you are constructing your criteria to count the correct records. Answer each question with one formula, sometimes by combining or nesting functions.

B. Answer these questions in cells I44858 to I44864:

1. How many plays were run in the "red zone" (i.e the yard line was 20 or less)?

2. How many offensive plays were run by the NY Giants (NYG)?

3. How many offensive plays were run by the NY Giants (NYG) in the red zone?

4. What percentage of the NY Giants' offensive plays were run in the red zone?

5. How many offensive plays were run by the NY Giants (NYG) and NY Jets (NYJ) combined?

6. Who ran fewer defensive plays, NYG or NYJ?

7. What were the average yards to go on third down plays that season? (Calculate the total yards to go on all third down plays, then divide by the number of third down plays.)

image text in transcribed

defensive team yardsto go gameld quarter minute down yard line offensive second team SU DAL 51 BAL 13 BAL 32 BAL 28 BAL 23 BAL 23 BAL 19 SF 40 SF 32 SF 10 BAL BAL BAL 25 SF description 40 10:buh merer loue worZO TUI 12 yarus V.VISUM. 28 (5:51) R Rice right tackleto SF 26 for 2 yards N. Bowman 26 (5:131 8 Pierce left tackleto SF 25 for 1 yard IN. Bowman), 25 (4:321/Shotaun), Flacco pass incomplete A Brooks), Penalty on SF-A.Brooks Defensive Offside declined. PENALTY on SF-AI 20 (4:281 Shotgun) / Flacco pass incomplete short right to D.Pitta. 20 (4:231J.Tucker 38 yard field poal is GOOD Center-M.COX Holder-S Koch 20 J.Tucker kicks 55 yards from BLT 35 to end zone Touchback 80 (4:19) (Shotgun) F. Gore right guard to SF 28 for 8 yards (D. Ellerbe; R. Lewis). 72 (3:40) C Kaepernick scrambles right end ran ob at SF 36 for 8 yards In Ellerbe). 54 (3:32)(Shotzun). Kaepernick pass incomplete deep right to V.Davis. 54 (3:25) Shotgun). Kaepernick pass deep middleto M.Crabtreeta BLT 40 for 24 yards (R. lewis C. Graham Pass 19 YACS 40 2:471/Shotgun) Gore left and pushed ob at BLT 7 for 33 yards E. Reed) 7 (2:39) (Shotgun) L.James right guard to BLT 5 for 2 yards (D. Ellerbe; D.Tyson). 5 (2:00) (Shotgun). Kaepernick pass incomplete short right to M. Crabtree (C. Graham). 5 (1:55) Kaepernick pass incomplete short right to M. Crabtree Smith 5 (1:50) (Shotgun). Kaepernick pass incomplete short right to M. Crabtree. 95 (1:45) R. Rice left puard to BLT 7 for 2 yards (N.Hawman; D.Goldson 93 (1:42 Pierce left end to BLT 7 for no pain (A. Hraaks). 93 (:56 V.Leach right suard to BLT for 1 yard(R.McDonald; N. Hawman 92 (0:12Punt formation) S. Koch right and ran ab in End Zone for 8 yards SAFETY C.Gulliver 92 S.Kach kicks 61 yards fram BLT2O to SF 19.T.Ginnta sa far 31 yards (1.Bynes. 39 SF 0 SF PS ZVISUZUS_OMWor 44836 20130203_BALSF 44837 20130203_BALSF 44838 20130203_BALSF 44839 20130203_BALSF 44840 20130203_BAL@SF 44841 20130203_BALSF 4484220130203_BALSF 44843 20130203 BALOOSF 44844 20130203_BAL@SF 44845 20130203_BALSF 44846 20130203_BALSF 44847 20130203_BALOOSF 44848 20130203_BALSF 44849 20130203_BAL@SF 44850 20130203_HALOSF 44851 20130203_HALSF 44852 20130203_HALOSF 44853 20130203_HALOSF 44854 20130203_HALOSF 44855 20130203_BALOOSE 44856 20130203_BALOOSE 44857 44958 44859 44860 44861 44862 44963 44864 44865 44866 44867 2012_nfl_pbp_data BAL 50 SF 46 BAL 42 BAL 56 BAL 12 BAL 12 BAL 12 BAL 11. How many plays were run in the "red zone (.ethe yard line was 20 or less)? 2. How many offensive plays were run by the NY Giants (NYGI? 3. How many offensive plays were run by the NY Giants (NYG) in the red zone? 4. What percentage of the NY Giants' offensive plays were run in the red zone? 5. How many offensive plays were run by the NY Giants INYG) and NY Jets (NY)combined? 6. Wha ran fewer defensive plays, NYG ar NY? 7. What was the average yards to go on third down plays during the season? + defensive team yardsto go gameld quarter minute down yard line offensive second team SU DAL 51 BAL 13 BAL 32 BAL 28 BAL 23 BAL 23 BAL 19 SF 40 SF 32 SF 10 BAL BAL BAL 25 SF description 40 10:buh merer loue worZO TUI 12 yarus V.VISUM. 28 (5:51) R Rice right tackleto SF 26 for 2 yards N. Bowman 26 (5:131 8 Pierce left tackleto SF 25 for 1 yard IN. Bowman), 25 (4:321/Shotaun), Flacco pass incomplete A Brooks), Penalty on SF-A.Brooks Defensive Offside declined. PENALTY on SF-AI 20 (4:281 Shotgun) / Flacco pass incomplete short right to D.Pitta. 20 (4:231J.Tucker 38 yard field poal is GOOD Center-M.COX Holder-S Koch 20 J.Tucker kicks 55 yards from BLT 35 to end zone Touchback 80 (4:19) (Shotgun) F. Gore right guard to SF 28 for 8 yards (D. Ellerbe; R. Lewis). 72 (3:40) C Kaepernick scrambles right end ran ob at SF 36 for 8 yards In Ellerbe). 54 (3:32)(Shotzun). Kaepernick pass incomplete deep right to V.Davis. 54 (3:25) Shotgun). Kaepernick pass deep middleto M.Crabtreeta BLT 40 for 24 yards (R. lewis C. Graham Pass 19 YACS 40 2:471/Shotgun) Gore left and pushed ob at BLT 7 for 33 yards E. Reed) 7 (2:39) (Shotgun) L.James right guard to BLT 5 for 2 yards (D. Ellerbe; D.Tyson). 5 (2:00) (Shotgun). Kaepernick pass incomplete short right to M. Crabtree (C. Graham). 5 (1:55) Kaepernick pass incomplete short right to M. Crabtree Smith 5 (1:50) (Shotgun). Kaepernick pass incomplete short right to M. Crabtree. 95 (1:45) R. Rice left puard to BLT 7 for 2 yards (N.Hawman; D.Goldson 93 (1:42 Pierce left end to BLT 7 for no pain (A. Hraaks). 93 (:56 V.Leach right suard to BLT for 1 yard(R.McDonald; N. Hawman 92 (0:12Punt formation) S. Koch right and ran ab in End Zone for 8 yards SAFETY C.Gulliver 92 S.Kach kicks 61 yards fram BLT2O to SF 19.T.Ginnta sa far 31 yards (1.Bynes. 39 SF 0 SF PS ZVISUZUS_OMWor 44836 20130203_BALSF 44837 20130203_BALSF 44838 20130203_BALSF 44839 20130203_BALSF 44840 20130203_BAL@SF 44841 20130203_BALSF 4484220130203_BALSF 44843 20130203 BALOOSF 44844 20130203_BAL@SF 44845 20130203_BALSF 44846 20130203_BALSF 44847 20130203_BALOOSF 44848 20130203_BALSF 44849 20130203_BAL@SF 44850 20130203_HALOSF 44851 20130203_HALSF 44852 20130203_HALOSF 44853 20130203_HALOSF 44854 20130203_HALOSF 44855 20130203_BALOOSE 44856 20130203_BALOOSE 44857 44958 44859 44860 44861 44862 44963 44864 44865 44866 44867 2012_nfl_pbp_data BAL 50 SF 46 BAL 42 BAL 56 BAL 12 BAL 12 BAL 12 BAL 11. How many plays were run in the "red zone (.ethe yard line was 20 or less)? 2. How many offensive plays were run by the NY Giants (NYGI? 3. How many offensive plays were run by the NY Giants (NYG) in the red zone? 4. What percentage of the NY Giants' offensive plays were run in the red zone? 5. How many offensive plays were run by the NY Giants INYG) and NY Jets (NY)combined? 6. Wha ran fewer defensive plays, NYG ar NY? 7. What was the average yards to go on third down plays during the season? +