Use the VLOOKUP function in MS Excel application 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.)
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
1. Use the VLOOKUP function in MS Excel application 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.)
2. Create another VLOOKUP function to fill in the phone number for Mark Dumont automatically in Cell G3.
3. Create any logical function by using one of the following logical operators: AND, OR, NOT. Highlight the data in standard yellow color where this logical function is used on the spreadsheet.
4. Create any “IF” function that uses a simple “named range” in calculation based on the example we covered in class. Please highlight the data in standard blue color on the spreadsheet that uses this “IF” function.
5. Use conditional formatting to highlight top 10 highest-earning frequent fliers with an orange color.
6. Use conditional formatting to highlight all frequent fliers who have less than 4,000 total miles with a red color. 7. Create a simple table by including all the data sets visible on the spreadsheet tab called “customers”.
8. Insert a date/time in Cell G6 by using any date-related function as discussed in our class session in the past.
9. Format/Apply “Olive Green, Table Style Medium 4” theme in your just created table. Make sure there is ‘banding’ applied to the entire table.
10. Next, sort the frequent fliers column only by total miles in descending order.
11. Create a ‘clustered column’ graph that only lists the customer last names (x axis) and their total flown mileage (y axis) with a graph title “Miles Flown”.
12. Lastly, rename the 1st spreadsheet tab from ‘customer’ to ‘analysis’, and apply a red color to the 1st tab.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
