10S1 Period 2 Tuesday 3rd March 2015

Working with numbers and charts – Functional Skills Module 5 – Lesson 9

In this lesson students are learning how to:

• select and apply appropriate formats
• carry out calculations with relative and absolute references

Starter

Here are two puzzles:

Puzzle1

Look at the image L9.1-100-Display.

What was entered into the four cells and what format do they have?  Now look at the image L9.2-100-Display-Format.  The same number was entered into all four cells.

The Roman number “C” should give you a clue.  What is the number behind the display?  Look at the image L9.3-100-DataEntry-Display-Format. 

Puzzle 2:

‘Spot the difference’.  Look at the image L9.4-10×10-text-number.

What is the result for each line (10 x 10 = 100).  Now look at the image L9.5-10×10-results.

Can you explain why cell C1 displays the expected and correct result but C2 still shows the formula.  Look at the image the L9.6-10×10-warning.

Spreadsheets can treat numbers like text if

• the user put a ‘ in front of the number, e.g. ‘100.

Look at the image L9.7-10×10-comment.  The different colour (red) and location (top-right) of the marker indicates a comment.  The comments do NOT affect calculations or formatting.

IMPORTANT – formatting matters.

TASK 1 Formatting Cells 

The number 45.27.  What formats could you apply?  Look at the table below:

Now explore the Alignment, Font, Border and Patterns tabs.

TASK 2 Formulas and Functions 

Look again at the formula used in puzzle 1 to multiply ten by ten (=A1*A2).  REMEMBER:

• + for additions 
• – for subtractions 
• / for divisions

Spreadsheets have a range of ready-made formulas for users to choose from.  Using these formulas is

• more efficient (saves time)
• reduces the risk of making mistakes when typing in a formula from scratch

How often would you have to enter the + symbol to add together the points of 100 members in the Loyalty Card Scheme?  The Function SUM is much better as it adds up all points between the first and last member.  The first and last cell set the range over which the function works.

Here are the other most commonly used Functions:  AVERAGE, COUNT, MAX and MIN.

Watch the SUM function VIDEO

Watch the AVERAGE function VIDEO

Watch the COUNT function VIDEO

Watch the MAX function VIDEO

Watch the MIN function VIDEO

Open Loyalty Card Scheme LESSON 9 spreadsheet and use the SUM function on the Points and Cash Value columns.  Now try switching between data-view and formula-view by using Ctrl + ` (to the left of the number key 1).

On which fields it would not make any sense to use the SUM function?

• all pure text fields
• text-number fields like postcodes
• number fields such as telephone numbers

Now find the number of entries (COUNT), the total, average (AVERAGE), highest (MAX) and lowest (MIN) entries in

• the Points field
• the Cash Value field

Can you suggest a way so that you do not have to do the work twice?

TASK 3 – Relative and Absolute Cell References 

If a formula contains a reference that must always point to the same cell, then this is called an absolute cell reference and is indicated by the $ sign.

Complete this Skill Builder exercise.

• Open  – FIVE TIMES TABLE.
• You need to change this spreadsheet so that you can quickly show different times tables with just a few key presses.
• Click on cell E5, press the equals sign.  Click on cell D3 and press Enter.  Cell E5 should display =D3.
• Click on cell D3 and enter another number.  Press Enter.
• A new number is shown in cell E5; all other numbers remain the same.
• Click on cell E5 again and copy the formula down to E16.  What has gone wrong?  Use the keyboard shortcut to show the formulas.  As you dragged down the formula, the important reference to cell D3 was lost!
• Click on cell E5 and add the dollar signs (=$D$3), then copy down to cell E16.  Cells E5 to E16 now all refer to cell D3.  This means the table will work as expected.
• Use the keyboard shortcut to switch back to see the results.
• Enter other numbers into cell D3.  What happens if you enter a number bigger than 1000?  What do you need to do to show all the numbers?

TEST YOURSELF 

At the end of the lesson you should be able to:

• display numbers and text in appropriate formats
• enter formulas using (+, -, *, /)
• use functions SUM, AVERAGE, COUNT, MAX and MIN
• use absolute cell references

WATCH THE VIDEOS (SUM, AVERAGE, COUNT, MAX and MIN) again.

EXTENSION WORK 

Complete this Skills Builder exercise.  PROMOTIONAL MATERIAL 

• Open PROMOTIONAL MATERIAL .  The table contains information about promotional material for a trade fair.
• Adjust the column widths so that all the headings are visible.
• In cell D2, enter the formula =B2*C2 to calculate the total cost of the 12 page leaflet.
• Copy the formula in D2 to cells D3 to D8 (move the mouse pointer onto the bottom-right handle of cell D2, hold down the left mouse button and drag down to cell D8).
• Click in cell C2 and drag into cell D8 to select the range C2:D8.  Format the data in this range as currency with two decimal places.

STEP 1

STEP 2

STEP 3

• Enter the formula =SUM(D2:D8) in cell D11 to calculate the cost of the order.
• Enter the formula =AVERAGE(C2:C8) in cell D10 to calculate the average cost of the promotional items.
• Enter a formula =D11+D12 in cell D13 to give the total including delivery.
• Format the data in D12 as currency with two decimal places.
• Add your name and the date to the header.
• Save the file.

FURTHER EXTENSION WORK 

If you have one piece of information about a member but need a second one.  The LOOKUP function as an easy way to find linked information.  Study carefully the following diagrams (L9.8 LOOKUP, L9.9 LOOKUP-Function-Arguments, L9.10 LOOKUP-Results and L9.11 LOOKUP-Formula).