**** * * * ******* * * * ** ** * * * ** * * ** * ** * * * * * * * ** * ** * ** * * * * * * * *** * *** * * * * * * *** * * * * * * ** * * * * * * * * **** * *** * ** * * * * *** * * * DIRECTIONS Macros and Templates to Create Scientific and Technical Graphs Using Lotus 1-2-3, Microsoft Excel, Novell Quattro Pro, 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 September,1995 Version 2.10 Copyright Statement, Association of Shareware Professionals ii ____________________________________________________________________________ Copyright 1995 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 to GraphMaT 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 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 7. Fitting Data to Equations 43 Other Tutorials in the GraphMaT User's Guide 46 Table of Contents iv ____________________________________________________________________________ ------------------------------------------------------------------------- Applying The Twelve Templates Chapter 6 Abstract of Chapter from GraphMaT User's Guide 47 ------------------------------------------------------------------------- Advanced Spreadsheet Graphing Tips Chapter 7 Abstract of Chapter from GraphMaT User's 48 ------------------------------------------------------------------------- Technical Support Chapter 8 Support Policy on Macros and Templates 49 Registration 49 ------------------------------------------------------------------------- References and Index GraphMaT User's Guide only License Agreement v ____________________________________________________________________________ GraphMaT is NOT "Public Domain" and it is 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 no payment, commercial benefit, or other consideration is receive for such reproduction or distribution, and this license agreement and the copyright notice which appears in various forms in the software and documentation is not changed. ------------- 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 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: Roger W. Gross or Daniel J. Gross P.O. Box 681 Menomonee Falls, WI 53052-0681 (414)-253-2297 CompuServe ID 71053.2765 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 Novell QuattroPro 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 that I'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, non-linear, and multiple regressions, fits data to curves, plots functions, solves simultaneous equations, transforms, displays and overlays signals. A user-friendly menu driven interface and practical documentation guide the user through the process of importing the data, performing the data analysis, constructing the graphs, and interpreting the results. Although initially designed for scientific or technical situations, it can be utilized in business or non-scientific fields. 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, Microsoft Excel, Novell QuattroPro, 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 and macro commands, 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: * You will learn more about applying statistics and graphing data. * You may 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 if without a strong background in statistics and data analysis. * You will spend less time searching for information in a spreadsheet. * You will be able to locate which file has the information or graph you are looking for. 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 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 me 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. I 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 release 5 for Windows and releases 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) Windows versions 1.0 (Borland) and 6.02 (Novell). 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 ver- sions 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, and 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), Novell Quattro Pro version 6.0 for windows, 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 books on computers, but there are few sources of practical information which combine the two. The GraphMaT 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 bonus chapters, one on applying 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 User's Guide also lists references on graphing and statistics if you need additional background in these areas. 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 250k 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: GRAPHMAT.ZIP ZIP file containing the following files README .BAT 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 Disk 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 GRAPHMAT.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" later in this chapter.) Start the spreadsheet as normal. The working directory is the one first displayed when selecting GraphMaT 8 ____________________________________________________________________________ /FileRetrieve or /FileOpen. If the directory displayed is not where you 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 QuattroPro 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 QuattroPro 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 GRAPHMAT.ZIP, you must first extract the files using PKUNZIP. The files require about 300k of free space and it is suggested that you to unzip them to the floppy where you have the ZIP file or to a second floppy. Type C:\PKWARE\PKUNZIP -e A:\GRAPHMAT.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, 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 may vary depending on how your spreadsheet program is setup. 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 to reflect how your system is setup. The files 0.WK1, 0GRAPH.WK1, and GRAPH.MAT must be copied 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 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 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 are 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 file 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}. 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. Since this feature is not required for evaluation of GraphMaT, the directions for this option have been omitted from this file. They are provided in the registration materials and are also covered in the GraphMaT User's Guide. 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. The "Common Problems" section which follows is relatively short. You may avoid some frustration if you read it before trying GraphMaT. "General Directions for All Graphs" 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. You might want to read the "General Directions" and then the directions for the individual macro before trying the tutorial. To get you used to the GraphMaT interface, 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. Don't overwrite 0GRAPH.WK1! The filename starts with "0" so it will be near the beginning of an alphabetical file list and you won't have to look too far to find it. Saving files named with higher numbers or letters keeps 0GRAPH.WK1 near the front of the list. 0GRAPH.WK1 +----------------------------------------------------------------------------- | 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 1 |##########################³ -> | 101| |##########################³ -> | .| |##########################³ -> | .| |##########################³ -> |5000| |##########################³ -> | .| |##########################³ -> | |\/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ GraphMaT 12 ____________________________________________________________________________ The diagram 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 the above diagram 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} instead of pressing {Enter} to 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 will position the cursor for you. The only exceptions are when highlighting cells to select data for a graph, when editing a entry, or when 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. Sometimes 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. See chapters 6 & 7 for more information. * The macro uses several 1 letter range names (ie. R,X,Y,Z). If you do name ranges in this worksheet, use 2 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: \A, \G, \I, \J, \K, \M or \R. * 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 the Installation chapter. * 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 to set recalculation to manual. (/ Worksheet Global Recalculation Manual). This is especially important with an older, slower PC. Make sure the macro is not still calculating before entering your data or giving up and stopping the macro. Some macros perform quite a few calculations after the data is entered. Don't panic if the graph doesn't appear instantly. The MULT example in the tutorial, which is 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 first and then try the tutorials before experimenting with your own graphs. All the GraphMaT macros follow a similar pattern described here. A brief description of each macro and any special directions are given in the next section. Please follow these directions carefully. If you don't, the macro will 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 a either 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 the each choice as it is highlighted. {Esc} will not display the previous menu as in normal Lotus, but halts the macro. 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+?} where ever it occurs in these directions. 4. The first time, select New to display the graph type menu. GraphMaT 16 ____________________________________________________________________________ 5. Some macros display a menu of graph settings. If the current settings are not acceptable, select the item to be changed. The color option is provided for programs (Lotus 2.x and Excel 5) where graphs are not in color unless specified. When settings are correct, press {Enter} to accept or continue. 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.) 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 spreedsheets 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 or Accept will erase existing data. A. Skip: bypasses all data entry operations and proceeds to the next step. Some macros provide the 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}. 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 new data from the keyboard. Provide the number of points when prompted. The cursor moves to the location for the first data 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 all the information and data are entered, the macro will construct 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. In 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 and {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 #3 on page 31.) 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 GraphMaT 18 ____________________________________________________________________________ in the new location. Moving the graph "fixes" a 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 31. 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 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. 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, and 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. 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 location. Chapter 4: Directions 19 ____________________________________________________________________________ After the macro is finished, 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 normal 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 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 a scatter diagram 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. Change to 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. In addition to being used for the axis and title, these are also used to name the graphs. Make sure the first 5 characters used to identify the independent variables are unique and descriptive. 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 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 set the order of the polynomial, the resolution of the curve, and 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, the 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, and a 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 AD10. Tutorial #7 includes 2 examples using the NORD macro. Chapter 4: Directions 21 ____________________________________________________________________________ * CURVEFIT: The CFIT macro is a general curve-fitting screen which determines the best function to represent the data of the 7 functions tested. It also uses a combination of 6 of the functions to provide an estimate. To use this macro, there must be a minimum of 8 data points. The following functions are used. NAME FUNCTION COMBINATION -------- --------------------- ----------- Linear Y=b+mX Yes Square Y=b+mX^2 Yes Cubic Y=b+mX^3 Yes Reciprocal Y=b+m/X Yes Log Y=b+mùlog(x) Yes Exp Y=b+me^X =b+mùexp(X) Yes Power Y=bX^m No Since the macro transforms the x-values, f(x) must be defined for all values of x. Do not use data points where x<=0. See Chapter 6 for more details. The CFIT macro constructs a bar graph named "CFIT" and eight scatter graphs with the names in the table above. The bar graph 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. The scatter graphs plot the actual data and overlays a curve of the values predicted by the regression analysis for each of the seven functions. Viewing each of the 8 scatter graphs shows how well each function fit the data. The "r" correlation coefficient is displayed as a graph legend and is also displayed on the spreadsheet along with the values for m and b for each function. The macro starts with a menu to set the resolution, toggle display of data labels and turn on color. After making any changes, Accept the settings and provide the titles, labels and data. When the macro is finished, 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 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 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 thought 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. After the macro constructs a graph it displays a menu to restart the macro, change the axis, display the regression data inset, toggle display of data labels, or end the macro. If the macro is restarted, it will skip to this menu. The regression equation is provided on the worksheet. The value predicted for g(y) is displayed in AJ16 based on a value of f(x) entered in AI16. Tutorial #7 demonstrates the XREG macro. Additional applications for the XREG macro are provided in Chapter 6. ----------------- 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. 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 entering the number of points plotted between the minimum and maximum, or the interval between points. When resolution is satisfactory, Accept will continue. The cell AA11 is a range named "X" allowing a function referencing this cell to be entered in AB11 in terms of X in Lotus 1-2-3 notation. If the equation starts with X, make sure you type +X so Lotus knows that X is a value not a label. For example, entering +X*@sin(X) will Chapter 4: Directions 23 ____________________________________________________________________________ plot the function y=x sin(x). After the function is plotted, a menu is displayed which allows displaying the graph, changing the function, changing the graph style, turning on display of data points, selecting from more options, or ending the macro. When turning on data points, the cursor is moved to AC10. After typing a legend, move the cursor down the AC column and type the y values opposite the x value in column AA. Pressing {Enter} will return to the menu. 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 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 AB11 and f(x) is entered in AC11. Tutorial #2 demonstrates the GX macro. * SIMULTANEOUS: The SIMUL macro plots 2 functions, f(x) and g(x), over the same x-range. Instead of plotting data points, it plots points where the functions intersect and optionally labels the intercepts with the f(x) 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 AC11 and the menu after the functions are plotted has different options. Selecting Solve from that menu brings up a 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 algorithm to use. Tutorial #1 demonstrates the SIMUL macro. Chapter 6 provides more information on the algorithms used to "solve" the simultaneous equations. * COMBINATIONS: The EQN macro plots up to 6 functions. One of these can be designated as a linear combination of the functions. Another of the functions may used to plot data points with labels. After completing the titles, range, and resolution, the number of functions to be plotted is entered. Note: to plot 2 functions, the combination and GraphMaT 24 ____________________________________________________________________________ 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. Tutorial #9 demonstrates 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, titles and y-axis title are entered, the macro inputs the number of data sets to plot. After entering the x-axis title the menu allows changing the x-axis interval or accepting the default of 1. The macro generates an x-axis starting with 0 and adding the interval for each subsequent data point. 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, performs several operations for which 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 scaling capability is available after macro ends but is not available after the graph is moved. Tutorial #6 demonstrates the PLOT macro. Chapter 6 has more information on the scaling and offsetting commands. * TRANSFORM: The TRAN macro performs various transforms on one or two sets of data points, sometimes 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 entry in the signal. Once completed, the transform can be displayed on a graph, saved as an 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 transforms programmed into the TRAN macro. Chapter 4: Directions 25 ____________________________________________________________________________ Transforms on single data sets: - DERIVATIVE Determines slope at each point in signal (dy/dx) - INTEGRATE Measures cumulative area under curve - SCALE Multiplies signal by a constant factor - OFFSET Add a constant signal - X_SCALE Compresses or expands a signal along the x-axis (interpolates to add data points or averages points to decrease number of points in signal) - LOG Takes base 10 log of a signal - ANTILOG Raises signal to 10^X - NAPERIAN Takes natural log (base e) of signal - EXP Raises signal to e^x - EXTRACT Extracts part of a signal - REVERSE Reverses a signal (first is last & last is first) - SMOOTH Takes a moving average of signal - FILTER Moving average for points below threshold. - DYNAMIC Moving average (number of points averaged decreases as slope increases) - BUNCH Stores data bunches (makes signal with fewer points) - USER Allows you to enter a formula in terms of A. Transforms based on input of two data sets: - SUM Adds two signals - DIFFERENCE Subtracts the second signal from the first - AVERAGE Averages the two signals - PRODUCT Multiplies the two signals - RATIO Divides the first signal by the second - CONCATENATE Appends signal B to signal A - USER Allows you to enter a formula in terms of A and B 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 Generates a signal from a formula you enter 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. Tutorial #5 demonstrates the TRAN macro. Chapter 6 has information on using transforms. GraphMaT 26 ____________________________________________________________________________ ------------------- The Index Worksheet ------------------- 0.WK1 +---------------------------------------------------------------------------- | 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.MAC Worksheet (R. Gross 6/12/95) none |10|TUTORIAL WK1 Cu assay calibrations Method A,.... |11| | .| | .| | .| 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 QuattroPro 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. --------------------------------- 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 graph 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, must be no more than 14 characters. (NOTE: QuattroPro hangs up here. Press {Ctrl+Break} to continue.) 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. NOTE: a plus sign before X tells Lotus 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 and accept the default algorithm. The macro finds the points closest to where the two curves intersect, places information in a table and returns to the menu. L -The macro labels the intersecting points on the graph with the (x,y) coordinates of the closest point graphed. D{Any} -Display the graph and return to the macro 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. See Chapter 6 for more details. * Chapter 7 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) 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 = Temperature in K Rather than plotting the number of moles against K, it can be plotted against any parameter which is a function of temperature. While there are many things which could be used, one that makes this graph easier to understand is to use the conversion from K to centigrade (C) as the other function. C = K-273.15 or g(x) = X-273.15 This 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 & subtitle. 100{Enter}300{Enter} -Designate that the curve be plotted between 100 & 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. GraphMaT 30 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- +X-273.15{Enter} -Enter the equation 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 we think of temperature in K as the X variable. D{any} -Display the graph. P -Turn on data points to allow you to display some data points on the graph. Experimental data -This defines a graph legend for data points. Note: do not press {Enter} until all data points have been completed. {down} -Move down one cell. The cursor is across from a value of 100 in the AA column. .12{down} -This places the value of .12 moles for 100K. .09{down}.07{Enter} -Similarly, place .09 for 150K & .07 for 200K. L -Select label to place coordinates for the data points you entered. D{any} -This will display the finished graph. EF -End the macro and convert formulas to values - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 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 and use the same macro. {Enter} -Accept graphname. The graph is displayed. {any}Y{Enter} -Press a key and enter Y to overwrite it. R{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. +X*@sin(X){Enter} -Replace the previous functions. Using these +X*@cos(X){Enter} two functions defines a spiral. P -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~D... -For an interesting display, also assign cells AC11..AC260 to the D, E and F series. OFCLDSELFSQQQ -Alternate format between lines and symbols. {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 of each variable; the concentration of the standards (CONC), the responses from the atomic absorption detector (AA), and the responses for 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 Detection{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. {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 has a macro which performs a Lotus file import. With the cursor at A103, press {Ctrl+I}, 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. {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 I140. 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 Detection{Enter} -Provide graph subtitle. AA Detection (ABS){Enter} -Provide x-axis title. EC Detection (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. {Alt+G} -Start macro and displays a menu. M -Select Move graph to a new location. A181{Enter} -Move the second 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 analyze for 16, the GraphMaT template is set up for a maximum of 8 independent variables. 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 the default settings: "Ind. variables: 8 Labels:y Color: n" NBA STATISTICS{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. GraphMaT 38 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- AAAAAAAA -The range "DATA" has all 8 variables so select "Accept" for each remaining variable to indicate the data is already in. -Wait while 10 graphs are constructed. {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. Use the cursor to highlight another graph. The graph named "NBA S_BAR" displays the absolute values of the correlation coefficients for the linear regressions of each independent variable with the dependent variable. Of the 8 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 (NBA STATS) which uses all independent variables to predict the dependent variable. For example, "NBA S_XY:OP-FG" displays the correlation between a team's wins and their opponent's field goal %. 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. R2{Enter}N -Change the operating mode to the following: "Transform: NONE Ranges=2 Points=50" 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 A data set. ???{Enter} -Enter a legend for B data set. I{Cursor}{Enter} -Import DATA_C.PRN as B data set. 0{Enter}.02{Enter}9999{Enter} Enter values for the x-axis. D{any} -For older versions, Change Settings Color to display the graph in color. OSATO-13550{Enter} -Change Operation to transform a Single set and 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} -You can display the graph after each transform. The tutorial will no longer instruct you to do so, but displaying the graph after gives a better feel for what is happening. ODATS -Select Operation Double so both A & B data sets are used as the basis of the transform. A different transform menu is displayed. Select Sum to add the two signals. MR -Again replace set A with the transform. OSA -Change to transform a single data set. TMME2{Enter}4{Enter} -Extract the part of the signal of interest, the data between x=2 and x=4. GraphMaT 40 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- MRMDD{any} -Replace set A with the transform and Duplicate it. When viewed by itself, the aberration caused by the small peak is no longer obvious. TDD{any} -Derivative is big compared to the signal. X2{Enter}0.1{Enter}99 -Change the x-axis interval to scale the {Enter}TDD derivative. You can see an indication of the buried peak in the derivative plot. {any}MRTDD{any} -Replace A set with the derivative and take a X2{Enter}0.02{Enter}99 derivative of the derivative. Then change {Enter} the x-axis interval back to normal. D{any} E The 2nd derivative shows an extra peak at about x=3.3 indicating the peak was impure. GraphMaT has many transforms that were not used in this tutorial. Repeat the macro and try the other transforms. The User transform allows you to enter your own formulas in terms of X, A, and B for the x-axis, first and second signal respectively. An interesting application is smoothing signals. Restart the macro. Set operation mode to Transform sets=1 Plot sets=1. Load DATA_B.PRN. Change the y-axis settings to manual with lower limit 13500 and upper limit 13700 so you can observe the effect of the transform on the baseline. Transform using Smooth (moving average) with 3 points. The baseline is still rough. Try with 5 or 10 points. This does smooth the baseline. Change y-axis setting to automatic. Note how moving average affects the peak. Now transform using Filter with 10 points and a threshold of 50. This transform smooths the baseline but when the slope increases it uses the unprocessed data so the top of the peak is unchanged. The Dynamic transform is similar to the Filter but is more automatic and gradually decreases as slope increases. 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. This examples shows how the 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} -Specify a graphname and press enter. ??{Enter}??{Enter}???{Enter} -Provide title, subtitle, and y-axis title. 2{Enter} -Indicate there are 2 data sets to plot. ???{Enter} -Provide title for x-axis. C0.02{Enter} -Change the x-axis interval to 0.02. ???{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. AM0.01{Enter} -Adjust manually the scale for the A series by entering 0.01 in cell AB7. AD{any} -Autoscale changes the scale on all signals to match signal A. Tallest peak of both signals are same. Press any key to continue. Z -Zeroing the graph brings the minimum point of each graph to touch the x-axis. You can display the after each change, and press a key to return to the menu. P -Return to the previous menu. R{Enter}...6{Enter}... -Restart macro. This time enter 6 for number of data sets and accept all other entries. {Enter}A{Enter}A... -Press enter or accept until you get to C. ???{Enter}I{cursor}{Enter}...-Import same two files for C, D, E & F series. -Manually adjust the scale of the 4 new signals AM{Cursor}.01 {right} by moving the cursor to cell AD7 and typing .005{right}.01{right} values into cells AD7 to AG7. Use the .01{Enter} 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. ZS50{Enter} -Adjust the new sets to zero and shift all 6 signals up 50 from the y-axis. Y100{Enter} -Separate each signal by 100. (ie., add 100 to every point of the B signal, 200 to every point of the C signal, etc.) GraphMaT 42 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- X.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. 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 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. A -Accept graph settings which should be : "Resolution=50 X-scale:auto color:n" If you want graphs in color and your program creates graphs in B&W, press C. ???{Enter}???{Enter} -Provide title and subtitle. ???{Enter} Provide 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. (Note: in some versions the macro ends and you must use normal commands to display 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 between the log of the concentration, log(x), and time, y. 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} -Provide graphname, title, & subtitle, Time (Hrs){Enter} -Enter title for x-axis. Log Concentration{Enter} -Use "Log" for y-axis based on CFIT analysis. S -Skip entering 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. SS -Suppress display of regression and labels. 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. Chapter 5: Tutorials 45 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- LDCD{any} -Add labels to the graph. Select Coordinates. Display the graph. Note the (x,y) labels are the original values, not the log values which are plotted. Those would have been displayed if f(x)g(y) had been selected. E{cursor} -End the macro. The graph that was created is similar to what can be done using normal spreadsheet operations with a log scale for the y-axis. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NOTE: * There are several other curvefitting tools built into GraphMaT. These are discussed in the GraphMaT User's Guide. These include the use of residuals and plotting the functions derived from the regression analysis. Several other sample data sets are provided with the registered version which provide additional practice in fitting data to functions. 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, so 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. GraphMaT 46 ____________________________________________________________________________ WHAT YOU TYPE EXPLANATION --------------------------- -------------------------------------------- {Alt+G} NRP -Restart and select the nth order polynomial regression macro, NORD. ???{Enter} -Enter a graphname. ???{Enter}... -Provide 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. 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. XGLCVP{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. ---------------------------- 8-10. User's Guide Tutorials ---------------------------- In addition to the expanded Tutorial on curve fitting, the GraphMaT User's Guide has additional tutorials on plotting linear combinations of equations and on combining graphs. Additional sample files are provided with the registered version which will provide further practice using GraphMaT. The tutorials provided with this version were selected to demonstrate the capabilities of GraphMaT. If the templates and macros are of use to you and you plan on using or adapting them, please register. Thank you. Chapter 6: Applications 47 ____________________________________________________________________________ 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. 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, Novell Quattro Pro or Trius 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 48 ____________________________________________________________________________ This chapter was written to help the casual user of spreadsheets get more out of these programs. One reason spreadsheets are not used more effectively is that they don't have everything built in. For example, even though address labels could be printed in a spreadsheet, it is easier to use a program designed to print address labels. Software designed for a specific application is 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. This is actually a good situation since there are many tasks for which specialized programs have not been written, and it assures you of getting exactly what you need. The manuals that come with the software and many books written about Lotus 1-2-3, Microsoft Excel, Novell Quattro Pro, or Trius AS-EASY-AS generally only give instructions on how to use the program's features; they do not explain how to apply them. What can actually be done with a spreadsheet is basically left up to the imagination and programming ability of the user. This chapter provides information on four key areas which were fundamental to creation of GraphMaT. There a plenty of books out there that rehash and explain all of the features, functions, and options available in today's spreadsheet programs, and it is not worth repeating those. However the information on these four areas are not commonly presented. They are combining formulas and @functions, macro statements that include formulas, spreadsheet graphing techniques, and hints on designing macros. These four sections will help you understand how the different features can be integrated to develop your own spreadsheet applications, modify GraphMaT, and create your own macros and templates. 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 49 ____________________________________________________________________________ -------------------------------------- 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 N84W18131 Menomonee Ave, Menomonee Falls, WI 53051 (414)-253-2297 CompuServe ID 71053.2765