Excel add-ins

As a convenience to readers, the editors have asked that the following index to relevant Isoplot functionality be included. This Excel add-in program is available from the author upon request (Ludwig 1999, in press). 

Figure 5.6 Example of output from the SPC XL control chart plotting MS Excel add-in.

Perform standardised cross-validated PLS on the data (note that the property parameter should be mean centred but there is no need to standardise), calculating five PLS components. If you are not using the Excel Add-in the following steps may be required, as described in more detail in Problem 5.8 ... 

If this is your first experience of cross-vahdation you are recommended to use the Excel Add-in, or first to attempt Problem 5.8. 

Tom Thurston and Les Erskine are thanked for a superb job on die Excel add-in, and Hailin Shen for outstanding help with Matlab. Numerous people have tested out die answers to the problems. Special mention should be given to Christian Airiau, Kostas... 

The Group Theory Calculator [the GT Calculator] is the set of interactive EXCEL spreadsheet files, one for each of the main molecular point groups, on the CDROM supplied with this manual. The group theoretical calculations, which can be performed with the calculator, are rendered possible because of the enhancement of the basic spreadsheet operations and displays using Visual Basic for Applications code and the complex-arithmetic routines available in the Analysis Tool Pack EXCEL Add-ins . Since the Analysis Tool Pack is not one of the standard components loaded in a typical installation of the EXCEL software package, it is necessary, before attempting to use the GT Calculator, to ensure that your version of the EXCEL programme includes this extra component. With any spreadsheet open, check the Add-ins list in the TOOLS menu on the main EXCEL toolbar and, if necessary, install this component in the usual way. 

To save a workbook as an Add-In, choose Save As... from the File menu. Choose Microsoft Excel Add-In from the Save File As Type drop-down list box, then press OK. In Excel for Windows, Add-In macros are automatically given the filename extension. xla. 

Optimization model was built in Excel spreadsheet as proposed. Inputs for the optimization were 30-days average mean return for each share, variance-covariance matrix, and initial investment (at the beginning of each month). Excel add-in Solver was implemented into the macro and used to minimize portfolio s variance at the beginning of the each month. For each optimization. Solver was calibrated as the minimum of... 

This is a particularly interesting feature that is widely used along the examples. It is the tool we can use within Excel to solve numerically a set of equations, problon optimization including fitting a set of data to a given linear and nonlinear equation and more. Solver is an add-in that needs to be activated to be used. For enabling it, we need to click on Office Button (top-left comer), then Excel Options, and in the Tab Adds-Ins, click on bottom go (Manage Excel Adds-Ins) there, look for... 

Solver is an Excel add-in that allows the user to iteratively solve systems of equations. Unfortunately, it is not installed by default on most computers. 

The Data Analysis add-in in Excel is another very useful Excel add-in that can improve the ability to perform certain statistical tasks. It is installed using the same procedure as installing the Solver add-in (see Sect. 8.4.1 Installing the Solver Add-In). In order to start Solver, in Excel 2007 or newer, locate the Data ribbon and go to the extreme right-hand side in the area marked Ana ly s i s. Solver should be there as shown in Fig. 8.7. In Excel 2003 or older, go to Tools —> Data Analysis. 

The Solver Parameters dialog box is shown in Figure 8-5. Click on Solve. The optimal solution should be returned. If Solver does not return the optimal solution, solve the problem again after saving the solution that Solver has returned. (In some cases, mnltiple repetitions of this step may be required because of some flaws in the version of Solver that comes with Excel. Add-ins that do not have any of these issues are available at relatively low cost.) The optimal solution turns out to be the one shown in Table 8-4. 

For a batch process modeled in SuperPro Designer, Monte Carlo simulation can be performed by combining Monte Carlo formulation tools, such as Oracle Crystal Ball from Oracle Corporation (Redwood Shores, CA) and Risk from Palisade Corporation (Ithaca, NY) with SuperPro. Both Oracle Crystal Ball and Risk are Microsoft Excel add-in applications that enable users to assign probability distributions to input parameters, manage the Monte Carlo simulation calculations, record the values of output variables, and analyze them statistically. The communication between Oracle Crystal Ball or Risk and SuperPro is accomplished using Visual Basic for Applications (VBA) scripts written in Excel. A detailed Monte Carlo simulation example based on a variation of this process that utilizes Oracle Crystal Ball and SuperPro Designer is available in the literature [144]. 

Once again, the results are identical with the hand calculations. One might ask why the hand calculations were explained when Excel Add-In would perform all of the requisite computations The reasons are twofold (1) so the power and results from the Regression Add-In can be fully appreciated, and (2) the Regression Add-In works only for linear regression, where the in Equation 7.9 do not involve the unknown parameters. Eor nonlinear regression, the calculations must be done by hand. Nonlinear regression is covered in Chapter 9. 

Solver is a powerful tool that is a standard Excel Add-In. It can produce solutions to many different kinds of problems, among which are the following ... 

---