Spreadsheet Problem Solving Template Tutorial

Spreadsheet Problem Solving Template Tutorial

We’re keen on the word “template” and if you’ve clicked that link you’ll get some technical thoughts on templates, but to me, in layman’s terms, or a programmer’s terms perhaps, it represents a stage of a journey worth snapshotting, that helps you not have to repeat all that hard work you’d done reaching that point.

Don’t know why, exactly, but Kenny Rodgers springs to mind about “template” usage …

You got to .. know when to hold ‘em …
Know when to fold ‘em …
Know when to walk away
Know when to run

… if that song says to you … “think about it” … then that’s what I’m getting at. Not everything is worth a template, believe me. “Template clutter” goes with scratching the head years later, saying … “now why did we do that” … if not well documented … and with that, as far as “not well documented” goes, it can be hugely more useful (and you can do better than we do with today’s slideshow‘s “My Templates” here) to keep your templates in aptly named folders with aptly named template files.

To me, where we got to with LibreOffice and our Problem Solving spreadsheet that we presented yesterday with Spreadsheet Problem Solving Primer Tutorial as shown below, that is worth “Saving As Template”.

With spreadsheet applications there may be different places where “template” selection becomes apparent (on the way back into your spreadsheet application) … for example …

• LibreOffice presents templates in its very first menu … while …
• Excel (in Microsoft Office) presents templates in its File menu within the submenu “New” (up above the submenu “Open”)

There is a certain sense of control you get with document processing work that happens when you use the “Template” (LibreOffice menu) option or “New” (Excel submenu) option and you see something created inhouse to encourage you to do more. This is the creative “power of the template” … or was that the book we read last week.

But it is not just “control” … for a super organized user the use of templates can save lots of time as well, and for the super super organized and thorough, it can help you with documentation at the same time, of folders and template names that are self evident.

Previous relevant Spreadsheet Problem Solving Primer Tutorial is shown below.

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.

If this was interesting you may be interested in this too.