Lab9 (CS 131)

Microsoft Excel – part 3

Activity Sheet

Name______Score______/10

Directions: Place this sheet on top of all completed activities and staple them. Number any printed documents with the activity number listed below. Hand in all completed activities at the end of the lab period to your lab instructor. Point value shown after each activity.

Constants

1)Open up the “lab9” workbook, linked on the site, and go to the worksheet “part 1”

2)Fill in F10 with the numbers at the end of your Pitt username—I’d use 51 for mim51

3)Notice how someone did not know about constants and used auto-fill from set 1 to figure out the averages for the other sets.

4)Fix the average Function in Set1 to treat the number of entries as a constant value. Do not use the Average function!

5)Print a screenshot of cell C12

6)Use Auto-fill to correctly find the average of the other cells.

7)Print out the corrected worksheet.

Some Chapter 11 Objectives

8) Navigate to sheet “part 2”

9)Using SUM, AVERAGE, MEDIAN, MIN, and MAX (refer to Objective 1)

  1. Fill in cell C7 with the numbers at the end of your Pitt username
  2. Fill in Cells C8 through C12 with the corresponding functions.
  3. Print a screenshot of Cell C11.
  4. Print a screenshot of Cell C12

10)COUNTIF (refer to Objective 2, 11.4)

  1. Pick red, green or blue. Type that in cell E40. Use the COUNTIF function to count the number of cells from F1 to F39 which contain that color.

11)IF (refer to Objective 2, 11.5)

  1. Now we want to see if the color you picked makes up a majority of the cells. Since there are 39 cells, 20 makes up a majority. In Cell F41, fix the IF function by adding a logical test. The logical argument will be whether F41 is greater than or equal to 20.
  2. Print a screenshot of Cell F41
  3. Try changing the COUNTIF in F40 to change what F41 displays.

12)Date (refer to Objective 3)

  1. In Cell A1, use the NOW function to display the date

13)Conditional Formatting (refer to Objective 2, 11.6)

  1. Use conditional formatting for cells N4 through N31 to visually display the quantity

14)Create an Excel Table (refer to Objective 4, 11.10)

  1. Convert Cells K4 through N31 into an Excel Table
  2. Print a screenshot showing the Excel Table, sorted by Price
  3. Print a screenshot showing the Excel Table, sorted by Color

15)Staple and hand in the lab by the end of class.