You have recently landed a part-time job as a business analyst for Campus Travel. In your first meeting, the operations manager learned that you are taking an introductory MIS class. As the manager is not very proficient in using office software tools, he is doing all frequent-filer mileage in two separate Excel workbooks. One is the customer's contact information, and the second is the miles flown. Being familiar with the possibilities of spreadsheet applications, you suggest setting up one workbook to handle both functions. To complete this, you must do the following: Open the spreadsheet frequentflier2.csv. You will see a tab for customers and a tab labeled miles flown. - Use the vlookup function to enter the miles flown column by looking up the frequent-flier number. (Hint: if done correctly with absolute references, you should be able to enter the vlookup formula in the first cell in the "miles flown" column and copy it down for all the cells.) Use conditional formatting to highlight all frequent fliers who have less than 4,000 total miles. - Finally, sort the frequent fliers by total miles in descending order. XLast_Name A1 Eartoni509518 -Li208-882-3232 ey Sto09-332-2 5 Bocker Hubort 50-336-657 3686 7Gillarpie Patrici 509-332-4578 Sn501 $94 11 Baker Beth 1 2hanMan 13 J509-357-4509 515086 Hutchinon Erie 15 Thampran Caitli 509-332-8790 Balduin MaryAnn 203-882-4455 17 Dumont Mark 19 Sandorran Sanja509-336-5567 GibnSonis0 3424 0 Clauron Gina 509-336-7777 1 HuntorAnna208-882-3424 HunDetbie509 Marr Dobbi 208-882-1525 3 AurtinJarep 509-333-4576 Clark-^ Janet, 509-322-6709 5 Thorntan Janot 509-332-8679 6 Hartan Cynia 509-357-5676 7 Walker Ken 2* | Nauman Mishaal 509-357-5673 9Bayd Glaria 0 Pract Ginny208-875-3333 So Hunt H5093-889 4719 33 Honderan Anela 509-332-4343 509-357-8889 Lind 556 Diane 208-382-5478 34 Steuart Kathy 37 Loaney Carl Fujimata Carla 509-332-4532 9Hormn Frank 509-332-1267 40 Walla Alox 1 Hall 42 WboCliff 43 Frazier Nany 509-332-1243 uimtFrank202434 $37399 304136 35797 9-357-3434 Frequent Flier Numbe Miles Flown 57822342 43476 1335 74826 3081 73235 3144 135797 6570 144323 183143 8275 206584 1766 21164 213608 1845 7343 224029 6211 253804 7526 276924 234736 304136 306513 1280 3803324375 3388467838 401413 3222 1911 752 985 424960 3833 434336 4724587603 515086 3857 5256305038 5303767906 5428807632 6031 5653 634054 725722 1971 40617 3812 42971 3266 4717 6028 752937 7888398100 8036868633 813258 8456 818401 837333 8678143205 8745636628 883456 2355 325803 5333 9184456057 5012 784 4811 156
