Designing an Excel Workbook for a Computer Store

Topics: Spreadsheet, Cartesian product, Enzyme Pages: 3 (739 words) Published: April 30, 2012
-------------------------------------------------

-------------------------------------------------

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

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

Instructions
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)...