Introduction

In the lab today, you will continue to apply the SUM, SUMIFS, AVERAGE, AVERAGEIFS, COUNT, COUNTIFS, along with cell ranges, IF statements, MAX/MIN, and using absolute cell references to lock rows/columns.

Complete the Lab

Download the Lab

Download the Excel (.xlsx) file for Lab 9

Complete the lab wihtin Excel

The Excel file you just downloaded contains eleven questions as part of the spreadsheet. The questions are clearly marked throughout the spreadsheet.

About the Data Source

The data in the lab is historical weather data about Champaign-Urbana. In addition to standard weather data in Columns B:J:

• Column K contains the number of days in a given month where the temperature was at or above 90 degrees at some point in the day.
• Column L contains the number of days in a given month where the temperature was at or below 32 degrees at some point in the day.
• Column M contains the number of days in a given month with snow. (This is different than Column E, "Rain Days" -- the number of days it rained. However, it is possible for rain and snow to both occur in one day.)

Functions and Concepts Used

To complete this lab, you will need to use several functions and concepts in Excel:

Arithmetic Operations and Functions

An overview of arithmetic operations and functions are found in last week's lab. This includes +, -, * (for multiplication), / (for division), SUM, SUMIFS, COUNT, COUNTIFS, AVERAGE, and AVERAGEIFS.

Maximum and Minimum Values

Excel provides the MAX and MIN functions that reutrns the maximum and minimum values in a range of cells. If the cell range A1:A5 contains the values 2, 5, 8, 11, and 1:

• =MIN(A1:A5) returns 1, the minimum (smallest) value in the range.
• =MAX(A1:A5) returns 11, the maximum (largest) value in the range.

Submission

To submit your lab, you must submit your completed Excel file to the CS 105 website. Ensure you save your file before uploading it.

Turn In / Submit your Lab