Q1. In the table on the right, create a listing of top five price - do not sort the data; it will screw up the format (10 pts.)

Q2. Copy the data to a new sheet, and arrange it to a lookup table. In the table used in Q1 (Q15), put a formula using one of the kinds of look up functions (VLOOKUP, HLOOKUP, LOOKUP, or INDEX) to find the top 5 prices in the data and retrieve the corresponding make from the lookup table; copy down the formula for other ranks (10 pts).

Q3. Calculate Average price for each make (P21); copy down the formula ( (10 pts.)

Q4. (1) Build a multiple regression model, which predicts car prices based on curb weight, engine size, hose power, and peak rpm. What's the R square of the regression model and what does it tell you? (5 pts.)

(2) Which variable you want to drop from the model first? Why? (10 pts.)

Answer Rank Q1 & 2 Top Make 1 2 3 4 5 03 Average Price make subaru chevrolet mazda toyota mitsubishi honda nissan dodge plymouth mazda mitsubishi dodge plymouth chevrolet toyota dodge honda toyota honda chevrolet nissan mitsubishi dodge plymouth mazda isuzu mazda nissan honda toyota toyota mitsubishi subaru nissan subaru honda toyota honda wheel base length width height curb weight engine size horse power peak rpm city mpg highway mpg price 93.7 156.9 63.41 53.7 2050 971 691 49001 31 36 5118 88.4 141.1 60.3 53.2 1488 61 48 5100 47 53 5151 93.1 159.1 64.2 54.1 1890 91 68 5000 30 31 5195 95.7 158.7 63.6 54.5 1985 92 62 4800 35 39 5348 93.7 157.3 64.4 50.8 1918 92 68 5500 37 41 5389 93.7 150 64 52.6 1837 79 601 5500 38 42 5399 94.5 165.3 63.8 54.5 1889 97 69 5200 31 37 5499 93.7 157.3 63.8 50.8 1876 90 681 5500 37 41 5572 93.7 1573 63.8 50.8 1918 90 681 55001 37 41 5572 93.1 159.1 64.2 54.1 1900 91 681 50001 31 38 6095 93.7 157.3 64.4 50.8 1944 92 68. 5500 31 38 6189 93.7 157.3 63.8 50.6 1967 90 681 5500 31 38 6229 93.7 157.3 63.8 50.6 1967 90 68 5500 31 38 6229 94.5 155.9 63.6 52 1874 90 70 5400 38 43 6295 95.7 158.7 63.6 54.5 2040 92 62 4800 31 38 6338 93.7 157.3 63.8 50.8 1876 90 68 5500 31 38 6377 86.6 144.6 63.9 50.8 1713 92 58 4800 49 54 6479 95.7 158.7 63.6 54.5 2015 92 62 4800 31 38 6488 93.7 150 64 52.6 1940 92 76 6000 30 34 6529 94.5 158.8 63.6 52 1909 90 70 5400 38 43 6575 94.5 165.3 63.8 54.5 1918 97 691 5200 31 37 6649 93.7 157.3 64.4 50.8 2004 921 681 5500 31 38 6669 93.7 157.3 63.8 50.6 1989 90 68 5500 31 38 6692 93.7 167.3 63.8 50.8 1989 90 68 5500 31 38 6692 93.1 166.8 64.2 54.1 1945 91 68 5000 31 38 6695 94.3 170.7 61.8 53.5 2337 111 78 4800 24 29 6785 93.1 159.1 64.2 54.1 1905 91 68 5000 31 38 6795 94.5 165.2 63.8 54.5 1938 97 69 5200 31 37 6849 86.6 144.6 63.9 50.8 1819 92 76 6000 31 38 6855 95.7 169.7 63.6 59.1 2280 92 62 4800 31 37 6918 95.7 166.3 64.4 53 2081 98 70 4800 30 37 6938 96.3 172.4 65.4 51.6 2365 122 88 5000 25 32 6989 93.7 157.9 63.6 53.7 2120 108 73 4400 26 31 7053 94.5 165.3 63.8 54.5 2017 103 55 4800 45 507099 972 172 65.4 52.5 2145 108 82 4800 32 37 7126 93.7 150 64 52.6 1956 92 76 6000 30 34 7129 95.7 166.3 64.4 52.8 2109 98 70 4800 30 37 7198 96.51 163.4 64 54.5 2010 92 76 6000 30 34 7295 Make alfa-romeo audi bmw chevrolet dodge honda isuzu Jaguar mazda mercedes-benz mercury mitsubishi nissan peugot plymouth porsche saab subaru toyota volkswagen volvo Q4 (1) R square What does it tell you? (2) Which variable to drop? Why? Answer Rank Q1 & 2 Top Make 1 2 3 4 5 03 Average Price make subaru chevrolet mazda toyota mitsubishi honda nissan dodge plymouth mazda mitsubishi dodge plymouth chevrolet toyota dodge honda toyota honda chevrolet nissan mitsubishi dodge plymouth mazda isuzu mazda nissan honda toyota toyota mitsubishi subaru nissan subaru honda toyota honda wheel base length width height curb weight engine size horse power peak rpm city mpg highway mpg price 93.7 156.9 63.41 53.7 2050 971 691 49001 31 36 5118 88.4 141.1 60.3 53.2 1488 61 48 5100 47 53 5151 93.1 159.1 64.2 54.1 1890 91 68 5000 30 31 5195 95.7 158.7 63.6 54.5 1985 92 62 4800 35 39 5348 93.7 157.3 64.4 50.8 1918 92 68 5500 37 41 5389 93.7 150 64 52.6 1837 79 601 5500 38 42 5399 94.5 165.3 63.8 54.5 1889 97 69 5200 31 37 5499 93.7 157.3 63.8 50.8 1876 90 681 5500 37 41 5572 93.7 1573 63.8 50.8 1918 90 681 55001 37 41 5572 93.1 159.1 64.2 54.1 1900 91 681 50001 31 38 6095 93.7 157.3 64.4 50.8 1944 92 68. 5500 31 38 6189 93.7 157.3 63.8 50.6 1967 90 681 5500 31 38 6229 93.7 157.3 63.8 50.6 1967 90 68 5500 31 38 6229 94.5 155.9 63.6 52 1874 90 70 5400 38 43 6295 95.7 158.7 63.6 54.5 2040 92 62 4800 31 38 6338 93.7 157.3 63.8 50.8 1876 90 68 5500 31 38 6377 86.6 144.6 63.9 50.8 1713 92 58 4800 49 54 6479 95.7 158.7 63.6 54.5 2015 92 62 4800 31 38 6488 93.7 150 64 52.6 1940 92 76 6000 30 34 6529 94.5 158.8 63.6 52 1909 90 70 5400 38 43 6575 94.5 165.3 63.8 54.5 1918 97 691 5200 31 37 6649 93.7 157.3 64.4 50.8 2004 921 681 5500 31 38 6669 93.7 157.3 63.8 50.6 1989 90 68 5500 31 38 6692 93.7 167.3 63.8 50.8 1989 90 68 5500 31 38 6692 93.1 166.8 64.2 54.1 1945 91 68 5000 31 38 6695 94.3 170.7 61.8 53.5 2337 111 78 4800 24 29 6785 93.1 159.1 64.2 54.1 1905 91 68 5000 31 38 6795 94.5 165.2 63.8 54.5 1938 97 69 5200 31 37 6849 86.6 144.6 63.9 50.8 1819 92 76 6000 31 38 6855 95.7 169.7 63.6 59.1 2280 92 62 4800 31 37 6918 95.7 166.3 64.4 53 2081 98 70 4800 30 37 6938 96.3 172.4 65.4 51.6 2365 122 88 5000 25 32 6989 93.7 157.9 63.6 53.7 2120 108 73 4400 26 31 7053 94.5 165.3 63.8 54.5 2017 103 55 4800 45 507099 972 172 65.4 52.5 2145 108 82 4800 32 37 7126 93.7 150 64 52.6 1956 92 76 6000 30 34 7129 95.7 166.3 64.4 52.8 2109 98 70 4800 30 37 7198 96.51 163.4 64 54.5 2010 92 76 6000 30 34 7295 Make alfa-romeo audi bmw chevrolet dodge honda isuzu Jaguar mazda mercedes-benz mercury mitsubishi nissan peugot plymouth porsche saab subaru toyota volkswagen volvo Q4 (1) R square What does it tell you? (2) Which variable to drop? Why