Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Optimisation using excel

  1. Feb 19, 2009 #1
    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

  2. jcsd
  3. Feb 19, 2009 #2
    Why not start with what your trying to optimize then people can give suggestions.
  4. Feb 19, 2009 #3
    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.
  5. Feb 19, 2009 #4
    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.
  6. Feb 19, 2009 #5
    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)
  7. Feb 19, 2009 #6
    Why don't you give an example of the function you want to optimize and the numerical method you want to use.
  8. Feb 20, 2009 #7


    User Avatar

    Staff: Mentor

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

  9. Feb 20, 2009 #8


    User Avatar
    Staff Emeritus
    Science Advisor
    Homework Helper

    Yup, I was going to suggest the Solver Add-In for Excel as well.
  10. Feb 20, 2009 #9
    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.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook