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

Re: RE: Excel Question

[Subject Prev][Subject Next][Thread Prev][Thread Next]
Dennis,

I don't think the original author would want me to do that. He was a little hesitant just giving it to me and it was made over the years with a great deal of time invested in it by him. I just made some improvements since he gave it to me on Friday of last week.

It is tools like these that keep us competitive... especially on OSHPD jobs like what this spreadsheet was created for.

I think it is a valid reason not to share, although many people share their work very generously for all to use on your website. I have downloaded several from your site to check them out, but I have yet to use any. I mainly use them to study their structure and see how other people present their calculations.

I can post the macro itself, but not the spreadsheet.

I guess that's why not ....

I'm at home now ( I had jury duty - didn't get picked) so I'll post the macro tomorrow.

-gerard
SF, CA


>>> dennis.wish(--nospam--at)gte.net 07/17/01 16:47 PM >>>
Gerry,
Why not share the macro with us and I'll post it on the VBA Discussion
Board. This would be very interesting to follow.
Dennis

-----Original Message-----
From: Gerard Madden [mailto:GMadden(--nospam--at)mplusl.com] 
Sent: Monday, July 16, 2001 10:05 AM
To: seaint(--nospam--at)seaint.org
Subject: RE: Excel Question


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 mx 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-----
From: Gerard Madden [mailto:GMadden(--nospam--at)mplusl.com] 
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 setthe 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?

TIA

-Gerard



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)mplusl.com 
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 
*   without your permission. Make sure you visit our web 
*   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 USubscribe, 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 
*   without your permission. Make sure you visit our web 
*   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 
*   without your permission. Make sure you visit our web 
*   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 
*   without your permission. Make sure you visit our web 
*   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 
*   without your permission. Make sure you visit our web 
*   site at: http://www.seaint.org