Here is an excerpt from the new introduction I have written for my book Adventures in Spreadsheeting - Fun Things to do with Spreadsheets.

Why Should Young People Use Spreadsheets?

Spreadsheets are incredibly versatile bits of software. All sorts of people, young and old, have found all sorts of different ways of using them for their own purposes. (See box at right.) Personally speaking, I probably think of spreadsheets mainly as great problem-solving tools, especially if some mathematical calculations are needed to find a solution.

If you are a young person, however, the main reason you should be using spreadsheets right now is that they can be used as “Mindtools”. A mindtool is a bit of computer software that can be used like scaffolding around a building site, something outside our head that helps us to build on and reshape the ideas inside our head in our brain and our mind.

People have been using a whole variety of objects to help them think better for countless centuries now. Consider, for example, how notches in a piece of wood might be used to keep count of larger things, how a diagram can help you understand a complicated situation or how a hand-held calculator can actually do some of your thinking for you.

People throughout the ages have also adopted various tactics for problem-solving, like spreading out the parts of something neatly on a clear sheet or surface. From this point of view, spreadsheet programs and other computer software are simply more modern inventions for doing problem-solving, exploring ideas and situations, creating things, etc.

To choose and use spreadsheets well, however, you are going to have to know a fair bit about them.

Spreadsheets are an especially clever invention because they can be used as mindtools in basically three different, but often overlapping ways. Unlike many computer games, however, where the levels for beginner and expert players are often clearly stated, you have to look rather carefully to see how this works in spreadsheets.

Take a really good look at the screenshot shown below. Parts of it should look very familiar to you.

This screenshot was created when another author tested out a copy of VisiCalc downloaded from the Internet. VisiCalc was invented in the late 1970’s and is often described as “the first spreadsheet program”. There was certainly no software like it before for personal computers.

Take an especially close look at what you can see in the large rectangle at the bottom of the screenshot. This area is what is nowadays called the worksheet and then as now it contains a lot of smaller rectangles called cells. Each cell can be referred to individually by using the letters and numbers around the edge of the worksheet. The cell here containing 211, for example is called Cell B5.

Here is a little challenge for you to think about: How could you reproduce what you see in this worksheet?

Method 1

The very simplest way would be to type the various words and numbers into appropriate cells. That would be using the spreadsheet program like a piece of grid paper. (If you wanted to sound a bit more sophisticated, you might say a spreadsheet program can be used like a word processing program.)

The important thing to note here is that spreadsheets can be neat ways to record and display bits of information. This makes it easier to visualize what the information is about and the relationships between its parts. The grid pattern of the worksheet cells makes it particularly handy to record information in the form of tables. Nothing, however, would prevent you from using another arrangement for the data if that seemed better to do.

Method 2

What if the 211 total was missing? How would you put it there in B5? Again, you would not have to know much about spreadsheets to do this. You could either do the calculation (72+85+54) mentally or grab your hand-held calculator and add the numbers 72, 85 and 54.

Let me tell you something that quite a few teachers, intentionally or otherwise, keep “secret”.

Spreadsheet programs themselves can be used like a hand-held calculator. You could use the arrow-keys on the keyboard (or, these days, your mouse) to make B5 the “active” cell. In VisiCalc, you could then type in +72+85+54 and press Enter. The cell will act just like the window in a hand-held calculator and display 211.

(In more modern spreadsheets such as Microsoft Excel, you would be more likely to type = (an equals sign) rather than the first plus sign.)

Doing calculations using the original numbers can sometimes be handy, say, when you want to do the calculation only once but want to keep a record of which numbers were used.

Method 3

Lastly, note the line just above the worksheet. In modern spreadsheets this is called the Formula Bar. In this spreadsheet it reads

B5 (V) +B1+B2+B3

The feature is probably the main reason why Bricklin and Frankston, the inventers of VisiCalc, gave their program that name. It was short for “Visible Calculations”. Any calculation that has been placed in a cell will be displayed in the formula bar when that cell is chosen to be the active cell.

(In Microsoft Excel, if you hold down the Control key (Ctrl) and tap the Tilde Key (~) all the formulae in your spreadsheet will be revealed.)

The formula bar in this example shows the formula that was actually typed into B5 by the author who downloaded VisiCalc. It is an instruction or command to the computer to add together whatever numbers are in cells B1, B2 and B3.

There is a good reason why spreadsheeters usually refer to cell names like B1, B2 etc. rather that cell contents like 72, 85, etc. When the numbers in the cells referred to are changed, your computer will automatically use the new numbers to calculate a new answer.

In fact, you can look at the whole table in the worksheet as a set of commands. Just typing “Apples” into cell A1 and “72” into Cell B1, for example, tells the computer to remember that the cells in those positions contain these values. In other words, each time you use a spreadsheet, you can think of yourself as using it like a programming language.

Programming languages are software used to give computers a list of instructions to follow in a certain order. Some people even say that spreadsheets are a type of programming language.

Why do people think that spreadsheets are programming languages? This might be more obvious if we look at this program written in the computer language BASIC.

10 APPLES = 72

20 ORANGES = 85

30 BANANAS = 54

40 TOTAL = APPLES + ORANGES + BANANAS

50 PRINT "TOTAL ="; TOTAL

This would do much the same job as the spreadsheet, albeit not quite so conveniently. In both cases, for instance, changing any of the input numbers (72, 85 and 54) would result in a correct new value for the Total being calculated.

In most ready-made programs that you buy from a computer store, the “code” written in the programming language is usually well hidden. The store-bought program that might be the equivalent to this spreadsheet might look something like this:

Programs that do a rather specific task like this are called “Applications”. That is why you will hear spreadsheeters talking about creating “Spreadsheet Applications”. These are probably what they regard as the “bee’s knees” in spreadsheeting.

When we choose to use spreadsheets for our own purposes, it is probably because we can remember how we have used spreadsheets in the past to do similar tasks. I hope this little book of mine will provide you with many happy such memories.

What is Spreadsheeting?

Spreadsheeting ain't Spreadsheets

What is spreadsheeting?

Spreadsheeting is often defined as “using spreadsheet programs”. This is certainly can be a good way to spreadsheet, but it is not the only way you can do it, nor does using a spreadsheet program guarantee successful spreadsheeting.

Spreadsheeting is basically a set of tactics you can use or an activity you can do to help you think about things more clearly. As it's name might suggest, spreadsheeting is built around the notion of spreading things neatly out in front of you, either literally or figuratively speaking. You can use various media for this purpose. Even imagining you are doing it will help solve problems, etc.

Consider the strategy a mechanic will use to fix up a motor cycle engine. He or she will usually take the engine apart. (This tactic is called analysis) and finally put it back together again. (This is called synthesis).

In between these stages, however, the mechanic is very likely to spread out and arrange the parts neatly on the bench. This will help him or her to think about which parts are missing or defective or should be working together.

When we spreadsheet nowadays, however, the things we spread out and arrange on our computer screen, are more likely to represent other things. Spreadsheet programs can enable us, for example, to “model” quite complex, dynamic situations. The process of doing this kind of thing is most worthy of being called "spreadsheeting".

Spreadsheeting is a useful way to

(a) explore ideas and situations
(b) Solve problems (particularly if these lend themselves to mathematical and logical solutions)
(c) Create useful and interesting things, like artworks or spreadsheet applications
(d) have fun (Two good signs of having fun are being able to focus on something while feeling confident or relaxed about outcomes.)

CREATING SPREADSHEET MINEFIELD GAMES

PART ONE

Real minefields are terrible things. Soldiers bury bombs in the ground. Real people, often long after the battles are over, step on the mines. Many are killed or are horribly injured. Mine disposal experts are needed to find and remove mines from the fields.

Can you represent this situation in a spreadsheet?

This will require you to take an Information Technology point of view of minefields. You will need to consider, for example, how information is inputted, stored, processed and outputted, then make a working “model”, bit by bit.

The player’s focus is on the area where his or her “steps” are inputted.

To keep things simple, let’s make this area a 5x5 array of cells and represent the steps as “1’s” placed in cells.

Prior to the start of the game, however, the game master has placed the mines in the field.

Their locations can also be represented as “1’s” placed in another 5x5 grid.

When the positions of steps and mines coincide there will be a terrible interaction.

This can be shown on another 5x5 grid filled with formulae that give a result if there are “1’s” in similar positions in the both of the first two areas.

Spreadsheets allow us to check for special situations with an IF function. If we were using Microsoft Excel, for example, we might place this formula in cell N2

=IF(B2+H2=2,9,” ”)

[If the sum is 2, the cell shows a “9”, if not, the double quotation marks make the cell appear empty.]

We can then use AutoFill to place similar formulae in the rest of this area.

The “9”, in this case, represents an explosion, but not very dramatically. So we can add another IF function formula under the first area to make this event more explicit, for instance:

=IF(Sum(N2:R6)>0, “YOU JUST STEPPED ON A MINE!", “SO FAR, SO GOOD”)

Once you have checked that this is all working the way you expect it to, you can work on how to make bigger minefield areas and how to hide the second and third areas from the players’ sight.

PART TWO

To help them locate and remove mines from minefields, mine disposal experts use mine detectors. These detectors collect harmless but useful information from the areas near mines. We can build on our basic game to represent this.

Let us first provide another 5x5 grid that collects and processes some mathematical information from all parts of the “minefield”.

Each cell in this new grid could contain a formula calculating the sum of the “1’s” in a 3x3 area immediately around each cell in the “minefield”.

In this case, we might place a formula like this in cell H10:

=SUM(G1:I3)

Again, AutoFill can be used to place similar formulae in the rest of this area.

When we walk through a minefield with a mine detector, it only reveals information about the area immediately near us.

Suppose we create another 5x5 area to show this.

Again, another IF function formula will do the trick. For example, in cell B10 , we could place the formula

=IF(B2=1,H10," ")

Then we could use AutoFill to place similar formulas in the rest of this area.

PART THREE

The next step might be to provide a way players can “dispose” of mines.

One way of doing this could be to give them the option of placing another number besides “1” in cells where they guess a “mine” is located.

You could then use IF function formulae to give them feedback about whether their guess was correct.

I will leave that task up to you.

Is there anyone out there ready to take up this challenge?

MAKE YOUR OWN SUDOKU

Quite a few people use their spreadsheets to help them solve Sudoku puzzles. Can you use yours to create a new Sudoku puzzle?



First of all, you need to know what you are creating.





(a) Each row lists each of the numbers from 1 to 9 once only

(b) Each column lists each of the numbers from 1 to 9 once only.

(c) The grid contains 9 smaller squares listing the numbers 1 to 9 once.

A fourth rule needs to be stated if you are going to create a "good" puzzle: (d) Apart from rules (a), (b) and (c) above, the numbers should look as if they were randomly distributed.


Simply putting the numbers 1 to 9 in a 9x9 grid to fit these rules may look easy when you start. It gets much harder the further you go. So we have to find a way to work smarter not harder.


Strategy 1. Start with a ready-made puzzle and change the numbers.

Suppose we

1. Made all the 9's into 1's, and added 1 to each of all the other numbers?, OR

2. Made all the 9's into 2's , all the 8's into 1's and added 2 to each of the other numbers? OR

3. Set up a more random pattern of changes, e.g 1=4, 2=7, 3=1, 4=9, 5=3, 6=8, 7=5, 8=2, 9=6?

Spreadsheets can be set up to do these tasks automatically. Hints: Methods 1 and 2 involve nested IF formulae. Method 3 can be done using HLOOKUP or VLOOKUP functions.



Strategy 2. Move whole rows and whole columns in a ready-made Sudoku puzzle.

1. Exchange the positions of the whole first column and the whole second column (TRY IT!)

2. Exchange the positions of the whole last row and the whole third last row.

3. Make the whole first three columns the middle three columns

4. Try moving whole columns or whole rows more randomly.

Any moves like these will not break rule (a) or rule (b) but doing more random moves soon breaks rule (c)



Strategy 3.

1. Create a simple non-random puzzle that obeys rules (a) and (b)




2. Swap around whole rows and whole columns until
(i) Rule (c) is obeyed. and
(ii)Rule (d) (Random appearance) is obeyed.

NOTE FOR TEACHERS AND PARENTS

Obviously the last strategy is the simplest, particularly if you know how to select and move rectangular arrays of numbers in your spreadsheet.


HAVE FUN!

Spreadsheets ain't just good business tools

Have fun with spreadsheets?

Many adults have trouble with the notion of having fun when they use spreadsheet programs. (Maybe they think of spreadsheets only as programs to use for boring things in their workplaces.) This is a bit like knowing Superman only in his Clark Kent disguise!

Young kids, bless them, do not feel this way about spreadsheets, especially if they are introduced to them the right way. The main secret I have found in this regard is to redefine spreadsheets from the learner's point of view -

Spreadsheets are software that can be used in basically three different ways:

(a) Like a piece of grid paper

(b) Like a hand-held calculator, and

(c) Like a simple programming language


Even students in Year Four can enjoy using spreadsheets like a piece of grid paper. Teach them how to create a table to record some facts and figures if you like. Better still, show them how to narrow the column widths on a work sheet. Then you get them making pentimo shapes or creating crosswords and wordsearches. They will also enjoy games locating cells using A1, B2 style references as well.

Many teachers avoid showing their students that they can do calculations in spreadsheet cells by typing something like = 25+36*4 . Their own teachers, particularly if they too first met spreadsheets as adults, probably wanted to push ahead and get them using cell references in formulae. I have found there are real benefits in getting young students to set out their typical maths problems neatly using a spreadsheet rather than a piece of paper and a hand-held calculator.

Using cell references in formulae to link cell contents together and make chains of calculations is a form of programming. This is no big deal for students when they understand the problems they are dealing with and can see immediate benefits from using cell references such as saving time and effort. Using spreadsheets in that sort of situation reinforces their "paper and pencil" learnings.

Nowadays, writing formulae is also much easier than in the past. Forms are available when you want to insert functions. For simple formulae, once you have typed = , you can, for example, just point to and click on the cell contents you want to use. In addition, using Autofill saves a lot of thinking about repetitive code.

Building on what students already know (Constructive learning) makes more sense than teaching Spreadsheeting as an isolated set of skills for doing unfamilar and complicated things. It is a pity more teachers or curriculum planners do not realise this.

Still not convinced that spreadsheets can be fun, even for adults? Thousands of people have shaken off the "Spreadsheets are business tools." mindset typically pushed by textbooks, mainstream computer magazines and, of course, spreadsheet teachers

Try doing a Google search pairing "spreadsheets" with the name of practically any "non-business" human activity that interests you. You may be surprised by what you find. .

Just for fun, for instance, I did a search pairing "spreadsheets" with"Rock and Roll". Amongst others, I found a site produced by a guy calling himself "Mr.Excel". He claims to have used a spreadsheet program ( not Microsoft Excel) to help a lady who wanted to belt out rock'n'roll tunes on her player piano.

As a teacher by trade ( I still do substitute teaching.) I am particularly interested in using spreadsheets as mindtools in all parts of the curriculum.

Anyone care to comment?