Need a book? Engineering books recommendations...

• To: seaint(--nospam--at)seaint.org
• Subject: Re: Two Spreadsheet Questions
• From: Scott Maxwell <smaxwell(--nospam--at)engin.umich.edu>
• Date: Tue, 12 Oct 1999 13:56:46 -0400

```Dennis,

With regards to problem #2, we had a similar problem with a seismic
spreadsheet that we used at our office.  We have a seismic sheet where you
enter in all the code parameters (in our case BOCA code parameter) as well
as physical parameter of the buildings.  The physical parameters would be
the slab/roof extents (floor dimensions for each floor), the floor to floor
heights, lengths of walls, and weights of slab and wall systems.  The
seismic sheet would then calculate length of walls and areas of slab and
then determine the masses of them.  The sheet would then calculate the
center of mass for each floor and the seismic forces based on the weights
and code parameters.

The sheet was originally done in Quattro Pro, but our office uses Excel.
Thus one of our engineers accepted the challenge of creating an Excel
version.  The orignial Quattro Pro spreadsheet would create plots that
would show the out line of the building, the slab and location of walls.
This was used to graphically verify in general terms the inputted data.
Quattro Pro did this with no problems.  However, when the sheet was created
in Excel, we encounterd problems with the plots.  It turns out that Excel
has problems "ignoring" data cells if there is anything in it (i.e. a
formula that produces an error or zero, or a cell with blank text).  A
simple example is a rectangular slab with a hole in the middle for an
elevator.  Our sheet would handle somethings by formula, which resulted in
formulas in "coordinates" table.  To define the "hole" there would have to
be a "blank" line in the table of coordinates.  The result would be that
Excel would treat the "blank" line as zeros (thus creating a point at the
origin) or we could "force" it to be "blank" text by using an "if...then"
statement to insert a "blank" (i.e. if something then "").

The end result, after my long winded dissertation, is that Excel will plot
a point if there is ANYTHING in the cell.  If it has a number value (this
would be any number including zero OR an error such as #DIV/0! or
#NUM...except maybe #N/A), then it treats it as a numerical data point with
error values being treated as a zero.  If it has a text value then it will
switch the graph to a basically a "line" chart rather than a "X-Y scatter".
The result that we found is that you need to make sure the cell(s) are
actually empty.  In our case, the guy who created the spreadsheet made
macros to modify the "empty" cells to make them really empty (in our case
deleting the formula).

So try clearing the cells with zeros in them.

Hope this helps,

Scott

------------------------------------------
Scott E. Maxwell, PE
Structural Engineer

SHG Inc.
500 Griswold, Suite 500
Detroit, MI  48226

Phone: (313) 442-8254
Fax:   (313) 442-8297

Work Email:     smaxwell(--nospam--at)dt.smithgroup.com
Personal Email: smaxwell(--nospam--at)engin.umich.edu
------------------------------------------

At 09:02 PM 10/11/99 EDT, you wrote:
>I need some help. Here are two problems that I need to resolve and I could
>
>Problem 1: I want to find the maximum and minimum values for a column of
>numbers and wish to ignore all zero values. In otherwords, how do I find the
>minimum values of all numbers in a list greater than zero?
>
>Problem 2: When creating a chart that draws the perimeter of a building from
>the coordinate input the starting point and ending point are the same.
>However, if there are more values in the column than are needed, the excess
>values are left simply as zeros.
>When charting from my input, the last point entered is connected back to the
>origin on the graph. I wish to have it ignore the zero values and graph only
>the values that start and end on the same point.
>Can anyone help me on these two problems????
>
>Thanks
>Dennis Wish PE
>
>

```