Formula Spreadsheet

An electronic spreadsheet is a tool that can be used to solve an engineering problem. Spreadsheets are commonly used to record, organize, and analyze data using formulas. Spreadsheets are also used to present the results of an analysis in chart form. Although engineers still write computer programs to solve complex engineering problems, simpler problems can be solved with the help of a spreadsheet. 

Connections Parameters Formulas Spreadsheet Calciiation Order XT... 

Connections Parameters Formulas Spreadsheet Calculation Order... 

The Excel spreadsheet is constructed so that on page one, the referenced properties are listed in Column C, and the same with conversion factors to SI units in Column D. Conversion formulas and values calculated in SI Units are in Column E. Column F is a duplicate of Column E, and this can be used for additional calculation by changing to other conditions or to an entirely new case. It is recommended toleave Column E alone for a comparison case and to copy Column F to another page to execute calculations. 

A non-linear regression analysis is employed using die Solver in Microsoft Excel spreadsheet to determine die values of and in die following examples. Example 1-5 (Chapter 1) involves the enzymatic reaction in the conversion of urea to ammonia and carbon dioxide and Example 11-1 deals with the interconversion of D-glyceraldehyde 3-Phosphate and dihydroxyacetone phosphate. The Solver (EXAMPLEll-l.xls and EXAMPLEll-3.xls) uses the Michaehs-Menten (MM) formula to compute v i- The residual sums of squares between Vg(,j, and v j is then calculated. Using guessed values of and the Solver uses a search optimization technique to determine MM parameters. The values of and in Example 11-1 are ... 

To use Table 61.2, which is based on the formula 1/(1 -b 0" 5 where i is the rate of interest and n is the number of years, the relevant factor is found for the rate and number of years and multiplied by the amount for which the NPV is required. Thus to find the NPV for 1500 receivable in 5 years at 10 per cent from the table is found the factor of 0.621 and this, multiplied by 1500, gives an NPV of 931.5. Most spreadsheets used on personal computers include a formula for calculating NPV, so avoiding the need to construct tables. 

Validation also applies to software. In a simple example, you could create an Excel spreadsheet template with fixed formulae to calculate the mean and standard deviation of a range of data. To validate this template you would enter a set of sample data and verify the template-calculated results against the manually calculated results. In order to be confident that the template could be used for further data sets you would password-protect the cell formulae and verify that they cannot be altered without it. 

All formulas are to be written out by hand with the specific numbers in the right places on a sheet provided for the purpose, and the calculations are to be done by calculator. Caution Write the numbers exactly as printed, do not round any digits, or the quality assurance unit (a sort of corporate vice squad) will not approve the report out of fear that someone could have cheated. A validated program can be used. While an Excel spreadsheet as such needs no validation, a simple cell-formula calls for extensive tests and documentation and proof that the sheet is password protected against fraudulent manipulation. On top of that, the analyst s supervisor is required to confirm the calculation and sign off on... 

A Microsoft Excel (Version 5.0 or higher) spreadsheet template form has been developed which allows the calculation of the complete reaction mass efficiency (RME) according to equation (4.1) and raw material cost (RMC) for any chemical transformation. Lines are numbered and line instructions are embedded in the same manner as a personal income tax form. Green metrics are evaluated to determine the greermess of the experiment in a rigorous quantitative way and to determine the bottom line cost of carrying out the experiment. Formula entries are inserted in appropriate cells to facilitate computation. Any... 

Most of the algorithms and formulae discussed in this chapter can be implemented as expressions in computer spreadsheets, and the rest as simple computer programs. Most are also incorporated into the Microsoft Excel spreadsheet program by the Isoplot add-in (Ludwig 1999, in press) as user-available functions and graphical routines (Appendix III). 

The construction of the problem table to find the minimum utility requirement and the pinch temperature is facilitated by using a spreadsheet. The calculations in each cell are repetitive and the formula can be copied from cell to cell using the cell copy commands. 

Figure 7.3 displays a Microsoft Excel spreadsheet containing the formulas and data for an LP transportation problem. This spreadsheet is one of six optimization examples included with Microsoft Excel 97. With a standard installation of Microsoft Office, the Excel workbook containing all six examples is in the file... 

The Excel Solver. Microsoft Excel, beginning with version 3.0 in 1991, incorporates an NLP solver that operates on the values and formulas of a spreadsheet model. Versions 4.0 and later include an LP solver and mixed-integer programming (MIP) capability for both linear and nonlinear problems. The user specifies a set of cell addresses to be independently adjusted (the decision variables), a set of formula cells whose values are to be constrained (the constraints), and a formula cell designated as the optimization objective. The solver uses the spreadsheet interpreter to evaluate the constraint and objective functions, and approximates derivatives, using finite differences. The NLP solution engine for the Excel Solver is GRG2 (see Section 8.7). 

An Excel spreadsheet formulation of this problem is shown in Figures E9.2c and E9.2d. The constraint coefficient matrix is in the range C10 F12 and G10 GI2 contains formulas that compute the values of the constraint functions. These formulas use... 

Figure 6.17 shows a model of a spreadsheet together with a dictionary interpreting the meaning of the pieces in the model. A spreadsheet is a matrix of named cells, into each of which the user can type either a number or a formula. In this simplified example, a formula can be only the sum of two other chosen cells (themselves either sum or number cells). 

Pattern 6.3, Orthogonal Abstractions and Refinement Sometimes the most dramatic simplifications and flexibility come by adopting a whole new view of domain terms (e.g., spreadsheets, cells, and formulas can support accounting, inventory tracking, and baseball scores). 

Spreadsheets (each cell may be the target of formulas in several other cells)... 

Figure 4-32. Excel spreadsheet applying the equation A=(Ct C)1 Y in two ways, stepwise and in one big formula.

This approach uses a lot of space on the spreadsheet, in particular the transpose of a long column is a veiy wide row. However, it is reasonably easy to detect potential errors in the formulas. 

This equation must be translated into the actual weighings required so that a spreadsheet can be drawn up. Errors can easily occur in the calculations unless the individual steps are understood. The sample weight is 0.5000 g and the calculation formula and any spreadsheet must be designed for this and allow for the fact that the original ash is carried out on 1.0000 g. The residue of undigested sample contains four components of the calculation ... 

Purpose of the spreadsheet (e.g., calculation of linear regression, including equation, graph, and formula used)... 

This sheet contains general information concerning the development and use of the spreadsheet. It describes the connection of cells of the various sheets and contains information concerning actions to be taken in redevelopment/update of the spreadsheet (e.g., update of limits and acceptance criteria, data format or formulas). 

Furthermore, the documentation has to describe which formulas are used in the spreadsheet for the calculation and explanation of abbreviations for example ... 

The design section will include information about purpose, system and functional overview, data flow, data sheet formats, and test strategy. The section describes how the requirements are addressed in the spreadsheet. The main part of the design documentation contains a printout of the spreadsheet in the formula view, with line and column headers. The purpose of the individual formulas and/or macro must be described, including the sequence in which the calculation will be performed. In addition, the following information should be provided ... 

The design describes the transformation of the requirements in the spreadsheet. In most cases a printout of the spreadsheet in the formulas view will be appropriate for addressing the transformation of requirements. 

The security strategy selected has to ensure that cells containing formulas (on purpose, by mistake, or by the auto-save function) of the spreadsheet cannot be overwritten. In today s standard office network environment, in some cases, the network itself is, not validated and does not fulfill the electronic record/electro-nic signature requirement. Therefore, the validated spreadsheets should be stored in a protected drive to which only restricted personnel have access. Furthermore, the server used for storage/handling should be qualified. Figure 18.7 is an example. 

The training description will include information about purpose, responsibilities, training deliverables, and training records. In some cases it will require generation of a document explaining the use of the spreadsheet. In this document, the function of the spreadsheet, the formulae used, the location of the secure templates, and the file name and path to use to save the completed form should be included. Training has to be documented. Figure 18.8 is an example. 

The response of many instruments is linear as a function of the measured variable, if variations due to experimental conditions or the instrument are taken into account. The objective is to determine the parameters of the linear equation that best represents the observations. The primary hypothesis in using the method of least squares is that one of the two variables should be without error while the second one is subject to random errors. This is the most frequently applied method. The coefficients a and b of the linear equation y = ax + b, as well as the standard deviation on a and on the estimation of y have been obtained in the past using a variety of similar equations. The choice of which formula to use depended on whether calculations were carried out manually, with calculator or using a spreadsheet. However, appropriate computer software is now widely used. 

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. 

D17 This cell defines the axial velocity of the first node, according to the difference formula (Eq. 4.27). In terms of the spreadsheet variables and cells, cell D17 is defined as... 

Figure D.4 illustrates a spreadsheet that implements an implicit solution to the problem described in Section 4.8. The differences in the spreadsheet for the implicit method and the explicit method in the previous section begin in cell D21, where the difference formula is entered.

A14 - A16 Every time a complex difference formula is entered, remember to save the text somewhere in the spreadsheet. These cells are the text for the various difference equations. It is not unusual during the construction of a spreadsheet that a mistake will cause the cells to become contaminated with, for example, DIV/0 . Since all the cells depend on each other, a mistake in one cell is propagated to all the other cells. The only choice for proceeding is to clear all affected cells and start over. Obviously one wishes to avoid tedious retyping in accomplishing this unwelcome task. 

Formulas begin with an equal sign. Arithmetic operations in a spreadsheet are i addition... 

It doesn t matter whether or not you use spaces around the arithmetic operators.) When you hit RETURN, the number 0.99997 appears in cell C5. The formula above is the spreadsheet translation of Equation 2-4. A 6 refers to the constant in cell A6. (We will explain the dollar signs shortly.) B5 refers to the temperature in cell B5. The times sign is and the exponentiation sign is A. For example, the term A 12 B5A3 means (contents of cell A12) X (contents of cell B5)3. ... 

Now comes the most magical property of a spreadsheet. Highlight cell C5 and the empty cells below it from C6 to C12. Then select the FILL DOWN command from the EDIT menu. This procedure copies the formula from C5 into the cells below it and evaluates the numbers in each of the selected cells. The density of water at each temperature now appears in column C in Figure 2-19d. 

---