Designing an Excel Workbook for a Computer Store

Only available on StudyMode
  • Download(s) : 643
  • Published : April 30, 2012
Open Document
Text Preview


New Perspectives excel 2010
Tutorial 7: Case Problem 1
PC-Market Distribution

Insert calculated columns in an Excel table
Use the IF function
Use structured references to create formulas in Excel tables
Create nested IFs
Use the VLOOKUP function to find and exact match
Use the VLOOKUP function to find an approximate match
Using the IFERROR function
Highlight duplicate values
Summarize data conditionally
Use the COUNTIF function
Use the AVERAGEIF function

Project overview
Linda Klaussen works for PC-Market Distribution, a computer supply store. She needs your help with designing an Excel workbook to enter purchase order information. She has already entered the product information on PC-Market’s line of modems. She wants you to insert a lookup function to search for data in the product table. The company also offers three shipping options—Standard, Express, and Overnight—that vary in price. She wants the purchase order worksheet to be able to calculate the total cost of the order, including the type of shipping the customer requests. STUDENT start FILE

NP_Excel2010_T7_CP1a_FirstLastName_1.xlsx (Note: Download your personalized start file from

Open the file NP_Excel2010_T7_CP1a_FirstLastName_1.xlsx and save the file as NP_Excel2010_T7_CP1a_FirstLastName_2.xlsx before you move to the next step. Verify that your name appears in cell B4 of the Documentation sheet. (Note: Do not edit the Documentation sheet. If your name does not appear in cell B4, please download a new copy of the start file from the SAM Web site.)

2.Go to the Purchase Order worksheet. In cell B5, enter the Product ID 1050. Then use the VLOOKUP function in cells C7, C8, and C9 as follows: a)...
tracking img