Project 2Completing a Three Dimensional

Workspace Using Logical and Lookup Functions

Jason C. H. Chen, Ph.D.Professor of

Management Information Systems

School of Business Administration

Gonzaga UniversitySpokane, WA 99258, USA

Chen@gonzaga.edu

2

Excel Skills Construct formulas containing the VLOOKUP

function and range names Construct formulas using a nested IF Function Copy data from Word into Excel Create linking formulas Drag and drop data from Word into Excel Insert range names Sort ranges Use the Text Import Wizard to import delimited

text into Excel

3

Key Terms Data array

A set of values appearing in a worksheet that is referenced by certain array functions.

Delimited A characteristic applying to field data stored

in ASCII text files. IF Function

A function that returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

4

Key Terms Lookup Function

An Excel function that finds a specific value in a list or data array by locating one value and returning a corresponding value.

Lookup table An array of data that is used in conjunction with a

Lookup function. Name

A stored name for a range of cells in a worksheet. Named ranges are viewed and selected using the Name box on the Formatting toolbar.

5

Key Terms Named range

A name you create that defines a range of cells. Once you name a range, you can refer to the range by name in formulas. You can also select the range using the Name box.

Nested IF Function An Excel formula containing more than one If function to test

multiple conditions. Sort

A method for ordering the contents of a selection by a single criterion, or by multiple criteria.

VLOOKUP Function An Excel function that searches for a value in the leftmost

column of a table, and then returns a value in the same row from a column you specify in the table.

6

Objectives Use the Fill Handle to create linking formulas for

displaying product descriptions for a group of worksheets

Import sales data from a variety of text and document formats

Insert names for five data ranges Sort the named ranges to display data in a

specified order Construct formulas in the Sales Summary

worksheet using the VLOOKUP function Specify conditions and returning values using a

Nested IF function

7

Two Required Files (from Project 1) Products.xls Selections Prototype.xls

8

Seven Steps for Developing Excel Workbooks

9

Running Case Selections, Inc. is a national department

store chain with retail stores throughout the United States and Canada.

Upper management is considering launching a web-based e-commerce initiative to market products directly.

The Finance Department wants to see how Excel might be used to track quarterly sales.

10

Finance Dept. Excel Application Prototype Microsoft Excel workbook

summarizing the sales of inventory items from five departments for the five most productive stores in the following cities: Boston, Dallas, Denver, Indianapolis, and Seattle.

11

Project 2 Challenge The sales data for each store must be

sorted in descending order by annual sales for each product.

The annual sales data for all stores in the Sales Summary worksheet must also be sorted by total sales across all five stores.

A list showing the store with the highest sales amount for each product.

12

Solution Structure

13

Task 1 (EX- 55)

WinCopy Win95

14

Different ways to entering data:Task 2: Drag-and-Drop(EX-56); Task 3, 5, 6:Copy and Paste;Task 4: Text Import Wizard (97 does not support)

15

Other Features Global Change on Format Cells

Task 5 Step 13 - 16 (EX-62, 63)

Create Named Ranges Task 6

Sort the Named Ranges Task 7

VLOOKUP and Save two open workbooks as a workspace Task 8

16

Task 8: VLOOKUP(lookup value, table array, returned column index number, false/true)

BostonSales created asnamed ranges of A5:G19from Boston Worksheetfalse: exact match only

col. 7

Project 2

Completing a Three Dimensional Workspace Using Logical and

Lookup Functions