PRDV252: Intermediate Excel

Unit 5: Lookup Functions and Review of Functions   This unit will introduce the VLOOKUP and HLOOKUP functions as well as how to create a web query that imports stock prices to your worksheet from a website.

Unit 5 Time Advisory
Completing this unit should take you approximately 3 hours.

☐    Subunit 5.1: 1 hour

☐    Subunit 5.2: 0.5 hours

☐    Subunit 5.3: 0.25 hours

☐    Subunit 5:4: 1.25 hours

Unit5 Learning Outcomes
Upon successful completion of this unit, you will be able to: - use the VLOOKUP and HLOOKUP functions; - create a web query that imports stock price data from a website; and - use Goal Seek to answer a what-if scenario.

5.1 Lookup Functions   - Reading: How to Use Microsoft Excel®: “Section 3.3” Link: How to Use Microsoft Excel®: Section 3.3 (PDF)
Instructions: Read Chapter 3, Section 3.3: Lookup Functions, which starts on page 265. You will continue to use the follow-along file from Unit 4 above (Excel® Text Chapter 3) as you finish this chapter.
Completing this assignment should take approximately 1 hour.
Terms of Use: The textbook used in the link above is released under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License without attribution as requested by the work’s original creator or licensee.

5.2 Review of PMT using Goal Seek   - Web Media: GCFLearnFree.org’s “Excel® 2010: Using What-If Analysis” Link: GCFLearnFree.org’s Excel® 2010: Using What-If Analysis (HTML)
Instructions: Go through the 5 pages, watching the video on page 2. This review uses the PMT function that you learned in subunit 3.3. The last page also introduces Scenarios and Data Tables. The class will not dig any deeper into these tools but you can use Help to learn more if these are features you would find useful. If you have Excel® 2010, when you have finished, there is a practice document at the end of this presentation that you can open and follow the instructions to practice the skills you learned. If you have an older version of Excel®, open your version and create the spreadsheet you see on page 3. Find Goal Seek in your program to practice what you learned here.
Completing this assignment should take approximately 30 minutes.
Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

5.3 Printing Formulas and Functions   So now you know a lot about writing formulas in Excel®. If you want to share your formulas with someone in a printed format, you will need to be able to see them in the spreadsheet for them to print. 

  • Reading: The Saylor Foundation’s “Printing Formulas” Link: The Saylor Foundation’s “Printing Formulas” (PDF)
    Instructions: Read the PDF about printing formulas. Then, open your own spreadsheet program and turn on the Formulas view and try printing it to one page.
    Completing this assignment should take approximately 15 minutes.

5.4 Exercises for Unit 5   - Activity: The Saylor Foundation’s “Practice Exercise for Unit 5” Link: The Saylor Foundation’s “Practice Exercise for Unit 5” (PDF)
Instructions: Open the accompanying PDF document and follow the directions to complete the Practice for the skills you just learned. This file is quite comprehensive and it reviews many of the functions you learned in Chapters 3-5. Think of it as a test of what you have learned so far.
Completing this assignment should take approximately 1hour and 15.