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]

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)   | this distance" (last words of Gen.
.......................................| John Sedgwick, Spotsylvania 1864)

******* ****** ******* ******** ******* ******* ******* ***
*   Read list FAQ at:
* * This email was sent to you via Structural Engineers * Association of Southern California (SEAOSC) server. To * subscribe (no fee) or UnSubscribe, please go to:
* Questions to seaint-ad(--nospam--at) 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: ******* ****** ****** ****** ******* ****** ****** ********