**** * * * ******* * * * ** ** * * * ** * * ** * ** * * * * * * * ** * ** * ** * * * * * * * *** * *** * * * * * * *** * * * * * * ** * * * * * * * * **** * *** * ** * * * * *** * * * DIRECTIONS Macros and Templates to Create Scientific and Technical Graphs Using Lotus 1-2-3, Microsoft Excel, Corel QuattroPro, Trius AS-EASY-AS or Other Spreadsheet Software by Roger W. Gross and Daniel J. Gross P.O. Box 681 Menomonee Falls. WI 53052-0681 (414) 253-2297 Feb., 1997 Version 2.21 Copyright Statement, Association of Shareware Professionals ii ____________________________________________________________________________ Copyright 1995, 1996 by Roger W. Gross and Daniel J. Gross. All rights reserved. No part of this publication may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any other language or computer language without prior written consent of the authors. GraphMaT is produced by a member of the Association of Shareware Professionals (ASP). ASP wants to make sure that the shareware principle works for you. If you are unable to resolve a shareware-related problem with an ASP member by contacting the member directly, ASP may be able to help. The ASP Ombudsman can help you resolve a dispute or problem with an ASP member, but does not provide technical support for members' products. Please write to the ASP Ombudsman at 545 Grover Road, Muskegon, MI USA 49442-9427, Fax 616-788-2765, or send a CompuServe message via CompuServe Mail to ASP Ombudsman 70007,3536. Trademarks of other companies mentioned in this documentation appear for identification purposes only and are the property of their respective companies. Table of Contents iii ____________________________________________________________________________ ------------------------------------------------------------------------- Legal Matters Copyright Notice ii Association of Shareware Professionals ii Table of Contents iii License Agreement v Warranty Statement vi ------------------------------------------------------------------------- Introduction Chapter 1 Spreadsheet Graphing 1 What is GraphMaT? 2 Benefits of GraphMaT 3 Shareware 4 Spreadsheet Compatibility 5 About the GraphMaT User's Guide 5 Conventions Used by GraphMaT 6 ------------------------------------------------------------------------- Installation Chapter 2 System Requirements 7 GraphMat Files 7 Spreadsheet Program Setup 7 GraphMat.ZIP 8 Installing the GraphMaT Files 9 Multiuser Option 10 ------------------------------------------------------------------------- Before You Begin Chapter 3 Getting Started 11 Worksheet Layout 11 Common Problems 12 ------------------------------------------------------------------------- Directions Chapter 4 General Directions for GraphMaT 15 The 12 Graphs: Descriptions & Special Directions 18 The Utility Menu 26 The Index Worksheet 26 ------------------------------------------------------------------------- Tutorials Chapter 5 1. Solving Simultaneous Equations 27 2. Plotting f(x) vs g(x) 29 3. Linear Regressions for Calibration 32 4. Multiple Regressions 37 5. Transforming Data Signals 39 6. Plotting Signals 41 Table of Contents iv ____________________________________________________________________________ ------------------------------------------------------------------------- Tutorials Chapter 5 7. Fitting Data to Equations 43 8. Plotting Linear Combinations of Equations 49 9. Combining Graphs 51 10. Miscellaneous Sample Graphs (User's Guide) 53 ------------------------------------------------------------------------- Applying The Twelve Templates Chapter 6 Abstract of Chapter from GraphMaT User's Guide 54 ------------------------------------------------------------------------- Advanced Spreadsheet Tips Chapter 7 Abstract of Chapter from GraphMaT User's 55 ------------------------------------------------------------------------- Technical Support Chapter 8 Support Policy on Macros and Templates 56 Registration 56 ------------------------------------------------------------------------ References and Index GraphMaT User's Guide only License Agreement v ____________________________________________________________________________ The GraphMaT files described in this book are NOT "Public Domain" and are NOT "Freeware". GraphMaT is a copyrighted software product developed and owned by its authors, Roger W. Gross and Daniel J. Gross, and is protected by copyright laws. It is distributed as shareware and the authors grant registered users the right to reproduce and use copies of this version of GraphMaT subject to the limitations specified below, and on the express condition that they do not receive any payment, commercial benefit, or other consideration for such reproduction or distribution. This license agreement and the copyright notice which appears in various forms in the software and documentation must be included in copies and may not be changed. For permission to distribute the GraphMaT files for a fee, contact the authors. ------------- Limitations: ------------- 1. Registered users are permitted to make and keep one (1) back-up copy of the software for their personal use. 2. Registered users may use, modify or create derivative works from any of the files included with GraphMaT for their own personal use. 3. A registered user may make as many working copies of the files as needed. They may be on as many disks or in as many directories or on as many computers as required, provided no more than one person uses any of the files at any one time. You must register additional copies or apply for a site license if more than one person will be using any GraphMaT at the same time on one or more computers. This applies to any files made from the original ones which contain any of the templates, formulas, formats, macros, and any derivatives or modifications of the original material or documentation. 4. Unmodified copies containing all of the files included on the original disk may be distributed to other persons, but solely for the purpose of evaluation. If after thirty (30) days they continue to use GraphMaT, they must register. 5. GraphMaT, or any derivative files must NOT be sold or otherwise provided as part of a larger system, or as a part of a more inclusive product or service, without express written consent and licensing from the authors. 6. Any graphs or charts prepared using GraphMaT or a derivative can be distributed or published in any format by registered users without consent of the authors. Rights to receive any financial or other benefit, and to modify the product or to employ its components in any kind of derivative work are reserved exclusively by the authors. Warranty Statement vi ____________________________________________________________________________ THIS SOFTWARE AND ACCOMPANYING WRITTEN MATERIALS (INCLUDING INSTRUCTIONS FOR USE) ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. FURTHER, THE AUTHORS DO NOT WARRANT, GUARANTEE, OR MAKE ANY REPRESENTATIONS REGARDING THE USE, OR THE RESULTS OF USE, OF THE SOFTWARE OR WRITTEN MATERIALS IN TERMS OF CORRECTNESS, ACCURACY, RELIABILITY, CURRENTNESS, FITNESS FOR A PARTICULAR PURPOSE, OR OTHERWISE. NEITHER THE AUTHORS NOR ANYONE ELSE WHO HAS BEEN INVOLVED IN THE CREATION, PRODUCTION OR DELIVERY OF THIS PRODUCT SHALL BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL OR INCIDENTAL DAMAGES (INCLUDING DAMAGES DUE TO LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION, LOSS OF DATA OR BUSINESS INFORMATION, AND THE LIKE) ARISING OUT OF THE USE OR INABILITY TO USE THIS PRODUCT EVEN IF THE AUTHORS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. THE ENTIRE RISK AS TO THE RESULTS AND PERFORMANCE OF THE SOFTWARE IS ASSUMED BY THE USER. IF THE SOFTWARE OR WRITTEN MATERIALS ARE DEFECTIVE YOU, AND NOT THE AUTHORS OR THEIR DEALERS, DISTRIBUTORS, AGENTS, OR EMPLOYEES, ASSUME THE ENTIRE COST OF ALL NECESSARY SERVICING, REPAIR, OR CORRECTION. NO ORAL OR WRITTEN INFORMATION OR ADVICE GIVEN BY THE AUTHORS, DEALERS, DISTRIBUTORS, AGENTS OR EMPLOYEES SHALL CREATE A WARRANTY OR IN ANY WAY INCREASE THE SCOPE OF THIS WARRANTY. This Warranty Statement shall be governed and construed in accordance with the laws of the State of Wisconsin. If you have any questions, contact us at: GraphMaT P. O. Box 681 Menomonee Falls, WI 53052-0681 (414)-253-2297 CompuServe ID 71053.2765 71053.2765@compuserve.com .N:-5 Chapter 1: Introduction 1 ____________________________________________________________________________ -------------------- Spreadsheet Graphing -------------------- One of the most important skills needed for success today is the ability to communicate effectively. In the past, many books and resources were available to help us improve our communication skills, however, most of them emphasized the verbal aspect of communicating rather than the visual. As technological advances have allowed us to utilize the impact of visual effects, the graphics arts, computer graphics programs, and associated materials has proliferated. But while "graphics" receives a fair amount of attention, another important aspect, "graphing," has been neglected. Frequently people say, "A picture is worth a thousand words." A simple image is a very effective way to communicate. It is less subject to the subtle variations in meanings which words may have and is therefore less likely to be misinterpreted. Graphs are pictures which often convey ideas that can't be described easily with words. Often relationships exist which can't be seen by looking at tables of numbers. When the data is graphed, the correlations are revealed. Graphing data by hand is tedious so people often skip making a graph because it is time consuming. The advent of the computer should have changed that, but many people still do not graph data when they should. Some are not aware of the benefits of graphing and there are few books devoted to graphing techniques. Some books are listed in the Reference section of the User's Guide. Others may know how the data should be graphed, but don't because it is too difficult to make the type of graph needed for their particular application with the software available. This is surprisingly true even in the high tech scientific and technical fields where there are many fine programs designed for engineering, math, or scientific applications. Why aren't these programs being used? They can be expensive, may not be readily available, often are slow, may not work on your PC, or you may not have enough space left on your hard drive for the program. Some programs have very specific applications. Some that have the best technical capabilities often lack the quality output that may be necessary for presentation or publication. A very common reason is that it takes time to learn the program, and often time is not available. The better scientific graphing programs have an overwhelming selection of options and require a knowledge of graphing and statistics to make the right choices. If any of these describe your situation, perhaps you should consider preparing graphs using a spreadsheet program. Rather than buying and learning a new program, learning more about a spreadsheet with its wide applicability may be a productive use of time. Actually, the spreadsheet programs are quite good, very versatile, and have become quite inexpensive. As new versions are introduced, anything you develop benefits from the enhancements added in the next update. Since many PC's already have a spreadsheet program installed, you probably already have access to one. GraphMaT 2 ____________________________________________________________________________ Current spreadsheet programs have many built in enhancements for graphs, produce quality output, and support many printers and file formats. They have the advantages of compatibility with other software programs. The files, data, and graphs used in Lotus 1ö2ö3, Microsoft Excel, or Corel Quattro Pro can be easily shared with their respective word processors, presentation graphics, and database programs. Importing graphs made with stand-alone graphics programs into your word processor may be more difficult than working in an environment where file compatibility is built in. One reason more technical graphs are not done in spreadsheets is that the built-in graphing or charting seems to be designed primarily for business rather than technical applications. They may plot on a log axis, have some statistical functions, and may even have some curve fitting capabilities. But if what they have is exactly what you need for your data, consider yourself lucky. In addition to the scientific and technical applications, there may also be occasions where business applications could benefit from some types of graphs which are not built into these spreadsheets. Spreadsheets do have all the math functions and graphical capabilities needed to perform some rather complex scientific and technical graphing. They can perform linear regression and multiple regressions. By combining this with the many built-in functions, they can perform various types of non-linear curve fitting and function plotting. They can be programmed to solve simultaneous equations, transform data sets and perform a variety of other graphical techniques. However, the formulas to do the statistics, to transform your data, or to construct the type of graph you need are not built in. The manuals and books we've seen don't put it all together and explain how to do it. ----------------- What is GraphMaT? ----------------- GraphMaT is an innovative approach to analysis, manipulation, and graphical display of data and mathematical functions. It performs linear and multiple regressions, curve fitting, plots functions, solves simultaneous equations, transforms, displays and overlays signals. A user-friendly menu driven interface and practical documentation guide you through the process of importing the data, performing the data analysis, constructing the graphs, and interpreting the results. Although initially designed for laboratory situations, it can be utilized in business or non- scientific fields as well. Chapter 1: Introduction 3 ____________________________________________________________________________ GraphMaT is not a stand-alone program or a Lotus add-in, but a collection of 12 technical macros and graph templates for use in Lotus 1-2-3, Excel, Quattro Pro, Trius AS-EASY-AS or other compatible spreadsheet program. It retains all the capabilities, features, and advantages of current spreadsheet software. Since the macros and templates employ standard Lotus 1-2-3 functions, they can be customized if desired. While it does not provide all the features of a presentation graphics program, and doesn't perform all of the analyses included in some stand-alone statistics programs, it will perform many common statistical and graphical operations. GraphMaT is a convenient working tool for the laboratory scientist, engineer, or knowledgeable professional, and a great introduction to application of statistics and graphical techniques for a student or anyone else who is faced with analyzing and graphing data. It does not perform all the math and statistics that you will ever need, but it is an easy way to perform many frequently encountered graphical analyses. --------------------- Benefits of GraphMaT? --------------------- What benefits might you obtain from using GraphMaT? Depending on your expertise in statistics and spreadsheet operation, you should expect to experience some or all of the following: * Learn more about applying statistics and graphing data. * Become more proficient in using spreadsheet software. * While the graphs included in GraphMaT could be done manually, using the pre-made templates minimizes the time required to prepare the graphs. * The macros speed up data entry and analysis. * Since it is faster, you may graph things which you normally wouldn't and may find correlations which otherwise could be missed. * The menu driven interface allows people with a limited knowledge of Lotus operations to prepare some rather complex graphs. * Since all formulas are already composed in the template, applying statistics is easy even you don't have a strong background in statistics and data analysis. * Spend less time searching for information in a spreadsheet. * Quickly locate files that have the information or graph you need. GraphMaT 4 ____________________________________________________________________________ GraphMaT utilizes some macro techniques, formulas, and options that are not commonly used. Using GraphMaT provides an opportunity to learn many practical tools which can be used in other applications. The Lotus macro language is convenient and can be used effectively in many situations. The spreadsheet regression analysis and @functions can be combined to perform some complex data analysis and plotting. Learning by snooping at a completed program like GraphMaT is more beneficial than purchasing and reading one of the many books written about these spreadsheet programs. Rather than reinventing the wheel, use existing techniques. --------- Shareware --------- The GraphMaT files and documentation are copyright materials distributed as shareware. This allows you to try it before you register (ie., buy it). GraphMaT does not carry a warranty of any kind. It was tested with several spreadsheet programs: some versions worked, some didn't. Test it on your system with the spreadsheet program you are using and with your data. After you try it, please send us your comments. If GraphMaT works on your system, performs operations which you can utilize, and you plan on using it, OR if you gained knowledge from it and the documentation which you feel is worth something to you, please register. If you continue to use it beyond a trial period, you must register. Copyright laws apply to both shareware and retail software, and generally the copyright holder retains all rights. Shareware and retail programs are of comparable quality. In both cases, there are good programs and bad ones! The main difference is in the method of distribution. The shareware author specifically grants the right to copy and distribute the software. Feel free to give unmodified copies of all the GraphMaT files to friends and associates so they may evaluate it also. Shareware is a distribution method, not a type of software. You should find software that suits your needs and pocketbook, whether it's retail or shareware. The shareware system makes fitting your needs easier, because you can try before you buy. And because the overhead is lower, prices are lower also. Shareware has the ultimate money-back guarantee -- if you don't use the product, you don't pay for it. If you try a shareware program and continue using it, you are expected to register. We don't like crippleware or nag-ware. The shareware GraphMaT files have the same macros and templates as the version we are currently using. We rely on your honesty to register. To help your honesty, we do offer incentives to register. These include technical support and a printed User's Guide. (For more information on registration, read the License Agreement on page v, Chapter 8: Technical Support, and the file REGISTER.TXT or contact the authors either by phone, mail or via CompuServe.) Chapter 1: Introduction 5 ____________________________________________________________________________ ------------------------- Spreadsheet Compatibility ------------------------- Why are these macros in Lotus release 2.01? GraphMaT was written for that version because many spreadsheet programs, including the current Lotus 1-2-3 releases support this macro language. Many spreadsheet programs claim to be compatible with Lotus 1-2-3. It is best that you test that claim before relying on it. GraphMaT has been used with Lotus releases 1 and 5 for Windows and 2, 3 & 4 for DOS. It has also been used with Microsoft Excel version 5 for Windows and various versions of Quattro Pro: versions 1 & 4 for DOS (Borland), version 5.5 for DOS (Novell or Corel) Windows versions 1.0 (Borland) and 6.02 (Novell or Corel). During testing we found some minor variations between the different programs. Some of these are covered in Chapter 3 (Common Problems) and others are noted in the tutorials. Additional information may be found in the ADDENDUM.TXT file. While GraphMaT is compatible with all the versions listed above, each of those programs are slightly different and have different capabilities. Some versions do not support all of the features used by GraphMaT, for example, Excel does not fully support data labels on XY graphs. Limitations of the different spreadsheet programs are noted in the GraphMaT directions. Despite the differences, all of the graphs can be made in any of the versions listed above. Some programs like AS-EASY-AS by Trius (version 5.5) and Lotus Symphony are not compatible with all of the macro commands used in GraphMaT. Essentially all of the GraphMaT graphs can be made in these programs. If you are interested in using GraphMaT with one of these or any other spreadsheet program or version not listed, contact the authors about GraphMaT specifically designed for your application. ---------------------- About the User's Guide ---------------------- There are books on scientific graphing and on computers, but there are few sources of practical information which combine the two. The GraphMaT 2.20 User's Guide attempts to bridge this gap. It can be purchased by registered users and contains all of the on-disk GraphMaT documentation in a more functional format, and includes sample graphs. In addition to an expanded tutorial section, the Guide also has two additional chapters, one on applications of the graphing and statistics used by GraphMaT, and one containing tips on combining functions, using formulas in macros, graphing in spreadsheets, and writing macros. The Guide also has a list of graphing and statistics references if you need additional information, and an index. While the on-disk file provides enough information to run GraphMaT, the Guide has a lot of valuable information that can help students, scientists and other professionals create and use graphs effectively. GraphMaT 6 ____________________________________________________________________________ ---------------------------- Conventions Used by GraphMaT ---------------------------- The following conventions are followed in these instructions, the program prompts, and menus. * Information which appears on the screen is given in "quotes". * Keys other than numbers, letters, and punctuation are bracketed: {Enter}, {Esc}, {Alt}, {Space}, {BkSp}, {Shift}, {Ctrl}, {F1}, {F2}.... * The special named keys for Lotus are indicated with their keyboard designation, not with their Lotus name. The Lotus "Graph" key is {F10} and "GoTo" is {F5}. * At some points, like when a graph is displayed, a macro pauses. To continue you may press any key. This is indicated by {any}. * Other keys or information you must type in a specific way are printed out in full using the letters, numbers or characters you are to type. * Key combinations are designated {Alt+G}. Hold down {Alt} and press G. * If the directions or prompt says "Enter" you must type the response and then hit the {Enter} key. For example: "Enter your name." means you type your name and press the {Enter} key. * Where you are to type numbers of your choosing will be indicated ###. * Where you are to type letters of your choosing will be indicated ???. * Where only certain letters or numbers are OK, the choices will be in parenthesis separated by /. For example: "Overwrite Graph (y/n)?" Here you press either 'y' or 'n' and press {Enter}. * Lotus menu commands follow /. Either the full command with the first letters capitalized or just the first letters are given. /RNC is the same as /Range Name Create. You can either type the letters or move the cursor over the selection and press {Enter}. Commands are given for Lotus release 2.01 since these also work in subsequent revisions. Some operations can also be done using the equivalent commands in the newer versions. * Windows operations are indicated as choose Tools Options, meaning click on the "tools" menu selection and then select "options" from the pull-down menu. When a dialog box is displayed you will be directed to select an item. If it is a tab, click on the tab to switch to another set of choices. If it is an option button or check box and the indicator is off, click on the item to turn it on. If it is already on, go on to the next step. To de-select the item, click on it to turn it off. * File names are printed in full: 0GRAPH.WK1 * Cell addresses are given in uppercase type: AD11. * Variables and equations are in uppercase, X = Y. A lowercase x = 7 refers to a specific value for a variable. Chapter 2: Installation 7 ____________________________________________________________________________ ------------------- System Requirements ------------------- In order to use GraphMaT you must have Lotus 1-2-3 release 2.01 or later, or another spreadsheet program which can use Lotus v.2.01 macro commands. You will also need about 350k of disk space. If you are installing from the ZIP file, you will need PKUNZIP.EXE version 2.04G or later to unzip the files. -------------- GraphMat Files -------------- The GraphMaT system consists of the following files: GRAPHM22.ZIP ZIP file containing the following files README .TXT A brief introduction to the GraphMaT system GRAPHMAT.TXT ASCII file of directions ADDENDUM.TXT Optional file with supplementary information REGISTER.TXT Registration and order form GINSTALL.BAT Batch program to copy files to working disk or directory GRAPH .MAT Worksheet with the graph macros and templates 0GRAPH .WK1 Worksheet with a graph menu macro 0 .WK1 Index worksheet In addition to these files there are several .PRN and .WK1 files which contain sample data for use with the tutorials. The 12 graph macros and corresponding templates reside in the GRAPH.MAT file and are brought into the 0GRAPH.WK1 worksheet as needed. This minimizes the size of the individual files while providing access to all of the macros in each worksheet file. After the graphing is completed, the file is saved using a different filename, preserving 0GRAPH.WK1 unchanged for the next application. Certain cells from 0GRAPH.WK1 files are brought into the index worksheet, 0.WK1, to make a list of file names and descriptions. If you give the GraphMaT files to a friend or associate for evaluation, either give them GRAPHM22.ZIP or all of the original files in the list above plus the sample files. ------------------------- Spreadsheet Program Setup ------------------------- GraphMaT can be used with several spreadsheet programs, however you may have to change some of their default settings. If the version you are using has an auto-save option, turn it off so the 0GRAPH.WK1 file is not overwritten. To use GraphMaT, all the files must be in the "default working directory" with the exception of GRAPH.MAT which may be moved to another location if desired. (See "Multiuser Option" in the User's Guide.) Start the spreadsheet as normal. The working directory is the one first displayed when selecting /FileRetrieve or /FileOpen. If the directory displayed is not where you GraphMaT 8 ____________________________________________________________________________ usually save your files, the default directory will have to be changed. Some programs also have special settings which must be changed in order to use the Lotus 1-2-3 classic menu and macros. In Lotus 1-2-3, the only setting is the default working directory. To change the default directory in release 2.01 or later for DOS, use / Worksheet Global Default Directory. Enter your working directory and then select Update. For the Lotus 1-2-3 release 5 for Windows, select Tools User_Setup and enter your working directory and click on {OK}. In Excel, choose Tools Options. On the General tab, type your default drive and directory into the "Default file location". On the Transition tab, type in a "/" for the "Menu or Help Key" and make sure "Lotus 1-2-3 Help, Transition Navigation Keys, Formula Evaluation, and Formula Entry" are all selected. Click on {OK}. Select Help Lotus 1-2-3. Select "Demo" and click on {Close}. In Quattro Pro for DOS, select Tools Macro KeyReader Yes to enable use of the Lotus 1-2-3 macros. Then select Option Startup Directory. Type the drive and directory of your working directory. After all changes are made, select Quit Update to make both changes permanent. You may also want to use the Lotus 1-2-3 menu option. Select Option Startup Menu_system 123. The screen will change and display the Lotus 1-2-3 menus. To make this change permanent, use / Worksheet Global Default Update. This works in versions 4.0 and 5.5. (If you want to switch back, use / Worksheet Global Default Files Menu_system Quattro.) In Quattro Pro for Windows ver 6.02, from the Application screen, (right click on Quattro Pro title bar or select Property Application) select the Macro tab and select 123/R2.01 for the slash key. Select the File Options tab. Type the drive and directory of your working directory in the place for Directory. On the General tab, select "Compatible Keys." Click on {OK}. For version 1, on the Start Up tab select "Compatible Keys" and type in the working directory. On the Macro tab and select "Key Reader." For other applications, check the ADDENDUM.TXT file for additional information. ------------ GraphMaT.ZIP ------------ If you obtain GraphMaT as GRAPHM22.ZIP, you must first extract the files using PKUNZIP. The files require about 450k of free space and it is suggested that you unzip them to the floppy where you have the ZIP file or to a second floppy. Type C:\PKWARE\PKUNZIP -e A:\GRAPHM22.ZIP B:{Enter} where PKWARE is the directory where PKUNZIP.EXE is located and B: is the destination. The destination could be A: if there is sufficient space, or it could be a directory on the hard drive if desired. To run PKUNZIP in Windows 3.1, from the Program Manager choose File Run. Type the command above and press {Enter} or click {OK}. Chapter 2: Installation 9 ____________________________________________________________________________ If you do not have PKUNZIP or have problems consult the PKZIP 2 User Guide or contact PKWARE, INC. at 9025 N. Deerwood Dr., Brown Deer, WI 53223 or phone them at (414) 354-8699. ----------------------------- Installing the GraphMaT Files ----------------------------- Since GraphMaT is not a stand alone program but files used by another program, the installation will vary depending on how your spreadsheet program is configured. The following illustrations assume you are installing from the A: floppy drive to a working directory on the D: drive named DATA which is designated D:\DATA. GraphMaT runs faster if the working directory is on a hard drive. You may have to make adjustments to the following commands for your system. The files 0.WK1, 0GRAPH.WK1, and GRAPH.MAT must be copied to the default working directory. If you intend to use the sample data files or run the tutorials, the other WK1 and the PRN files must also be in the working directory. DOS Installation ---------------- To prepare a working directory or diskette, use DOS copy or a file manager to copy the required files to the Lotus default working directory. To copy all the files, type COPY A:\*.* D:\DATA{Enter}. If you do not intend to run the tutorials copy GRAPH.MAT and use DOS wildcards 0*.* to copy the other two required files. The batch program GINSTALL.BAT can copy these files for you. Make the diskette with the GraphMaT files the current drive by typing A:{Enter}. Then run the program by typing GINSTALL D:\DATA {Enter}. Windows 3.1 Installation ------------------------ To prepare the working directory for use, use the Windows File Manager to copy the GraphMaT files to the working directory. To start, double-click on the File Manager icon which is usually found in the Main group. Select the A: drive where the GraphMaT files are located by clicking on the appropriate drive icon. Select the files to be copied. There are several ways to select files. If all of the files on the disk to be copied are adjacent, click on the first file, hold down {Shift} and click on the last file. To select individual files hold down {Ctrl} and click on each file to be copied. To cancel a file selected, hold down {Ctrl} and click on the file. (For more information on selecting files consult your Windows User's Guide.) Once the desired files are selected choose File Copy, type the working directory D:\DATA for the destination and press {Enter} or click on {OK}. &a-.5R You may also use the batch program GINSTALL.BAT to copy these files for you from Windows. From the Program Manager choose File Run. Type the command line A:\GINSTALL D:\DATA and press {Enter} or click {OK}. GraphMaT 10 ____________________________________________________________________________ ---------------- Multiuser Option ---------------- A registered user or a company with a site license may access a single copy of the GRAPH.MAT file from copies of 0GRAPH.WK1 in many working directories. In addition to saving disk space, any improvements, modifications, or customizations made to the one GRAPH.MAT file are immediately available in all working directories. The directions for the multiuser option are given on page 10 of the User's Guide and are provided to registered users. Chapter 3: Before You Begin 11 ____________________________________________________________________________ --------------- Getting Started --------------- People have a tendency to develop their own conventions and habits. What seems logical or straight forward to one person will be confusing to another. This section describes the logic behind these macros. Hopefully, if you read it the macros will make sense and will be easy to follow. Although GraphMaT was designed to be user friendly, it is advisable to study some of the material in the User's Guide before starting GraphMaT. "General Directions for GraphMaT" in Chapter 4 gives a rough idea how to interact with the macros. Some of the operations which are unique to a particular macro are covered separately in a section called "The 12 Graphs: Descriptions and Directions" also in Chapter 4. The "Common Problems" section which follows is relatively short. You may avoid some frustration if you read it before trying GraphMaT. To help familiarize you with GraphMaT, a number of tutorials are provided. It is recommended that you do them in order as some of the later ones skip some of the steps covered in the previous tutorials. If you can't get a tutorial to work, review the "Common Problems" section and if all else fails, refer to the "Technical Support" section. ---------------- Worksheet Layout ---------------- 0GRAPH.WK1 is a template to be used repeatedly. After using it to construct a graph which you want to save, choose another name for saving the file. Do not overwrite the 0GRAPH.WK1 file! The filename starts with "0" so it appears near the beginning of an alphabetical file list making it easier to find. Saving files with names starting with higher numbers or letters keeps 0GRAPH.WK1 near the front of the list. +----------------------------------------------------------------------------- | A B C R ...Z AA AB AC ....AM ......AZ BA BB BC ... IV | +-----------------------------+------------------------------------------ | 1|::::::::::::::::::::::menu:::|graph::::::::::graph::::::|BA1 -> | 2|FILE EXT:Description::macro::|data:::::::::::macro::::::| -> | 3|:::::::::::::::::::::::::::::|::::::::::::::::::::::::::| -> | .|:::::::::::::::::::::::::::::|::::::::::::::::::::::::::| -> | .|:range:::::::::::::::::::::::|::::::::::::::::::::::::::| -> | .|:table:::::::::::::::::::::::|::::::::::::::::::::::::::| -> | .|:::::::::::::::::::::::::::::|::::::::::::::::::::::::::| -> | 99+-----------------------------+::::::::::::::::::::::::::| -> | 100|Range R_1 |::::::::::::::::::::::::::| -> | 101| |::::::::::::::::::::::::::| -> | .| |::::::::::::::::::::::::::| -> | .| |::::::::::::::::::::::::::| -> |5000| |::::::::::::::::::::::::::| -> | .| |::::::::::::::::::::::::::| -> | |\/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ Figure 3.1 Ä Layout of the 0GRAPH.WK1 spreadsheet. GraphMaT 12 ____________________________________________________________________________ Figure 3.1 shows the layout of the 0GRAPH.WK1 worksheet. The shaded areas are reserved. Cells A1 to Z99 contain a range table and the graph menu macro. Cells AA1 to AZ8192 are reserved for the graphing macro. Use these only for the graphing for which they were intended. Other use may corrupt the macros. --------------- Common Problems --------------- * The macros reference many cells and the location of the macro is critical. It can be corrupted by moving cells, by inserting or deleting rows or columns or by deleting ranges. DO NOT do anything that will affect the shaded areas. NEVER insert columns to the left of column BA. NEVER insert rows between 1 and 101. DO NOT move any cell in the shaded areas. DO NOT enter data into shaded regions except as directed by the macro. * Unshaded regions in Figure 3.1 can be used. If you want to enter data before starting the macro, place it an unshaded region. To be used in the macro, data must be arranged in columns. The columns cannot have any blanks. It can also be in ASCII files (Lotus .PRN files) or can be in another worksheet (WK?) file. Check out the sample files to see acceptable formats. After the macro is finished, you may insert rows so it doesn't affect rows 1 to either 100 or the last row of macro data as indicated at AC1. * The most common problem people have had with these macros is using a {cursor} key instead of pressing {Enter} when data entry in a cell is completed. DO NOT use a cursor key while the macro is running unless directed to do so!!! As long as the "CMD" indicator is showing the GraphMaT macro will position the cursor for you. There are a few exceptions as highlighting cells to select data for a graph, editing a entry, or selecting options from a menu. If you accidentally move the cursor, stop the MACRO with {Ctrl+Break} {Esc}. Restart the macro and select Repeat from the menu. * Each macro will move the cursor to cells where information is required. Use the default entry and/or the description in a neighboring cell to determine what information to provide and whether a label or a value is required. If you want to start a title which requires a label with a number, type an apostrophe followed by the number (for example: '1994 DATA). * If you are unsure of which choice to make from a menu, moving the cursor to the various menu choices will display additional information about the menu options. This information may appear in various places on the screen depending on which spreadsheet is being used. Often the menu is requesting a choice relating to the cell where the cursor is positioned. In some Windows versions the prompts and menus obscure a part of the sheet. Usually they can be moved to a different place on the screen. Chapter 3: Before You Begin 13 ____________________________________________________________________________ * If you have no idea how to respond, accept the default entry or settings. The macros should work with these choices. You can always repeat the macro and change items you wish later. * Make sure Scroll-lock is off. The "SCROLL" indicator must not show. The arrow keys must move the cursor, not the window. * Some of the problems encountered are not due to the macro, but to errors encountered because of undefined calculations. Many functions have points or ranges where they are undefined, often at zero. When working with functions, be sure you are not working in these regions. This can also affect the regression macros. Chapters 6 & 7 in the User's Guide have more information on this. * The macro uses several one letter range names (ie. R,X,Y,Z). If you do name ranges in this worksheet, use two or more letters in the name. If you want to add your own macros to the 0GRAPH.WK1 or a spreadsheet file derived from it, do not use the following names for the macro: \G, \R or \U. * Some versions of Lotus and other spreadsheet programs allow you to select either text or graphic screen. The macros may run slower in a graphics display mode. In a graphics mode, using a non-proportional font may line things up better. * Since some of the graphs are quite complex, it is best to use as much of the screen as possible for the graphs. You may want to turn off the display of some of the optional icons and menubars and use the full screen display. * In non-Lotus spreadsheet programs, settings must be changed to be able to read or run macros written using the Lotus menu tree. Change these before retrieving the 0GRAPH.WK1 file. Refer to Installation in Chapter 2. * Excel version 5.0 for windows will run these macros. The graphs can be viewed using the Lotus menu commands if the Lotus help demo mode is active but are not available via Excel commands. To work with the graphs, save the file as a Lotus .WK1 file and then retrieve it. Each graph will be on a separate sheet. * Since these macros perform many calculations, it is best if recalculation is set to manual. (/ Worksheet Global Recalculation Manual). It is especially important with an older, slower PC. Make sure the macro is finished calculating before trying to enter data or giving up and stopping the macro. Some macros perform quite a few calculations when they start and after the data is entered. Don't panic if the graph doesn't appear instantly. The MULT example in Tutorial 4, one of the more calculation intensive macros, takes 9 seconds to construct the 10 graphs in Lotus release 2.01 on a 33mHz 486. It takes 6 secs in QuattroPro 4.0 for DOS, 5 secs for Lotus release 5 for Windows, and 25 sec. for Excel 5.0 in Windows. If Excel runs out of memory and uses the disk, it can take several minutes. Lotus release 2.01 takes 15 sec. on a 386, 54 sec. on a 286, and 4 minutes on a XT. GraphMaT 14 ____________________________________________________________________________ This page is mostly blank. Hopefully it will remain that way. We anticipate there may be more problems and therefore left some space for you to record them and the solutions. If you have any problems and/or solutions to them, please contact us so we can alert other GraphMaT users. Chapter 4: Directions 15 ____________________________________________________________________________ ------------------------------- General Directions for GraphMaT ------------------------------- It is suggested that you read this chapter and then run the tutorials before trying GraphMaT on your own data. All GraphMaT macros have a similar pattern and guide you through selecting the type of graph, entering the data, performing the data analysis, and constructing the graph. A brief description of each macro and special directions are given in the next section. Please follow the directions carefully. If you don't, the macro may get mixed-up. If it beeps several times or otherwise appears to be confused, use {Ctrl+Break} {Esc} to abort the macro. Then restart it with step #3 and select Repeat for step #4. If that doesn't work, start over from step #2. The macro gets information needed to construct the graph using a combination of the following three methods: * Cell entry: The macro will move to the cells where information is required and wait for your response. At that point you have three options: - To accept the default label or value press {Enter}. - To replace a default entry, type either a label or number as indicated and press {Enter}. - To modify a default entry, press {F2}. This puts you in the EDIT mode allowing editing of the existing entry. You can use the {left} or {right} keys to position the cursor. You may also use the {Insert}, {BkSp}, {Home}, {End}, or {Del} keys. After your changes are complete, press {Enter}. * Input command: Type your response to a prompt and press {Enter}. * Menu: Selections are made as in normal Lotus by either highlighting the selection with the cursor keys followed by {Enter}, or by typing the first letter of the selection. For versions with mouse support, you may also use the mouse to make selections. Additional information is displayed about each choice as it is highlighted. In some places, Previous or Next are menu choices provided to change menus instead of using {Esc} to display the previous menu. The following steps are typical of all 12 macros. Obviously there are some variations since different information is required for the different graphs. These directions use the classic Lotus 1-2-3 commands. There are alternate ways of performing some of the tasks in some versions. More details and some variations for different versions are noted in "Common Problems" in Chapters 3 and the Tutorials in Chapter 5. 1. Enter Lotus 1-2-3 or other spreadsheet program in the usual manner. 2. /File Retrieve 0GRAPH.WK1 {Enter}. 3. {Alt+G} invokes the macro and the main menu is displayed. {Ctrl+G} starts the macro in Windows versions of Lotus, Excel, and Quattro Pro. In Windows substitute {Ctrl+?} for {Alt+?} wherever it occurs in these directions. 4. The first time, select New to display the graph type menu. GraphMaT 16 ____________________________________________________________________________ 5. Some macros have a menu for graph options. When Accept is highlighted, the current settings are displayed. Select any item that needs to be changed. The color option is only for programs (Lotus 2.x and Excel 5) where graphs are not in color unless specified. When settings are correct, press {Enter} to accept. 6. The macro will move to cells in which the graph's name, titles, and other parameters are entered. (Quattro Pro users, see NOTE in Tutorial #1 on page 28.) 7. Some macros have the option of entering labels associated with each data point. The labels can be displayed as data labels by each point on the graph if desired. Select the appropriate option from the choices displayed in the menu. A. Accept: uses values present in the column below the cursor. Select this if you don't want to enter labels, if previously entered labels exist, or if you plan on entering labels after the macro has finished. B. Number: will number each data point consecutively. C. Coordinates: will compose the (x,y) coordinate of each data point. D. Other: will allow you to use any of the methods available for entering data in the following step. 8. When creating graphs in spreadsheets you normally select which cells are to be used for each data series in the graph. GraphMaT templates already have the cells selected and require that the data be brought into the defined cells. Several options are provided for bringing the data into the graph. Select the appropriate option from the choices displayed in the menu. Any choice other than Skip, Accept or Values Edit will erase existing data. A. Skip: bypasses all data entry operations and proceeds to the next step. Some macros provide this option of using previously entered values for all series of data when repeating a macro to change some other parameter. B. Accept: uses values present in the column below the cursor. C. Range: copies values from cells in the same sheet. Values must be in a continuous column and start in the top cell of the range. Highlight the range name or type the cell addresses. Then press {Enter}. D. Point: is a variation of Range used when the data is not in a named range and you don't know the cell addresses. Move the cursor to the top cell of the data. Press {.} to anchor and use the cursor key to highlight the column of data. Press {Enter} when the bottom cell is highlighted. (Note: This is one of the few times the cursor can be moved around the worksheet while the macro is running.) E. Import: brings in values from a .PRN file, an ASCII file in which the values start in the first line of the file and each entry is on a separate line. Highlight the file name and press {Enter}. In some cases a comma or space delimited file can be imported which contains both x and y data sets. Chapter 4: Directions 17 ____________________________________________________________________________ F. Combine: brings in values from another .WK1 worksheet file. You must provide the cell addresses or a range name for the data in the file. Then press {Enter}, highlight the file name and press {Enter}. The values must be in columns and must start on the top line of the range. G. File: is a special case of combine where an entire worksheet file contains only data starting in cell A1 and extending down. H. Values: inputs data from the keyboard. Select New. Enter the number of points when prompted. The cursor moves to the location for the first point. Type the value and press {Enter}. The macro continues repositioning the cursor until all data points are entered. (Don't touch the cursor keys; that will confuse the macro.) If a wrong number is entered continue entering data and complete the macro. Changes are covered in step 11. 9. After entry of the information and data is complete, the macro constructs the graph(s). If the graph is displayed, examine it, then press any key to continue. Several macros allow adjustments to be made after the graph is displayed. Select an option from the menu to make additional changes. If no more changes are desired, select Accept from the menu to continue. 10. Some macros have a master menu to allow for additional options or to start over. You may select any of those or choose End which will name the graph and return to normal Lotus operation. 11. While in normal Lotus operating mode, the spreadsheet can be used as any other file provided you observe the precautions described in Chapter 3. There are two options which relate to the GraphMaT templates. A. If an incorrect value was entered for data, move to the cell and correct the wrong entry. It may be necessary to repeat the macro to incorporate the changes into the graphs or calculations. B. Cells which are "unprotected" are displayed in a different color and can be changed in normal Lotus operations to modify the graph. Some changes can be displayed immediately by pressing {F9} to recalculate, {F10} to view the graph and then any key to return to the spreadsheet. Other changes will not be incorporated until the macro is repeated (Step 12). 12. {Alt+G} Repeat will restart the macro. Use this option to make changes to the graph. Some macros require a name for the graph. You will either have to choose to overwrite it or type another name and press {Enter}. Items can be changed as desired or press {Enter} to use values previously entered. (Quattro Pro users see NOTE in Tutorial #1 on page 28.) 13. {Alt+G} Move will move the graph and associated data to another location in the worksheet. Either enter a cell location, a range, or move the cursor to the top left corner of the new location for the graph data. The cells which will be moved are shown in location AD1. This tells how much area will be required in the new location. Moving the graph "fixes" a GraphMaT 18 ____________________________________________________________________________ copy of the graph so the macro will no longer affect the moved copy. The graph can be modified as desired using the Lotus graph options. You can go back to step 12 to create a second graph using the same macro. (Quattro Pro users see NOTE on page 33.) 14. {Alt+G} New creates a new graph. Go to step 5. The macro will erase the previous graph unless it was moved (step 13). 15. {Alt+G} Erase removes the macro commands and any graph data left in the macro range (AA1.AZ5000). This reduces the size of the file when saved. (Make sure you have moved your graph data to a safe location.) 16. The graphs can be enhanced using fonts, colors, borders or any other features the version you are using supports. (Excel users see "Common Problems" in chapter 3.) Make the graph the current graph with /Graph Name Use. Make changes like /Graph Options Color. Then make the change permanent with /Graph Name Create. 17. {Alt+G} Save will move to cells A2, C2 and H2 so you can update this information for the index file. It will save the file using the name in cell A2. If you enter an existing name, it will ask you if you want to replace it. If you don't, it requests another name. If you save the file using normal Lotus commands make sure that the file is NOT saved as 0GRAPH.WK1. ------------------------------------------ The 12 Graphs: Descriptions and Directions ------------------------------------------ This section gives a brief description of the graph(s) produced by each macro. For an explanation of how to apply the graphing technique and for help in interpreting the graph see Chapter 6 of the User's Guide. All 12 macros basically follow the general directions above but each has some unique features. The special directions for those are provided here. ------------------ REGRESSION GRAPHS: ------------------ * LINEAR: The REG macro performs a linear regression on a minimum of three x,y pairs of data. The XY scatter diagram it creates plots data points and the regression equation, y = mx + b. It optionally displays the regression data (the slope and intercept of the regression equation, the standard error, and the correlation coefficient) in an inset on the graph. To assist in evaluation of the data, this macro can generate a scatter plot of the residuals and a bar chart showing the distribution of the residuals. These graphs have the same name as the main graph with "_R" and "_D" appended. After data is entered, a menu lets you choose whether to display regression data on the graph. A menu then lets you adjust the position and size of the inset. When all adjustments are complete, Accept the settings. A final menu chooses whether to perform the residuals analysis. Chapter 4: Directions 19 ____________________________________________________________________________ After the macro is ended, there are several items which can be adjusted. The regression line is calculated between the minimum and maximum x-values displayed in AB22 and AB23. The dimensions of the inset are defined in unprotected cells in AB18.AB19 and AD19.AD20. The range for the regression line and the dimensions of the inset can be adjusted by changing the values in the unprotected cells. The regression equation is included in the worksheet allowing predicted values to be calculated. Enter a value for x in AI16, press {F9} and the calculated y is displayed in AJ16. A value for y can be entered in AJ17 and the calculated x is displayed in AI17. The Lotus 1-2-3 /Copy function can make additional copies of the formulas. These features are maintained after the {Alt+G} Move option is used. Tutorial 3 uses the LLD and REG macros. Chapter 6 has more information on residuals analysis. * DETECTION: The LLD macro is a special case of the linear REG macro used for calibrating assays which show a linear dependence. Concentration is plotted on the x-axis and response on the y-axis. In addition to the regression data in the REG macro, LLD also calculates a lower limit of detection (LLD) defined as three times the standard estimate of error (s') divided by the slope (m). Directions for performing the LLD macro are the same as for the REG macro. The worksheet again has the calibration equations which can be copied using the Lotus 1-2-3 /Copy. This time the formula also includes a factor in cells AH16 and AH17 which can be used to compensate for sample dilution. Tutorial #3 uses both the REG and LLD macros. Chapter 6 has information on detection limits and quantitation. Chapter 7 explains how to modify macros and add them to the GRAPH.MAT file. * MULTIPLE: The MULT macro performs linear regressions between a dependent variable and up to 8 independent variables. It calculates an equation to predict a value for the dependent variable based on all of the independent variables. The resulting graph is a scatter diagram which plots the predicted value on the x-axis against the actual value of the dependent variable on the y-axis. It also constructs a bar graph of the absolute values of the correlation coefficients indicating which, if any, of the independent variables correlate with the dependent variable. The name of this graph has "_BAR" appended to the graphname. It also builds scatter diagrams for each independent variable vs. the dependent variable and overlays the linear regression equation. The descriptions of the independent variables are appended to the graphname to name each of these graphs. GraphMaT 20 ____________________________________________________________________________ The macro begins with a menu which displays the current settings when Accept is highlighted. Change the number of independent variables or toggle the display of data labels, then Accept the settings to continue. Enter a title, subtitle, name of the dependent variable, and names of the independent variables. Make sure the first 5 characters used to identify the independent variables are unique and descriptive since they are used for the axis titles and the first 5 characters are used as names for the graphs. If you change the title or variable names and repeat the macro, you will have duplicate graphs. The first set can be deleted using /Graph Name Delete. Tutorial #4 demonstrates a use of the MULT macro. * POLYNOMIAL: The NORD macro is a curve fitting function which provides up to a 7th order polynomial fit based on a regression analysis of the equation: Y = aX^0 + bX^1 + cX^2 + dX^3 + eX^4 + fX^5 + gX^6 + hX^7 (4.1) The resulting scatter diagram of the data points has the polynomial curve plotted between the specified x values. After entering the titles, the macro displays a menu in which you can change the curve resolution, set the order of the polynomial, or toggle the x-scale between auto and fixed. The order can be no higher than the number of data points minus 2. The "curve" is actually a series of points connected by straight lines. If the resolution selected is too low to follow the curves in the polynomial, the straight line segments become obvious. In the default "auto" mode, the polynomial is plotted between the minimum and maximum values of the independent X variable. In the "fixed" mode, limits are entered by the user. Make the desired changes and select Accept to proceed to the data entry section. After all the data has been entered, the graph is displayed. A final menu allows you to make changes to the resolution, order, x-scale, or data labels and then process the data again to incorporate the changes into the graph. Select End from the menu to complete the macro. Provisions are available in the worksheet for calculating values predicted by the regression polynomial. Enter a value for x in AB15, press {F9} to calculate. The estimate for y is displayed in AD15. Tutorial #7 includes 2 examples using the NORD macro. Chapter 4: Directions 21 ____________________________________________________________________________ * CURVEFIT: The CFIT macro is a general curve-fit screening to determine which of the following functions best represent the data. This macro requires a minimum of five (x,y) data points. Since the macro transforms the x-values, all functions must be defined at every value of x. Do not use data points where x<=0. NAME FUNCTION -------- ------------------------------------------ Linear Y=b+m*X Square Y=b+m*X^2 Cubic Y=b+m*X^3 Recip(rocal) Y=b+m/X Log(arithmic) Y=b+m*log(x) or Y=b+m*ln(x) Exp(onential) Y=b+m*10^X or Y=bex = b+m*exp(X) Power Y=b*X^m Poly(nomial) Y=b+m*X+n*X^2+o*X^3 The macro starts with a menu to change the curve resolution, toggle the x- axis scale between auto and fixed, add color, toggle between base 10 or e for log and exponential functions, or enter an offset for the x-axis. In the default "auto" mode the function is plotted between the minimum and maximum values of the x-variable. In the "fixed" mode, limits are entered by the user. Make the desired changes and select Accept to proceed. Provide titles, labels and data as directed. The macro constructs a bar chart using the graphname you provide. It shows the absolute values of the correlation coefficients to indicate which, if any, of the functions describe a relationship between the x and y data values. It also creates eight scatter graphs with the names in the table above appended to the first six characters of the graphname. The scatter graphs plot the data and overlay the function using the constants predicted by the regression analysis. Viewing a scatter graph shows how well the function fits the data. The function and the correlation coefficient, r, are displayed in the graph legend. When the macro is finished, the correlation coefficients and constants for each function are displayed on the spreadsheet. A value for x can be entered into cell AB17. After recalculating with {F9} the values predicted by each function are displayed in cells AD17..AK17 Tutorial #7 demonstrates the CFIT macro. Chapter 6 has additional applications and an explanation of the CFIT macro. GraphMaT 22 ____________________________________________________________________________ * FUNCTIONS: The XREG macro performs a linear regression on f(x) and g(y). Functions are chosen independently for both X and Y so they may be the same or different. For example, this would allow you to determine if there is a correlation between the log of x and the reciprocal of y or any other combination of functions supported by Lotus 1-2-3. This is a slightly different approach to semi-log or log-log plots. Instead of plotting the numbers on a log scale, the numbers are transformed to their logarithm and plotted on a linear scale. This has the advantage of allowing scales based on any function supported by Lotus 1-2-3, not only linear or log scales. The XREG graph is an XY scatter diagram with f(x) plotted against g(y). It superimposes the regression line, g(y) = m f(x) + b and optionally displays the regression data (slope, intercept, correlation coefficient) and the axis transforms in an inset on the graph. The XREG macro also includes a residuals analysis. The operation of XREG is similar to the linear regression macro (REG). There is an additional choice for data labels, (f(x),g(y)), which labels the data points with the transformed values rather than the original values. If Coordinates are used for labels, the original values will be displayed even though the transforms of the points are plotted. The macro also has a menu to select a function for each axis. The User option on that menu allows entering a custom function for X or Y using Lotus math notation. (See Chapter 6 of the GraphMaT User's Guide for instructions for this feature.) After constructing a graph, the macro displays a menu to restart, change the axis, display the regression data inset, toggle display of data labels, perform a residuals analysis, or end the macro. If the macro is repeated, it will skip to this menu. The regression equation is provided on the worksheet. Values predicted for the transforms and regression analysis are included in cells AH16 to AK19. Type numbers into the unprotected cells and press {F9} to recalculate the sheet. The calculated values are displayed in the other cells in the same row. Tutorial #7 #9 (and #10 in the User's Guide) demonstrate the XREG macro. Additional applications for the XREG macro are provided in Chapter 6 of the User's Guide. ----------------- FUNCTIONS GRAPHS: ----------------- * y=f(x): The YFX macro plots y=f(x) for any functions supported by Lotus 1-2-3. It does this by calculating f(x) at regular intervals of x and plotting these points. For a simple curve, a resolution of 20 to 50 points is satisfactory. For more complex functions, higher resolution may be required. The YFX macro also plots data points on the same graph and can perform a residuals analysis. In addition to the usual information, the macro requires minimum and maximum values for X. A menu provides the option to change the curve resolution by Chapter 4: Directions 23 ____________________________________________________________________________ entering the number of points plotted between the minimum and maximum, or the interval between points. When resolution is satisfactory, Accept will continue. At this point you may enter x and y values to be plotted as in the previous macros. Select No if data is not going to be plotted. For defining the function, cell AA9 is a range named "X" allowing a function referencing this cell to be entered in AB9 in terms of X using Lotus 1-2-3 notation. If the equation starts with X, type +X so Lotus knows that X is a value not a label. For example, entering +X*@sin(X) will plot the function y=x*sin(x). After the function is plotted, a menu allows displaying the graph, changing the graph style, changing the function, selecting from more options, or ending the macro. If data points were plotted, the menu includes changing datalabels or performing a residuals analysis. The other options include copying the values used to plot the function to another location in the sheet, or to another file. When End is selected a choice is given to leave formulas or change to values. If you do not plan on repeating the macro, convert to values. Tutorial #2 uses the GX macro which operates like the YFX macro. * f(x)=g(x): The GX macro plots of one function of X vs a second function of X. Values of g(x) are plotted on the x-axis and values of f(x) are plotted on the y-axis. Operation is similar to the YFX macro except g(x) is entered in AB9 and f(x) is entered in AC9. Tutorials #2 and #10 in the User's Guide demonstrate the GX macro. Chapter 6 in the User's Guide provides examples on applying the GX macro. * SIMULTANEOUS: The SIMUL macro plots 2 functions, f(x) and g(x), over the same x-range. It determines where the functions intersect and optionally labels the intercepts with the (x,y) coordinates. The description of how the functions are plotted is given for the YFX macro. The SIMUL macro does not solve the simultaneous equations, but determines at which x value(s) graphed the two functions are equal. If the functions are not equal at any point graphed, there are several algorithms to determine where the two lines cross or approach each other. It uses the algorithm selected to pick the point(s) closest to the probable intersection(s). The values of x, f(x), g(x), and g(x)-f(x) are displayed in a table starting with AH2.AK2. The operation is similar to the YFX macro except g(x) is entered in AC9 and the menu after the functions are plotted has different options. Selecting Solve from that menu brings up another menu to select which algorithm to use. Moving the cursor to each option displays a brief description of the highlighted selection to help decide which is best for the situation. Tutorial #1 demonstrates the SIMUL macro. Chapter 6 in the User's Guide provides more information on the algorithms used to "solve" the simultaneous equations. GraphMaT 24 ____________________________________________________________________________ * COMBINATIONS: The EQN macro plots up to 6 functions. One of these can be designated as a linear combination of the other functions. Another of the functions may be used to plot data points with labels. After entering the titles, range, and resolution, the number of functions to be plotted is entered. Note: to plot 2 functions, plus the combination and data points, enter 4. The macro presents a menu to choose if the combination and/or data points are to be plotted. The cursor moves to cells which contain the axis titles and legends for each function. Then it moves to cells where the functions are entered in terms of X using Lotus 1-2-3 notation. After constructing the graph, a menu to display the graph, make modifications, or end the macro is provided. Tutorials #9 and #10 (in the User's Guide) demonstrate the EQN macro. ------------------------------------------- DATA PLOTTING AND SIGNAL PROCESSING GRAPHS: ------------------------------------------- * PLOT: The PLOT macro displays up to 6 sets of data points. The number of data points is limited by Lotus 1-2-3 and the computer's memory. Chromatograms with several thousand data points can be plotted. The attenuation and offset for each can be adjusted manually or automatically to allow scaling, superimposing or offsetting data sets. While this macro can not generate or plot functions, it can import a function as a .PRN file generated by the YFX or TRAN macros. After the graphname is entered, an initial menu allows changing the x- interval, the initial x-value, and the number of signals plotted. After entering the titles and y-axis title and x-axis title, the cursor moves to AH9 for the legend of the first set and then to AH11 where the data set is brought in. After all of the sets are in, a menu is displayed. A submenu, Adjust, has several options for which descriptions are displayed as the options are highlighted. Manual allows adjusting the scale or offset of each set by typing changes into cells AB7.AG8. Press {Enter} after all changes are made. Previous returns to the prior menu and End will return to normal operation. The other options in these menus are described in Chapter 6 of the GraphMaT User's Guide. The scaling capability using cells AB7.AG8 is available after macro ends but is not available after the graph is moved. Tutorials #6 and #10 (in the User's Guide) demonstrate the PLOT macro. Chapter 6 of the User's Guide has more information on the PLOT commands. * TRANSFORM: The TRAN macro performs transforms on one or two sets of data points, often referred to as signals. The first data set is designated "Signal A" and the second is "Signal B." The first entry in signal A is a range named "A" so it can be referenced as "+A" in the transform formula. The same applies for B and X. The transform formula is copied to perform the same operation on every point in the signal. Once completed, the Chapter 4: Directions 25 ____________________________________________________________________________ transform can be displayed on a graph, saved as a .PRN (ASCII) or .WK1 file, or it can replace the original signal and be used as the basis for further transforms. The following is a list of the available transforms. Transforms on single data sets: - SCALE Multiplies signal by a constant factor - OFFSET Add a constant to the signal - X_SCALE Compresses or expands a signal along the x-axis - INTEGRATE Measure cumulative area under signal - DERIVATIVE Determine slope at each point in signal (dy/dx) - REVERSE Reverse a signal (first is last & last is first) - EXTRACT Extract part of a signal - LOG Take common log of a signal (base 10) - ANTILOG Take antilog of signal (raise 10 to the X power) - NAPERIAN Take natural log of signal (base e) - EXP Take exponential of signal (raise e to the x power) - USER Enter a transform in terms of A & X - SMOOTH Savitzky-Golay polynomial smoothing - ROUND Round data points to integers - AVERAGE Moving average of data points in signal - FILTER Moving average for points below threshold. - DYNAMIC Variable moving average (points averaged varies with slope) - BUNCH Store avg. of data bunches (makes signal with fewer points) - LIMIT Set maximum for signal (points below limit are unchanged) Transforms based on input of two data sets: - SUM Add signal A and Signal B - DIFFERENCE Subtract signal B from the signal A - AVERAGE Average signals A and B - PRODUCT Multiply signal A times signal B - RATIO Divide signal A by signal B - CONCATENATE Appends signal B to signal A - USER Enter a transform in terms of A, B and X Transforms based on X: - LOG Generates a log(x) signal - ANTILOG Generates a 10^X signal - NAPERIAN Generates a ln(x) signal - EXP Generates a e^X signal - RECIPROCAL Generates a 1/x signal - USER Enter a transform in terms of X The TRAN macro begins with a menu to select a mode of operation. At the point of entering the transform, you can select one from the menu, or if you select User, you can enter formulas in terms of A, B, or X using any function supported by Lotus 1-2-3. After performing the transform and constructing the graph, a menu to display the graph, make modifications, or end the macro is provided. Tutorials #5 and #10 (in the User's Guide) demonstrate the TRAN macro. Chapter 6 has additional information on applying transforms. GraphMaT 26 ____________________________________________________________________________ ---------------- The Utility Menu ---------------- The 0GRAPH.WK1 file has another menu which is invoked by pressing {Alt+U}. The operations included aid in importing or combining data from other files. It can help also in saving data in files with the correct format for use in GraphMaT. ------------------- The Index Worksheet ------------------- +---------------------------------------------------------------------------- | A B C D E F G H I + +------------------------------------------------------------------------- | 1| ********* Use [ALT+A] to restart macro ********* | 2| | 3|DISK VOL Drive D: | 4|DIRECTORY \DATA | 5| | 6|WORKSHEETS DESCRIPTION GRAPHS | 7| | 8|0 WK1 Index of files 6/12/95 12:34 | 9|0GRAPH WK1 GRAPH.MAT Worksheet (R. Gross 6/12/95) none |10|TUTORIAL WK1 Cu assay calibrations Method A,.... |11| | .| | .| | .| Figure 4.1 Layout of the 0.WK1 index file. The following operations are available from the index worksheet menu. 1. Update: Adds new files to the index. Move the cursor to the desired file and press return. The information in cells A2..I2 of the file will be brought into the index which is then alphabetized. 2. Delete: Removes a file from the index. When the prompt shows "POINT" move the cursor to the row containing the file to delete and press {Enter}. Do not remove 0.WK1 or rename it. 3. Edit: Pauses the macro. You can move around the index, edit entries in cells or add new information. Pressing {Enter} brings up the menu. 4. Retrieve: Saves the index and retrieves a file. Use the cursor key to highlight the desired file when the prompt is "POINT" then press {Enter}. 5. Abort: Exits Lotus without saving any changes. 6. Save: Exits Lotus saving any changes. Chapter 5: Tutorials 27 ____________________________________________________________________________ --------- Tutorials --------- To run the tutorials, GraphMaT must be installed with all its files in your working directory. Begin your spreadsheet program in the usual manner, making sure that any relevant items covered in the Spreadsheet Program Setup section were completed. The left column in these directions shows the keystrokes used for Lotus 1-2-3. The right column has an explanation. For other programs, some operations may require different commands. For example, choose FileOpen, select Lotus file type in Excel instead of /FileRetrieve, or select GraphView instead of pressing {F10} to display the current graph in Quattro Pro for Windows. Some of these alternates are noted in the directions and in the NOTES at the end of each tutorial. Some programs allow you to change to a Lotus 1-2-3 menu. This may be advisable if you are not real familiar with either your spreadsheet program or Lotus 1-2-3. The first tutorial has all the keystrokes and explanations for each step. Subsequent tutorials may omit explanations previously covered and skip some steps which will be indicated by ???{Enter}... There you are free to make up your own responses. Chapter 5 of the GraphMaT User's Guide includes examples of the graphs created in the tutorials using several spreadsheet programs. --------------------------------- 1. Solving Simultaneous Equations --------------------------------- The SIMUL macro plots Y=X^2+2X and Y=0.5X+10 over the range X=-5 to X=+5. It finds the two points on the graph where the two curves intersect. The points are marked on the graph and labeled with the (x,y) coordinates. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH.WK1{Enter} -Retrieve 0GRAPH.WK1. {Alt+G} -The Macro will start and display a menu. (Windows versions use {Ctrl+G}.) N -Select New from the menu. FS -Select the type of graph. A template and a macro are brought in from GRAPH.MAT. Functions{Enter} -Enter a graph name no more than 14 characters. (See NOTE) Sample Graph{Enter} -Enter title for the graph. Solutions{Enter} -Enter subtitle. {Enter}{Enter} -Accept defaults of -5 and 5 for the x-axis. A -Accept default curve resolution of 200 points. Distance{Enter}Time{Enter} -Change titles for x-axis and y-axis. {Enter}{Enter} -Accept f(x) and g(x) as legends for graph. GraphMaT 28 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- +x^2+2*x{Enter} -Enter the first equation in terms of X using Lotus notation. A plus sign before X indicates that X is a number not label. +0.5*x+10{Enter} -Enter the second equation in terms of X. D -Select display graph from the menu. {any} -Press any key to return to the macro menu. SA -Select Solve, Accept default. The macro plots points and returns to menu. (See NOTE) L -The points on the graph are labeled with the (x,y) coordinates of the closest point graphed. D{Any} -Display the graph and return to the menu. F -Select Functions. Enter these two functions. +2*X^2*@sin(X){Enter} Then label the intercepts. Experiment with +10*X*@cos(X^2/2){Enter} your own functions. SALD{any} Try other algorithms if desired. EV -When finished, select End to stop the macro. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * Some versions of Quattro Pro stop after the graph name is entered. Press {Ctrl+Break} to continue. Some versions display a graph, often a blank screen. Press any key and when it asks you "Overwrite graph? (Y/N)" enter Y. * The points marked on the graph are not always the exact intercepts of the two equations, but are the closest of the points plotted to an intercept. Information about these points are given in a table starting in cells AH5 to AK5. If the differences between the values of f(x) and g(x) in cells AK6 and below are too large, restart the macro and either use a narrower x-axis range or increase resolution by plotting more data points. Chapter 6 in the GraphMaT User's Guide has more details on the algorithms used. * Chapter 7 of the User's Guide has information the formula used to generate the labels which have 3 significant figures. The labels located in column AD can be edited if desired. Chapter 5: Tutorials 29 ____________________________________________________________________________ ------------------------ 2. Plotting f(x) vs g(x) ------------------------ The GX macro plots a function, f(x), on the y-axis, but rather than plotting it against the variable, X, it is plotted against another function, g(x), on the x-axis. In this example, f(x) is the number of moles of an ideal gas required to produce a pressure of 1 atmosphere in a 1 liter vessel as a function of temperature in Kelvin (K), X. This function is the ideal gas law: PV = nRT or n = PV/RT = 1/(0.082 * T) or f(x) = 1/(0.082 * X) (5.1) where P = Pressure = 1.0 atm V = Volume = 1.0 liter n = number of moles R = Gas Constant = 0.082 liter atm / deg mole T = X = Temperature in K Rather than plotting the number of moles against temperature in degrees K, it can be plotted against any parameter which is a function of temperature. While there are many functions which could be used, one that is easy to visualize is the conversion from K to centigrade (C). C = K-273.15 or g(x) = X-273.15 (5.2) The graph in this tutorial plots the moles of ideal gas against the temperature in C. The macro also plots data entered as x and f(x): in this case temperature in K and moles. The temperature is converted to C so it is plotted on the same scale. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NFG -Start the GX macro as in previous tutorial. ??{Enter}??{Enter}??{Enter} -Specify graphname, title and subtitle. 100{Enter}300{Enter} -Designate that the curve be plotted from 100 to 300K (whatever that relates to in C.) P40{Enter}A -Change number of points plotted to 40. Using more than necessary slows calculations and increases file size. Complex curves may require more points. Temperature{Alt+F1}0^C{Enter}-Enter x-axis title composing a degree sign. For programs which do not support the Lotus {Alt+F1} "compose" key, see NOTE. Moles{Enter} -Enter the y-axis title indicating the number of moles of an ideal gas required. Experimental data{Enter} -This defines a graph legend for data points. XVN3{Enter}100{Enter} -Select X Values New and enter the temperature 150{Enter}200{Enter} in K for the 3 data points. GraphMaT 30 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- VN.12{Enter} -Enter the moles (n) measured at the three .09{Enter}.07{Enter} temperatures. C -Select Coordinates for labels. +X-273.15{Enter} -Enter the formula to convert from K to C. +1/(.082*X){Enter} -Enter the formula for moles (n) of ideal gas shown above. The macro only recognizes the variable, X, so think of temperature in K as the X variable. D{any} -Display the graph. EF -This ends the macro. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * There is another easy way to create a degree symbol which is ASCII character 248 in some fonts. Hold down the {Alt} key and type 248. When you let go of the {Alt} key, the character will appear if the program supports these characters and if character 248 is the degree sign in the font you are using. If neither of these work, consult the manual for the spreadsheet you are using. * If you want to plot f(x) against X, you can enter +X for g(x). If you rerun this tutorial and make that change, the x-axis will be in K instead of C. The following example of the GX macro is the one used to create the graph on the cover of the GraphMaT User's Guide. While this graph is not technically useful, this tutorial will help you understand how this macro functions. Chapter 5: Tutorials 31 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- {Alt+G}R -Restart to make changes to the graph. {Enter} -Accept graphname. The graph is displayed. {any}Y{Enter} -Press a key and enter Y to overwrite it. RY{Enter}GraphMaT{Enter} -Select Restart and clear data. Change title Version 2{Enter} and subtitle. 0{Enter}20{Enter} -Set minimum to 0 and maximum to 20. P250{Enter}A -Change number of points to 250 and accept. g(x){Enter}f(x){Enter} -Change x- and y-axis titles. {Enter}N -Do not enter data. +X*@sin(X){Enter} -Replace the previous functions. Using these +X*@cos(X){Enter} two functions defines a spiral. SP -Turn off data points from previous graph. SB -Plot this using both lines and points. D{any}EF -Display graph. End macro keeping formulas. {Cursor}450{Enter} -Move to AC5, enter 450 for the maximum. {F9}{F10} -Recalculate and display graph. See NOTES. {any}521{Enter}{F9}{F10} -Enter 521 or any other number into AC5 to see what patterns are drawn. +250+500*@RAND{Enter} -Instead of picking a number yourself, let the computer do it by entering this in AC5. /gCAC11.AC260~DAC11.AC260~ -For an interesting display, have all series plot EAC11.AC260~FAC11.AC260~ the same cells alternating lines and symbols. OFCLDSELFSQQQ {right} -Move to cell AD5. {calc}{graph}{?}~{branch AD5} Type this macro into cell AD5. See NOTE. {Enter}/RNC\Z{Enter}{Enter} -This creates a name for the macro. {Alt+Z}{Enter}{Enter}... -Start the macro. Pressing {Enter} makes the macro recycle in some versions. {any}{Ctrl+Break}{esc} -Stop the macro. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * For programs which do not support the {F10} you may have to use /GraphView, /GraphUse or another command. Check the instruction manual for your program. * The graph no longer looks like a spiral because there are too few data points. You could restart the macro and increase the number of points plotted, but this pattern looks more interesting. * Excel does not allow you to create Lotus 1-2-3 macros. * If the macro works but is too fast, enter 0 in AB262. Copy this cell down 1000 cells, or more if desired, in both column AB and AC. Then extend the graph X and F ranges to include all the zeros in their column. The computer takes more time plotting all the zeros which won't show on the graph. GraphMaT 32 ____________________________________________________________________________ ------------------------------------- 3. Linear Regressions for Calibration ------------------------------------- This tutorial performs a linear regression used to calibrate an instrument for quantitative analysis. The LLD macro, a variation of the REG macro, plots instrument readings against concentration of the standards. It determines a regression equation for the best fit line of the data points. In addition to the calculations in the REG macro, it calculates a lower limit of detection (LLD) and has an additional factor in the regression equation. This example demonstrates the ability of GraphMaT to use data in named ranges and how to work with several graphs on a single worksheet. It is a slightly longer tutorial which covers GraphMaT features not used in other examples. The sample file LLD.PRN is imported into the worksheet. Ranges are made for each of the variables; concentration of the standards (CONC), responses from the atomic absorption detector (AA), and responses from the electrochemical detector (EC). The first graph plots CONC against AA response. That graph is moved to another area of the worksheet. A second graph plots the same CONC against EC response. That graph is also moved. The regression equation is used to calculate concentrations for the AA responses of some samples. A third graph plots the AA response against the EC response to determine how well the methods compare using the REG macro. This graph is also moved. The range table is updated. The worksheet file is saved after updating the file description. Then the index worksheet, 0.WK1, is updated. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter} -Retrieve 0GRAPH.WK1. {Home}{F5}R_1{Enter} -{Home} takes you to the upper left corner of the spreadsheet where the range table is located. {F5} or "goto" key followed by R_1 takes you to the start a a range named "R_1" at cell A101. {F2}: data{down} -Change the description for range "R_1" by changing the information in cell A101. The label in the top left cell of each range is displayed in the range table. Conc{right}AA{right}EC{Enter}-Enter labels in cells A102, B102 & C102. {Cursor}/FIN{Cursor}{Enter} -Move to cell A103 and import the comma delimited file LLD.PRN. (See NOTE) /RNCconc{Enter}{end}{down} -Create a range named "conc" which contains {Enter} the 5 concentration values. {Cursor}/RNCaa{Enter}{end} -Move to B103. Create range for "aa" data. {down}{Enter} {Cursor}/RNCec{Enter}{end} -Move to C103. Create range for "ec" data. {down}{Enter} Chapter 5: Tutorials 33 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- {Alt+G}NRD -Start the macro and select New graph using the detection (LLD) macro. Method A{Enter} -Enter a graph name. Analysis of Copper{Enter} -Enter graph title and Subtitle. Method A: AA Detector{Enter} {F2} (ppm){Enter} -Edit x-axis title to "Concentration (ppm)" Peak height (Abs){Enter} -Replace "Response" for y-axis title. SA -Suppress display and accept current labels. R -Select range for concentration data. {Cursor}{Enter} -Highlight "CONC" range and press enter. R{Cursor}{Enter} -Highlight "AA" range and press enter. The macro constructs a graph using the data. D -Display regression data in graph insert. {any} -Graph is displayed. Press any key to continue. X1.2{Enter}{any} -Change the width of insert. (See NOTE.) A -Accept the location and size of the insert. S -Skip residuals analysis. {Alt+G} -Start macro and displays a menu. M -Select Move graph to a new location. A121{Enter} -The range showing in AD1 (AA2.AK28) and the graph are moved to A121. (See NOTE) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * LLD.PRN contains all the data required for this tutorial. When imported correctly there will be a number in each of 15 cells A103.C107 and no commas will be present. To import LLD.PRN with Excel, Open it as a comma delimited text file. This puts the data in the upper left hand corner of a new sheet. Select the cells with the data. Choose EditCopy. Switch to the 0GRAPH window. Make sure the cursor is in cell A103 and choose EditPaste. Go back to the LLD.PRN window and close it. To simplify this, 0GRAPH.WK1 includes a utility macro. Move the cursor to A103, press {Ctrl+U}Import, highlight LLD.PRN and press {Enter}. * This step can be repeated until the insert frame is in the desired position and size. The text may not fit. In many versions, the font can be adjusted after the macro is completed to make it fit in the frame. * For Windows versions of Quattro Pro, there is an extra step when moving a graph. The macro creates a temporary file and tries to erase it when finished. This operation is not supported in Windows versions of QP. After each move, you must erase this file. You can minimize QP, start Windows File Manager, delete 0GRAPH.TMP found in your default working directory, and return to QP. There have also been problems with not all of the graphs moving correctly. It is best to save a file before moving a graph. Remember to save 0GRAPH.WK1 using a different file name: do not overwrite the original file. GraphMaT 34 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- {Alt+G}R -Repeat the macro so changes can be made. Method B{Enter} -Name the second graph "Method B" {Enter} -Accept the title. Method B: EC Detector{Enter} -Enter a new subtitle. {Enter} -Keep same x-axis title. {F2}{BkSp}{BkSp}{BkSp}{BkSp} -Erase "Abs)" from y-axis title and add "nA)" nA){Enter} Y-axis title should be "Peak Height (nA)" SA -Suppress display and accept current labels. A -Accept current X data. R{Cursor}{Enter} -Highlight "EC" range and press enter. D{any}A -Display regression data and accept the location and size of the insert. S -Skip residuals analysis. {Alt+G} -Start macro and display a menu. M -Select Move graph to a new location. {Home}{PgDn}{PgDn}...{cursor}-Move past previous graph data to cell A151. {Enter} -Move the second graph to the cursor location. {Alt+G}E -Start macro and select Erase. Y{Enter} -You must confirm before the macro is erased. /GNU{cursor}{Enter} -Display Method A graph. Note the lower limit of detection (LLD). {any}NU{cursor}{Enter} -Display Method B graph. Method A had a lower LLD and better correlation coefficient. {any}Q -Quit the Lotus menu Chapter 5: Tutorials 35 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /RNCR_2{Enter} -Create a range named "R_2" with A121 in the A121.A122{Enter} upper left corner and a range named "R_3" /RNCR_3{Enter} with A151 in the upper left corner. Ranges A151.A152{Enter} must be larger than 1 cell for the range table. {Alt+R} -Update the range table located in the upper left corner of the sheet. The range table shows the range names, their location, and the contents of the upper left cell. (Use {Ctrl+R} in Windows.) {home} -Move to the range table. Look at the descriptions and select where you want to go. {F5}R_2{Enter} -Go to the range for Method A data. {cursor} -Move cursor to H139. /C{esc}{up}{up}{up}.{end} -Make 2 copies of cells H136.J136 starting at {right}{Enter}.{down}{Enter} H139. These cells have the formula for calculating a concentration (x) for a response (y) using the regression equation. {cursor}20{Enter} -Enter values for the response in unprotected {down}20{Enter}{F9} cells J139 and J140. Recalculating displays predicted concentrations at I139 and I140. {cursor}10{Enter}{F9} -Column H has a factor for dilution. Enter a value in H139 to adjust the result in I139. GraphMaT 36 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- {Alt+G} -To construct another graph to check how well the two methods correlate against each other, restart the macro. N -Select New graph from the menu RL -This time select Regression, Linear. Correlation{Enter} -Enter the name for the graph. Copper Analysis{Enter} -Provide graph title. AA vs. EC Detectors{Enter} -Provide graph subtitle. AA Detector (ABS){Enter} -Provide x-axis title. EC Detector (nA){Enter} -Provide y-axis title. SA -Suppress display and Accept current labels. R{Cursor}{Enter} -Highlight "AA" range and press enter. R{Cursor}{Enter} -Highlight "EC" range and press enter. D{any}A -Display regression data and accepts the location and size of the insert. S -Skip residuals analysis. {Alt+G} -Start macro and display a menu. M -Select Move graph to a new location. A181{Enter} -Move this graph to the A181. /RNCR_4{Enter} -Create a range named "R_4" with A181 in the A181.A182{Enter} upper left corner. {Alt+R} -Update the range table. {Alt+G}E -Start macro and select Erase. Y{Enter} -You must confirm before the macro is erased. /GNU{Cursor}{Enter}{any}Q -Display any of the three graphs. {Alt+G}S -Start macro, select Save file. TUTORIAL{Enter} -"TUTORIAL" replaces "0GRAPH" at A2. Cu assay calibrations{Enter} -Enter a file description. Method A, Method B{Enter} -Enter names of graphs. The macro will save the file using TUTORIAL.WK1 as the filename. /FR{Cursor}{Enter} -Retrieve 0.WK1. U{Cursor}{Enter} -This updates the index with the info you entered in TUTORIAL.WK1. R{Cursor}{Enter} -Highlight 0GRAPH.WK1 to start next tutorial. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTE: * You cannot specify cells for data labels on XY graphs using the Excel commands. You can do it using the Lotus commands. The texts in the insert are data labels. * The macro does not specify color for the graphs. Most programs have color as the default. If you need to add color use /GraphOptionsColor. You must also re-create the named graph to make the change permanent. Chapter 5: Tutorials 37 ____________________________________________________________________________ ----------------------- 4. Multiple Regressions ----------------------- Another form of regression analysis available determines the relationship between a number of independent variables on a single dependent variable. While Lotus 1-2-3 can perform an analysis on as many as 16 independent variables, the GraphMaT template is set up for a maximum of 8. In this example, the number of wins the NBA teams had during the 1993-4 season is the dependent variable. The problem is to determine which statistics have the biggest impact on a team's record. The variables and their abbreviations for this tutorial are: WINS Number of wins by a team during the season (dependent variable) PPG Average points scored per game by the team REB Rebounds made during the season TO Turnovers committed by the team during the season FG% Percent of field goal attempts made during season PF Personal fouls committed during the season OP-PPG Average points scored per game by their opponents OP-TO Turnovers committed by opponents during the season OP-FG% Percent of field goal attempts made by opponents during season WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NRM -Start the MULT macro as in previous tutorial. A -Accept default settings: "Ind. variables: 8 Labels:y Color: n" BBALL{Enter} -In this macro the title is both title and graph name so it is important to enter the correct title the first time through this macro. '1993-4 SEASON{Enter} -Subtitle. (Use ' to start label with a number.) TEAM{Enter} -Enter a description for the labels. ?????{Enter}?????{Enter}... -Enter the abbr. of the 9 variables in the order listed above. The first 5 characters of these variables are used to name the scatter graphs. OC -Select Other as source for labels and indicate the labels will be Combined from another WK1 file. TEAM{Enter} -The range for the labels is named "TEAM." {Cursor}{Enter} -Highlight NBASTATS.WK1 & press {Enter}. C -Select Combine data from a WK1 file. DATA{Enter}{Cursor}{Enter} -Combines the range "DATA" from the file NBASTATS.WK1. AAAAAAAA -The range "DATA" has all 8 variables so Accept each remaining variable to indicate the data is already in. -Wait while 10 graphs are constructed. GraphMaT 38 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- {Cursor}{Enter} -Use the cursor keys to highlight a graph and press {Enter} to display it. {any}NU{Cursor}{Enter} -Press any key to return to the graph menu. Highlight another graph to view. (See NOTE) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTE: * Excel 5 returns an error when a macro ends in a menu. To view the other graphs use the Lotus commands, /Graph Name Use. Saving the spreadsheet as a WK1 file and retrieving it produces a workbook with each graph on a separate sheet. The graph named "BBALL_BAR" displays the absolute values of the correlation coefficients for the linear regressions of each independent variable with the dependent variable. Of the eight variables tested, the best predictor of a team's record is their opponent's field goal percentage. The number of turnovers committed by their opponents has little correlation with a team's record. There are 9 XY-scatter graphs, one for each independent variable and one named with the title (BBALL) which uses all independent variables to predict the dependent variable. For example, "BBALL_XY:OP-FG" displays the correlation between a team's wins and their opponent's field goal percentage. Each data point can be labeled with the team's name. There is considerable overlap making some hard to read, but ones which deviate from the norm would be easily identified. The correlation coefficient is displayed in x-axis title. When viewing a graph where correlation is poor, the best fit line does not appear to fit well. This is typical. Chapter 5: Tutorials 39 ---------------------------- 5. Transforming Data Signals ---------------------------- This example uses two chromatograms for signals. Two signals are summed to produce a signal with a small peak hidden under a large peak. Then to demonstrate it is actually two peaks, the large peak will be extracted and the 1st and 2nd derivatives will be generated to reveal the small hidden peak. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NDT -Start the TRAN macro as in previous tutorial. ???{Enter} -Enter a graphname. S2{Enter} -Change the operating mode to the following: "Transform: NONE Signals=2...." A -Then Accept the operating mode. ???{Enter}... -Enter title, subtitle, y-axis and x-axis labels and legend for A range or Press {Enter} to accept default labels. I{Cursor}{Enter} -Import DATA_A.PRN as signal A. ???{Enter} -Enter a legend for signal B. I{Cursor}{Enter} -Import DATA_C.PRN as signal B. 0{Enter}.02{Enter}9999{Enter} Enter values for the x-axis. D{any} -Display graph. For older versions, use Operation Color Accepy to add color. OTTA -Change Operation to transform a Single set -13550{Enter} -Select Transform Offset. A negative offset lowers and a positive offset raises each point. D{any} -Display the graph. Note the A data set is offset from the x-axis. The transform brings the signal down to the x-axis. MR -More displays a second menu. Replace overwrites signal A with the transformed data. D{any} -Display the graph. Signal A is offset and the transform is on the x-axis. The tutorial will no longer instruct you, but displaying the graph after each operation will help understand what is occurring. MRA -More displays a second menu. Replace A_signal with the transformed data. OTA -Change Operation so both A & B signals are the basis for the transform. TS -A different transform menu is displayed. Select Sum to add the two signals. MRA -Again replace signal A with the transform. OTTTA -Change to transform a single data set. TE2{Enter}4{Enter} -Extract the part of the signal of interest, the data between x=2 and x=4. X2{Enter}0.02{Enter}4{Enter} -Redefine the x-axis to correspond with the extracted data points. GraphMaT 40 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- MRAMDD{any} -Replace signal A and Duplicate it. When viewed by itself, the aberration from the small peak is no longer as obvious. TD1{Enter}1{Enter}D{any} -Derivative is big compared to the signal. TD1{Enter}0.2{Enter}D -Repeat the derivative transform using a smaller scale. You can see an indication of the buried peak in the derivative plot. {any}MRATD1{Enter}1{Enter}D -Replace signal A with the derivative and take a derivative of the derivative. The second derivative appears large and somewhat noisy. {any}TD3{Enter}.2{Enter} -Entering a higher value for "Number of points" reduces noise. See Chapter 6 for more information on noise reduction. MID -Interchange puts the Data_A legend first. The 2nd derivative shows an extra peak at about x=3.3 indicating the peak impurity. See NOTE. {any}E -End the macro. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTE: * In addition to the aberration in the first and second derivatives due to the impurity, the graph shows that the peak has a non-Gaussian shape. GraphMaT has many transforms not covered in this tutorial. Repeat the macro and try the other transforms. The User transform allows entering formulas in terms of X, A, and B for the x-axis, first and second signals respectively. Set operation mode to Transform: A Signals:1. Load either DATA_A.PRN or DATA_B.PRN and try the transforms for a single data set. Set operation mode to Transform: A&B Signals:2. Load both signals and try the transforms designed for two signals. Tutorial 10 and Chapter 6 have more information on transforms and their application. Chapter 5: Tutorials 41 ____________________________________________________________________________ ------------------- 6. Plotting Signals ------------------- This example displays 6 chromatograms. Since these are relatively large files only two are used and each will be used three times. It demonstrates how signals can be scaled and offset for an effective display. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NDP -Start PLOT macro as in previous tutorial. ???{Enter} -Enter a graphname. I+0.02{Enter} -Change the x-axis Interval to 0.02. A -Accept default setting of 2 signals. ???{Enter}... -Enter titles. ???{Enter}I{cursor}{Enter} -Specify a legend for A series, select Import, highlight DATA_A.PRN and press {Enter}. ???{Enter}I{cursor}{Enter} -Import DATA_B.PRN for the B series. D{any} -Display graph. You can view the graph after each change then press any key to continue. AM+0.01{Enter} -Adjust Manually the scale for the A series by entering 0.01 in cell AB7. AH -Autoscale Height changes the scale on all signals so the tallest peak of each signal is the same as the tallest peak in signal A. Z -Zero the graph adjusts the minimum point of each graph to touch the x-axis. P -Return to the Previous menu. RS+6{Enter}A -Restart macro. This time enter 6 for number of signals and accept all other entries. {Enter}... -Press {Enter} or Accept until signal C. ???{Enter} I {cursor} -Import the same two files for signals {Enter}... C, D, E, & F. AM{Cursor}+.01{right} -Adjust Manual the scale of the 4 new signals +.005{right}+.01{right} by moving the cursor to cell AD7 and typing +.01{Enter} values into cells AD7 to AG7. Use the cursor key to move to the next cell after typing each number. Press {Enter} after the last change is made to return to the menu. ZS+50{Enter} -Zero the new sets and Shift all 6 signals up 50 on the y-axis. Y+100{Enter} -Y offset separates each signal by 100. (ie., adds 100 to every point of the B signal, 200 to every point of the C signal, etc.) GraphMaT 42 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- XA+.25{Enter}D -Separate each signal by .25 along the x-axis similar to the Y offset above. This gives a 3-d look to the display. {any}PE -Return to the Previous menu and End the macro. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * The graph series are in reverse order: the signal marked F on this graph is graph series A. This is done because series A is displayed first and the other series are plotted on top of A making the graph appear more dimensional. Also for programs which have a vertical legend, the A series is on top so it lines up with the signals as they are offset on the graph. * There are more options provided in the PLOT macro than were demonstrated in this tutorial. Some of the other features are used in Tutorial 10 in the User's Guide which uses these same sample data files and looks closer at the individual peaks. * In the section of Chapter 6 of the User's Guide on the PLOT macro, each menu option is described in detail. * Tutorial 10 and Chapter 6 of the User's Guide has more information on the autoscale, zero, and offset functions for overlaying peaks. Chapter 5: Tutorials 43 ____________________________________________________________________________ ---------------------------- 7. Fitting Data to Equations ---------------------------- GraphMaT has several tools for finding an equation that fits data. This tutorial demonstrates several of them. The first step uses a screening curve- fit macro, CFIT. Seven transforms of the x variable data are regressed against the y data to determine whether any of them correlate. See Chapter 6 of the User's Guide for more specifics on the CFIT macro. The first example uses concentration of a compound during a reaction as x, and the reaction time as y and evaluates relationships between f(concentration) and time. The concentrations are in the file CFIT_CON.PRN and the corresponding times are in the file CFIT_HRS.PRN. Usually time is plotted on the x-axis, however entering time as x would be used to determine the correlation between the f(time) and concentration. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NRC -Start CFIT macro as in previous tutorial. REACTION{Enter} -Enter graphname. A -Accept graph settings which should be : "Res..=50 X-..:auto Color:n Log base:10 Off..=0" If you want graphs in color and your program creates graphs in B&W, press C. ???{Enter}???{Enter} -Enter title and subtitle. ???{Enter} Enter a legend for data. X = Concentration{Enter} Enter x-axis title. Y = Time (Hrs){Enter} Enter y-axis title. S -Skip labels for data points. I{Cursor}{Enter} -Import CFIT_CON.PRN as x and I{Cursor}{Enter} CFIT_HRS.PRN as y data. The macro constructs several graphs. {cursor}{Enter} -Display the CFIT bar graph. The correlation coefficient for the log relationship is best. {any}NU{cursor}{Enter} -Display the log graph. Some versions terminate the macro. See Note on page 38 for displaying other graphs. {any}Q -Quit the macro and display the spreadsheet. For the log equation, b=43.8 and m=-42.5. {cursor}7{Enter}{F9} -Type a value for x in cell AB17. The spreadsheet calculates y using each regression equation and displays the predicted values in cells AD17-AK17. The concentration in AI17 predicted by the log equation, 7.9, is close to 8.2 value measured at time = 7.01. GraphMaT 44 ____________________________________________________________________________ The results show there is a good correlation for both the log and polynomial fits. Since the log relationship is common in chemical reactions, it may be worth generating a semi-log plot of the data. This is done next using the XREG macro. To make a more conventional plot, this example plots time as the x variable and concentration as y. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NRF -Start XREG macro. ??{Enter}??{Enter}??{Enter} -Enter graphname, title, & subtitle, Time (Hrs){Enter} -Enter title for x-axis. Log Concentration{Enter} -Use "Log" for y-axis based on CFIT analysis. SA -Suppress and Accept blanks for labels. I{Cursor}{Enter} -Import CFIT_HRS.PRN as x data. I{Cursor}{Enter} -Import CFIT_CON.PRN as y data. AA -Accept normal (linear) x & y scales for now. S -Suppress display of regression. D{any} -Observe data does not fit using linear scales. F -Select Function to change axis scale. A -Accept the normal (linear) x-axis. LD -Change y-axis to Log to plot the log of the concentration and Display the graph. Note the fit is good on this semi-log plot. {any}S -Add Statistics to graph display. The slope and intercept are the different from the CFIT macro since the x & y-axis were interchanged. D{any}???{any}A -Adjust position if needed. Accept when OK. LDC -For Labels to Display, select Coordinates. Chapter 5: Tutorials 45 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- D{any} -Display the graph. (See Note) A{any}{any}{any} -Perform residual Analysis. (See Note) R -Restart macro. {Enter}... -Accept or {Enter} previous information. DA -For Display Accept previous labels. VE{cursor}21.1{Enter} -Selecting ValuesEdit for x-data allows changing value in AE30 to 21.1. AAA -Accept y-data and axis scales. A{any}{any}{any}E -Perform Analysis & End macro. (See Note) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * The two graphs displayed in the User's Guide show that the one created using GraphMaT appears similar to the one done using normal spreadsheet operations with a log scale for the y-axis. The transform axis in the GraphMaT macro simplifies the regression analysis. * The (x,y) labels are the original coordinates. If f(x)g(y) had been selected, the log values which were plotted would have been displayed instead. * The analysis displays an XY plot of the residuals, a bar chart of the distribution of residuals, and the original graph. In the residuals plot, the point at 18.3 minutes appears out of line. If deviation from the regression line is due to random errors, residuals will appear random and the bar chart will display a normal distribution. This example only has 11 data points. Avoid making statistical inferences based on small data sets. However, visual deviations bear investigation and often data points can be thrown out or corrected if mistakes are found. In this case, it was found that 18.3 was typed in place of the correct time, 21.1 min. * After the corrected value is entered, the residuals appear to follow a pattern. * Based on the regression analysis using the corrected data, the concentration, y, can be estimated from time, y, using the equation: log(y) = 1.035 - 0.0234 * x or y = 10^(1.035 - 0.0234 * x) (5.3) GraphMaT 46 ____________________________________________________________________________ The equation from the regression analysis can be plotted and the experimental values can be added to the graph using the YFX macro. A file is made of the residuals, the difference between the actual data and the value predicted by the function. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NFY -Start the YFX macro as in previous tutorials. ???{Enter}... -Enter graphname, title and subtitle. 0{Enter}50{Enter} -Specify graph range of 0 to 50. A -Accept default resolution settings. ???{Enter}... -Enter axis titles and legends for the function and data points. Y Indicate that data will be plotted. I{cursor}{Enter} -Import CFIT_HR2.PRN, the corrected x data. I{cursor}{Enter} -Import CFIT_CON.PRN for the y data. OI{cursor}{Enter} -Import CFIT_HR2.PRN for labels. +10^(1.035-.0234*X){Enter} -Enter the equation for the concentration (Y) as a function of time (X). D{any} ... -Display the graph and make any changes to the style if desired. A{any}{any}{any} -The macro displays a scatter diagram of the residuals and a bar chart showing the distribution of the residuals. See Note. MSRCFIT_RES{Enter} -More displays an alternate menu from which you can save the residuals in a PRN file. EV -Name the graphs and End the macro. {Alt+G}STUT7.WK1{Enter}... -Save the file. Chapter 5: Tutorials 47 ____________________________________________________________________________ Next a polynomial curvefitting macro, NORD, will find an equation to correct for the residuals. While the semi-log plot of the concentration vs. time has a theoretical basis, this correction does not. Perhaps the temperature or some other uncontrolled condition varied slightly during the experiment or the method used to measure the concentration was not linear. The object of this section is only to find an empirical mathematical fit for the experimental data. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NRP -Start the NORD macro. ???{Enter}... -Enter graphname, title, subtitle, legend for data, x-axis and y-axis titles. C -Continue using the default settings: "Resolution=50 Order=1 X-scale=auto" SI{cursor}{Enter} -Skip labels. Import CFIT_HR2.PRN (time) I{cursor}{Enter} and import CFIT_RES.PRN, (residuals). The macro constructs and displays a graph with a 1st order (ie. linear) regression line. {any} -Press a key to return to the menu. O#{Enter}P{any} -Select Order, enter 2 then 3 and Process the data to display 2nd and 3rd order fits. E -End the macro. See Note. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTE: * The constant for the polynomial is displayed in cell AF5 and the coefficients are displayed in the row starting in cell AE11. The residuals can be approximated using the equation: y = - 0.30 + 0.057 x^1 - 0.0025 x^2 + 0.00003 x^3 (5.4) The next step is to add this correction to the plot of the function in the YFX macro. The file TUT7.WK1 is retrieved and the previous function is edited to include the correction for the residuals. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FRTUT7{Enter}{Alt+G}R -Retrieve TUT7.WK1 and restart the YFX macro, {Enter}{any}Y{Enter} overwriting the previous graph. F{F2} -Select function and change to the edit mode. -.3+.057*X-.0025*X^2 -Type in the function and press {Enter}. +.00003*X^3{Enter} A{any}{any}{any}EV -Perform the residuals Analysis and note the small values and a more random distribution. GraphMaT 48 ____________________________________________________________________________ The next example plots the family income in thousands of dollars as x vs. the percentage of households as y. The CFIT screen does not find any close correlations, so instead of going to the XREG macro, the tutorial uses a nth order polynomial curve to approximate the data. This tutorial gives some more information on limitations of polynomial curve-fitting. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NRC -Start CFIT macro as in previous tutorial. A... -Follow the same directions for the CFIT macro earlier in this tutorial. Family Income (in $1000) -Enter x-axis title. {Enter} Households (%){Enter} -Enter y-axis title. S -Skip labels for data points. F{cursor}{Enter}A -Bring in NORD.WK1 file which has both x and y data. Accept the y data. {cursor}{Enter} -Display the CFIT bar graph. There are no good correlation coefficients. {cursor}{Enter}{any}... -Display the individual scatter graphs to show that none of them come close. Q -Quit the macro. {Alt+G}NRP -Restart and select the nth order polynomial regression macro, NORD. ???{Enter} -Enter a graphname, title, subtitle, legend for data, x-axis and y-axis titles. C -Continue using the default settings: "Resolution=50 Order=1 X-scale=auto" SF{cursor}{Enter}A -Skip labels. Bring in NORD.WK1 file as before. The macro constructs and displays a graph which has a 1st order (ie. linear) regression line superimposed. {any}O2{Enter}P -Press any key to return to the menu. Select Order, enter 2 and Process the data to display a new graph of the 2nd order analysis. {any}O#{Enter}P -Repeat to display 3rd, 4th, 5th, & 6th order graphs. {any}O7{Enter}P{Enter} -The macro will not allow a 7th order analysis since there is insufficient data. O4{Enter} -Switch back to the 4th order analysis. GraphMaT 49 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- X1{Enter}150{Enter}P{any} -Enter x-axis limits which extend beyond the data. Process the data. Note that the polynomial curve is unpredictable when extended beyond the range of the data. XSLCVP{any}E -Change the x-axis back to automatic. Label the data points with the Values and display the finished graph and end the macro. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * Occasionally there are errors with regression analysis used by the CFIT macro. See chapter 6 in the User's Guide for more information on polynomial curve-fits in spreadsheets. -------------------------------------------- 8. Plotting Linear Combinations of Equations -------------------------------------------- Often a relationship is define by several functions. This occurred in the reaction rate tutorial where a very small correction was found to the semi-log relationship. In other cases it may be advantageous to plot the individual components rather than just the entire function as was accomplished with the YFX macro. This example plots each term and the sum of the three terms of the van Deemter equation for chromatographic column efficiency, H, in terms of flow velocity, v. H = A + B/v + Cv (5.5) WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NFC -Start EQN macro. ???{Enter}... -Enter a graphname, title and subtitle. 0.1{Enter}4{Enter} -Enter min and max for graph. GraphMaT 50 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- I.1{Enter}A -Change Interval to 0.1 and Accept settings. 4{Enter} -Enter 4 functions (3 + the sum). See Note. C -Change "Combination off" to "on". ???{Enter}... -Enter y- and x-axis titles. The micron symbol is made using Lotus {Alt+F1}/u compose key. Total{Enter}???{Enter}... -Enter legends for sum and 3 functions. +2{Enter}+3/X{Enter} -Enter the terms for eddy diffusion, +7*X{Enter} longitudinal diffusion, and mass transfer. D -Display the graph. EF -End macro leaving formulas in tact. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * GraphMaT's EQN macro will plot 6 functions. One of the functions can be designated as a sum of the individual functions. Another function can be reserved for plotting data points. * If you want to plot data points, you may restart the macro. Type a different name for the graph, this time enter 5 for the number of functions, and select Both to plot both sum and data. This time th macro will move to allow entering a legend for data. After entering the functions, you may move the cursor and enter values in column AG, for example move to AG20 and type 13 for the value at 1ml/min. Move the cursor down to AG30 and type 18 for the value at 2ml/min. Press {Enter} after all data have been entered. Select Coordinates to have GraphMaT label the points with the (x,y) coordinates. GraphMaT 51 ____________________________________________________________________________ ------------------- 9. Combining Graphs ------------------- There are occasions where several sets of data and their corresponding regression lines must be combined on a single graph. For example plotting a property of several compounds under varying conditions, or the sales of several products over time. This tutorial demonstrates the use of a double reciprocal graph. The initial enzyme reaction velocity, vo, at various substrate concentrations, [S], is measured. The reciprocals, 1/vo and 1/[S], are plotted. The experiment is repeated with an inhibitor present. The graphs are used to determine if the inhibition is competitive. In this tutorial, two linear regression graphs are made using the XREG macro. Each graph is moved to another section of the worksheet. Then the information from both graphs is combined and displayed on a single graph. Chapter 7 of the GraphMaT User's Guide has additional information on the spreadsheet graphing techniques used in this tutorial. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /FR0GRAPH{Enter}{Alt+G}NRF -Start XREG macro. GRAPHA{Enter}???{Enter}... -Use GRAPHA as name, then provide titles. SA -Suppress and Accept no labels. CX{Enter}{cursor}{Enter} -Combine "X" range, [S], from TUT9.WK1 CA{Enter}{cursor}{Enter} -Combine "A" range, vo, from TUT9.WK1 RR -Use Reciprocal for both axes. D{any}H.52{Enter}{any} -Display regression information, reposition X2.5{Enter}{any}AE and scale the insert. Skip analysis. {Alt+G}MA105{Enter} -Move the graph to cells starting at A105. {Alt+G}RGRAPHB{Enter} -Restart the macro for the second graph. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Repeat the steps for GRAPHB. Accept the x values and Combine range "B" from TUT9.WK1 for the initial velocity data. The statistics insert should be properly positioned and not require adjustments this time. When finished, move this graph to cell A135. WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- {Alt+G}EY{Enter} -Erase the macro. The rest of the graph is made using normal Lotus commands. {F5}R_1{Enter} -Go to range R_1 which starts at A101. /REB135.D138{Enter} Erase the GRAPHB titles. These strings act as zeros and may be displayed on the graph. GraphMaT 52 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- /GNU{cursor}{Enter}{any} -Make GRAPHA the current graph. X{cursor}{Enter} -Extend the X graph series to B161. BC139.C161{Enter} -Adds the B series to the graph. (See note) F{cursor}{Enter} -Extend the F series to D161. ODF{down}{Enter}RQQ -Extend the F series datalabels to E161. NCGRAPH{Enter}Q -Name the new graph. {F9}{F10} -Recalculate sheet and display graph. {cursor}???{Enter} -See notes below and make changes to individual cells. {F9}{F10} -Recalculate sheet and display graph. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTES: * To extend the regression lines enter x coordinates of 0 in cells B125 and B155. * To change the vertical scale of the statistic box for GraphA, enter 0.33 at D123. * If additional changes are required to get the printing to fit inside the statistic insert on the graph, the coordinates in cells B121, B122, D122, D123, B151, B152, D152, or D153 can be changed. * Replace the axis descriptions in the statistic inserts by entering "Experiment A" at E115, "No inhibitors added" at E117, "Experiment B" at E145, and "Inhibitor added (6mM)" at E147. * Since both experiments were performed using the same substrate concentrations, it was possible to use the same x values for graph series A and B. GraphMaT 53 ____________________________________________________________________________ ------------------------------- 10. Miscellaneous Sample Graphs ------------------------------- The User's Guide has additional tutorials and sample data to accompany the explanations provided in chapter 6 of the GraphMaT User's Guide. Chapter 6: Applications 54 ____________________________________________________________________________ This chapter is not intended as a statistics reference. There are many fine statistics books, a couple of which are listed in the References section of the User's Guide. However, there are relatively few good sources for information on using the computer to apply the statistics. Likewise, many books on applying graphical methods do not give any suggestions on how to use a computer to prepare the graphs. The manuals which come with Lotus 1-2-3, Microsoft Excel, Quattro Pro or AS-EASY-AS do not have much information on applying the statistics and graphing (or "charting" as it is being renamed) capabilities built into the current spreadsheet software. Lotus 1-2-3 release 5 manual only has 3 pages on performing a regression analysis. Regression is omitted entirely from their release 4 (DOS) manual and little information is provided through the help facility. The manuals only give enough instructions to create a basic graph. Even in the supplementary books written for these programs, the graphs discussed are relatively simple. They make no attempt to teach you how to use graphical methods to evaluate data. This chapter is a practical source of information on applying the graphing and regression capabilities for which the 12 GraphMaT macros were designed. It will help you determine which GraphMaT macro to use in a situation and how to use them effectively. It fits in this gap between the text books and the computer manuals. Chapter 6 is not distributed as a disk file with the GraphMaT software. It is available exclusively in the printed User's Guide. Chapter 7: Advanced Spreadsheet Graphing Tips 55 ____________________________________________________________________________ This chapter was written to help the casual user of spreadsheets get more out of the program. One reason spreadsheets are not used more frequently is that they are not quite as easy to use as other software since everything is not built in. For example, even though address labels could be printed in a spreadsheet, it is easier to use a program that was designed to only print address labels. Software designed for specific applications are generally easy to use. Spreadsheets are not that type of program. You start with a blank sheet, a host of features, and a library of functions. Then you have to decide which to use and how to combine them to make them do what you want. There are many applications for which specialized programs are not available. Spreadsheets could be used for many of them. Designing your own also assures you of getting exactly what you need. It is often faster and simpler to customize a spreadsheet than to write a program in BASIC, C, Fortran, or other programming language since many operations are already programmed into spreadsheets. The manuals that come with the software and many of the books written about Lotus 1-2-3, Excel, Quattro Pro, or AS-EASY-AS generally only give instructions on how to use each program feature; they do not explain how to combine or apply them. The examples provided are generally quite trivial and do not give a true picture of what can be accomplished using a spreadsheet. This is basically left up to the imagination and programming ability of the user. There are many books available that describe the features, functions, and options available in today's spreadsheet programs, and it is not worth repeating those. This chapter concentrates on three key areas which were fundamental to the GraphMaT macros and templates. The information on these three areas is not commonly presented. They are nested @functions, macro statements that include formulas, and spreadsheet graphing techniques. Also included is a section which describes GraphMaT's format which will enable you to modify existing graphs or add another type of graph. This chapter will help you understand how the different features can be integrated to develop your own spreadsheet applications. Chapter 7 is not distributed as a disk file with the GraphMaT software. Like Chapter 6, it is available exclusively in the printed User's Guide. Chapter 8: Technical Support 56 ____________________________________________________________________________ -------------------------------------- Support Policy on Macros and Templates -------------------------------------- We hope GraphMaT and the associated documentation is trouble-free and useful for you. We have used these macros and templates extensively and have designed both them and the directions so as to avoid problems. We realize that problems may arise. In most cases you should be able to resolve them by referring to the GraphMaT documentation or the manuals which came with your spreadsheet program. If you have problems that you cannot solve, we will try to help you resolve them. We do request that you do not call for technical support until after you have read the manual and tried the tutorials. Technical support is available for registered users for at least 90 days after you register. We will also attempt to answer your questions during the evaluation period. This is the advantage of shareware: you can try it before you buy it. Please note that technical support for GraphMaT is limited to getting the GraphMaT files onto your system and the operation of the macros. It cannot involve the application of statistics, nor use of the spreadsheet commands or functions, nor interpretation of the data. Many of the problems people have using these macros are actually problems with the spreadsheet program. We cannot provide support for normal spreadsheet operations. For example, GraphMaT does not produce any printed output of the graphs or set the font for the graph titles. These are done using normal spreadsheet operations. For support on these operations, please consult the manual for you spreadsheet program or contact the maker of your spreadsheet program directly. We are interested in what you are doing with GraphMaT and what problems you have encountered. If you have comments or suggestions we would appreciate hearing from you. ------------ Registration ------------ For information on registering GraphMaT, consult the file REGISTER.TXT on the disk or contact us. Roger W. Gross or Daniel J. Gross GraphMaT P.O. Box 681 Menomonee Falls. WI 53052-0681 (414)-253-2297 71053.2765@compuserve.com