# RE: Excel Question

• To: "'seaint(--nospam--at)seaint.org'" <seaint(--nospam--at)seaint.org>
• Subject: RE: Excel Question
• From: "Jones, Mark A" <Mark.A.Jones(--nospam--at)jacobs.com>
• Date: Mon, 16 Jul 2001 12:32:14 -0400
```IIRC, you can have Excel show the iterations.  Do this then do a max() or
dmax().  dmax() is for searching databases, if you put it in that format.
This should eliminate the local maxima and minima.

Mark Jones

-----Original Message-----
Sent: Friday, July 13, 2001 8:29 PM
To: seaint(--nospam--at)seaint.org
Subject: Excel Question

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

TIA

-Gerard

Civil Engineer

Middlebrook + Louie, Structural Engineers
71 Stevenson Street, Suite 2100
San Francisco, CA 94105
Tel: 415.546.4900
Fax: 415.974.3680
Web: www.mplusl.com

*
*   This email was sent to you via Structural Engineers
*   Association of Southern California (SEAOSC) server. To
*   subscribe (no fee) or UnSubscribe, please go to:
*
*   http://www.seaint.org/sealist1.asp
*
*   Questions to seaint-ad(--nospam--at)seaint.org. Remember, any email you
*   send to the list is public domain and may be re-posted
*   site at: http://www.seaint.org

============================================================================================
NOTICE - This communication may contain confidential and privileged information that is for the sole use of the intended recipient.  Any viewing, copying or distribution of, or reliance on this message by unintended recipients is strictly prohibited.  If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer.

==============================================================================

*
*   This email was sent to you via Structural Engineers
*   Association of Southern California (SEAOSC) server. To
*   subscribe (no fee) or UnSubscribe, please go to:
*
*   http://www.seaint.org/sealist1.asp
*
*   Questions to seaint-ad(--nospam--at)seaint.org. Remember, any email you
*   send to the list is public domain and may be re-posted