Spreadsheet Problem Solving Primer Tutorial
Spreadsheet programs represented some of the first “killer apps” on the market that made personal computing popular, so you’d expect, given the “taste” of worldwide opinion regarding software usefulness, the spreadsheet and spreadsheet programs may have a high degree of sophistication.
If your spreadsheet program is lacking the ability to define “formulae”, it is lacking a bit in this spectrum of usefulness. Today we use those “formula” functionalities to problem solve for a mathematical problem you might empirically solve, if totally left to your own devices … but you aren’t now, are you? … when we say you can use …
- Google Docs “Sheets” module web application … and/or …
- LibreOffice “Calc Spreadsheet” desktop (on Mac OS X) application
… and today we happen to use both, because we started thinking the web application way, and shaping to “Save As” we saw the “Download As” option, and, as you would … wouldn’t you? … we involved that wonderful LibreOffice desktop suite of programs.
So the two above can use “formulae”, and for any serious work to save you some time mathematically, your spreadsheet program should include this functionality. Let’s face it though, not all of us want to “save time mathematically” that way either because …
- we use calculators for that
- we actually enjoy working it out with paper and pen (or pencil) … gasp!
Anyway, the premise of today’s tutorial came from page 310 of New Century Maths 9 – stages 5.2/5.3 whose advice we followed closely, so, thanks.
Here’s the problem (to solve) … as an image 
… and in words, like in real life, you may be given a sheet of cardboard 20 x 14 and are asked to be able to turn it into an open box of maximal volume by cutting out four corner squares so that you can do your folding … origami anyone?
Now you can emprically solve by trial and error, or you can use a spreadsheet “formula” technique as we do today, and as is recommended at New Century Maths 9 – stages 5.2/5.3.
We start by using Google Docs “Sheets” module web application and …
- place headings
- place 20 into D4 cell (as the sheet’s length)
- place 14 into E4 cell (as the sheet’s width)
- place 0 (zero) into C7 “Side of square” cell
- place formula =D4-2*C7 into D7 “Length” cell … can you work out why?
- place formula =E4-2*C7 into E7 “Width” cell … can you work out why?
- place formula =C7 into F7 “Height” cell … can you work out why?
- place formula =D7*E7*F7 into G7 “Volume” cell … your mathematics knowledge will help you here
- place formula =C7+1 into C8 as a (first) incrementing idea
- highlight C8 and wait for bottom right blue dot that you can Fill Down via a drag to cell C14 (causing all those cells to be populated with incrementing values) … why C14? Hint: 14 / 2 = 7 = 14 – 7
- for the cells in the range D8-G14 follow the patterns of the 7th row formulae, changing the appropriate row number … so that G14’s formula, for instance, would be =D14*E14*F14
- see the results that help you in your problem solving venture …
… and you see from the column G what is best as a corner square size, as in column C … but this is for an increment of one … to refine …
- place smallest of interesting big volume square length ideas into C7 “Side of square” cell
- place formula =C7+(your fractional idea) into C8 as another incrementing idea
- see the results that have you in your problem solving venture … perhaps make “(your fractional idea)” smaller and repeat, as required
… and so we did this for 20 x 14 and then for (cell D4 becomes) 87 x 14 (still in cell E4) … 
… in Google Docs and “Downloaded As” a hard disk file for use with LibreOffice “Calc Spreadsheet”, and you can see us doing this at today’s slideshow. Google Docs provided this “Share” link for the spreadsheet as it looked coming out of Google Docs (before “Download As”).
Perhaps this is a precursor to you thinking more about spreadsheet programming, and perhaps investigate ideas like VBA in Microsoft Office Excel, for example? Or stick to (just) “formulae” functionality just now … after all, it helps you problem solve, but perhaps in a less permanent way, for a big percentage of the problems where “formula”, in any context, can help solve a problem, in the spreadsheet world.
If this was interesting you may be interested in this too.
