Can Excel be Used for Design Optimization? Tips and Resources for Success

• piyudhaker
In summary: When you run Solver, Excel displays the Solver dialog box. In the Solver dialog box, you specify the target cell and the adjustable cells. You can also specify the constraints. The Solver dialog box also provides information about the problem and the solutions that are found by Solver.In summary, Solver is a suite of commands that can solve problems. You can use it to find an optimal value for a formula in one cell.
piyudhaker
Quick question,

I am taking this analog design class and the class includes a project which requires design optimisation using either excel,or mathcad

Has anyone used excel to optimise the design?

If yes, can you please guide me to the resource on how to setup excel to optimise or give me an example.

P.N: It is kinda urgent

Thanks!

Lets say the design probelm is design of a transresistance using NMOS CG input stage followed by the differential amplifier and then followed by the single ended output stage.

And the design question is to design the transistor width so as to minimise the power.

piyudhaker said:
Lets say the design probelm is design of a transresistance using NMOS CG input stage followed by the differential amplifier and then followed by the single ended output stage.

And the design question is to design the transistor width so as to minimise the power.

You are going to need to make a function which gives the power output as a function of the transistor width. You can either express this in the cells of the spreadsheet or write a macro in visual basic.

Then you need a method of finding the minimum, you could do this graphically or you could use a numeric method. If you use a numeric method you could represent each iteration in a separate cell. Alternatively, you could could create circular reference and let excel calculate the values iteratively (remmerber to set the maxium itteration property). Alternatively you could create a maco that keeps changing the value of the cell where you you specify the transistor width until an optimum value is found.

However I know the method of doing it, but i do no know how to do it exactly in excel. Thats what i am interested in. I know there would be a equation but i do not know how would i run the optimisation using excel. It would be really helpful if i could get an example to look at(the example could have optimisation on any parameter, may be bandwidth or something else need not be necessariliy power)

piyudhaker said:
However I know the method of doing it, but i do no know how to do it exactly in excel. Thats what i am interested in. I know there would be a equation but i do not know how would i run the optimisation using excel. It would be really helpful if i could get an example to look at(the example could have optimisation on any parameter, may be bandwidth or something else need not be necessariliy power)

Why don't you give an example of the function you want to optimize and the numerical method you want to use.

piyudhaker said:
However I know the method of doing it, but i do no know how to do it exactly in excel. Thats what i am interested in. I know there would be a equation but i do not know how would i run the optimisation using excel. It would be really helpful if i could get an example to look at(the example could have optimisation on any parameter, may be bandwidth or something else need not be necessariliy power)

We are not going to give you an example Excel spreadsheet that does optimization. Part of the assignment you have been given is for you to figure out how to do that. What does Excel tell you when you use its Help function to read about optimization?

Something like this probably...

Excel said:
Define and solve a problem by using Solver

Solver is part of a suite of commands sometimes called what-if analysis (what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments.) tools. With Solver, you can find an optimal value for a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) in one cell — called the target cell — on a worksheet. Solver works with a group of cells that are related, either directly or indirectly, to the formula in the target cell. Solver adjusts the values in the changing cells that you specify — called the adjustable cells — to produce the result that you specify from the target cell formula. You can apply constraints (constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.) to restrict the values that Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula.

Yup, I was going to suggest the Solver Add-In for Excel as well.

optimization can be a bit of a PITA. there's really no one way to do it. but in general, you first create what is called an objective function, and try to minimize that. in this case, the objective is power, and the function's independent variable is width. but it could be anything and even multivariate.

from there, the process can be highly iterative for non-trivial objective functions, and it is very easy to get stuck in local minima without finding the global minimum. a bit of random searching and starting over may be required. I'm not really a spreadsheet programmer, but it doesn't seem like the ideal environment for optimization problems.

1. What is optimisation in Excel?

Optimisation in Excel refers to the process of using Excel's built-in tools and functions to find the best possible solution to a problem. It involves setting up a model, defining constraints, and using solver or other functions to find the optimal value for a specific variable or set of variables.

2. How do I use solver in Excel for optimisation?

To use solver in Excel for optimisation, first set up your model with the objective function and any constraints. Then, go to the Data tab and click on Solver. In the Solver Parameters window, select the target cell for the objective function, set the constraints, and choose the appropriate solving method. Click Solve to find the optimal solution.

3. Can I use Excel for multi-objective optimisation?

Yes, Excel has the ability to handle multi-objective optimisation using the solver add-in. By setting up multiple objective functions and defining their respective weights, you can use solver to find the optimal values for all objectives simultaneously.

4. What are the benefits of using Excel for optimisation?

Using Excel for optimisation offers several benefits, including its user-friendly interface, the ability to handle complex models and constraints, and the availability of various built-in functions and add-ins. It also allows for quick and efficient analysis and can be easily integrated with other tools or programs.

5. Are there any limitations to using Excel for optimisation?

While Excel is a powerful tool for optimisation, it does have some limitations. It may not be suitable for very large or complex models, and it may not provide the most advanced solving methods. Additionally, the results may not always be the most accurate, and there is a risk of errors if the model is not set up correctly.

• Electrical Engineering
Replies
2
Views
1K
• Mechanical Engineering
Replies
11
Views
2K
Replies
2
Views
1K
• Electrical Engineering
Replies
3
Views
1K
• Electrical Engineering
Replies
11
Views
1K
Replies
24
Views
2K
• Mechanical Engineering
Replies
5
Views
4K
Replies
1
Views
961
• Electrical Engineering
Replies
1
Views
1K
• Electrical Engineering
Replies
13
Views
2K