Simnet hcc

Excel 2013 Chapter 2 Working with Formulas and Functions Last Updated: 2/23/15 Page 1

USING MICROSOFT EXCEL 2013 Guided Project 2-2

Guided Project 2-2 Hamilton Civic Center (HCC) is a nonprofit community fitness center with an indoor pool, sauna, indoor track, project room, racquetball courts, meeting rooms, and a gift shop. HCC provides training and sponsors athletic and social events for adults and children. Tara Strachan is HCC’s administrator for the gift shop. She has been asked to create an invoice template that links to the gift shop’s product inventory spreadsheet.

Skills Covered in This Project

 Create and copy basic formulas.

 Apply mathematical order of operations.

 Use relative, absolute, and mixed cell references.

 Apply the VLOOKUP function.

 Apply the IF function.

 Apply the SUMIF function.

 Apply the TODAY function.

1. Open the HCCInvoice-02.xlsx start file. The file will be renamed automatically to include your name.

Change the project file name if directed to do so by your instructor, and save it.

NOTE: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the

top of the workbook so you can modify it.

2. Enter a VLOOKUP function with 3D references on the Invoice sheet tab.

a. Click cell C15 on the Invoice sheet tab.

b. Click the Insert Function button

[Formulas tab, Function Library

group] to open the Insert Function

dialog box.

c. Type VLOOKUP in the Search for a

function box and click Go.

d. Select the VLOOKUP function from

the list and click OK to open the

Function Arguments dialog

box (Figure 2-67).

e. Click the Lookup_value argument

text box and click cell B15 (Item #).

f. Click the Table_array argument

text box and click the Gift Shop

Products sheet tab.

g. Select the cell range A4:F18.

h. Press F4 to apply absolute cell reference symbols to the A4:F18 range.

i. Click the Col_index_num argument box and type 2, the column number.

j. Omit the Range_lookup argument; if you include a Range_lookup argument, it will default to TRUE

and find the closest match instead of the exact match you want to find.

k. Click OK. The formula syntax should be =VLOOKUP(B15,'Gift Shop Products'!$A$4:$F$18,2). The result is

“Shorts” and displays in C15 on the Invoice tab.

3. Copy the VLOOKUP formula.

a. Select cell C15.

b. Place your pointer over the Fill Handle.

c. Click and drag the fill pointer through cells C16:C17.

2-67 VLOOKUP Function Arguments dialog box

Step 1

Download start file

Excel 2013 Chapter 2 Working with Formulas and Functions Last Updated: 2/23/15 Page 2

USING MICROSOFT EXCEL 2013 Guided Project 2-2

4. Enter another VLOOKUP function with 3D

references on the Invoice sheet tab to

insert unit price.

a. Click cell F15 on the Invoice sheet tab.

b. Click the Recently Used button

[Formulas tab, Function Library group].

c. Select VLOOKUP to open the Function

Arguments dialog box (Figure 2-68).

d. Click the Lookup_value argument text

box and click cell B15.

e. Click the Table_array argument text

box and click the Gift Shop Products

sheet tab.

f. Select the cell range A4:F18 and press

F4 to apply absolute cell reference

symbols to the range.

g. Type 5 in the Col_index_num argument text box.

h. Click OK. Verify that the formula syntax is =VLOOKUP(B15,'Gift Shop Products'!$A$4:$F$18,5). The result

of the calculation is $6.875 and appears in cell F15 on the Invoice tab.

5. Copy the VLOOKUP formula.

a. Select cell F15.

b. Drag the Fill Handle through cells

F16:F17.

6. Create an IF function to determine if

the product is backordered.

a. Click the Invoice sheet tab.

b. Select cell E15.

c. Click the Insert Function button

[Formulas tab, Function Library

group].

d. Type IF in the Search for a function

box and click Go.

e. Select the IF function and click OK

to open the Function Arguments dialog

box (Figure 2-69).

f. Click the Logical_test argument text box and click cell A15.

g. Type <=.

h. Click the Gift Shop Products sheet and select C5. The argument syntax is A15<= 'Gift Shop

Products'!C5.

i. Type "No" in the Value_if_true argument box.

j. Type "Yes" in the Value_if_false argument box.

k. Click OK. Verify that the formula syntax is = IF(A15<='Gift Shop Products'!C5,"No", "Yes"). The result of

the calculation is "No" and displays in E15.

l. Press Enter.

m. Copy the formula in E15 to E16:E17.

7. Create a multiplication formula to calculate the sales total for shorts and use the Fill Handle to copy the

formula.

a. Click cell G15.

b. Type =.

c. Select A15, type * (multiplication sign), and select F15.

d. Press Enter. Verify that the formula syntax is = A15*F15. The result should be $13.75.

Excel 2013 Chapter 2 Working with Formulas and Functions Last Updated: 2/23/15 Page 3

USING MICROSOFT EXCEL 2013 Guided Project 2-2

e. Select cell G15.

f. Drag the Fill Handle through cells G16:G17. The totals for G16 and G17 should be $10.3125

and $39.875.

g. Apply the Accounting numeric formatting to cells F15:G17.

8. Create a SUMIF formula to total the line items that are not backordered.

a. Click cell G31 and insert the SUMIF function.

b. Type E15:E17 in the Range argument box.

c. Type "No" in the Criteria argument box.

d. Click the Sum_range argument text box and select G15:G17.

e. Click OK. Verify that the formula syntax is =SUMIF(E15:E17,"No",G15:G17). The result in G31 should be

$63.9375.

f. Apply the Accounting formatting to G31.

9. Create a formula with multiple operators and apply the mathematical order of precedence.

a. Click cell G33.

b. Type =G31+(G31*G32) and press Enter. Verify that the formula syntax is =G31+(G31*G32). The result

in G33 should be $68.0934375.

c. Apply the Accounting formatting to G33.

10. Use auditing tools to review a formula.

a. Select cell G31.

b. Click the Show Formulas button and the Trace Precedents button [Formulas tab, Formula

Auditing group].

c. Review the formulas.

d. Click the Remove Arrows

button [Formulas tab, Formula

Auditing group].

e. Click the Show Formulas button

[Formulas tab, Formula Auditing

group] to return to results view.

11. Insert a date that will update every

time you open the file.

a. Click cell E5.

b. Type =TODAY() and press Enter.

c. Format the date to Short Date.

12. Save and close the workbook

(Figure 2-70).

13. Upload and save your project file.

14. Submit project for grading. Step 3

Grade my Project

Step 2

Upload & Save

2-70 Excel 2-2 completed