# Designing an Excel Workbook for a Computer Store

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

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

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

NP_Excel2010_T7_CP1a_FirstLastName_1.xlsx (Note: Download your personalized start file from www.cengage.com/sam2010)

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

Please join StudyMode to read the full document