Category Archives: Excel

Pocket Money Problem.

Standard

Spreadsheetscan be used to investigate problems and if you use formulas properly the investigation can be quickly altered.  Today I would like you to investigate the following pocket money problem. You will be given pocket money over 10 weeks but which of the following options will you pick?

  • Option 1 – To get £5 pocket money each week.
  • Option 2 – You will be given £1 in the first week then and extra £ in each following week – (W1 = £1, W2 = £2, W3 = £3 etc.)
  • Option 3 – You will be given £4 in week 1, but to get an extra 20p each week (W1 = £4, W2 = 4.20, W3 = £4.40 etc.)
  • Option 4 – To start with £5 in week 1, but to get an extra 10p each week (W1 = £5, W2 = 5.10, W3 = 5.20 etc.)

Display your working outs in a table – I have given you a little clue showing my formula for working out the weeks.Formula Take care to use formulas well as Miss Carmen might added in a twist later in the lesson!

Advertisements

Sort it out Year 6!

Standard

Last time we tried this the game was blocked!  But now we can play Starry Night to get some data.  I want you to play the game 3 times – each time record the score onto my online form.

From the online data – pick five people and create a spreadsheet that will record their scores.  I have put together an example below:-

Starry Nights Score Sheet

How can you find other people’s results?  Well that is easy – RESULTS – this is the real time results for the form!  How clever.

If you have time you can make it look very pretty.

Record the scores and then use what you learned last term – =sum(…) to work out a total.

Starry Nights Score Sheet1a

It would be really useful to sort the table data into order!  The computer can do this for you automatically.

First select all the cells in the table! – include the headings for the table!

Sort Fill in sort

Sorted

Sometimes in a set of data it is useful to find out what the largest – Max or smallest – min score is.

To do this you use two functions =min(..) and =max(..) – I have added these in boxes at the bottom of the table.

Finish

Challenge.

Swap a person on your table of result for someone else.  Can you reorder it?  What happens to the Min / Max?

Real Life Challenge.

When might it be useful to use these functions in school?  If you have an idea comment on the post.

Let’s =SUM() things up!

Standard

Over the last two weeks using Spreadsheet you have learned how to use basic formulae and functions to let the computer do what it is good for… fast calculations.  Last week with the sweetshop challenge you added up four numbers but Excel has a special feature to make it even easier – Auto Sum!

Autosum The character before is from the Greek and is a Sigma, which in maths terms means summation!

You can type =sum( Then input the cells ) but you can also just click this button and it will fill it in for you!  But you need to be careful you are choosing the write cells.

Sum1sum2sum3

It is always worth indicating that this is a sum either using colours and lines or a comment – total!

sum4

This is useful as you might return to the spreadsheet many weeks later and have forgotten or more usually the spreadsheet may be used and looked at by other people.

With your new found skills can you use Σ sum to solve the race-points challenge? Oh and you can’t use the autosum button on both pages, one you will have to type!

 

Spreadsheets to model real life problems.

Now you have started to use more formula in your spreadsheet you can now use them to model real life problems.

Problem

Mr Connolly needs to make sure his daughter eats a healthy packed lunch each day at school, but he doesn’t want to spend a huge amount he says £15 should be plenty – he wants lots of change!

Each day daughter will need :-

  • A sandwich – It has to be brown bread, with a little butter or spread and she doesn’t like the same filling two days in a row.
  • Two portions of fruit or vegetable.
  • A drink – nothing with too much sugar
  • One ‘exciting‘ treat, a bag of crisp, a cake or chocolate snack.

He shops at TESCO so have a look at how much things would cost.  Like maths this is a two or even a three step problem, but when you have the formulas in place it will work everything out for you.  Just rememberyou might need to use some skills from last weeks lesson!

You will need to make some assumptions about your shopping – can you justify them?

Shopping List

SHopping List

 

 

Introducing Spreadsheets

Standard

Over this half term we  will be learning lots about Spreadsheets.  We will be using one in particular Microsoft Excel, however there are others that you can use and they all do similar things.  This half terms lessons will be just as useful for other spreadsheets.

Spreadsheets are really useful programs for helping you handle large amounts of data and mean you can make a changes and rather than having to recalculate all the sums the computer does all the work.  So they aren’t just useful for school but can be used in the home and by businesses.  In fact Mr Connolly used them this week to help with the After school club letters.

KEY VOCABULARY – TERMINOLOGY

  • row
  • column
  • cell
  • formula

Just like anything technical spreadsheets have special words (terminology) that you need to understand to help your use the program fully.

Spreadsheet

So the cell highlighted in purple is know as …

The computer uses this as a reference, like a house on a street, what is inside may change but the street and house number remain the same.

Using this new knowledge why don’t you try Wizards-challenge.

With your new knowledge about cell references you can now use the data.  You can input formulas into other cells to create another value.  Just like in maths you can use any of the four operations; you can + add, subtract, / divide, or * multiply!  But take care as the symbols are not exactly the same!

Formula

The first activitiy was just a warm up… Now the super challenge of the lesson can you use formulas to turn the bars gold. Using cell references and formulas try The-gold-mine.

At the end of the lesson I give you a Question to think about…

Why is it useful to reference the cell and not just put in the answer?

More than just =sum()

Standard

Last week the game website was blocked…  It just shows how we try and keep you safe in school by restricting websites, however now it should be unblocked so we will have another go at a new Spreadsheet skill.  But first we need to generate some data!

Have a play on the Starry Night Game – How about we say best of three goes remember to record your scores.  Enter your scores onto the online form.

I have been really smart and the data is easily collected into a Google Spreadsheet so you can look at all the results!

Using what you learned about =sum() last week I want you to create a Spreadsheet in Excel that totals your score and that of five of your friends.  Can you make it clear and easy to read?

Starry Nights Score Sheet1a

If I wanted to I could sort my data so it is like a league table – the best score at the top, counting down!  Remember you can let the computer do the work with the sort function.

Sort Make sure the tab is set to Data, and select the sort function.

The icons with AZ, ZA and an arrow just sort the first column alphabetically – which is useful but not today.

Pressing on the sort function a dialogue box pops up.

Fill in sort

Pressing OK sorts your data out in an instant!  A great way to quickly and easily sort data.  You can even sort by more than one column – Surname, First name – which is incredibly useful but that is for the future!

Sorted

That is all very useful but sometime I might need more information – like what was the lowest score in any game, what was the top score?  With only 25 bits of data to scan over all with scores only in the hundreds it won’t take a year 6 mathematician long.  But the numbers could be much bigger, be decimals or even scores for the whole year group so no longer 25 bits of data but…  180!

Again let the computer take the strain!  It has a function built in to work out the smallest – minimum =min() score in a range or even the biggest – maximum =max()

In side the brackets you but the cell references for all the cells you want to sort – remember it won’t be the total column!

Finish

Who could think of uses for this sort of function in school?