EXCEL SOLVER: LP Simplex and dual Simplex method choose
ISSUE: In the Solver (Data Analysis add-on) isn't clear which of methods is uses: Simplex or dual Simplex method (in the https://www.solver.com/ described, that uses both methods - look below). In the offisial provider page (https://www.solver.com/excel-solver-algorithms-and-methods-used) - described, that using both methods, citate: The Microsoft Office Excel Solver tool uses several algorithms to find optimal solutions. ... The Simplex LP Solving Method for linear programming uses the Simplex and dual Simplex method with bounds on the variables, and problems with integer constraints use the branch and bound method, as implemented by John Watson and Daniel Fylstra, Frontline Systems, Inc. ... - but how can I choose concrete method (or know whith is using)? LP Simplex and dual Simplex method choose precondition: Add solver: https://support.office.com/en-us/article/Load-the-Solver-Add-in-in-Excel-612926fc-d53b-46b4-872c-e24772f078ca?ui=en-US&rs=en-US&ad=US Calculate: https://support.office.com/en-us/article/Define-and-solve-a-problem-by-using-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040?omkt=en-US&ui=en-US&rs=en-US&ad=US / Example of a Solver evaluation To repoduce: Open MS Excel On the Data tab, in the Analysis group, click Solver On select a solving method: choose LP Simplex insert valid parameters and click Solve Actually: choosing only LP Simplex Expectation: choose LP Simplex + method type { Simplex, dual Simplex }
Excel solver varying in time to solve - sometimes slow and sometimes fast
Hello, I am trying to use solver in an optimization issue. I have several sheets with cross-referencing and I am trying to solve by adjusting 104 variables. Most often, it is very slow using approximately 5 seconds to go through one subproblem. A few times I have experienced it being much faster completing around 100 subproblems each second. Can anyone shed some light on why it varies so much considering how long it takes Solver to solve the issue? It went through around 12 000 subproblems when it was fast. There are 4 constraints. I have uploadet the exact file I am using.Minimze standard deviation of portfolio using Solver - no feasible solution
Hi, Minimize standard deviation of portfolio with 104 assets - no solution according to Solver I hope someone can help with this issue. I have created in Excel a portfolio of 104 stocks where I have computed the historical annual deviation and return for each stock. Also, I have created a separate sheet using the analysis toolpack to create a matrix of each asset's correlation coefficient with one another. On the basis of this, I have created a formula computing the standard deviation of the portfolio depending on the weight of each asset in the portfolio. When I use Solver to minimize the standard deviation of the portfolio by adjusting the variables concerning the weight of each asset, it says that there is no feasible solution. In any case, there must be a standard deviation that is the minimum? The cells containing the weights is $J$2:$J$105 The formula I use to calculate the standard deviation of the portfolio is: =SQRT(MMULT(MMULT(TRANSPOSE(K2:K105),cor_edit!B2:DA105),K2:K105)) Where (K2:K105) is the standard deviation multiplied with the weight. The adjustable variables in Solver is not set to this, but the column with the weights only. cor_edit!B2:DA105 is the matrix with the correlation coefficients. My constraints are 1) $J$107 = 1 This cell is the sum of all the weights i.e. 100% must be invested. I have tried setting it to the following but with no improvement: $J$107 <= 1 $J$107 >= 0,9 2) $J$2:$J$105 <= 0,1 i.e. an asset cannot make up more than 10%. 3) $J$2:$J$105 >= 0 i.e. an asset cannot have a negative weight. I have tried using the GRG in Solver with multistart and evolutionary. In evolutionary I have tried (in addition to the default settings) with the following settings: Convergence: 0,1 Mutation speed: 0,1 Population size: 200 Maximum time: Has been trying with as high as 7200 (two hours). I have checked the box requiring bounds for variables What I have tried so far to make it work: 1) I have enabled 'Set precision as displayed' adjusting the weights to two decimals trying to reduce the number of options by reducing decimals. 2) I have copy and pasted all values to a new excel file only inserting numbers and not the references. Now, I only have the relevant references i.e. of the sum of all weights, standard deviaiton multiplied with weight, the historical return of the portfolio (weight multiplied with annual return) and of course the formula for calculating the standard deviation. I figured the formulas calculating the standard deviation, return etc might be increasing the workload of excel. As these are not to be adjusted, the formulas have been removed in these cells preserving only the actual value. Ideally, I would like to create a chart of the historical return of the portfolio depending on each value of standard deviation of the portfolio. Do you have any suggestions of how I can find the minimum standard deviation of the portfolio given the mentioned constraints? For your information, when I removed the constraint of the sum being equal to 1 or within a certain range, the Solver worked but only resulting in a sum of 40% which I would like to be 100%. Any help will be much appreciated!A more complicated solver scenario, workload distribution
Hi, I would like to use the solver plugin to automate the task of worker allocation, I have watched a few solver videos which give a basic introduction on how to use it, and I would like some advice is solver can perform in the following scenario. The problem Each year 25 customers are distributed between the 5 staff members based on a workload ability (table1) Next the staff pick and equal or greater number of customers who they feel their skill set will help as a backup, As there are a greater number of Backup choices there will be some overlap. (table3) Finally the staff are allocated their backup customers ensuring they meet at least their workload ability quota.(table2) Here's a sample set of data for part 1. Each staff members maximum workload (table1) Staff Name Workload Bennie 5 Marchelle 3 Gita 7 Albert 4 Cecil 6 A list of customers, their pre-allocated staff member, and the Backup1 to be solved (table2) Customers Staff Staff Name Primary Support Backup Ada Gita ? Awilda Marchelle ? Beula Gita ? Brenna Bennie ? Delora Bennie ? Elisha Albert ? Esta Marchelle ? Janis Albert ? Jann Gita ? Julissa Cecil ? Kareem Cecil ? Lacy Gita ? Maida Gita ? Marta Cecil ? Nadene Bennie ? Nanci Cecil ? Precious Albert ? Rebbeca Cecil ? Rene Gita ? Roy Albert ? Sherie Cecil ? Soo Bennie ? Soraya Gita ? Suzi Marchelle ? Wilmer Bennie ? And here is a sample set of data for the staffs choices as backup (table3) Staff Customers Bennie | Ada Elisha Janis Kareem Lacy Nanci Marchelle | Beula Brenna Delora Julissa Maida Precious Rene Soo Suzi Gita | Ada Delora Esta Julissa Marta Nadene Roy Soo Albert | Awilda Esta Jann Julissa Maida Nadene Rebbeca Sherie Wilmer Cecil | Beula Elisha Esta Lacy Nadene Precious Rene Soraya As you can see each staff member has picked at least or more than their current workload figures. I now need to distribute each staff member as the backup making sure that they are not already the primary support, and everyone gets a single unique backup based on the staff choices from table3. I know this is simple enough to achieve by hand using simple trial and error however with a larger sample set of tens of staff and hundreds of customers this becomes a lot more difficult. I hope I have explained the question correctly and please feel free to give me some pointers on how I can achieve this.Need help with Solver or a better option
I am not sure if this can be done in solver or if there is something better to use. I am querying a large data set of golf stats from multiple excel spreadsheet into 1 combined stats spreadsheet for fantasy golf. Now that I have that, I want to analyze the data to get the bet options. Below is my info. My objective - Maximum Score. I have 2 different custom scores I am looking at, I will decide on which one to use after analysis Item to Change - Golfers. Can be either the name, identifier or combo Constraints Must chose only 6 golfers Max $ spent Min $ spent Number of Lineups to create - 1 to XXX I will be choosing a subset of X number of golfers from X number of entries (i.e. 45 of 150) for consideration I would also like to set a maximum number of times to use each (i.e. Golfer A max 6 times, Golfer B max 3, etc.) When solved, I would like the results on a separate tab in separate cells: G1, G2, G3, G4, G5, G6, Total Score, Total Cost. Any help is greatly appreciated.
Simple Solver Problem - Can't get it to work
Hello I have a simple solver problem that I can't seem to get to work and I'm not sure why. It is a simple distribution of resources problem, and what I'm trying to solve for is the smallest number of exchanges required to meet the constraints. Here's a photo of the excel sheet: So in the above example, Brad is short 996 units, Matt is owed 294 units, etc. The red cells are the variable cells. The blue cells sum the totals next to each person and the corresponding red cells. The net should equal 0. So the blue cells are constraints that must = 0. Finally, the objective is the count cell. I want this to be the minimum number of "exchanges" that need to occur. I.e. Brad gives Matt 294 (now Matt's balance = 0), Brad gives John 143 (now Matt's balance = 0), and that's 2 exchanges. Here is a screenshot of the solver parameter setup: Here is a solution that I came up on my own...just plugging in numbers: See how the count = 9, the total number of exchanges. However, when I run the solver solution, no matter if I use GRG Non Linear or Evolutionary, I get something like this...which is certainly not the minimum since I was able to find a solution for 9 exchanges just guessing...(10 is greater than 9): What am I missing? This seems pretty simple for solver, but I can't get it to work. Please help! Thank you,
MS Solver Error. Not working.
Hi guys, I just upgraded my Office from 32bit to 64bit and since then my solver addin won't work. It appears at the right hand side of my screen, I can set target values, variables etc. but when it comes to running the addin a error message at the bottom pops up saying: "You do not have the permission for this action." I'm already running Excel as admin, reinstalled the addin, completetly deleted the solver folder etc. I have no clue. Anyone knows what to do? Thanks in advance!
