CS 105

Midterm 1: Second Chance

Due: Tuesday, March 31 (8:00pm)
On the course website

MP4: First Excel MP

Due: Tomorrow, March 17 (8:00pm)
On the course website

Cell Range

In Excel, we can specify the range of cells in a rectangular region by using top-left:bottom-right.

A1:A10 // Selects 10 cells

B1:D3  // Selects 9 cells (3x3)

E5:A1  // Incorrect (though Excel
       // auto-corrects to A1:E5)
       // ...will never see in lecture

SUM

Finds the sum of input with of data type Number

=SUM(number1, [number2], ...)

=SUM(A4:A12)
=SUM(A1:D8)

Data Types

Excel has only five data types (plus special values)

1. Number
2. String
3. Boolean
4. Date
5. Currency
6. Special Values: Blank, Error

Strings

In Excel, you can force a value to be a string by starting the value with a '

You can join two Strings together in a formula with &

="Hello" & "World"  // HelloWorld
=A1 & A2
=B1 & " Points"     // 4 Points

SUM

Finds the sum of input with of data type Number

If the value is type String, the cell is ignored (even if the String is a number!)

SUM / COUNT / AVERAGE / __IFs

All six functions work the same way: if the value is type String, the cell is ignored

(If you want to count how many cells are not empty, you can use function COUNTA.)

SUMIFS

Specifies one or more criteria that must be true for the cell to be counted. The range of cells must be equal to the criteria.

=SUMIFS(sum_range, criteria_range1, criteria1)

=SUMIFS(sum_range, criteria_range1, criteria1,
                   [criteria_range2], [criteria2],
                   ...)

Excel

Continue on Excel Spreadsheet