REAL ASSIGNMENTS OF LOOKUP, REFERENCE AND DATABASE FUNCTIONS IN EXCEL
Andrey Ampilogov, based in Rusia, currently works as SPSS programmer. He holds a Bacherlor of Applied Science (BASc), Quantitative Economics. Andrey has held several positions as senior analyst at major financial institutions in Rusia and the US.
This challenge consists of 20 exercises focused on the Lookup, Reference and Database functions of Excel, especially the VLOOKUP. All exercises are real, based on business cases where these type of functions are a must. You will practice all types of lookup functions such as vlookup, hlookup, reference and database functions like index, match, sumif, sumproduct, indirect and many others in different scenarios that tend to occur at corporate level.
All assignments come with a real set of data and describe a regular situation or problem that you will have to solve using the most suitable Excel function. Basically, these exercises replicate real world problems or situations that you may encounter at any position of wide range of industries.
Moreover, the workload is mixed, some exercises are short, finished with just one formula, but others are rather mini projects where you will have to implement and combine several formulas to yield the expected outcome.
Once you finish all the exercises, you will be more confident with Excel and gain the expertise and knowledge required to succeed any job interview.
WHAT YOU WILL PRACTICE:
1) Find, match and retrieving values of multiple tables and/or sheets in order to get insights about a business. Not only vlookup but olso other formulas used to retrieve values from one place to another.
2) Confront the situations when lookup functions do not work and learn how to fix them.
3) Common Excel tools and tricks to handle and analyze data.
4) Tools and tricks to work with massive data sets (more than 60 000 rows).
5) Real applications of the following functions: vlookup, sumif, hlookup, index-match, ifna, indirect, sumproduct, offset, find…
6) Nested functions.
7) Text functions: len, wide, concat, substitute, left, lower, etc.
8) Conditional statements.
90% of problems require an intermediate level of Excel. Students must have attended a complete course of Excel and feel confident with the tool. Five exercises are considered of advanced level.
The spreadsheets included in the project have been done with the latest Excel version. It is then compatible with Excel 2007, Excel 2010, Excel 2013 and Excel 2016.
You will receive a protected zip folder and its password in the email confirmation after purchase.
The file is made up of 2 PDFs and 42 Excel files:
– PDF with assignments.
– PDF with the solutions (57 pages). It includes detailed explanations and print screens with the formulas required to solve each problem.
– 22 Excel files with the data required for each exercise.
– 20 Excel files with the solutions to each exercise.
If you need additional information, do not hesitate to contact us.
Specification: LOOKUP, REFERENCE, DATABASE FUNCTIONS PROBLEMS
Only logged in customers who have purchased this product may leave a review.