yes

data in 2pic

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 J K L M Price of Gasoline Per Gallon High/Low 2 3 4 5 6 7 8 9 10 11 12 13 14 15 15 17 18 10 20 21 22 23 24 25 20 Year 1976 1977 1978 1979 1980 1981 1982 1983 1984 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 S 0.60 $0.59 $0.59 $0.60 $0.62 0.62 $0.63 $0.63 $0.63 $0.63 $0.63 $0.63 $ 0.64 $0.64 S 0.65 $0.66 $0.67 $0.67 $0.67 $0.67 $0.67 0.66 0.67 S 0.65 $ 0.65 $0.65 S 0.65 $0.66 $0.66 $0.67 $0.68 $0.69 $0.69 0.70 S0.71 S0.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 $ 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 S 1.21 $1.21 $ 1.23 $1.24 $ 1.23 $1.21 $ 1.20 $ 1.20 $1.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.86 $0.83 $0.82 $0.82 $0.865 0.91 $0.91 $0.93 $0.94 $0.95 $0.97 $1.00 $0.99 $0.98 $0.99 $0.95 $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 $2.03 $1.03 $1.00 $0.99 $1.04 $ 1.04 $1.02 $ 1.04 $1.06 $1.09 $1.08 $ 1.19 $1.29 $1.38 $ 1.33 $ 1.35 $ 1.25 $ 1.14 $ 1.08 $ 1.10 $ 1.16 $ 1.16 $1.13 $1.14 51.14 $1.12 S 1.13 $1.12 $1.07 S 1.05 $1.06 $ 1.08 $1.14 $1.18 $1.17 $1.16 $ 1.16 $1.15 $1.16 $1.14 $ 1.12 $ 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 $ 1.13 $ 1.12 $1.16 $ 1.25 $ 1.32 $1.30 $1.27 $1.24 51.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.09 $1.08 $1,05 $1.03 $1.04 $1.03 $0.99 B c D E F G H 1 K L M 0 Price of Gasoline Per Gallon - Year High/Low 2 3 4 5 6 7 1976 1977 1978 1979 1980 1981 1982 1983 1984 10 11 12 13 14 15 18 17 13 19 20 21 22 23 24 25 25 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 $ 0.64 $0.64 $0.65 S0.66 $0.67 $0.67 $0.67 $0.67 $0.67 $0.66 $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.34 $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 51.41 $1.40 $1.39 $1.38 $1.38 $ 1.33 $1.37 51.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 50.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 51.12 $1.11 $1.09 $1.06 $1.03 51.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 $ 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 $1.16 $1.16 $1.15 $1.16 $ 1.14 $1.12 $ 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 $ 1.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 J K L M Price of Gasoline Per Gallon High/Low 2 3 4 5 6 7 8 9 10 11 12 13 14 15 15 17 18 10 20 21 22 23 24 25 20 Year 1976 1977 1978 1979 1980 1981 1982 1983 1984 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 S 0.60 $0.59 $0.59 $0.60 $0.62 0.62 $0.63 $0.63 $0.63 $0.63 $0.63 $0.63 $ 0.64 $0.64 S 0.65 $0.66 $0.67 $0.67 $0.67 $0.67 $0.67 0.66 0.67 S 0.65 $ 0.65 $0.65 S 0.65 $0.66 $0.66 $0.67 $0.68 $0.69 $0.69 0.70 S0.71 S0.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 $ 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 S 1.21 $1.21 $ 1.23 $1.24 $ 1.23 $1.21 $ 1.20 $ 1.20 $1.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.86 $0.83 $0.82 $0.82 $0.865 0.91 $0.91 $0.93 $0.94 $0.95 $0.97 $1.00 $0.99 $0.98 $0.99 $0.95 $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 $2.03 $1.03 $1.00 $0.99 $1.04 $ 1.04 $1.02 $ 1.04 $1.06 $1.09 $1.08 $ 1.19 $1.29 $1.38 $ 1.33 $ 1.35 $ 1.25 $ 1.14 $ 1.08 $ 1.10 $ 1.16 $ 1.16 $1.13 $1.14 51.14 $1.12 S 1.13 $1.12 $1.07 S 1.05 $1.06 $ 1.08 $1.14 $1.18 $1.17 $1.16 $ 1.16 $1.15 $1.16 $1.14 $ 1.12 $ 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 $ 1.13 $ 1.12 $1.16 $ 1.25 $ 1.32 $1.30 $1.27 $1.24 51.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.09 $1.08 $1,05 $1.03 $1.04 $1.03 $0.99 B c D E F G H 1 K L M 0 Price of Gasoline Per Gallon - Year High/Low 2 3 4 5 6 7 1976 1977 1978 1979 1980 1981 1982 1983 1984 10 11 12 13 14 15 18 17 13 19 20 21 22 23 24 25 25 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 $ 0.64 $0.64 $0.65 S0.66 $0.67 $0.67 $0.67 $0.67 $0.67 $0.66 $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.34 $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 51.41 $1.40 $1.39 $1.38 $1.38 $ 1.33 $1.37 51.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 50.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 51.12 $1.11 $1.09 $1.06 $1.03 51.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 $ 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 $1.16 $1.16 $1.15 $1.16 $ 1.14 $1.12 $ 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 $ 1.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