1.3.3:Introduction to spreadsheets.

Refer to the spreadsheet below shown in the earlier animation.

1)

How many cells are shown?

CheckShow answer

2)

How many cells contain data? Do not count the label in cell D1.

CheckShow answer

3)

Cell B1 contains the word "Weight". What does cell A2 contain?

CheckShow answer

4)

Which cell contains the number 12?

CheckShow answer

Feedback?

Autofill

Autofillis a useful spreadsheet feature that recognizes a pattern and fills additional pattern values.

Spreadsheet Practice 1.3.1: Autofill.

The following table, captured in Google Sheets, specifies the start of a pattern.

Autofill cells: Enter the first few values of a pattern. Highlight the cells. Drag the green box by the fill handle, denoted by the arrow in the image below, to the desired number of cells.

Patterns may include text, numbers, or dates, among others.

If a pattern is not recognized, the autofill feature repeats the pattern provided.

Link to spreadsheet

Feedback?

PARTICIPATIONACTIVITY

1.3.4:Autofill.

1)

For the spreadsheet below, selecting cells A1:A3, then dragging the selection down, fills cells A4:A5 with _____.

7, 8

8, 10

2)

For the spreadsheet below, selecting cells A1:C1, then dragging the selection right, fills the cells to the right with _____.

Monday, Tuesday

Thursday, Friday

3)

Selecting cells A1:A3, then dragging the selection down will fill subsequent cells with _____.

Alfa Romeo, MINI Cooper, Isetta

Mivalino, Mopetta

Feedback?

Spreadsheet formulas

An important spreadsheet feature allows a user to type aformulain a cell to compute that cell's value based on other cells' values. A formula begins with = followed by a math expression using operators like +, -, *, /, and parentheses (). Another cell's value can be included using that cell's row number and column letter. Ex: = 3 * A1.

PARTICIPATIONACTIVITY

1.3.5:Spreadsheets: Formulas.

Start

2x speed

f

x

1

2

3

4

5

A

B

C

D

*

8

Formulas in column D

Bill

20

30

15% tip

Total

formula bar

=

=

0.15*A2

0.15*A2

3

Captions

keyboard_arrow_down

Feedback?

PARTICIPATIONACTIVITY

1.3.6:Spreadsheets: Entering formulas.

Type 10 in A1, 20 in B1, and 30 in C1. In D1, type: = A1 + B1 + C1. The cell should show 60.

Next, update A1 to be 15; D1 should automatically change to 65.

Finally, click on D1, and modify the formula to use * instead of +.

[

--

] :

-	A	B	C	D	E	F
1
2
3
4
5
6
7
8
9
10

Feedback?

PARTICIPATIONACTIVITY

1.3.7:Entering formulas.

Refer to the spreadsheet below shown in the earlier animation.

1)

What value appears if the formula = A2 + B2 is entered in C2?

CheckShow answer

2)

What formula should be used to find a 15% tip on the $30 bill in cell A3?

CheckShow answer

Feedback?

Spreadsheet functions

A spreadsheetfunctionis a predefined formula that supports common tasks such as computing the average, minimum, or maximum of a group of cells. Spreadsheets commonly support a number of functions encompassing engineering, statistics, finance, and other applications. Users can also define custom functions, which is an advanced topic that is not discussed in this section.

Table 1.3.1: Common functions.

Function name	Description	Function syntax
ABS	Returns the absolute value of a number.	ABS(value)
COUNT	Returns a count of the number of numeric values in a dataset.	COUNT(value1, [value2, ...])
MAX	Returns the maximum value in a numeric dataset.	MAX(value1, [value2, ...])
MIN	Returns the minimum value in a numeric dataset.	MIN(value1, [value2, ...])
SUM	Returns the sum of a series of numbers and/or cells.	SUM(value1, [value2, ...])

Feedback?

Above, thefunction syntaxdefines how the function is used, and specifies the function's name and accepted arguments. Afunction's argumentsare surrounded by parentheses and specify the data that the function operates on. Arguments may be numbers, cells, a range of cells, or a combination thereof. The [ ] arguments are optional.

To call a function in a spreadsheet, = is followed by the function's name and then arguments separated by commas. Ex: =SUM(A1, A2, A3) calculates sum of cells A1, A2, and A3. Therange operator(:) defines a reference to a group of cells. Ex: =SUM(A1:A4, B10) calculates the sum of cells A1, A2, A3, A4, and B10.

PARTICIPATIONACTIVITY

1.3.8:Functions.

Complete each formula.

1)

Determine the sum of cells A1 and C3.

=SUM()

CheckShow answer

2)

Determine the sum of all rows from A5 and A27 using the range operator.

=SUM()

CheckShow answer

3)

Determine the largest value in cells D2 and D3.

=

CheckShow answer

4)

Determine the number of numeric values from rows F5 to F21, cell G12, and cell H9.

=

CheckShow answer

Feedback?

Spreadsheet functions

Table 1.3.2: Spreadsheet-functions: Common functions.

Function name	Description	Function syntax
ABS	Returns the absolute value of a number.	ABS(value)
COUNT	Returns a count of the number of numeric values in a dataset.	COUNT(value1, [value2, ...])
MAX	Returns the maximum value in a numeric dataset.	MAX(value1, [value2, ...])
MIN	Returns the minimum value in a numeric dataset.	MIN(value1, [value2, ...])
SUM	Returns the sum of a series of numbers and/or cells.	SUM(value1, [value2, ...])

Feedback?

Challenge activities

CHALLENGEACTIVITY

1.3.1:Spreadsheets: Formulas and functions.

359608.2310732.qx3zqy7

Start

[

--

] :

-	A	B	C	D	E	F
1
2
3
4
5
6
7
8
9
10

1

2

3

4

5

Check

Next

1

2

3

4

5

Feedback?

CHALLENGEACTIVITY

1.3.2:Tables and spreadsheets.

359608.2310732.qx3zqy7

Start

What value is in cell D1? If no value, type: Empty

	A	B	C	D
1	26	49	35
2	26	47	24
3	22	4	22
4	47	48	15
5

1

2

3

4

5

Check

Next

1

2

3

4

5