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

RE: RE: Excel Question

[Subject Prev][Subject Next][Thread Prev][Thread Next]
Gerry,
I was only interested in the macro. I thought it would be great to post
and discuss how the macro works in order to learn from it. At the time,
I thought of a similar problem I had in a spreadsheet I created to
analyze a URM building. The problem involved the push tests on a
building. A minimum of two tests are to be taken per wall elevation, per
level. One test per 1,500 square feet of wall beyond this. If I recall
correctly, the engineer had to remove the top 20% of the test results
and then take the next highest value. This was one part of the
spreadsheet that I had always completed manually because I could not
figure out how to write the macro. Similarly, the macro can be used in
the Rigid Diaphragm Analysis to discover the greatest shear in all lines
of resistance to calculate Rho.
I respect anyone who desires to protect his work and those who create
software with the intention of profiting from it. I don't agree that a
spreadsheet creates a competitive edge any longer. This may have been a
true statement ten years ago as very few companies used computers and
software, but the edge no longer exists in all but the type of work that
simply can not be done by hand to a point where it can produce an
ecconomical design. FEM  programs are a good example of this. Not for
simple frames, but for the design of high-rise structures where the
design pushes the strength limits to minimize material costs.
Having spent literally thousands of hours to create spreadsheets that
are donated to the professional community, I am not too sympathic to
those who don't care to share utilities with the intent on developing or
evolving one professionals ideas into more sophisticated and useful
tools. In my opinion, we don't share our work like we should. There are
bits and pieces of innovative and creative ideas in the templates,
spreadheets, VBA macros and other productivity tools that we create.
Most of these reach a point of stagnation due to the developers limits.
Imagine how much more efficient and useful the tools could become if the
community acted like one large developer - incorporating the indeas of
those few who have something new and creative to contribute.
Some years ago I had this idea that I would donate my spreadsheets,
Mathcad tools and other templates that I use to the public domain on the
condition that those who use them and have the ability to improve upon
them would do so and donate their work back to the community. I coined
the idea as "Evolutionary Software". Although I still believe it is a
valid idea, there are few who contribute let alone improve upon existing
software.
With that in mind, I received a call last week from an engineer who
downloaded the lateral design spreadsheet that David Merrick and I
worked on. He had incorporated some of his ideas into the spreadsheet
and corrected some of the errors he discovered. He offered to e-mail me
the spreadsheets when they are ready to be posted back on the SEAOC and
Structuralist.Net discussion forum. In return his name and errata will
be added to the software as a contributer. This is what I was hoping to
achieve and in time I think that we will see more contributions from the
professional community.
In the mean time, I do respect another engineers desire to protect his
or her work. However, I have greater respect for those who contribute to
their profession with the intention of improving the tools and conversly
the designs produced to create ecconomic and safe buildings.

Dennis

-----Original Message-----
From: Gerard Madden [mailto:gmadden(--nospam--at)mplusl.com] 
Sent: Tuesday, July 17, 2001 7:13 PM
To: dennis.wish(--nospam--at)gte.net; seaint(--nospam--at)seaint.org
Subject: Re: RE: Excel Question


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 


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