Introduction

Spreadsheets are incredibly powerful tools. They may be used for much more than just calculating a total or average.

The following spreadsheet problems are spreadsheet program agnostic. That is, they can be solved using any spreadsheet program (Eg. Apple Numbers, OpenOffice Calc, Microsoft Excel or Google Sheets). Solving interesting spreadsheet challenges is a great way to develop your spreadsheet ability and problem solving skills. It is also a lot of fun and can be very fulfilling.

Feel free to adapt and modify these problems to suit your skill level/ interest. If you think a particular challenge is interesting but a little too difficult, or not difficult enough, then maybe you only solve part of the problem, or think of ways to take it even further. Keep in mind too that spreadsheets can be used to automate a great many tasks, saving you time and effort. As you're working through the challenges, think about how the things you are learning could be applied to other problems you would like to solve.

In the Functions to consider under each challenge a number of useful functions have been listed. These are just suggestions and it is possible to solve them using different functions depending on your approach.

Challenges

Two Thirds of Average

There is a game where you have to guess what two thirds of the average of all guesses will be. The closest person to the answer wins. You can see an online version here. Build a spreadsheet that will allow you to enter two columns of data, the persons name and their guess. The spreadsheet will then print in a cell to the side two thirds of the average of all the guesses and highlight the guess which is closest to this number.

average, indirect, match, min, abs

Bonus Points

A class is given the opportunity for bonus points on their next assignment. Each student may elect to recieve either 1 or 4 bonus points. Each student will get the marks they selected added to their total mark for the assignment, unless more than 20% of students select the 4 bonus marks in which case nobody will get any bonus marks. You don't know how many students will be in the class so your solution should work with a variable number of students. Your spreadsheet should print out the number of students who selected each option, the percentage of students which selected each option and whether the marks are awarded or not.

if, countif, counta, ceiling

Mobile phone plan comparison

Mobile phone plans can be hard to decipher exactly what you get and are often designed to make them hard to compare. Create a spreadsheet which allows you to enter various phone plan details and it will calculate from that how many hours/ minutes of call time you will get on the plan and also what the actual call rate (in real money terms) is. Your spreadsheet should also highlight the best deal.

Rolling average

Grab two dice and make a prediction about what the average sum of rolling the two dice should be. Now create a spreadsheet which allows you to enter a series of rolls of the dice. Next to each roll calculate the average of that roll and all of the rolls before it. This will allow you to see how the average changes over time as more rolls are introduced to the average. How many rolls are required before the average settles down? Is the average sum of the two dice close to your prediction?

average

Guessing Game

Create a spreadsheet where a hidden number (the target) may be entered into a cell (make it hidden by making the text colour the same as the background colour). The player is then able to enter guesses (one under another in a column). After each guess a count of the number of guesses is updated in a cell to the side of the column of guesses. In another cell it prints either Higher, Lower or You win accordingly.

counta, indirect, if, address

Tic Tac Toe

The classic game of Tic Tac Toe can be recreated in a spreadsheet using a little creativity. Formatting the cells with colours and borders also makes the playing board stand out and allows you to hide other cells used for processing.

Hint: whilst this game can be created using a lot of and and or functions, you can actually save a lot of processing by assigning a value (between 1 and 9) to each square on the playing board such that the sum of each row, column and diagonal is 15. This makes determining a winner much easier.

if, sum

Tips and Advice

The following tips and advice will make working with spreadsheets a more enjoyable task.

• Break the problem down. Don't try and solve it all in one massive formula in a single cell. Spread it out over several cells, building towards your answer. Solving a series of smaller steps is easier than solving one large step.
• Lay out and format your spreadsheet nicely. Use colour, spacing and borders to clearly separate the data and results in your spreadsheets. This will make it easier to see the overall structure of the solution and make identifying and solving errors easier.
• Test functions you haven't used before with sample data. This will make it easier to test if your implementation of the function is correct. Often functions in spreadsheets have a non obvious format (order and format of arguments) and it's easier to see if your idea of how the function should work is correct or not with a simplified set of data.
• There are multiple approaches to solving these challenges. Some solutions require breaking the problem down in different ways, some solutions use mathematics to create shortcuts. All approaches are valid as long as they achieve the desired functionality.
• Be patient and persistent in your research. There are many functions available within spreadsheets. They do all manner of incredibly useful and intersting things. Think about what steps would be useful to solve a problem then spend a bit of time researching functions available to you. Whilst there may not be a function which does exactly that, there may be functions which do something similar (the suggested functions under each challenge will give you a starting point). There names aren't always obvious ones so you may need to do a bit more research than just searching for a function with a name you assume such a function should be called, not finding it and giving up.

Solutions

Looking for a spreadsheet with solutions? Send us an email and we'll give you link to a page with just that.
(The email must be from a .edu domain or I won't respond)