Need a book? Engineering books recommendations...
Return to index: [Subject] [Thread] [Date] [Author]
Re: Two Spreadsheet Questions
[Subject Prev][Subject Next][Thread Prev][Thread Next]- 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 >use some advice from you spreadsheet experts out there. > >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 > >
- References:
- Two Spreadsheet Questions
- From: Seaintonln
- Two Spreadsheet Questions
- Prev by Subject: Re: Two Spreadsheet Questions
- Next by Subject: Re: Two Spreadsheet Questions
- Previous by thread: Re: Two Spreadsheet Questions
- Next by thread: Re: Two Spreadsheet Questions
- About this archive
- Messages sorted by: [Subject][Thread][Author][Date]