Excel Numerical Solver

[Hebe Zuelke]

Ihave tried using Solver to maximize the projected point total, but the variable fields in this case would be the Player's Names and it seems like the variable field needs to be a number, not a list of strings.

Solver manages to find an optimal solution. The problem is really small and it is very quick. Solver works with up to 200 variables and 100 constraints, for large problems you will need the (commercial) extended version:

Of course, you can just order the real player names so that they fit this setting. For example, if you sort the players of each list alphabetically, then (Player A, QB) = first player of team QB, etc.

Excel's solver is built on numerical methods. Applying to a domain that consists of discrete values, like strings or football players is probably going to fail. You should consider writing a brute force solver in a "real" programming language, like c#, java, python, ruby, or javascript. If there are performance problems, then optimize from there.

Make a spreadsheet that has every possible combination of position players (that meet your criteria) on each row. Then make an Excel formula that calculates projected point total based on the players in that row. Sort the spreadsheet by your projected point column.

When I purchased my IMAC the sales person said that apple software wasn't any different from Windows application. Well I am finding out that I am unable to do alot with the applications. I am unable to do my class assignment in numbers because I do not have the SOLVER as excel, so I wanted to know if anyone knows of an additional application or an add-on so I would be able to completed my assignments as needed.

Thank's for the information, but this will not help I am in a class that combines stats, equations, etc.. Its a business class an combines all math to solve problems. This week we are working on "pretend" manufacturing company and I am unable to do my class assignments base on apple application "numbers". With SOLVER I can put what I need an calculate all my formulas. Again appreciate the information.

To use Solver, click the Solver button on the Data tab. (The bundled version of Solver doesn't use the menu Tools Solver.) Enjoy using Solver, and click here for Solver Help, applicable to both Excel 2010 for Windows and Excel 2011 for Mac.

"Solver" is a numerical analysis module to find the maximum, minimum, or zero value of a formula by tweaking some of its inputs. It usually does this by iteratively changing the inputs to find a reasonable approximation of the targeted value.

We understand your goal. What we are saying is that's not possible. There is no single equation that can solve for Pe. You'll need to use numerical methods of calculation. In Mathcad, this could be a solve block or root function. In Excel, the solver is probably your best option. In Excel, you could also have a table of possible values for Pe, calculate epsilon for each, and then do a lookup for the closest match.

Not everything that can be solved numerically (e.g. solve block) can be solved symbolically. Due to the complexity of this equation, I'm not surprised that no solution is found. It might be possible to find a solution by applying some constraints. Use the symbolic keyword "assume" before "solve". For example, is gamma always bigger than 1? If so, include "assume, gamma>1".

If you're familiar with VBA programming, you could create a custom function that performs a similar calculation and then iterates until the variance is below an acceptable level. Maybe someone in an Excel forum even has a better idea for a custom function.

PerformOptimization() starts with a line search using ExponentialLineSearch(). During the initial line search the optimizer has no idea how close it is to a local minimum. It could be right next to the minimum, or it could be 1,000,000 units away. The line search starts by finding the direction of the negative gradient (the direction where the function is decreasing). Suppose that direction is to the right. The algorithm recognizes this and starts by checking a point 0.05 to the right of the starting point. Then 0.1 to the right of that. Then 0.2 further than that. Then 0.4, 0.8, 1.6, etc. The idea is that the more steps it takes to find where the derivative changes sign, the further away the local minimum must be. By exponentially increasing (i.e. by doubling) the search distance, we are covering more ground in less time. The Excel Solver uses a similar strategy for its line search.

At this point we have two points L and R (formerly called B and C), and we need to adjust them to be closer together in the x direction. In other words, we have good starting conditions and now we can begin using parabolic approximations in the false position method.

The next step is to choose the two best points. QUpdateLR() does that. A numerical optimization algorithm has a duty to use the lowest possible (valid) y value it finds at any step in the algorithm. This algorithm fulfills that duty by taking the point with the lowest y value among the four of them. It then looks at the derivative. If the derivative is negative, that point becomes the new L. If the derivative is positive, that lowest point becomes the new R. Once that point is assigned, the algorithm has to choose the complementary point. If it just assigned the new point to L, it looks at the several points to the right for the nearest one with a positive derivative. If it assigned the new point to R, it looks at the several points to the left and uses the nearest one with a negative derivative. The points Left and Right are now closer together, and this is the essence of the false position method.

The exit condition relating to numerical stability is an examination of the two x values of points L and R. It takes their ratio and subtracts 1. If the result is within 5E-9, the points are deemed too close together and therefore unsuitable for the fitting process. The numerical instability would manifest itself as a divide-by-zero error in Solve3x3AugmentedMatrix(), and you can see this for yourself by forcing the IF statement to evaluate to False. Long story short: the exit condition is that the optimization has run as far as it can practically go. Usually this is less than 10 iterations.

Excel has a rule that a user-defined function can return a value but cannot change anything anywhere else on a spreadsheet. Because Minimize() is blatantly circumventing that rule, this program has to take on a lot of the responsibilities that Excel normally takes care of. It maintains a list of all of the cells that contain calls to Minimize(). It remembers the merit function values of each of them (to determine whether something has changed and therefore needs to be optimized again). It has to do its own error checking.

If in an equation multiple values are unknown but the target value and some other values of the equation are known, can we guess those multiple unknown values using any spreadsheet or excel function or using any other method?

The PolymathPlus solver is available as a web application or a Windows desktop application. A link to the online solver becomes visible on the left menubar after login. We are encouraging users to switch to the online version while keeping support for the Windows desktop application for paid licenses.

Our internal algorithms have been extensively used over the years by many engineering faculties in universities across the world. Students and professionals will appreciate the ease of use and the comprehensive solution reports of our application.

PolymathPlus can solve linear equations of any size. The number of equations should be equal to the number of unknown linear variables. Here's an example of 3 linear equations to be solved simultaneously:

When solving simultaneous nonlinear equations, the equations have to be written as root expressions (expressions that are equal to zero in the solution). In addition, an initial guess should be provided for each nonlinear variable.

Below is a sample problem and data entry for solving 2 nonlinear equations:

When entering the problem into PolymathPlus, we should enter the root expressions, each associated to a nonlinear variable function. We should also provide an initial estimate for each nonlinear variable. In the example below, we have 2 nonlinear variables x and y, hence 2 root functions, and we provide an initial guess of 1 for both x, and y.

In case we only have one nonlinear equation to solve (with any number of auxiliary expressions), we need to provide a min/max estimates for the nonlinear variable. For example, below is a PolymathPlus program for solving one nonlinear equation where we need to evaluate V:

PolymathPlus supports curve fitting for polynomial, multi-linear, and nonlinear regression. The report evaluates the model variables, generates a regression chart, residual plots, and provides statistics on the accuracy of the model.

Here is an example of the data entry required for solving a linear regression model for a given set of data points.

We solve a polynomial regression model with the order of 1 (hence linear regression) which best fits the linear equation matching the given data points.

Below is an example of a data entry for solving nonlinear regression model for a given set of data points. The model variables to be found are a and b, for which we should also provide an initial guess.

PolymathPlus enables users to customize the format of solution reports and the selection of algorithms using hints. These hints are stored within the PolymathPlus program file and are prefixed with #@.

3a8082e126