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

[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.

Thanks in advance for your help.

P.N: It is kinda urgent

Thanks!

 

[John Creighto]

Why not start with what your trying to optimize then people can give suggestions.

 

[piyudhaker]

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.

 

[John Creighto]

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.

 

[piyudhaker]

Hi Thanks for the reply..
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)

 

[John Creighto]

Hi Thanks for the reply..
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.

 

[berkeman]

Hi Thanks for the reply..
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...

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.

 

[Redbelly98]

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

 

[Proton Soup]

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.