Homework answers / question archive / Illustrated Excel 2016 | Module 8: SAM Project 1a Refresh Pilates Studio ANALYZING TABLE DATA GETTING STARTED ? Open the file IL_EX16_8a_ FirstLastName _1

Illustrated Excel 2016 | Module 8: SAM Project 1a Refresh Pilates Studio ANALYZING TABLE DATA GETTING STARTED ? Open the file IL_EX16_8a_ FirstLastName _1

MS Excel

Share With

---

Illustrated Excel 2016 | Module 8: SAM Project 1a

Refresh Pilates Studio

ANALYZING TABLE DATA

GETTING STARTED

?

Open the file

IL_EX16_8a_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

IL_EX16_8a_

FirstLastName

_2.xlsx

by changing the “1” to a

“2”.

o

If you do not see the

.xlsx

file extension

in the Save As dialog box, do not

type it. The program will add the file extension for you automatically.

?

With the file

IL_EX16_8a_

FirstLastName

_2.xlsx

still open, ensure that your

first and last name is displayed in cell B6 of the Documentation sheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

PROJECT STEPS

1.

Kecia Gill is the co-owner of Refresh Pilates Studio. You are working part-time at

the studio to help Kecia organize the class information and analyze the studio

income using Excel. You begin by improving the accuracy of the input data.

Switch to the

Schedule

worksheet and use data validation to create an in-cell

dropdown list that restricts data in the Room column

in the table (E7:E15) using

Mat Room, Equipment Room

as the source values.

Select all data in row e then go to Data validation -> list -> range e7:e15 (select

all data again)

check

2.

Kecia is adding a new stretch class on Wednesday mornings. You will add that

information to the schedule.

Add a new record on the

Schedule

worksheet, entering the data shown in Table

1 below

into row 16. Use the in-cell dropdown list to enter the text

Mat

Room

into cell E16.

Table 1: Data for the Schedule Worksheet Table

A

B

C

D

E

F

G

1

6

PL109

Stretc

h

11:00

AM

Wednesda

y

Mat

Room

$15

Floo

 

3.

Kecia asks you to create a hard copy of the schedule containing a list of the

Reformer classes located below the scheduled classes.

On the

Schedule

worksheet, complete the following actions:

a.

Enter the text

Reformer

into cell A19.

b.

Using the range A18:A19 as the criteria, create an advanced filter that

extracts records from the table in the range A6:G16 to the range

beginning at cell A21. (

Hint:

You will need to switch the action in the

Advanced Filter dialog box to the “Copy to another location” option.)

Data -> filter advance -> copy location -> $A$6:$G$16 -> $A$18:$A$18

-> =A21

4.

Kecia has created an area in the workbook where class and instructor

information can be easily retrieved. She asks you to enter functions to help with

the data retrieval.

Switch to the

Lookup

worksheet. In cell J7, enter a formula using the

VLOOKUP

function to display the class name corresponding to the class code in cell J6 as

follows:

a.

Use cell

J6

as the lookup value.

b.

Use

Table1

(the table on the

Schedule

worksheet) as the table array.

c.

The function should return the value in column

2

of the table array.

d.

Use

FALSE

as the Range_lookup argument.

Formulas-> lookup&reference -> vlookup -> inset information as

instructed in order _ j6 then skip table array then 2 on the next one and

last one false then go back to the table array go to the schedule sheet and

select the first table press ok barre must come out

5.

In cell J10, enter a formula using the

DSUM

function to calculate the total

amount paid for the instructor name in cell I9 as follows:

a.

Use

Table2[#All]

(the entire table on the

Lookup

worksheet, including

the header row) as the formula database.

b.

Use the

Amount Paid

field header (in cell F5) as the field argument.

c.

Use the range

I8:I9

as the criteria argument.

IN CELL J10-> =Dsum( highlight all table in the page table 2 then coma ,

select amount paid F5 THEN COMA , then selec instructor and wright I8:I9

THEN CLOSE PARENTHEIS ) ENTER

6.

In cell J11, enter a formula using the

DCOUNTA

function to count the number of

classes on the

Schedule

worksheet taught by the instructor in cell I9 as follows:

a.

Use

Table1[#All]

(the entire table on the

Schedule

worksheet, including

the header row) as the formula database.

b.

Use the

Class Code

field header (in cell C5 of the

Lookup

worksheet) as

the field argument.

c.

Use the range

I8:I9

as the criteria argument.

 

 

Illustrated

Excel 2016 | Module 8: SAM Project 1a

In cell J11 =DCOUNTA(go to schedule sheet and select full table then

COME, select class code on the same table then go to fx where the

formula its display at the top click at the end of the senence and then

COMA, then write I8:I9 close parenthesis ) enter must come out to 3

7.

Kecia asks you for a list of just the morning classes organized by the instructor

name.

Switch to the

Morning Classes

worksheet. Use a number filter in the Time

column to display only morning classes (<12:00 PM).

[Mac Hint: In the filter

dialog box, filter values that are less than 12:00 PM.]

PRESS THE LITTLE ARROW NEXT TO THE TIME HEADLINE AND PRESS NUMBER FILTER

LOOK FOR LESS THAN AND WRITE 12:00PM THEN OK

8.

Sort the filtered table in ascending order (A to Z) by instuctor (column G).

Select the arrow next to the name and press a to z

9.

Kecia asks you for help in getting a breakdown of the income for the past week

by class.

Switch to the

Subtotals

worksheet. Sort the table on the

Subtotals

worksheet by

the Class value (Column D) in ascending (A to Z) order.

10.

Convert the table to a range.

11.

Add subtotals

to the range A5:F41 that, for each change in the

Class

value, will

use the

Sum

function to add subtotals to the

Amount Paid

field. Keep the

default settings of displaying the summary below data and replacing current

subtotals

.

12.

Use the outline buttons to display only class names with subtotals and the

grand total.

13.

Kecia is meeting with her business partner and asks you for hard copies of the

worksheets in the workbook. You will add identifying information on each

worksheet.

Group the

Schedule

,

Lookup, Morning Classes,

and

Subtotals

worksheets. Add a

header with the text

Refresh Pilates Studio

to the center header section of

the four grouped worksheets, and use Header & Footer Elements to insert a

footer with the

Sheet Name

in the center footer section. Switch to Normal view

if necessary, and ungroup the worksheets.

Your workbook should look like the Final Figures below. Save your changes, close the

workbook, and then exit Excel. Follow the directions on the SAM website to submit your

completed project.

Final Figure 1: Schedule Worksheet

Final Figure 2: Lookup Worksheet

Final Figure 3: Morning Classes Worksheet

Final Figure 4: Subtotals Worksheet