NEEDING HELP WITH FORMULAS FOR THESE QUESTIIONS. This is not the full sheet but if I understand the formula then I can adjust the range.
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 92 68 TA 97 Average Price 68 Make alfa-romeo audi bmw chevrolet dodge honda isuzu jaguar 90 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.4 53.7| 2050 69 4900 36 5118 88.4 141.1 60.3 53.2 1488 48 5100 53 5151 93.1. 159.11 64.21 54.11 1890) 911 68 5000 31 5195 95.7 158.7 63.61 54.5 1985 92 62 4800 39 5348 93.7 157.31 64.4 50.8 1918 5500 41 5389 93.71 150 64 52.6 1837 5500 42 5399 94.51 165.31 63.81 54.5 1889 5200 37| 5499 93.7 157. 3 63.8 50.8 1876 5500 41 5572 93.7 157. 3 63.8 50.8 1918 5500 41 5572 93.1 159.1 64.21 54.1 1900 5000 38 6095 93.7 157.3 64.4 50.8 1944 68 5500 31 38 6189 93.7 157.3163.8 50.6 1967 90 68 5500 31 38 6229 93.7 157. 3 63. 8 50.6 1967 5500 38 6229 94.5 155.9 63.6| 52|| 1874 701 5400 43 6295 95.7 158. 7 63. 6 54.5 2040 4800 38 6338 93.7 157.3163.8 50.8 1876 5500 38 6377 86.6 144.6) 63.9 50.8 1713 4800 49 54 6479 95.71 158.71 63.61 54.5 2015 4800 38 6488 93.7 150 64 52.6| 1940 6000 34 6529 94.5 158. 8 63.6 1909 90 70 43 6575 94.5 165.3 63.8 54.5 1918 5200 37 6649 93.71 157.3 64. 4 50.8 2004 5500 38 6669 93.7 157.3163. 8 50.6 1989 90 5500 38 6692 93.7 167.3 63.8|| 50.8 1989 5500 38 6692 93.1 166. 8 64.2 54.1 1945 5000 38 6695 94.3 170.7 61.8 53.5 2337 4800 29 6785 93.11 159.1 64.21 54.1 1905 5000 38 6795 94.51 165.31 63.8 54.5 1938 5200 37 6849 86.6 144.61 63.9 50.8 1819 6000 38 6855 95.7| 169. 7 63.6 59.1 4800 37 6918 95.7 166.3 64.4 53 2081 98 4800 37 6938 96.3 172.4 65.4 51.6 2365 5000 32 6989 93.7 157. 9 63.6 53.7 2120 4400 31 7053 94.5 165.3 63.8 54.5 2017 103 4800 507099 97.2 172 65.4 52.5 2145 108 4800 37 7126 93.71 1501 64 52.6 1956 76 6000 34 7129 95.7 166.3 64.4 52.8 2109 701 4800 37 7198 96.5 163. 4 64 2010 6000 34 7295 307 521 5400 38 mazda mercedes-benz mercury mitsubishi nissan peugot plymouth porsche saab subaru toyota volkswagen volvo 90) 91 68 2280 (1) R square = What does it tell you? 122 108 (2) Which variable to drop? Why? Answer Rank Q1 & 2 Top Make 92 68 TA 97 Average Price 68 Make alfa-romeo audi bmw chevrolet dodge honda isuzu jaguar 90 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.4 53.7| 2050 69 4900 36 5118 88.4 141.1 60.3 53.2 1488 48 5100 53 5151 93.1. 159.11 64.21 54.11 1890) 911 68 5000 31 5195 95.7 158.7 63.61 54.5 1985 92 62 4800 39 5348 93.7 157.31 64.4 50.8 1918 5500 41 5389 93.71 150 64 52.6 1837 5500 42 5399 94.51 165.31 63.81 54.5 1889 5200 37| 5499 93.7 157. 3 63.8 50.8 1876 5500 41 5572 93.7 157. 3 63.8 50.8 1918 5500 41 5572 93.1 159.1 64.21 54.1 1900 5000 38 6095 93.7 157.3 64.4 50.8 1944 68 5500 31 38 6189 93.7 157.3163.8 50.6 1967 90 68 5500 31 38 6229 93.7 157. 3 63. 8 50.6 1967 5500 38 6229 94.5 155.9 63.6| 52|| 1874 701 5400 43 6295 95.7 158. 7 63. 6 54.5 2040 4800 38 6338 93.7 157.3163.8 50.8 1876 5500 38 6377 86.6 144.6) 63.9 50.8 1713 4800 49 54 6479 95.71 158.71 63.61 54.5 2015 4800 38 6488 93.7 150 64 52.6| 1940 6000 34 6529 94.5 158. 8 63.6 1909 90 70 43 6575 94.5 165.3 63.8 54.5 1918 5200 37 6649 93.71 157.3 64. 4 50.8 2004 5500 38 6669 93.7 157.3163. 8 50.6 1989 90 5500 38 6692 93.7 167.3 63.8|| 50.8 1989 5500 38 6692 93.1 166. 8 64.2 54.1 1945 5000 38 6695 94.3 170.7 61.8 53.5 2337 4800 29 6785 93.11 159.1 64.21 54.1 1905 5000 38 6795 94.51 165.31 63.8 54.5 1938 5200 37 6849 86.6 144.61 63.9 50.8 1819 6000 38 6855 95.7| 169. 7 63.6 59.1 4800 37 6918 95.7 166.3 64.4 53 2081 98 4800 37 6938 96.3 172.4 65.4 51.6 2365 5000 32 6989 93.7 157. 9 63.6 53.7 2120 4400 31 7053 94.5 165.3 63.8 54.5 2017 103 4800 507099 97.2 172 65.4 52.5 2145 108 4800 37 7126 93.71 1501 64 52.6 1956 76 6000 34 7129 95.7 166.3 64.4 52.8 2109 701 4800 37 7198 96.5 163. 4 64 2010 6000 34 7295 307 521 5400 38 mazda mercedes-benz mercury mitsubishi nissan peugot plymouth porsche saab subaru toyota volkswagen volvo 90) 91 68 2280 (1) R square = What does it tell you? 122 108 (2) Which variable to drop? Why
