# RE: Excel Question

• To: <seaint(--nospam--at)seaint.org>
• Subject: RE: Excel Question
• Date: Mon, 16 Jul 2001 10:05:02 -0700
```Thanks for all the replies...

My friend sitting next to me kindly wrote about a 10 line macro that did the trick without having to plot everything in table form to find the max.

What his macro did was it went through the entire range of angles, recorded the maximum value for as it went through each iteration, stored that value and compared it to each result from each iteration. If the iteration produced a value greater than the maximum previously calculated, it would update the max variable and continue through until all the range values were checked with the final step being to display the solution. He also showed me how to change the iteration value and range if I wanted to in the future.

VBA is really slick ... I have a book and the language seems very straight forward ... I just haven't had the time to try it myself.

thanks again to all.
-Gerard

>>> Mark.A.Jones(--nospam--at)jacobs.com 07/16/01 09:32AM >>>
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
*   site at: http://www.seaint.org

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