Introduction

In lecture this week, we have started to take a look at Microsoft Excel and spreadsheets. In lecture, you have learned the first set of functions: SUM, SUMIFS, COUNT, COUNTIFS, AVERAGE, and AVERAGEIFS. You will use these functions to complete the lab today.

Complete the Lab

Download the Lab

Download the Excel (.xlsx) file for Lab 8

Complete the lab wihtin Excel

The Excel file you just downloaded contains ten questions as part of the spreadsheet. The questions are clearly marked throughout the spreadsheet. You should find a total of 10 questions.

Functions and Concepts Used

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

Arithmetic Operations

Just like JavaScript, Excel allows the use of basic arithmetic operations in formulas such as +, -, * (for multiplication), and / (for division).

The following formulas are all valid: =4 * 2, =(10 + 3)/2, and =6-3+2.

Simple Arithmetic Functions

The SUM, COUNT and AVERAGE functions each take a comma-separated list or a range of cells as their parameter(s) and calculates the sum of their values (SUM), the number of values (COUNT), or the average of their values (AVERAGE).

AVERAGE is not explicitly necessary, as the formula =SUM(A1:A10)/COUNT(A1:A10) will result in the same value as the formula =AVERAGE(A1:A10).

The following formulas are all valid:

• =SUM(A1, A2, A3, A4, A5), calculates the sum of cells A1 - A5
• =SUM(A1:A5), also calculates the sum of cells A1 - A5
• =SUM(A1:A5)/COUNT(A1:A5), finds the average of cells A1 - A5
• =AVERAGE(A1:A5), also finds the average of cells A1 - A5

Conditional Arithmetic Functions

The SUMIFS, COUNTIFS and AVERAGEIFS functions are similar to their simple counterparts but only perform their action if a conditional is true. Due to the presence of the conditional, these functions take in an extra function arguments:

• =SUMIFS( sum_range, criteria_range1, criteria1 )
• =COUNTIFS( criteria_range1, criteria1 )
• =AVERAGEIFS( average_range, criteria_range1, criteria1 )

In the SUM and AVERAGE versions, one range is given for the range of cells that you want to compute a sum or average (sum_range or average_range). Using the example sheet displayed to the right, the following formulas are all valid:

• =SUMIFS( B2:B8, A2:A8, "Monday" ), returns the sum of the value in B2:B8 when the value in A2:A8 is equal to Monday.
  • The result is 10, since A2 and A5 are the only two values that meet the critera1 and the values in B2 and B5 are 5 and 5 (5 + 5 = 10).
• =AVERAGEIFS( B2:B8, A2:A8, "Tuesday" ), find the average of the Tuesday cells. The result is 4 because (7 + 1 = 8, 8 / 2 = 4).

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