Business Computer Applications II

EXCEL UNIT 2

• Read all the directions before you begin.

• Complete the following instructions over the functions below.  If you are unable to complete one of the sets of instructions, leave it and continue on to the next function.  You may print this webpage if you if you wish.

• Download the Function Reference spreadsheet by clicking the following link.  Save it to your U:/ Drive. Function Reference

• Download the Function Reference Help Sheet.  Right click the link and save it to  your U:/Drive.  Open it in Word.  You may print it if you wish.  Function Reference Help Sheet

• If there is function not available when you go to Insert Function Icon, click cancel and go to tools, add-ins and select the first two items in the list, click OK.  Go back to the Insert Function.  The function that was missing should now be available.

• Open the spreadsheet Loginname Function Reference. 

• Open the worksheet, Work Schedule.

1  In Cell A5, key in =TODAY()

1  In Cell B5, key in =NOW()

1  In Cell H9, click , the Insert Function Icon.

2  Select the Networkdays Function.

3  Key F9 in the start_date argument box.

4  Key G9 in the end_date argument box.

5  In the holidays argument box, select cells A9:A21  Make A9 and A21 absolute references.

6  Copy the function from H9, to cells H10:H20.

1  In Cell B9, key in =WEEKDAY(A9)

2  Copy the formula in cell B9 to cells B10:B21

1  In Cell C8, Key in Day of the Week

2  In Cell C9, key in =CHOOSE(B9, Sunday,Monday,Tuesday, Wednesday,Thursday,Friday,Saturday)

3  Copy the function in cell C9 to C10:C21

1  Go to Cell G5, Use the Insert Function and select the PMT function.

2  Key in A5/12 in the Rate Argument Box.

3  Key in B5 in the Nper Argument Box.

4  Key in -C5 in the PV Argument Box.

5  Key in F5 in the Type Argument Box.  Press OK.

6  Copy the Function from G5 to G6:G9

1  Go to Cell H5, Use the Insert Function and select the PPMT function.

2  Key in A5/12 in the Rate Argument Box.

3  Key in D5  in the Per Argument Box.

4  Key in B5 in the NPER Argument Box.

5  Key in -C5 in the PV Type Argument Box.

6  Key in F5 in the Type Argument Box. 

7  Press OK.

8  Copy the Function from H5 to H6:H9

1  Go to Cell I5, Use the Insert Function and select the IPMT function.

2  Key in A5/12 in the Rate Argument Box.

3  Key in D5  in the Per Argument Box.

4  Key in B5 in the NPER Argument Box.

5  Key in -C5 in the PV Type Argument Box.

6  Key in F5 in the Type Argument Box. 

7  Press OK.

8  Copy the Function from I5 to I6:I9

1  Go to Cell J5, key in a formula that will give you the Payment of the loan.  It should match what is found in the Payment Function Column.

1  Go to Cell K5, Use the Insert Function and select the CUMIPMT function.

2  Key in A5/12 in the Rate Argument Box.

3  Key in B5 in the Nper Argument Box.

4  Key in C5 in the PV Argument Box.

5  Key in D5 in the Start_period Argument Box.

6  Key in E5 in the End_period Argument Box.

7  Key in F5 in the Type Arugment Box. 

8  Press OK.

9  Copy the Function from K5 to K6:K9

FV

1  Go to Cell G15, Use the Insert Function and select the FV function.

2  Key in A15/12 in the Rate Argument Box.

3. Key in B15 in the NPER Argument Box.

4  Key in C15 in the Pmt Argument Box.

5  Press OK.

6.  Copy the function in cell G15 to Cell G16.

7.  Go to Cell G16.

8.  Press F2.

9.  Drag the purple range finder box from cell C16 to D16.  C16 should not be selected.

10. Press Enter.

11. Go to Cell G16.  Press Insert Function.  Check the wizard.

12. While in the wizard, delete D16 from the PMT box and key in D16 in the PV box.

1  Go to Cell D21, Use the Insert Function and select the SLN function.

2.  Key in A21 in the Cost Argument Box.

3.  Key in B21 in the Salvage Argument Box.

4.  Key in C21 in the Life Argument Box.

5.  Press OK.

1  Go to Cell C70, Use the Insert Function and select the COUNT function.

2.  Key in C5:C68 in the Value1 Argument Box.

3.  Press OK.

1  Go to Cell C71, Use the Insert Function and select the COUNTIF function.

2.  Key in C5:C68 in the Range Argument Box.

3.  Key in >50 in the Criteria Argument Box.

3.  Press OK.

1.  Go to Cell C72, Use the Insert Function and select the ROUNDUP function.

2.  Key in AVERAGE(C5:C68) in the Number Argument Box.

3.  Key in 0 in the Num_Digits Argument Box.

4.  Press OK.

1.  Go to Cell C73, Use the Insert Function and select the ROUNDDOWN function.

2.  Key in AVERAGE(C5:C68) in the Number Argument Box.

3.  Key in 0 in the Num_Digits Argument Box.

4.  Press OK.

1.  Go to Cell E5, Use the Insert Function and select the RANK function.

2.  Key in C5 in the Number Argument Box.

3.  Key in $C$5:$C$68 in the Ref Argument Box.

4.  Press OK.

5.  Copy the function in Cell E5 to Cells E6:E68.

1.  Go to Cell h5, Use the Insert Function and select the PERCENTILE function.

2.  Key in $C$5:$C$68 in the Array Argument Box.

3.  Key in G5 in the K Argument Box.

4.  Press OK.

1.  Go to Cell I5, Use the Insert Function and select the PERCENTRANK Function.

2.  Key in $C$5:$C$68 the Array Argument Box.

3.  Key in C5 in the X Argument Box.

4.  Press OK.

5.  Copy the function in Cell I5 to Cells I6:I68).

6. Compare the Percent Rank with the Percentile Function.

1.  Go to J4.  Key "Vested".

2.  Go to Cell J5. 

3.  Nest the PERCENTILE function in an IF function to find out if an employee is old enough to be vested in the companies pension plan.  If the employee is in the 80th percentile, they are old enough to be vested.  If not they are ineligible.

CONVERT

1.  Go to Cell D5, Use the Insert Function and select the CONVERT function.

2.  Key in C5 in the Number Argument Box.

3.  Key "mi" in From_unit box Argument Box.

4.  Key "m" in the To_unit box.

5.  Click OK.

6.  Result is display in meters.  Divide the function by 1000 to convert it to kilometers.

7.   Copy the function in Cell D5 to Cells D6:D9

CONVERT Continued

1.  Go to Cell C10, Use the Insert Function and select the CONVERT function.

2.  Key in D10 in the Number Argument Box.

3.  Key "m" in From_unit box Argument Box.

4.  Key "mi" in the To_unit box.

5.  Click OK.

6.  Multiply the function by 1000 to convert it from kilometers to miles.

7.   Copy the function in Cell C10 to Cells C11:C12

VLOOKUP

1  Go to Cell B5, Use the Insert Function and select the VLOOKUP function.

2. Key in 11-A-1406 in Cell A5.

3  Key in $A$5 in the LOOKUP_value Argument Box.

4. Press Tab.

5  Select Cells $A$13:$D$16 

6.  Press Tab.

7.  Key 2 in the Col_index_num box.

8.  Key FALSE in the Range_lookup box.

9.  Press OK.

10. Copy the Function in cell B5 to cells C5 and D5.

11. In cell C5, Key 3 in the Col_index_num box. (or press F2 to edit Cell C5)

12. In cell D5, Key 4 in the Col_index_num box or F2.

HLOOKUP

1  Go to Cell H6, Use the Insert Function and select the HLOOKUP function.

2. Key in 11-A-1406 in Cell H5.

3  Key in $H$5 in the LOOKUP_value Argument Box.

4. Press Tab.

5.  Select Cells $G$12:$K$15 

6.  Press Tab.

7.  Key 2 in the Row_index_num box.

8.  Key FALSE in the Range_lookup box.

9.  Press OK.

10. Copy the Function in cell h5 to cells H7 and H8.

11. In cell H7, Key 3 in the Col_index_num box or F2.

12. In cell H8, Key 4 in the Col_index_num box or F2.

1  Go to Cell D5, Use the Insert Function and select the CONCATENATE function.

2  Key in B5 in the Text1 Argument Box

3. Key in " " in the Text2 Argument Box

4. Key in A5 in the Text3 Argument Box

5. Click OK.

5. Copy the Function in cell D5 to cells D6:D68.

1.  Go to Cell E5, Use the Insert Function and select the PROPER function.

2.  Key in D5 in the Text Argument Box.

3.  Click OK.

4.  Copy the Function from Cell E5 to cells E6:E68.

1.  Go to Cell F5, Use the Insert Function and select the UPPER function.

2.  Key in D5 in the Text Argument Box.

3.  Click OK.

4.  Copy the Function from Cell F5  to cells F6:F68.