{"id":410,"date":"2019-04-04T16:57:52","date_gmt":"2019-04-04T13:57:52","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=410"},"modified":"2019-05-06T12:30:09","modified_gmt":"2019-05-06T09:30:09","slug":"using-the-solver-add-in","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/using-the-solver-add-in\/","title":{"rendered":"Using the Solver add-in"},"content":{"rendered":"\n<p>In another tutorial we saw how we can find ourselves a better loan for a new car, using <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-goal-seek-in-excel\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Goal Seek (opens in a new tab)\">Goal Seek<\/a><\/strong> . Although Excel\u2019s Goal Seek is a powerful tool, it has one basic limitation. It allows only one variable to be changed to help us achieve our goal.<\/p>\n\n\n\n<p>Excel provides an even more powerful tool that bypasses this limitation. The <strong>Solver<\/strong>. The <strong>Solver <\/strong>is in the form of an add-in which must be enabled before you can use it.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/h1py-iZtMck?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/solver.xlsx\">solver<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/solver.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>Let\u2019s see how to do that first.<\/p>\n\n\n\n<p>We click on the file tab of the ribbon, and then on options.\nThen select add-ins, and from the dropdown list on the bottom right we select\nExcel Add-ins. We click on Go and a popup window appears which contains a list\nof add-ins. We tick the box with the solver add-in and press OK. At the end of\nthe data tab of the ribbon a new button has just be added by the name solver.<\/p>\n\n\n\n<p>Let\u2019s test it.<\/p>\n\n\n\n<p>In our example we are planning on buying a new car and have\nour eyes on a certain model with a cost of 20000$.<\/p>\n\n\n\n<p>Unfortunately, we don\u2019t have the money and have to turn to a\nbank for a loan.<\/p>\n\n\n\n<p>The initial offer from a banker is shown in the current\nspreadsheet.<\/p>\n\n\n\n<p>The rate is 6% per year for 5 year monthly payments (60\npayments in total). <\/p>\n\n\n\n<p>As we can see the monthly payment needed to fill the\nrequirement is about 386$.<\/p>\n\n\n\n<p>This happens to be over our budget and we want to explore\nmore possibilities.<\/p>\n\n\n\n<p>We feel that we cannot pay more than 300$ per month, but we\ncan handle a bigger duration of the loan, but up to 80 months.<\/p>\n\n\n\n<p>Also the interest should not exceed 6%.<\/p>\n\n\n\n<p>In such a small scale example we could have used goal seek\nmultiple times until we reached the desired results. With solver we are a lot\nmore flexible. We click on our newly added button.<\/p>\n\n\n\n<p>In Set Objective field we set cell E7.<\/p>\n\n\n\n<p>In the \u201cBy changing variable cells\u201d field we provide the\ncells that can be changed to achieve our objective. These are cells B7 and C7.<\/p>\n\n\n\n<p>Now we can add our required restrictions.<\/p>\n\n\n\n<p>First the required monthly payment to -300 in cell E6.<\/p>\n\n\n\n<p>Then the annual rate to less than or equal to 6%.<\/p>\n\n\n\n<p>And finally the number of months to less than or equal to\n80.<\/p>\n\n\n\n<p>Before we click on solve let\u2019s have a small tour of the\ninterface. Below the add button are the buttons to change or delete one of the\nadded constraints. Then it is the button to reset all constraints, followed by\nthe button to load or save a set of constraints for future use.<\/p>\n\n\n\n<p>By clicking on the options button we have access to various\noptions of the solver add-in. Let\u2019s review some of them.&nbsp; <\/p>\n\n\n\n<p>The constraint precision, which defines the steps of the\nvalues of each constraint, in order to reach our objective. A smaller number\nincreases the precision of our calculation but in larger scale projects can\nvastly increase the time of execution.<\/p>\n\n\n\n<p>The \u201cshow iteration results\u201d is the option to use if we need\nto access all the evaluation steps of the solver add-in. <\/p>\n\n\n\n<p>Automatic scaling makes sure that extreme values are not\nused for the solution of the solver (for example one monthly installment of\n20000 dollars with zero interest). So we select it and press ok. <\/p>\n\n\n\n<p>Since our problem is non-linear we will choose the\nnon-linear method and click on solve.<\/p>\n\n\n\n<p>A new window appears. On the left we have the option to keep\nthe solution or revert to the original values, with a checkbox just below to\nreturn us to the solver window. On the right there are a number of reports the\nsolver add-in can produce and store on separate worksheets. <\/p>\n\n\n\n<p>Since we are satisfied with the supplied solution we press\nok.<\/p>\n\n\n\n<p>The solver add-in has a vast array of applications. We cannot cover all these in this lessons but, hopefully you learned enough to be able to use and eventually master it.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>In another tutorial we saw how we can find ourselves a better loan for a new car, using Goal Seek . Although Excel\u2019s Goal Seek is a powerful tool, it has one basic limitation. It allows only one variable to be changed to help us achieve our goal.<br \/>\nExcel provides an even more powerful tool that bypasses this limitation. The Solver. The Solver is in the form of an add-in which must be enabled before you can use it.<\/p>\n","protected":false},"author":2,"featured_media":411,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[11,28],"class_list":["post-410","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-data","tag-solver"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/solver.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-6C","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=410"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/410\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/411"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}