Scenario: You are provided with historical gas prices and have been asked to analyze gas prices for your transportation company. 1. Download the file called "Homework 4 Data" 2. Calculate the average price per year for each year in Column "Average Price per Year" 3. Write an IF function for the Column "High/Low" to classify each year as being "High" if the average for the year is above $1.00 and "Low" if less than or equal to $1.00: 4. Use conditional formatting to highlight all cells that are marked as "Low". 5. How many years of data do you have here. Hint: Use a COUNT function. (Enter formula in D37) = 28 6. What is the average gasoline price over the years from 1980 - 1989 (including both these years)? (Enter formula in D38) =$1.14 7. What is the average gasoline price in the month of July over the years 1979-2003 (including both years)? (Enter formula in D39) = $1.21 8. What is the lowest gasoline price over the years 1976- 2003 (including both years)? (Enter formula in D40) = $.59 9. What is the maximum gasoline price over the years 1979-2003 (including both years)? (Enter formula in D41) =$1.75 10. Which months historically have had the highest gas price (include all years!) (Enter answer in D42 - HINT: for this you need to calculate monthly averages and there is a tie) B D E F G H 1 K L 0 P Price of Gasoline Per Gallon -N Year High/Low 2 3 4 5 5 7 1976 1977 1978 1979 1980 1981 1982 1983 1984 9 10 11 12 13 14 15 18 17 13 19 20 21 22 23 24 25 26 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Average Price Per Year $0.61 $ 0.60 $0.59 50.59 $0.60 $0.62 $0.62 $0.63 $0.63 $0.63 $0.63 $0.63 $0.63 S 0.64 $0.64 $0.65 $0.66 $0.67 $0.67 $0.67 $0.67 50.67 $0.66 S 0.67 $0.65 $ 0.65 $0.65 S 0.65 $0.66 $0.66 $0.67 $0.68 $0.69 $0.69 $0.70 $0.71 $0.72 $ 0.73 $0.76 $0.80 $0.84 $0.90 $0.95 $0.99 $1.02 $1.03 $1.04 $1.07 $ 1.13 $ 1.21 $1.25 $1.26 $1.27 $1.27 $1.27 $1.27 $1.26 $1.25 $1.25 $ 1.26 $ 1.30 S 1.38 $1.42 $ 1.41 $1.40 $1.39 $1.38 $1.38 $ 1.38 $1.37 $1.37 $ 1.37 $1.36 $ 1.33 $1.28 $1.23 $1.24 $1.31 $1.33 $1.32 $1.31 $1.30 $ 1.28 $ 1.26 $ 1.23 $ 1.19 $1.15 $1.22 $1.26 $1.28 $1.29 $1.29 $1.27 $1.26 $1.24 $1.23 $ 1.22 $ 1.21 $1.21 $ 1.23 $1.24 $1.23 $1.21 $ 1.20 $ 1.20 51.21 $1.21 $ 1.19 $1.15 $ 1.13 $ 1.16 $1.21 $ 1.23 $1.24 $1.24 $1.23 $1.22 $1.20 $1.21 $1.21 $ 1.19 $ 1.12 $0.98 $0.89 $0.92 $0.96 $0.89 $0.84 $0.56 $0.83 $0.82 $0.82 $0.86 $0.91 $0.91 $0.93 0.94 $0.96 $0.97 $1.00 $0.99 50.98 $0.98 $0.96 $0.93 $ 0.91 $0.90 $0.93 $0.96 $0.96 $0.97 $0.99 $0.97 $0.96 $0.95 $0.93 $0.92 $0.93 $0.94 $1.07 $1.12 $1.11 $1.09 $1.06 $1.03 $1.03 $1.00 $0.98 $ 1.04 $1.04 $1.02 $1.04 $1.06 $1.09 $1.08 $ 1.19 $1.29 51.38 $1.38 1.35 $1.25 $ 1.14 $1.08 S 1.10 $ 1.16 $1.16 $1.13 $1.14 $1.14 $1.12 $ 1.13 $1.12 $1.07 $ 1.05 $1.06 $ 1.08 $1.14 $1.18 $1.17 51.16 $1.16 $1.15 $1.16 $ 1.14 $1.12S 1.11 $1.10 $ 1.11 $1.13 $1.13 $1.11 $1.10 $ 1.09 1.13 $1.11 $1.07 $1.04 $ 1.05 $1.05 $1.06 $1.08 $1.11 $1.14 $1.18 $1.18 $1.15 $1.16 $ 1.14 $ 1.13 $ 1.12 $1.12 1.14 $1.20 $1.23 $1.20 $ 1.16 $1.15 $1.13 $1.10 $1.10 SL.13 $ 1.12 $1.16 $1.25 $1.32 $1.30 $1.27 $1.24 $1.23 $1.23 $1.25 $ 1.26 $ 1.26 $ 1.26 $1.24 $1.23 $1.23 $1.23 $1.21 $1.25 $1.28 $1.24 $1.21 $1.18 $ 1.13 $ 1.08 $1.04 $1.05 $1.09 $1.0951.08 $1.05 $1.03 51.04 $1.03 $0.99 Scenario: You are provided with historical gas prices and have been asked to analyze gas prices for your transportation company. 1. Download the file called "Homework 4 Data" 2. Calculate the average price per year for each year in Column "Average Price per Year" 3. Write an IF function for the Column "High/Low" to classify each year as being "High" if the average for the year is above $1.00 and "Low" if less than or equal to $1.00: 4. Use conditional formatting to highlight all cells that are marked as "Low". 5. How many years of data do you have here. Hint: Use a COUNT function. (Enter formula in D37) = 28 6. What is the average gasoline price over the years from 1980 - 1989 (including both these years)? (Enter formula in D38) =$1.14 7. What is the average gasoline price in the month of July over the years 1979-2003 (including both years)? (Enter formula in D39) = $1.21 8. What is the lowest gasoline price over the years 1976- 2003 (including both years)? (Enter formula in D40) = $.59 9. What is the maximum gasoline price over the years 1979-2003 (including both years)? (Enter formula in D41) =$1.75 10. Which months historically have had the highest gas price (include all years!) (Enter answer in D42 - HINT: for this you need to calculate monthly averages and there is a tie) B D E F G H 1 K L 0 P Price of Gasoline Per Gallon -N Year High/Low 2 3 4 5 5 7 1976 1977 1978 1979 1980 1981 1982 1983 1984 9 10 11 12 13 14 15 18 17 13 19 20 21 22 23 24 25 26 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Average Price Per Year $0.61 $ 0.60 $0.59 50.59 $0.60 $0.62 $0.62 $0.63 $0.63 $0.63 $0.63 $0.63 $0.63 S 0.64 $0.64 $0.65 $0.66 $0.67 $0.67 $0.67 $0.67 50.67 $0.66 S 0.67 $0.65 $ 0.65 $0.65 S 0.65 $0.66 $0.66 $0.67 $0.68 $0.69 $0.69 $0.70 $0.71 $0.72 $ 0.73 $0.76 $0.80 $0.84 $0.90 $0.95 $0.99 $1.02 $1.03 $1.04 $1.07 $ 1.13 $ 1.21 $1.25 $1.26 $1.27 $1.27 $1.27 $1.27 $1.26 $1.25 $1.25 $ 1.26 $ 1.30 S 1.38 $1.42 $ 1.41 $1.40 $1.39 $1.38 $1.38 $ 1.38 $1.37 $1.37 $ 1.37 $1.36 $ 1.33 $1.28 $1.23 $1.24 $1.31 $1.33 $1.32 $1.31 $1.30 $ 1.28 $ 1.26 $ 1.23 $ 1.19 $1.15 $1.22 $1.26 $1.28 $1.29 $1.29 $1.27 $1.26 $1.24 $1.23 $ 1.22 $ 1.21 $1.21 $ 1.23 $1.24 $1.23 $1.21 $ 1.20 $ 1.20 51.21 $1.21 $ 1.19 $1.15 $ 1.13 $ 1.16 $1.21 $ 1.23 $1.24 $1.24 $1.23 $1.22 $1.20 $1.21 $1.21 $ 1.19 $ 1.12 $0.98 $0.89 $0.92 $0.96 $0.89 $0.84 $0.56 $0.83 $0.82 $0.82 $0.86 $0.91 $0.91 $0.93 0.94 $0.96 $0.97 $1.00 $0.99 50.98 $0.98 $0.96 $0.93 $ 0.91 $0.90 $0.93 $0.96 $0.96 $0.97 $0.99 $0.97 $0.96 $0.95 $0.93 $0.92 $0.93 $0.94 $1.07 $1.12 $1.11 $1.09 $1.06 $1.03 $1.03 $1.00 $0.98 $ 1.04 $1.04 $1.02 $1.04 $1.06 $1.09 $1.08 $ 1.19 $1.29 51.38 $1.38 1.35 $1.25 $ 1.14 $1.08 S 1.10 $ 1.16 $1.16 $1.13 $1.14 $1.14 $1.12 $ 1.13 $1.12 $1.07 $ 1.05 $1.06 $ 1.08 $1.14 $1.18 $1.17 51.16 $1.16 $1.15 $1.16 $ 1.14 $1.12S 1.11 $1.10 $ 1.11 $1.13 $1.13 $1.11 $1.10 $ 1.09 1.13 $1.11 $1.07 $1.04 $ 1.05 $1.05 $1.06 $1.08 $1.11 $1.14 $1.18 $1.18 $1.15 $1.16 $ 1.14 $ 1.13 $ 1.12 $1.12 1.14 $1.20 $1.23 $1.20 $ 1.16 $1.15 $1.13 $1.10 $1.10 SL.13 $ 1.12 $1.16 $1.25 $1.32 $1.30 $1.27 $1.24 $1.23 $1.23 $1.25 $ 1.26 $ 1.26 $ 1.26 $1.24 $1.23 $1.23 $1.23 $1.21 $1.25 $1.28 $1.24 $1.21 $1.18 $ 1.13 $ 1.08 $1.04 $1.05 $1.09 $1.0951.08 $1.05 $1.03 51.04 $1.03 $0.99