Excel: Formulas & Functions Exercises

Cell Referencing

1.  Open formula_examples.xls and select referencing tab.

2.  Use AutoFill to copy the formulas from E2, F2 and G2 to E4, F4 and G4.

3.  Undo.

4.  Make formulas in E2, F2 and G2 mixed references.

5.  Use AutoFill to copy the formulas from E2, F2 and G2 to E3, F3 and G3.

Name a Constant, Cell & Range of Cells

1.  Open formula_examples.xls and select naming worksheet.

2.  Define a constant FICA as 0.0765.

§  Insert > Name > Define

3.  Calculate the FICA tax in cell E2 using the named constant.

4.  Select cell B7.

5.  Type federal in the Name Box.

6.  Name cell B8 state.

7.  Calculate the Federal and State taxes in cells F2 and G2 using the names.

8.  Highlight cells E2, F2 and G2 and name the range deductions.

9.  In cell H2, calculate the total deductions by using the named range created in Step 8.

Natural Language Formulas

1.  Open formula_examples.xls and select nat_lang worksheet.

2.  Use labels to sum the RVS and RGC campuses.

3.  Use labels to sum May.

4.  Copy the Monthly Totals May formula through August.

Date

1.  Open formulas_examples.xls and select Date worksheet.

2.  Enter Now function in cell E1.

3.  Format the cell to show the date without the time.

4.  Use the Date function to make Marcia Cranston’s review date 1 year from the date hired. Use the Date function to make Thomas Perkins review date 6 months from the date hired.

5.  Use the Year function to display the current year in cell E1.

6.  Use the DATEDIF function to calculate the Employment Length in years for Marcia Cranston and in months for Thomas Perkins.

IF

1.  Open formula_examples.xls and select IF1 worksheet.

2.  Use the IF function to determine the Hours Remaining for Greta Harper and Charlie Marshall. If the individual has less than 12 hours, have the cell display remaining hours. If the individual has 12 or more hours have the cell display the word “complete”.

3.  Select IF2 worksheet.

4.  Correct the Div error using the IF function. (Hint: Have the cell display nothing if cell B10 is equal to 0).

PMT

1.  Open formula_examples.xls and select PMT worksheet.

2.  In cell B3, use the PMT function to calculate the monthly payment for a loan with a 7.5% yearly rate of interest, 5-year term and total amount of $22,000.

3.  In cell B6, use the PMT function to calculate the annual interest earned on an investment with an interest rate of 5.25%(interest calculated annually), a 5-year term, and an investment of $10,000.

VLOOKUP

1.  Open formula_examples.xls and select Lookup worksheet.

2.  Use the VLOOKUP function to automatically fill the addresses column based on the campus listed in the Campus column. (See below for steps to create this formula.)

Formulas for the Exercises

Formulas for Name a Constant, Cell & Range of Cells

Step 3 =D2*FICA

Step 7 =D2*federal and =D2*state

Step 9 =SUM(deductions)

Formulas for Natural Language Formulas

Step 2 =SUM(RVS) and =sum(RGC)

Step 3 =SUM(May)

Formulas for Date

Step 2 =NOW()

Step 4 =DATE(YEAR(B4)+1,MONTH(B4),DAY(B4)) and =DATE(YEAR(B5),MONTH(B5) +6,DAY(B5))

Step 5 =YEAR(NOW())

Step 6 =DATEDIF(B4,NOW(),"y") and =DATEDIF(B5,NOW(),"m")

Formulas for IF

Step 2 =IF(C15>=12,"complete",12-C15) and =IF(C26>=12,"complete",12-C26)

Step 4 =IF(B10=0,””,B2/$B$10). By using empty quotes as the true value, the cell will display as if it is empty.

Formulas for PMT

Step 2 =PMT(.075/12,5*12,-22000)

Step 3 =PMT(.0525,5,-10000)

Formulas for VLOOKUP

Steps for creating the VLOOKUP function:

  1. Select cell D3
  2. In the Formula Bar type =VLOOKUP(
  3. Select cell C3 and type a comma ,
  4. Select the campuses worksheet
  5. Highlight cells A2 – C8 and type a comma ,
  6. Type the number 2
  7. Type the end parentheses ) to close the function

=VLOOKUP(C2,campuses!$A$2:$C$8,2)