Spreadsheets useful syntaxes

The examples herein are done using Excel98, on a Macintosh personal computer. It is easy to anticipate some changes as Excel progresses to newer releases, and there well may be some small differences between IBM-compatible PCs and the Macintosh implementations. While the syntax and communication interfaces will likely change in the future, the functional requirements for solving certain problems will not. The descriptions provided in this appendix are quite detailed nevertheless, some knowledge of spreadsheet operations is presumed. 

B1 - B2 These cells contain the values of the variable names in cells A1-A2. The association of the numbers in B1-B2 with the names in A1-A2 is accomplished with the INSERTJMAMEJDEFINE command. (The syntax INSERT NAMEJDEFINE means to use the INSERT pull-down menu, followed by the DEFINE and NAME sub-menus. In many cases there are also convenient keyboard shortcuts that avoid actually using the pull-down menu.) As much as possible, it is important to define names that appear in subsequent formulas. It is very difficult to read and debug a spreadsheet that is programmed entirely with explicit cell references. The variable name for the annulus gap thickness Delta r is in A3 and the value is computed in B3 as = r.out - r in. 

Spreadsheets have program-specific sets of predetermined functions but they almost all include trigonometrical functions, angle functions, logarithms (p. 262) and random number functions. Functions are invaluable for transforming sets of data rapidly and can be used in formulae required for more complex analyses. Spreadsheets work with an order of preference of the operators in much the same way as a standard calculator and this must always be taken into account when operators are used in formulae. They also require a very precise syntax - the program should warn you if you break this ... 

The most popular spreadsheet programs include Microsoft Excel, Lotus 1-2-3, and Quattro Pro. Because of its wide availability and general utility, we have chosen to illustrate our examples using Microsoft Excel on the PC. Although the syntax and commands for other spreadsheet applications are somewhat different from those for Excel, the general concepts and principles of operation are similar. The examples we present may be accomplished using any of the popular spreadsheet applications the precise instructions must be modified if an application other than Excel is used.- In our examples, we will assume that Excel is configured with default options as delivered from the manufacturer unless we specifically note otherwise. 

Example = sin(A5) is an example of a function in Microsoft8 Excel11. If you write this in a cell, the spreadsheet will calculate the sine of the number in cell A5 (assuming it to be an angle in radians) and write it in the cell. Different programs may use a slightly different syntax. 

This can simply be done in Excel using the function TDIST with syntax TDIST(f, df, tails). Therefore in a blank cell of a spreadsheet you would input =TDIST(2.23, 10, 2). 

Let s calculate the standard deviation of the results. Highlight cell B9. Under the Statisticcd function, select STDEV for the Function name. Alternatively, you can type the syntax into cell B9, =stdev(B4 B7). Now copy the formula to cell C9. Perform the standard deviation calculation using Equation 3.2 and compare with the Exel values. The calculation for series A is 0.05 mL. The value in the spreadsheet, of course, should be rounded to 0.05 mL. 

The table below shows the progression of applying the bisection method. It uses the Excel IF function to choose whether f (a) or f (b) is replaced by f (c) (and correspondingly whether a or b is to be replaced by c). The syntax for the IF function is as follows (see the explanation of the Excel spreadsheet below for a full explanation of how the IF function is used) ... 

---