Statistical Functions and Formulas: Apply It 1

  • Define functions in Microsoft Excel
  • Use lookup functions and formulas
  • Use logical functions and formulas
  • Use financial functions and formulas

Open the data file and copy it into Excel. You should have two worksheets of data. Save the workbook as LastName.Functions.xlsx replacing “Last Name” with your last name.

Activity 1: Customer Orders

You work for an online retail company and need to match customer orders with their shipping details. The data includes order numbers, customer names, product IDs, and shipping information.

Directions:

  1. Find the worksheet titled “Lookup”
  2. Insert three columns to the left of the column containing IDs
  3. In cell A1 enter “Lookup Name” and in cell B1 enter “Carrier”
  4. Now we want to create a VLOOKUP formula that scans the data for a given name, then outputs the shipping carrier of that customer. In cell B2 enter the function =VLOOKUP(A2,E2:H805,4,FALSE)

When you enter a customer’s name in A1, their shipping carrier will appear in B2.

Activity 2: Employee Performance

You need to evaluate employee performance based on sales targets and attendance. The data includes employee IDs, names, monthly sales, and attendance rates.

In sales, sales growth is calculated as the percent increase or decrease in sales over a given time period. Conversion rate is the percent of sales (closed deals) out of leads (potential sales).

Directions:

  1. Find the worksheet titled “Logical”
  2. Calculate sales growth:
    1. Insert a new column after “Previous month’s sales” and title the column “Sales growth (%)” in cell E1.
    2. Use the formula =(C2-D2)/D2 to calculate percent growth.
    3. Use the number formatting tools to convert Column E to percentages.
  3. Calculate conversion rate:
    1. Enter “conversion rate (%)” in cell H1.
    2. Use the formula G2/F2 to find the percent of leads converted to sales.
    3. Use the number formatting tools to convert Column H to percentages.
  4. Determine the performance rating:
    1. Enter “Performance Rating” in cell I1.
    2. Use the formula =IF(AND(C2>=5000,E2>=.10,H2>=.20),”Excellent”,IF(AND(C2>=3500,E2>=.05,H2>=.15),”Good”,IF(OR(C2>=3000,E2>=0,H2>=.10),”Satisfactory”,”Needs Improvement”)