Anyone can build.
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.
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
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 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.
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?
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
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.
The following tips and advice will make working with spreadsheets a more enjoyable task.
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)