{"id":20393,"date":"2016-02-29T03:01:51","date_gmt":"2016-02-28T17:01:51","guid":{"rendered":"http:\/\/www.rjmprogramming.com.au\/ITblog\/?p=20393"},"modified":"2016-02-29T21:40:42","modified_gmt":"2016-02-29T11:40:42","slug":"spreadsheet-problem-solving-primer-tutorial","status":"publish","type":"post","link":"https:\/\/www.rjmprogramming.com.au\/ITblog\/spreadsheet-problem-solving-primer-tutorial\/","title":{"rendered":"Spreadsheet Problem Solving Primer Tutorial"},"content":{"rendered":"<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/Mac\/LibreOffice\/ProblemSolving\/\"><img decoding=\"async\" style=\"float:left;border: 15px solid pink;\" alt=\"Spreadsheet Problem Solving Primer Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/Mac\/LibreOffice\/ProblemSolving\/biggest_volume-218of.jpg\" title=\"Spreadsheet Problem Solving Primer Tutorial\"  \/><\/a><p class=\"wp-caption-text\">Spreadsheet Problem Solving Primer Tutorial<\/p><\/div>\n<p><a target=_blank title='Spreadsheet information from Wikipedia ... thanks' href='https:\/\/en.wikipedia.org\/wiki\/Spreadsheet'>Spreadsheet<\/a> programs represented some of the first &#8220;killer apps&#8221; on the market that made personal computing popular, so you&#8217;d expect, given the &#8220;taste&#8221; of worldwide opinion regarding software usefulness, the spreadsheet and spreadsheet programs may have a high degree of sophistication.<\/p>\n<p>If your spreadsheet program is lacking the ability to define &#8220;formulae&#8221;, it is lacking a bit in this spectrum of usefulness.  Today we use those &#8220;formula&#8221; functionalities to problem solve for a mathematical problem you might <a target=_blank title='empirically' href='https:\/\/www.google.com.au\/search?q=impirically&#038;ie=utf-8&#038;oe=utf-8&#038;gws_rd=cr&#038;ei=kwLSVpqYLMKb0gS8y5ywAQ#q=empirically'>empirically<\/a> solve, if totally left to your own devices &#8230; <font size=1>but you aren&#8217;t now, are you?<\/font> &#8230; when we say you can use &#8230;<\/p>\n<ul>\n<li><a target=_blank title='Google Docs' href='https:\/\/docs.google.com\/document\/u\/0\/?pref=2&#038;pli=1'>Google Docs<\/a> &#8220;Sheets&#8221; module web application &#8230; and\/or &#8230;<\/li>\n<li><a target=_blank title='LibreOffice from The Document Foundation' href='http:\/\/www.libreoffice.org'>LibreOffice<\/a> &#8220;Calc Spreadsheet&#8221; desktop (on Mac OS X) application<\/li>\n<\/ul>\n<p> &#8230; and today we happen to use both, because we started thinking the web application way, and shaping to &#8220;Save As&#8221; we saw the &#8220;Download As&#8221; option, and, as you would &#8230; <font size=1>wouldn&#8217;t you?<\/font> &#8230; we involved that wonderful LibreOffice desktop suite of programs.<\/p>\n<p>So the two above can use &#8220;formulae&#8221;, and for any serious work to save you some time mathematically, your spreadsheet program should include this functionality.  Let&#8217;s face it though, not all of us want to &#8220;save time mathematically&#8221; <b>that<\/b> way either because &#8230;<\/p>\n<ul>\n<li>we use calculators for that<\/li>\n<li>we actually enjoy working it out with paper and pen (or pencil) &#8230; gasp!<\/li>\n<\/ul>\n<p>Anyway, the premise of today&#8217;s tutorial came from page 310 of <i>New Century Maths 9 &#8211; stages 5.2\/5.3<\/i> whose advice we followed closely, so, thanks.<\/p>\n<p>Here&#8217;s the problem (to solve) &#8230; as an image <img title='20 x 14' src='http:\/\/www.rjmprogramming.com.au\/Mac\/LibreOffice\/ProblemSolving\/huh.jpg'><\/img> &#8230; 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 &#8230; <a target=_blank title='Origami images' href='https:\/\/www.google.com.au\/search?q=origami&#038;biw=1173&#038;bih=582&#038;source=lnms&#038;tbm=isch&#038;sa=X&#038;ved=0ahUKEwiH2_S-_ZjLAhWEG6YKHfkECaEQ_AUIBygC'>origami<\/a> anyone?<\/p>\n<p>Now you can <i>emprically<\/i> solve by trial and error, or you can use a spreadsheet &#8220;formula&#8221; technique as we do today, and as is recommended at <i>New Century Maths 9 &#8211; stages 5.2\/5.3<\/i>.<\/p>\n<p>We start by using <a target=_blank title='Google Docs' href='https:\/\/docs.google.com\/document\/u\/0\/?pref=2&#038;pli=1'>Google Docs<\/a> &#8220;Sheets&#8221; module web application and &#8230;<\/p>\n<ul>\n<li>place headings<\/li>\n<li>place 20 into D4 cell (as the sheet&#8217;s length)<\/li>\n<li>place 14 into E4 cell (as the sheet&#8217;s width)<\/li>\n<li>place 0 (zero) into C7 &#8220;Side of square&#8221; cell<\/li>\n<li>place formula =D4-2*C7 into D7 &#8220;Length&#8221; cell &#8230; can you work out why?<\/li>\n<li>place formula =E4-2*C7 into E7 &#8220;Width&#8221; cell &#8230; can you work out why?<\/li>\n<li>place formula =C7 into F7 &#8220;Height&#8221; cell &#8230; can you work out why?<\/li>\n<li>place formula =D7*E7*F7 into G7 &#8220;Volume&#8221; cell &#8230; your mathematics knowledge will help you here<\/li>\n<li>place formula =C7+1 into C8 as a (first) incrementing idea<\/li>\n<li>highlight C8 and wait for bottom right blue dot that you can <i>Fill Down<\/i> via a drag to cell C14 (causing all those cells to be populated with incrementing values) &#8230; why C14? Hint: 14 \/ 2 = 7 = 14 &#8211; 7<br \/>\n<img src='http:\/\/www.rjmprogramming.com.au\/Mac\/LibreOffice\/ProblemSolving\/biggest_volume-187of.jpg' title='Fill down'><\/img>\n<\/li>\n<li>for the cells in the range D8-G14 follow the patterns of the 7th row formulae, changing the appropriate row number &#8230; so that G14&#8217;s formula, for instance, would be =D14*E14*F14<\/li>\n<li>see the results that help you in your problem solving venture &#8230;\n<\/ul>\n<p> &#8230; and you see from the column G what is best as a corner square size, as in column C &#8230; but this is for an increment of one &#8230; to refine &#8230;<\/p>\n<ul>\n<li>place smallest of interesting big volume square length ideas into C7 &#8220;Side of square&#8221; cell<\/li>\n<li>place formula =C7+(your fractional idea) into C8 as another incrementing idea<\/li>\n<li>see the results that have you in your problem solving venture &#8230; perhaps make &#8220;(your fractional idea)&#8221; smaller and repeat, as required<\/li>\n<\/ul>\n<p> &#8230; and so we did this for 20 x 14 and then for (cell D4 becomes) 87 x 14 (still in cell E4) &#8230; <img title='20 x 14' src='http:\/\/www.rjmprogramming.com.au\/Mac\/LibreOffice\/ProblemSolving\/huh87.jpg'><\/img> &#8230; in Google Docs and &#8220;Downloaded As&#8221; a hard disk file for use with LibreOffice &#8220;Calc Spreadsheet&#8221;, and you can see us doing this at today&#8217;s <a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/Mac\/LibreOffice\/ProblemSolving\/\" title='Slideshow'>slideshow<\/a>.  Google Docs provided this &#8220;Share&#8221; <a target=_blank title='Share Google Docs link' href='https:\/\/docs.google.com\/spreadsheets\/d\/14d_j1tWqlaA9HVzEymhyS5xaIHs8Skk1ZOquGYSxjHs\/edit?usp=sharing'>link<\/a> for the spreadsheet as it looked coming out of Google Docs (before &#8220;Download As&#8221;).<\/p>\n<p>Perhaps this is a precursor to you thinking more about spreadsheet programming, and perhaps investigate ideas like <a target=_blank href='https:\/\/en.wikipedia.org\/wiki\/Visual_Basic_for_Applications' title='Visual Basic for Applications information from Wikipedia ... thanks'>VBA<\/a> in Microsoft Office Excel, for example?  Or stick to (just) &#8220;formulae&#8221; functionality just now &#8230; after all, it helps you problem solve, but perhaps in a less permanent way, for a big percentage of the problems where &#8220;formula&#8221;, in any context, can help solve a problem, in the spreadsheet world.<\/p>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d20393' onclick='var dv=document.getElementById(\"d20393\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/tag\/spreadsheet\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d20393' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Spreadsheet programs represented some of the first &#8220;killer apps&#8221; on the market that made personal computing popular, so you&#8217;d expect, given the &#8220;taste&#8221; of worldwide opinion regarding software usefulness, the spreadsheet and spreadsheet programs may have a high degree of &hellip; <a href=\"https:\/\/www.rjmprogramming.com.au\/ITblog\/spreadsheet-problem-solving-primer-tutorial\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,37],"tags":[360,403,454,513,521,694,752,990,1185,1319,1364],"class_list":["post-20393","post","type-post","status-publish","format-standard","hentry","category-elearning","category-tutorials","tag-download","tag-excel","tag-formula","tag-google","tag-google-docs","tag-libreoffice","tag-mathematics","tag-problem","tag-spreadsheet","tag-tutorial","tag-vba"],"_links":{"self":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/20393"}],"collection":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/comments?post=20393"}],"version-history":[{"count":13,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/20393\/revisions"}],"predecessor-version":[{"id":20429,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/20393\/revisions\/20429"}],"wp:attachment":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/media?parent=20393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/categories?post=20393"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/tags?post=20393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}