Standard Grade Computing Studies Unit 7 – Spreadsheet
Name: . . . . . . . . . . . . . . . . .
Class: . . .
Bishopbriggs Academy
Contents: Unit 8 - Spreadsheets SS1 – Using a Spreadsheet Package
3
• What a Spreadsheet is
3
• What can be held in cells
4
• Simple formulae
5
• Homework / Written exercise 1
6
• Foundation Examination type Question
7
SS2 – Making Changes
8
• The changes that can be made to the layout of a spreadsheet
8
• The common built-in functions
9
• Manual and automatic calculation
10
• Cell protection
10
• Homework / written exercise 2
11
• General Examination type Question
12
SS3 – Advanced Features
2
14
• What is cell referencing?
14
• Why do we use IF statements?
15
• Why we use graphs
15
• Homework / written exercise 3
16
• Credit Examination type Question
17
Bishopbriggs Academy - Standard Grade Computing Studies
SS1
Using a Spreadsheet Package
In this chapter you will learn • • •
What a Spreadsheet is What can be held in cells Simple formulae
A spreadsheet is a General Purpose Package which is used mainly to deal with numbers and formulas. It is basically an electronic sheet of squared paper, each square is called a cell. It is usually laid out like this: formula line
Definitions Cell
a location (box) in a spreadsheet where something can be stored. Each cell can be identified by its cell reference. The cell reference is made up of the column letter and row number, i.e. C7, B2
Row
a horizontal line of cells going from left to right. Rows are numbered from 1 upwards.
Column
a vertical row of cells. Columns have their own letter.
Unit 7 – Spreadsheets
3
What can be held in cells? 1.
Text
labels, column headings etc.
2.
Numbers
whole numbers, decimals etc
3.
Formulae
a mathematical operation performed on the contents of other cells.
Description
Unit cost
Quantity
Total
Crisps Soft Drink Coffee
0.3 0.35 0.4
4 6 5
=B3*C3 =B4*C4 =B5*C5
Text
Numbers
Formula (it is the answer you would see here not the formula)
On the Screen this completed spreadsheet would look like this… Description Crisps Soft Drink Coffee
4
Unit cost
Quantity £0.30 £0.35 £0.40
Total 4 6 5
£1.20 £2.10 £2.00
Bishopbriggs Academy - Standard Grade Computing Studies
Entering simple formulae Almost any of the common mathematical ‘sums’ can be performed in a spreadsheet like multiply, divide, add and subtract. The signs are: * + /
Multiply Add Subtract Divide
A formula sets out the calculation that has to be carried out on data. For example: = A4 + A5
this would add the contents of cell A4 and A5 together.
=D6 * D9
this would multiply the contents of cells D6 and D9 together.
Notice that the formula always starts with an equals sign (=). Most spreadsheet packages use the equals(=) sign at the start of a formula. The equals sign must be the very first character in the cell for the spreadsheet to recognise that that cell contains a formula.
Unit 7 – Spreadsheets
5
Homework / Written Exercise 1
1 2 3 4
A Name Joe Mary Fred
B Test1
C Test2 5 6 9
D Total
E
3 1 2
1.
What is the value of the cell in a) A1
2.
Which cell has the value
3.
What formula might you enter in cell D2?
4.
Which two other cells would you expect to contain a formula?
5.
Write down what these formulas would be.
6.
What values would you expect to see in each of those cells?
7.
How do you indicate to a spreadsheet that you are entering a formula?
8.
The title average is entered in cell E1. Write down a formula to calculate Joe’s average, in cell E2.
9.
Now write down the formulas to calculate Fred and Mary’s average marks in cells E3 and E4.
6
b) B2
a) Joe b) 3
c) A4 c) 2
Bishopbriggs Academy - Standard Grade Computing Studies
Foundation Level Exam type question When a customer goes into the Pizza company to place an order, the following information appears on the screen. A Pizza
1 2 3 4 5 6 7 8
Tomato Pineapple Ham Pepperoni
B Price
C Quantity
£3.50 £3.70 £3.90 £4.20
D Cost
2
£7.40
1
£4.20
Total
£11.60
In this case the customer has ordered 2 pineapple pizzas and 1 pepperoni pizza at a total cost of £11.60. (a)
What type of application package is being used?
(b) (i)
Name one cell that contains text.
(ii) Name one cell that contains a value.
(iii) Name one cell that contains a formula
Unit 7 – Spreadsheets
7
SS2 – Making Changes In this chapter you will learn about: • the changes that can be made to the layout of a spreadsheet • the common built-in functions • manual and automatic calculation • cell protection
Changes that can be made to a spreadsheet. Alter the column width – If your label or column heading is too long to fit inside the cell then the column width can be made bigger to fit it in. The column width can also be made smaller to suit the entries in the cell. A good reason for increasing the column width is to make sure that there is enough room to display the formulas properly. Item Chair
Number Unit Cost 4 £ 25.00
Cost + VAT =B2*C2 * 1.175
Alter cell format/attributes – The attributes of a cell dictate how the data is to be displayed. Cell attributes can be changed in the following ways: • numbers can be displayed to any number of decimal places you like • numbers can be set to appear as currency • data can be centred, left aligned or right aligned 3 Dec. Places Currency 1.230 £ 1.20 8
Centred 1
Left 2
Right 3
Bishopbriggs Academy - Standard Grade Computing Studies
Insert rows or columns – This allows you to enter new rows and columns into your spreadsheet to make room for more data or formula.
Common built-in functions A spreadsheet also has a number of built in functions such as SUM and AVERAGE these allow us to perform more complex calculations. The function: =AVERAGE (A1..B3) This would find the average of the cells between A1 and B3. =MAX (A1..A10) This would find the biggest number in the list from A1 to A10 =Min (A1..A10) This formula would find the smallest number in the list from A1 to A10 =SUM (A1..A10) This would add up the contents of cells A1 to A10
Unit 7 – Spreadsheets
9
Automatic and manual calculation: Automatic calculation As soon as you enter your formula into your spreadsheet the software will automatically carry out the calculation. When you change a value in a cell any other cells that are affected by that value are changed (automatically). Manual Calculation This means you have to tell the software to perform the calculation.
Replication Replication simply means copying. If you have lots of similar formulas to enter into cells then they can be copied down a column or across a row. This can save the user a lot of time at the keyboard. Item Sofa Chair Footstool Sideboard Standard Lamp Coffee Table Bookcase
Cell Protection Cell protection can be ‘turned on’ in a spreadsheet to make sure that the contents of a cell are not changed or erased by accident. Cell protection ensures that a cell cannot be edited until the protection is turned off. 10
Bishopbriggs Academy - Standard Grade Computing Studies
Homework / Written Exercise 2 1.
Why is it sometimes necessary to change the width of a column?
2.
What is meant by a cell "attribute"?
3.
State three attributes that can be altered in a cell.
4.
What is meant by a built in function?
5.
Give two examples of a built in function.
6.
What is meant by: a) Inserting a new row b) Deleting an existing column
7.
State two ways you might alter the format of a sheet.
8.
Why do you need to justify labels?
9.
What is meant by 'replicating data'?
10. How can you stop someone changing the contents of a cell by accident?
Unit 7 – Spreadsheets
11
General Level Exam type Question Perfect patios are a company which design and install patios. Jim works as a salesperson for perfect patios. When Jim visits a house, he measures the area for the patio and helps the owners choose a suitable building material. Jim uses a computer to calculate the cost during the visit to the house. (a)
Suggest a suitable type of computer for this task _______________________
(b) Give a reason for your answer. _____________________________ Here is an example of the basic spreadsheet which Jim uses to get estimates for customers. Perfect Patios
Estimate
Item Unit Cost Quantity Item Cost Grey Monoblock 0.2 500 100 Red Monoblock 0.26 130 33.8 Foundation(bag) 1.81 16 28.96 Labour (hours) 8 32 256 Total 418.76
(c)
12
The contents of D4 have been calculated by the computer. Suggest a formula that could have been entered in cell D4.
Bishopbriggs Academy - Standard Grade Computing Studies
(d) The contents of cell D8 have also been calculated by the computer. Suggest a formula that could have been entered in cell D8. (e)
When Jim made the spreadsheet, he did not have to type all the formulas. He was able to replicate some of them. (i) Which cells did he replicate?
(ii) Which cells did it need to be replicated into?
(f)
The customer decides also to include some black monoblock brick. Explain what Jim will have to do to the spreadsheet before he enters the data.
(g)
Jim wants all the costs in column D to appear as money. Explain how Jim will do this.
Unit 7 – Spreadsheets
13
SS 3 Advanced features of a Spreadsheet In this chapter you will learn • • •
What is cell referencing? Why do we use IF statements? Why we use graphs.
As we often need to copy or replicate certain formulae over and over again between cells it is important that we use the correct method of cell referencing. Relative Referencing: the references to cells in formulas change according to the formula’s new position in the sheet when the formula is replicated. By default, all cell references are assumed to be relative. Absolute Referencing: The references to cells in formulas remain the same when the formula is replicated. Absolute references to cells in a formula are usually indicated by including $ signs i.e. $B$2 A 1
B
C
D
VAT=
0.175
3
item
price
VAT
total cost
4
TV
400
=$B$1*B4
=B4+C4
5
video
250
=$B$1*B5
=B5+C5
6
camera
120
=$B$1*B6
=B6+C6
2
In column C, cell B1 is referenced absolutely so it has not changed, but references to the other cells have changed relatively. 14
Bishopbriggs Academy - Standard Grade Computing Studies
Conditions This is a very useful facility of the spreadsheet. It is usually called an ‘IF’ statement and it allows you to make choices, depending on the values placed in other cells. It is often used in spreadsheets to calculate discounts, VAT etc. A 1
item
B
C
D
cost
number sold
total cost
2 3
jacket
50
4
200
4
dress
30
6
162
5
shoes
20
7
126
6
bag
10
5
50
The values in column D are the results of an IF statement in this case… = IF( C3>5, B3 * C3 *0.9, B3 * C3 ) This formula calculates a discount of 10% on sales of more than 5 items.
Graphs Large complicated spreadsheets may contain thousands of numbers arranged in their rows and columns. Most people would find this difficult to understand or to be able to see any trends or patterns in the figures. The figures in a spreadsheet may be turned into a graph. A graph represents the numbers as a picture. This makes the numbers easier for us to understand and to spot any trends or patterns within the numbers.
Unit 7 – Spreadsheets
15
Homework / Written Exercise 3 1.
What are the two ways of referencing cells before replicating in a formula? a)
What is the difference between them?
b)
Give an example of where each might be used.
2.
What is used to represent data in pictorial form?
3.
A teacher uses a spreadsheet to keep a record of a class’ test marks. The record also calculates pupils and class average.
16
a)
Give two reasons why this is a better method of keeping the class records than writing them on paper.
b)
Explain how can the spreadsheet be used to show if a student has passed or failed their test?
Bishopbriggs Academy - Standard Grade Computing Studies
Credit Level Examination type Question 1.
Stephanie uses a spreadsheet to keep track of her fantasy football team. A section of the spreadsheet is shown below.
Formula are used to calculate the column totals in D14, E14 and F14. The formula in cell D14 was replicated into the other two cells. What is meant by the term replicating?
(b) Write down the formula that has been entered into cell D14.
Unit 7 – Spreadsheets
17
(c)
When the formula was replicated, was relative referencing or absolute referencing used? Explain your answer.
(d) Cell B16 contains a formula to show if a team is valid or not. A team is valid if the total player cost in D14 is Less than or equal to the funds available in B15. If a team is valid, the cell shows “YES” and if not the cell shows “NO”. What formula should be entered into cell B16?
18
Bishopbriggs Academy - Standard Grade Computing Studies
