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]
Thanks to all of you who responded, here are the two solutions that appear to 
be the simplist.

1. To prevent a column of numbers from averaging in the zero values that fill 
unused cells in each column, simply clear the data from these cells. Excel 
will disregard empty cells but will include any cell that contains a zero. 
Also, be careful if you have hidden the zeros from view (tools / options / 
display / zeros). Make sure that unwanted cells are cleared of any values.

2. The trick to charting a finite set of cells in a column is to make sure of 
two things. The first and last points must be equal AND all subsequent points 
in the list must be set equal to the first and last point entered. This is 
simple to do using two blocks of columns. The first is where you input data 
and the second is where the data is stored. Set up an If / Then function in 
the storage block that states:

If the value in this input cell is equal to zero, 
then enter the value for the cell above. 

You start this on the second cell down the list:

=IF(B3=0,B2,B3)

This way all cells in the column will end on the last point entered. If no 
values are input, all cells will be Zero.

The problem that occured in my spreadsheet was rather simple but took a long 
time to find. IN order to avoid zero value evaluation for minimum values, I 
added an arbitrary small value (0.0000001) so that the spreadsheet would not 
consider it zero in possible calculations that results in zero in the 
denominator (leads to divide by zero errors). HOWEVER, this lead to another 
more serious problem as the value was divided by this arbitrary 0.000001 
value resulting in a false but large value. This may have effected the overly 
conservative results I was receiving in the torsion analysis of my 
spreadsheet. 
To compensate, I added a conditional that tested the input coordinates for 
the start and finish of a wall. If the square root of these numbers equals 
zero, the cell value was zero rather than receiving a Divide by Zero error.

Sound confusing - it really is not, but I have to admit that I stared at the 
problem for months and never saw the simple solution that lay benigth.

Thanks again for all who responded.

Dennis 

In a message dated 10/13/1999 7:31:10 PM Pacific Daylight Time, 
jwatson(--nospam--at)inconnect.com writes:

<< You could define the limits of your plot so that the origin is out of
 the view able area, technically is will still print the points but you
 won't see them.  Another option is just let the program print the
 points.  If you start and end your drawing at the origin, then any
 duplicate points will simply plot on top of each other and you won't see
 them.
 
 Hope this helps,
 
 Jake Watson, E.I.T.
 Salt Lake City, UT >>