Return to index: [Subject] [Thread] [Date] [Author]

Excel Question

[Subject Prev][Subject Next][Thread Prev][Thread Next]
This is for all you PHD's  :>)

I using the SOLVER tool on Excel to perform iterations to find a worst case value. The value is dependent on a number of variables in the spreadsheet.

What I am asking it to do is to change the value of one cell until another reaches its maximum value. In this case, I am asking for an angle. from 0 to 180 degrees, that gives me maximum force on a group of bolts.

What the problem is, as explained to me by a PHD who sits next to me, is that the curve for the function can have several peaks and valleys. The SOLVER tool built into the program searches for a LOCAL MAXIMUM from the initial point entered in the cell.

For example, if I have a function that has 3 peaks and 4 valleys

Valley 1 = 0 degrees
Peak 1 = 35 degrees
Valley 2 = 50 degrees
Peak 2 = 80 degrees
Valley 3 = 115 degrees
Peak 3 = 125 degrees    = CORRECT ANSWER
Valley 4 = 180 degrees

Assuming straight lines between peaks and valleys in the function (for simplifying my example)

Peak 3 gives me the worst case force and is the true correct answer. The solver will only select Peak 3 if my initial input value is Greater than Valley 3 and Less than Valley 4. What I want is to set the cell to zero and have it go through the entire range of values, from 0 to 180, and spit out the right number

My PhD colleague says there are more sophisticated solver modules.

My questions are ... does anyone have any hints or know of a place I could download a solver that would do what I want?



Gerard Madden, P.E.
Civil Engineer

Middlebrook + Louie, Structural Engineers
71 Stevenson Street, Suite 2100
San Francisco, CA 94105
Tel: 415.546.4900
Fax: 415.974.3680
Email: gmadden(--nospam--at)

*   This email was sent to you via Structural Engineers 
*   Association of Southern California (SEAOSC) server. To 
*   subscribe (no fee) or UnSubscribe, please go to:
*   Questions to seaint-ad(--nospam--at) Remember, any email you 
*   send to the list is public domain and may be re-posted 
*   without your permission. Make sure you visit our web 
*   site at: