{"id":6899,"date":"2019-08-28T13:29:47","date_gmt":"2019-08-28T12:29:47","guid":{"rendered":"http:\/\/smart--grid.net\/?page_id=6899"},"modified":"2022-12-03T23:02:06","modified_gmt":"2022-12-03T22:02:06","slug":"resolution-lp-avec-excel","status":"publish","type":"page","link":"https:\/\/complex-systems-ai.com\/en\/linear-programming-2\/resolution-lp-with-excel\/","title":{"rendered":"LP resolution with Excel"},"content":{"rendered":"<div data-elementor-type=\"wp-page\" data-elementor-id=\"6899\" class=\"elementor elementor-6899\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-5c2f5c9 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5c2f5c9\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-4c405ef\" data-id=\"4c405ef\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-bfa165b elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"bfa165b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/complex-systems-ai.com\/en\/linear-programming-2\/\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Linear programming<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-afaa625\" data-id=\"afaa625\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-6218a4e elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"6218a4e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/complex-systems-ai.com\/en\/\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Home page<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-7746433\" data-id=\"7746433\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-7b7b515 elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"7b7b515\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/fr.wikipedia.org\/wiki\/Algorithme_du_simplexe\" target=\"_blank\" rel=\"noopener\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Wiki<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-6159e57d elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"6159e57d\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-7a46fb95\" data-id=\"7a46fb95\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-2c473a04 elementor-widget elementor-widget-text-editor\" data-id=\"2c473a04\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/complex-systems-ai.com\/en\/linear-programming-2\/resolution-lp-with-excel\/#Resolution-LP-avec-excel\" >LP resolution with excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/complex-systems-ai.com\/en\/linear-programming-2\/resolution-lp-with-excel\/#Ecrire-un-LP-avec-Excel\" >Write a LP with Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/complex-systems-ai.com\/en\/linear-programming-2\/resolution-lp-with-excel\/#Resoudre-le-modele\" >Solve the model<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Resolution-LP-avec-excel\"><\/span>LP resolution with excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n<p>To solve the linear program with Excel (LP resolution with Excel), you need the Solver module. On the File tab, click Options. Under Add-ins, select Solver Add-in and click the OK button. Check the solver add-in and click OK. If the maneuver does not work, you can click on Solver then Go to.<\/p>\n\n<p>You will find the solver on the Data tab in the Analysis group, as shown in the following image.<\/p>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" class=\"alignnone wp-image-6808 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp19.png\" alt=\"lp solve with excel linear programming\" width=\"428\" height=\"94\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp19.png 428w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp19-300x66.png 300w\" sizes=\"(max-width: 428px) 100vw, 428px\" \/><\/figure>\n<\/div>\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Ecrire-un-LP-avec-Excel\"><\/span>Write a LP with Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n<p>To formulate a problem of <a href=\"https:\/\/complex-systems-ai.com\/en\/linear-programming-2\/\">linear programming<\/a>, consider the following three questions:<\/p>\n\n<ol class=\"wp-block-list\">\n<li>What are the decisions to be made? To resolve this issue, Excel needs to determine the amount of each product to order (bikes, mopeds, and child seats).<\/li>\n<li>What are the constraints on these decisions? The constraints here are that the amount of capital and storage used by products cannot exceed the limited amount of capital and storage (resources) available. For example, each bicycle uses 300 units of capital and 0.5 units of storage.<\/li>\n<li>What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products. The objective is therefore to maximize this quantity.<\/li>\n<\/ol>\n\n<p>In the following example, the decision variables are in yellow, the constraints in light blue, and the objective function in dark blue.<\/p>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone wp-image-6809 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp20.png\" alt=\"lp solve with excel linear programming\" width=\"604\" height=\"253\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp20.png 604w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp20-300x126.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/figure>\n<\/div>\n\n<p>Select each range and on the Formulas tab, in the Defined Names group, click Define Name:<\/p>\n\n<figure class=\"wp-block-table gridtable\">\n<table>\n<tbody>\n<tr>\n<th>Range Name<\/th>\n<th>Cells<\/th>\n<\/tr>\n<tr>\n<td>UnitProfit<\/td>\n<td>C4: E4<\/td>\n<\/tr>\n<tr>\n<td>OrderSize<\/td>\n<td>C12: E12<\/td>\n<\/tr>\n<tr>\n<td>ResourcesUsed<\/td>\n<td>G7: G8<\/td>\n<\/tr>\n<tr>\n<td>ResourcesAvailable<\/td>\n<td>I7: I8<\/td>\n<\/tr>\n<tr>\n<td>Total Profit<\/td>\n<td>I12<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n\n<p>Insert the following SUMPRODUCT function (in order to create constraints and objective functions):<\/p>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" class=\"alignnone wp-image-6810 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp21.png\" alt=\"lp solve with excel linear programming\" width=\"604\" height=\"253\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp21.png 604w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp21-300x126.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/figure>\n<\/div>\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Resoudre-le-modele\"><\/span>Solve the model<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n<p>To find the optimal solution, perform the following steps (you must adapt each step to your model).<\/p>\n\n<ul class=\"wp-block-list\">\n<li>On the Data tab, in the Analysis group, click Solver.<\/li>\n<li>Enter the solver parameters.<\/li>\n<li>You have the choice of entering the range names or clicking on the cells in the worksheet. Enter the total profit for the goal.<\/li>\n<li>Click on Max.<\/li>\n<li>Enter the direction of inequalities for the variables<\/li>\n<li>Add the constraints according to this process<\/li>\n<li>Check the box &quot;Define non-negative unconstrained variables&quot; and select &quot;Simplex LP&quot;.<\/li>\n<li>Click on Solve<\/li>\n<\/ul>\n\n<p>The solver fills the table with an optimal solution.<\/p>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-6811 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp22.png\" alt=\"lp solve with excel linear programming\" width=\"604\" height=\"253\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp22.png 604w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp22-300x126.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/figure>\n<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>","protected":false},"excerpt":{"rendered":"<p>Linear Programming Homepage Wiki Solving LP with excel To solve the linear program with Excel (solving LP with Excel), you need the module \u2026 <\/p>","protected":false},"author":1,"featured_media":0,"parent":486,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-6899","page","type-page","status-publish","hentry"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/6899","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/comments?post=6899"}],"version-history":[{"count":3,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/6899\/revisions"}],"predecessor-version":[{"id":16816,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/6899\/revisions\/16816"}],"up":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/486"}],"wp:attachment":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/media?parent=6899"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}