Need a book? Engineering books recommendations...

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

RE: Sharing Neils tip on Neat Excel Equation Writer

[Subject Prev][Subject Next][Thread Prev][Thread Next]
If tools\calculation\accept labels in formulas is set on. Then excel will
interpret text to the left of a number as a label for that cell, without
creating a cell name. I found this out by accident when I wrote the formula
first before defining the "variables". Whilst the values and labels were
correct in the local context, I hadn't intended on naming those cells for
the inputs to the current formula. I now have the feature turned off. (An
equals sign after the text, prevents it from becoming a cell label.)

(insert>name>apply is good to know, I used to use Borland Quattro Pro, and
it automatically replaced cell references with cell names.)

(I have a vague memory that Multiplan permitted naming of blocks and then
using Row, column references to the cells in the named block. Sometimes this
would be useful, anyone know if this is possible in excel.)


Regards
Steven CONRAD Harrison
B.Tech (mfg & mech), MIIE, gradTIEAust
mailto:sch.tectonic(--nospam--at)bigpond.com
Roy Harrison & Associates
Consulting Engineers (Structural)
PO Box 104
Para Hills
SA 5096
South Australia
tel: 8395 2177
fax: 8395 8477

-----Original Message-----
From: Christopher Wright [mailto:chrisw(--nospam--at)skypoint.com] 
Sent: Tuesday, 27 March 2007 02:13
To: seaint(--nospam--at)seaint.org
Subject: Re: Sharing Neils tip on Neat Excel Equation Writer


On Mar 26, 2007, at 2:58 AM, G Vishwanath wrote:

> Excel's inability to make cell formulas read exactly like we write  
> them on paper was the only major grouse I had against it. Excel's  
> formulas are compact and good for execution but not for  
> documentation and later reviewing.
> The multilevels of parentheses, the mathematical operators etc are  
> all in one line and it takes some effort to read and understand  
> them in all except trivial cases.
Use names. I have a series of boiler code spreadsheets I had the same  
problem with, beginning with one sheet for each situation. First  
thing to do is bind them into a workbook so the kinds of information  
that should be commonly available to all calculations is in a single  
spot. My variables are arranged by name in the first column and each  
separate calculation in subsequent columns. Naming everything is  
trivial. Pick a range where your calculations will go and include  
that first column. Do insert>name>create and defines each row or  
portion with the name you've put in the first column. If the  
subsequent columns have formulas, select them and do  
insert>name>apply. The formula cell references are replaced by the  
names automagically. So the obscure B4*B11/(2*B9*B10-1.2*B4) becomes  
the highly readable P*D/(2*S*E-1.2*P) in the twinkling of an eye.

Another useful device is user functions written in Visual Basic. You  
can do iterative arithmetic, complicated algebra or table look-ups  
without cluttering up your worksheet  You need to work at VBA for a  
little, but there are good books around. And as people wise up and  
replace their Windows iron with Macs, the worksheets translate  
transparently.

Christopher Wright P.E. |"They couldn't hit an elephant at
chrisw(--nospam--at)skypoint.com   | this distance" (last words of Gen.
.......................................| John Sedgwick, Spotsylvania  
1864)
http://www.skypoint.com/~chrisw/



******* ****** ******* ******** ******* ******* ******* ***
*   Read list FAQ at: http://www.seaint.org/list_FAQ.asp
* 
*   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 
******* ****** ****** ****** ******* ****** ****** ******** 



******* ****** ******* ******** ******* ******* ******* ***
*   Read list FAQ at: http://www.seaint.org/list_FAQ.asp
* 
*   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 
******* ****** ****** ****** ******* ****** ****** ********