Spreadsheets

Mr Arthur

Aims of the Lesson 1

Today’s Lesson1. Note taking

1. What is a Spreadsheet

2. Columns/Rows

3. Simple Formula

4. Complex Formula

2. Complete Credit Task

3. Complete Red Folder

What is a Spreadsheet?

A spreadsheet is an example of a GPP that is used to help you process, store and calculate numbers.

A B C D E F1 The 6 Times Table23 6 x 1 = 64 6 x 2 = 125 6 x 3 = 186 6 x 4 = 24

Columns/Rows

Columns Boxes that go vertically,

for example, column A

Rows Boxes that go

horizontally, for example, Row 1

Columns/Rows/Cell Reference

A B C D E F1 The 6 Times Table23 6 x 1 = 64 6 x 2 = 125 6 x 3 = 186 6 x 4 = 24

Cell E3

Cell A1

Columns

Rows

Simple Formula

Simple Formula

Addition =A1+A2

Subtraction =A1-A2

Multiplication =A1*A2

Division =A1/A2

Complex Formula Complex Formula

Adding a list of numbers = sum(A1:A10)

Calculating Averages = average(A1:A10)

Finding the largest = max(A1:A10)

Finding the smallest = min(A1:A10)

Making Decisions =if(A1>65,”OAP”,”young thing”)

What is a spreadsheet used for?

All types of calculations

TuckshopMars Bars Coke Crisps

Monday 32 44 57Tuesday 12 22 23Wednesday 56 76 73Thursday 23 19 34Friday 16 20 12

Total Items 139 181 199

Unit Cost £0.34 £0.50 £0.30

Cash Total £47.26 £90.50 £59.70

A B C D12345678910111213

TuckshopMars Bars Coke Crisps

Monday 32 44 57Tuesday 12 22 23Wednesday 56 76 73Thursday 23 19 34Friday 16 20 12

Total Items =SUM(B3:B7) =SUM(C3:C7) =SUM(D3:D7)

Unit Cost 0.34 0.5 0.3

Cash Total =B9*B11 =C9*C11 =D9*D11

A B C D

•When data changes results are usually re-calculated

Formulae

Always starts with an equals sign For example =A3 + B9

Symbols used:+ Add- Subtract* Multiply/ Divide

Formulae is usually re-calculated when the number in a cell is changed

SUM

SUM is a function that spreadsheets use to add up a list of numbers to save typing time.

For example : =SUM (A2:A8) instead of:

= A2 + A3 +A4 + A5 + A6 + A7 + A8

What is the sum equivalent of:

= B7 + C7 + D7 + E7 + F7 + G7

Automatic/Manual Calculation

Automatic – spreadsheet will calculate a formula automatically. Most common

Manual – The result of the formula will not be calculated automatically.

Insert row/column Sometimes you need to add an

extra row or column to your spreadsheet to make room for more numbers or formulae.

A new column will have to be added if the tuck-shop decide to start selling Snickers.

The new column will be inserted in-between columns B and C.

TuckshopMars Bars Coke Crisps

Monday 32 44 57Tuesday 12 22 23Wednesday 56 76 73Thursday 23 19 34Friday 16 20 12

Total Items 139 181 199

Unit Cost £0.34 £0.50 £0.30

Cash Total £47.26 £90.50 £59.70

A B C D12345678910111213

Insert a new column

Charting

Why would you want to create a chart? Eye-catching Easier to understand at a

glance Concise

Bar ChartEarnings

£0.00

£100.00

£200.00

Eilidh Calum Bill

Name

Ea

rnin

gs

Line graph

Earnings

£0.00

£100.00

£200.00

Eilidh Calum Bill

Name

Earn

ings

Earnings

Eilidh

Calum

Bill

Pie Chart

Complex Formulae

Average e.g.. =Average(B2:B10) What will this formula work out?

Max e.g. = Max(B12:B67) What will this formula work out?

Min e.g. =Min(B2:B10) What will this formula work out?

Replication

What does it mean? Copying - formulae can be copied into different

cells to save time

•Relative referencing - formula is changed to match the line that it is on

Name Hours worked Hourly rate EarningsEilidh 37 £4.50 £166.50Calum 25 £4.00 £100.00Bill 30 £6.25 £187.50

A B C D1234

=B2*C2=B3*C3=B4*C4

Formatting

TuckshopMars Bars Coke Crisps

Monday 32 44 57Tuesday 12 22 23Wednesday 56 76 73Thursday 23 19 34Friday 16 20 12

Total Items 139 181 199

Unit Cost £0.34 £0.50 £0.30

Cash Total £47.26 £90.50 £59.70

CentredLeft

aligned

Right aligned

Column width changed

Cell attributes - 2 decimal places and formatted to show currency

Cell Protection

What is cell protection? Locking a cell so that the contents can’t be changed

E.g. locking the formulas in C4 and C5 so that the user can’t accidentally change them.

Milk £1.00

Customer No of Pints CostSmith 5 £5.00Jones 3 £3.00

A B C12345

Formulae using IF (Credit)

Spreadsheets uses an IF formula to decide what to put in a cell depending on what is in another cell

For example: =IF(B2>50, “PASS”, “FAIL”)

Pupil Prelim Pass or FailDavid 60 PASSEllen 45 FAILRhona 98 PASS

A B C1

2

3

4

Relative & Absolute Referencing (Credit)

What is relative referencing?- When a formula is replicated the cell reference is

changed to suit the line or column that it is in

=IF(B4>49,"PASS","FAIL")=IF(B5>49,"PASS","FAIL")=IF(B6>49,"PASS","FAIL")

Absolute Referencing (Credit) What is absolute referencing?

When a formula is replicated the cell reference is not changed - it stays exactly the same

C1 always stays the same, as it uses the $ signs

Milk 1

Customer No of Pints CostSmith 5 =$C$1*B4Jones 3 =$C$1*B5

A B C Milk £1.00

Customer No of Pints CostSmith 5 £5.00Jones 3 £3.00

A B C12345

Fully Labelled Charting (Credit) Label for chart series Title x-axis and y-axis labelled Label for each point on a chart showing relation to data

Pocket Money Spent

1.2 1.2 1.2

31.2

1.2

21.2

1.2 1.2

0

5

10

15

20

25

30

35

1

week

wk1

wk2

wk3

wk4

wk5

wk6

wk7

wk8