SHEET -- FOR ATARI ST A BASIC interpreter in a spreadsheet environment Atari, 520 ST and ST are trademarks of Atari Corporation GEM is trademark of Digital Research, Inc. Lotus 123 is trademark of Lotus Development Corporation Copyright (C) 1988, Chor-ming Lung. All rights reserved. Table of Content ________________ Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Disclaimer . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 PART I -- TUTORIAL . . . . . . . . . . . 3 Example 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Example 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Example 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Example 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Example 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Example 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Example 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Example 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Example 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 PART II -- Spreadsheet operations . . . . . . . . 22 MENU SELECTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Desk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 About SHEET . . . . . . . . . . . . . . . . . . . . . . . 23 File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 New N . . . . . . . . . . . . . . . . . . . . . . . . 23 Load L . . . . . . . . . . . . . . . . . . . . . . . . 23 Load WKS . . . . . . . . . . . . . . . . . . . . . . . . 23 Load PART . . . . . . . . . . . . . . . . . . . . . . . . 23 Save S . . . . . . . . . . . . . . . . . . . . . . . . 23 Save WKS . . . . . . . . . . . . . . . . . . . . . . . . 23 Save PART . . . . . . . . . . . . . . . . . . . . . . . . 23 Print P . . . . . . . . . . . . . . . . . . . . . . . . 23 Quit Q . . . . . . . . . . . . . . . . . . . . . . . . 23 Sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Erase . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Defaults . . . . . . . . . . . . . . . . . . . . . . . . 23 Options . . . . . . . . . . . . . . . . . . . . . . . . . 24 Dates . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Row/Col . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Insert I . . . . . . . . . . . . . . . . . . . . . . . . 25 Delete D . . . . . . . . . . . . . . . . . . . . . . . . 25 Col widths . . . . . . . . . . . . . . . . . . . . . . . 25 Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Reformat R . . . . . . . . . . . . . . . . . . . . . . . 25 Erase E . . . . . . . . . . . . . . . . . . . . . . . 25 Copy C . . . . . . . . . . . . . . . . . . . . . . . 25 Move M . . . . . . . . . . . . . . . . . . . . . . . 25 Adjust A . . . . . . . . . . . . . . . . . . . . . . . 25 Graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 - i - MOVING AROUND . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 EDITING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Types of cell entry . . . . . . . . . . . . . . . . . . . . . 28 Formula . . . . . . . . . . . . . . . . . . . . . . . . . 28 Constant . . . . . . . . . . . . . . . . . . . . . . . . 28 Statement . . . . . . . . . . . . . . . . . . . . . . . . 28 Command . . . . . . . . . . . . . . . . . . . . . . . . . 28 Text . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Rules for constructing formula . . . . . . . . . . . . . . . . 29 Cell input . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Move the editing cursor . . . . . . . . . . . . . . . . . 30 Modify your input . . . . . . . . . . . . . . . . . . . . 30 Terminate your input . . . . . . . . . . . . . . . . . . 30 Miscellaneous operations . . . . . . . . . . . . . . . . 30 Modify a cell . . . . . . . . . . . . . . . . . . . . . . . . 31 Editing direction . . . . . . . . . . . . . . . . . . . . . . 31 CUSTOMIZED DISPLAY . . . . . . . . . . . . . . . . . . . . . . . . 32 Change column width . . . . . . . . . . . . . . . . . . . . . 32 Default display format . . . . . . . . . . . . . . . . . . . . 33 Display format: . . . . . . . . . . . . . . . . . . . . . . . 33 Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 De-select blocks . . . . . . . . . . . . . . . . . . . . . . . 34 Change display format . . . . . . . . . . . . . . . . . . . . 35 COPY AND MOVE . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Relative and Absolue cell reference . . . . . . . . . . . . . 37 ERASE CELLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 INSERT AND DELETE . . . . . . . . . . . . . . . . . . . . . . . . . 39 Row/Column selection . . . . . . . . . . . . . . . . . . . . . 39 LOAD, SAVE AND PRINT . . . . . . . . . . . . . . . . . . . . . . . 40 File extension . . . . . . . . . . . . . . . . . . . . . . . . 40 Load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Save . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Load PART . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Save Part . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Lotus files . . . . . . . . . . . . . . . . . . . . . . . . . 41 Print . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 PART III -- SHEET BASIC . . . . . . . . . . 43 Cells and Variables . . . . . . . . . . . . . . . . . . . . . . . . 44 Cell reference with dimension . . . . . . . . . . . . . . . . 44 Variables . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Cell reference with variables . . . . . . . . . . . . . . . . 44 String . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 String operators String operators . . . . . . . . . . . . . . . . . . . . . . . 45 Flow control Statements . . . . . . . . . . . . . . . . . . . . . . 47 IF .. THEN .. ELSE .. . . . . . . . . . . . . . . . . . . . . 47 GOTO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 GOSUB .. RETURN . . . . . . . . . . . . . . . . . . . . . . . 47 - ii - LOOP .. EXIT IF .. ENDLOOP . . . . . . . . . . . . . . . . . . 47 FOR .. NEXT . . . . . . . . . . . . . . . . . . . . . . . . . 48 RUN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 I/O Statements . . . . . . . . . . . . . . . . . . . . . . . . . . 49 REDIRECT TO . . . . . . . . . . . . . . . . . . . . . . . . . 49 PRINT statement . . . . . . . . . . . . . . . . . . . . . . . 49 Miscellaneous statements . . . . . . . . . . . . . . . . . . . . . 51 SAVE SCREEN . . . . . . . . . . . . . . . . . . . . . . . . . 51 RESTORE SCREEN . . . . . . . . . . . . . . . . . . . . . . . . 51 WAIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 DEFINE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 MESSAGE . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Text files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 GET_FILE . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 OPEN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 CLOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 INPUT #n . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 PRINT #n . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Worksheet commands . . . . . . . . . . . . . . . . . . . . . . . . 55 COPY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 MOVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 ERASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 REFORMAT . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 SORT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 SWAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Console commands . . . . . . . . . . . . . . . . . . . . . . . . . 57 CLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 CURSORON . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 CURSOROFF . . . . . . . . . . . . . . . . . . . . . . . . . . 57 VIDEOHIGH . . . . . . . . . . . . . . . . . . . . . . . . . . 57 VIDEONORM . . . . . . . . . . . . . . . . . . . . . . . . . . 57 GOTOXY . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 INKEY . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 UNGET . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 INPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Graphic commands . . . . . . . . . . . . . . . . . . . . . . . . . 59 Setup commands . . . . . . . . . . . . . . . . . . . . . . . . 60 WINDOW . . . . . . . . . . . . . . . . . . . . . . . . . 60 VIEWPORT . . . . . . . . . . . . . . . . . . . . . . . . 61 SCALE . . . . . . . . . . . . . . . . . . . . . . . . . . 61 CLEAR . . . . . . . . . . . . . . . . . . . . . . . . . . 61 EXITGRAPH . . . . . . . . . . . . . . . . . . . . . . . . 61 Line/Dot commands . . . . . . . . . . . . . . . . . . . . . . 62 ATTRIBUTE . . . . . . . . . . . . . . . . . . . . . . . . 62 LINE . . . . . . . . . . . . . . . . . . . . . . . . . . 62 LINE_TO . . . . . . . . . . . . . . . . . . . . . . . . . 62 PLOT . . . . . . . . . . . . . . . . . . . . . . . . . . 63 PLOT DATA . . . . . . . . . . . . . . . . . . . . . . . . 63 SPLINE . . . . . . . . . . . . . . . . . . . . . . . . . 63 Turtlegraphics . . . . . . . . . . . . . . . . . . . . . . . . 64 - iii - FORWARD . . . . . . . . . . . . . . . . . . . . . . . . . 64 TURN . . . . . . . . . . . . . . . . . . . . . . . . . . 64 TURN_TO . . . . . . . . . . . . . . . . . . . . . . . . . 64 Axis command . . . . . . . . . . . . . . . . . . . . . . . . . 65 MARKX . . . . . . . . . . . . . . . . . . . . . . . . . . 65 MARKY . . . . . . . . . . . . . . . . . . . . . . . . . . 65 LABELX . . . . . . . . . . . . . . . . . . . . . . . . . 65 LABELY . . . . . . . . . . . . . . . . . . . . . . . . . 65 Text command . . . . . . . . . . . . . . . . . . . . . . . . . 66 PTEXT . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Miscellaneous . . . . . . . . . . . . . . . . . . . . . . . . 67 MOUSE_POS . . . . . . . . . . . . . . . . . . . . . . . . 67 Tips on using SHEET BASIC . . . . . . . . . . . . . . . . . . . . . 68 Interrupt . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Using previous row format . . . . . . . . . . . . . . . . . . 68 Error Error . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Multiple statements . . . . . . . . . . . . . . . . . . . . . 68 Garbled screen . . . . . . . . . . . . . . . . . . . . . . . . 68 Adjust . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Appendix A BUILT-IN FUNCTIONS . . . . . . . . . . . . . . . . . . . 69 Math . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 General functions . . . . . . . . . . . . . . . . . . . . 69 ABS . . . . . . . . . . . . . . . . . . . . . . . . 69 INT . . . . . . . . . . . . . . . . . . . . . . . . 69 FRAC . . . . . . . . . . . . . . . . . . . . . . . . 69 FACT . . . . . . . . . . . . . . . . . . . . . . . . 69 SQR . . . . . . . . . . . . . . . . . . . . . . . . 69 SQRT . . . . . . . . . . . . . . . . . . . . . . . . 69 Trigo & logarithm functions . . . . . . . . . . . . . . . 69 SIN . . . . . . . . . . . . . . . . . . . . . . . . 69 COS . . . . . . . . . . . . . . . . . . . . . . . . 69 TAN . . . . . . . . . . . . . . . . . . . . . . . . 69 ASIN . . . . . . . . . . . . . . . . . . . . . . . . 69 ACOS . . . . . . . . . . . . . . . . . . . . . . . . 69 ATAN . . . . . . . . . . . . . . . . . . . . . . . . 69 LOG . . . . . . . . . . . . . . . . . . . . . . . . 69 LN . . . . . . . . . . . . . . . . . . . . . . . . . 69 EXP . . . . . . . . . . . . . . . . . . . . . . . . 69 RAD_DEG . . . . . . . . . . . . . . . . . . . . . . 69 DEG_RAD . . . . . . . . . . . . . . . . . . . . . . 69 DATE functions . . . . . . . . . . . . . . . . . . . . . 69 DATE . . . . . . . . . . . . . . . . . . . . . . . . 69 DAY . . . . . . . . . . . . . . . . . . . . . . . . 69 MONTH . . . . . . . . . . . . . . . . . . . . . . . 69 YEAR . . . . . . . . . . . . . . . . . . . . . . . . 69 Matrix functions . . . . . . . . . . . . . . . . . . . . 69 INVERSE . . . . . . . . . . . . . . . . . . . . . . 69 TRANSPOSE . . . . . . . . . . . . . . . . . . . . . 69 Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . 70 AVERAGE . . . . . . . . . . . . . . . . . . . . . . . . . 70 MEDIAN . . . . . . . . . . . . . . . . . . . . . . . . . 70 STD . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 VAR . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 SQR_SUM . . . . . . . . . . . . . . . . . . . . . . . . . 70 - iv - Miscellaneous . . . . . . . . . . . . . . . . . . . . . . . . 70 MAX . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 MIN . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 SUM . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 RAND . . . . . . . . . . . . . . . . . . . . . . . . . . 70 IFF . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 ISEMPTY . . . . . . . . . . . . . . . . . . . . . . . . . 70 TRUE . . . . . . . . . . . . . . . . . . . . . . . . . . 70 FALSE . . . . . . . . . . . . . . . . . . . . . . . . . . 70 ALERT . . . . . . . . . . . . . . . . . . . . . . . . . . 70 COL_WIDTH . . . . . . . . . . . . . . . . . . . . . . . . 71 LAST_ROW . . . . . . . . . . . . . . . . . . . . . . . . 71 LAST_COL . . . . . . . . . . . . . . . . . . . . . . . . 71 SEARCH . . . . . . . . . . . . . . . . . . . . . . . . . 71 Finance . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Future growth . . . . . . . . . . . . . . . . . . . . . . 71 GRATE . . . . . . . . . . . . . . . . . . . . . . . 71 GBASE . . . . . . . . . . . . . . . . . . . . . . . 71 GFUTURE . . . . . . . . . . . . . . . . . . . . . . 71 Investment . . . . . . . . . . . . . . . . . . . . . . . 71 INVEST_PV . . . . . . . . . . . . . . . . . . . . . 71 INVEST_FV . . . . . . . . . . . . . . . . . . . . . 72 INVEST_INTEREST . . . . . . . . . . . . . . . . . . 72 INVEST_TERM . . . . . . . . . . . . . . . . . . . . 72 Loan . . . . . . . . . . . . . . . . . . . . . . . . . . 72 LOAN_PAY . . . . . . . . . . . . . . . . . . . . . . 72 LOAN_AMOUNT . . . . . . . . . . . . . . . . . . . . 72 LOAN_TERM . . . . . . . . . . . . . . . . . . . . . 72 Withdraw . . . . . . . . . . . . . . . . . . . . . . . . 72 WDRAW_SAVE . . . . . . . . . . . . . . . . . . . . . 72 WDRAW_AMOUNT . . . . . . . . . . . . . . . . . . . . 72 WDRAW_TERM . . . . . . . . . . . . . . . . . . . . . 72 Deposit . . . . . . . . . . . . . . . . . . . . . . . . . 72 DEPOSIT_FV . . . . . . . . . . . . . . . . . . . . . 72 DEPOSIT_AMOUNT . . . . . . . . . . . . . . . . . . . 72 DEPOSIT_TERM . . . . . . . . . . . . . . . . . . . . 72 String functions: . . . . . . . . . . . . . . . . . . . . . . 72 FILENAME$ . . . . . . . . . . . . . . . . . . . . . . . . 72 BUF$ . . . . . . . . . . . . . . . . . . . . . . . . . . 72 MID$ . . . . . . . . . . . . . . . . . . . . . . . . . . 72 LEFT$ . . . . . . . . . . . . . . . . . . . . . . . . . . 73 RIGHT$ . . . . . . . . . . . . . . . . . . . . . . . . . 73 SPACE$ . . . . . . . . . . . . . . . . . . . . . . . . . 73 CHR$ . . . . . . . . . . . . . . . . . . . . . . . . . . 73 STR$ . . . . . . . . . . . . . . . . . . . . . . . . . . 73 VAL . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 LEN: . . . . . . . . . . . . . . . . . . . . . . . . . . 73 POS . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Appendix B CONTROL KEYS . . . . . . . . . . . . . . . . . . . . . . 74 Non-editing . . . . . . . . . . . . . . . . . . . . . . . . . 74 Editing . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Appendix C Program specification . . . . . . . . . . . . . . . . . 75 Appendix D Lotus and SHEET . . . . . . . . . . . . . . . . . . . . 76 - v - Appendix E Recalculation . . . . . . . . . . . . . . . . . . . . . 77 Appendix F FILE FORMAT . . . . . . . . . . . . . . . . . . . . . . 78 Header for SHT . . . . . . . . . . . . . . . . . . . . . . . . 78 Cell contents for SHT format . . . . . . . . . . . . . . . . . 78 Header for PRT . . . . . . . . . . . . . . . . . . . . . . . . 78 Cell contents for PART format . . . . . . . . . . . . . . . . 78 Appendix G Problems . . . . . . . . . . . . . . . . . . . . . . . . 78 Biorthym program . . . . . . . . . . . . . . . . . . . . . . . . . 80 Calendar program . . . . . . . . . . . . . . . . . . . . . . . . . 82 Roots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 - vi - SHEET User manual By Chor-ming Lung October 9, 1988 Preface: ________ I have used spreadsheet programs for quite a while. I feel they are fantastic tools. I am quite familiar with Lotus 123. One thing that I feel terrible is its MACRO language. It is not as user-friendly as its display. I feel that BASIC is more powerful and yet easier to use than MACRO. Thinking that may be profitable to write a BASIC interpreter in a spreadsheet environment, I started to write the program on Oct. 1987. After a few months' struggle, I have created the program and named it SHEET. Making SHEET as shareware, I uploaded the first version on February 1988 to GEnie and CompuServe. Right now, SHEET can do not only numerical calculation, but also, graphic, string operations and text files access. Its growth depends on you. If you like it, please send me $25 or whatever you think it is worth. My address is: Mr. Chor-ming Lung 330 Tremont St A-708 Boston, MA 02116 U.S.A. You can also send your comments or suggestions by Email. My addresses are: GEnie: LUNG CompuServe: 72740,40 If you have access of GEnie, you can also leave your message on the bulletin board. It is Page 475, Category 2, Topic 28. Disclaimer: __________ I have made every effort to insure the accuracy of the program. However, there is no warranty either express or implied for its uses. Requirements: ____________ * Atari 520 ST or 1040 ST with disk drive(s). * Monochrome or color monitor. * SHEETxxx.PRG, CALC.RSC and SHEET.CFG on the same directory. (xxx is the version number of SHEET. Currently, it is SHEET20.PRG or version 2.0) * You must know how to operate with GEM. * Some BASIC programming experience is desirable if you want to use SHEET BASIC effectively. Page 1 SHEET User manual By Chor-ming Lung October 9, 1988 Introduction: ____________ Thank you for using SHEET. SHEET is a combination of spreadsheet and BASIC. The spreadsheet functions and commands make this BASIC interpreter a lot easier to handle data and the BASIC interpreter make the spreadsheet a lot powerful than many other spreadsheet programs commercially available. The arrangement of this manual consists of three parts. The first part consists of 9 example worksheets as a tutorial. The second part shows the details of how to use the spreadsheet commands. The last part consists of BASIC commands and their usage. If you have problems in locating cell cursor with the mouse, you should read Appendix G. You should read example 1 thoroughly. It contains informations for operating the spreadsheet. Example 3 is a trivial program but it is an important one too. If you do not understand the cell reference method in this program. You cannot understand the rest examples. I have tried my best to describe how it works. If you still have problem, you should read PART III about cell reference. There are three "big" program listings after the Appendixes. They use many commands of SHEET BASIC. You should not miss them too. Finally, I would like to thank all the users who provide suggestions to make SHEET more useful and reliable. I like to thank Mr. Donald A. Edwards and Dr. Stan Liebowitz specifically. Without their enthusiasm, I can hardly imagine some of the features found in version 2.0. If you have made suggestions but they are not implemented yet, it is not because they are being ignored. Instead, I do not have enough time to get everything done on this version. Page 2 SHEET User manual By Chor-ming Lung October 9, 1988 PART I -- TUTORIAL Example 1: "AMORT.SHT" This example shows you how to operate with SHEET. A lot of the basic ideas of how to use the mouse and your keyboard with your worksheet are being introduced. If you understand it, you can read PART II without any difficulty. Example 2: "ROSES.SHT" This example shows you some advanced feature of spreadsheet design. It introduces the idea of absolute and relative cell reference. Example 3: "RANDOM.SHT" This example is a simple SHEET BASIC program. The main idea introduces in this example is a new cell reference method. You can use the cell as a cell reference or as a BASE which you can stretch horizontally or vertically. Example 4: "LOTTERY.SHT" This example shows you how to print your output to your printer. It generates 6 different numbers from 1 to 36, and sorts those numbers in ascending order, and then prints those numbers to your printer. Example 5: "FTEST.SHT" This file shows you the commands for ASCII file accesses. Example 6: "NUMBER.SHT" This file shows you how to use the file access commands to retrieve data stored in ASCII file and then put them into individual cells. Example 7: "GRID.SHT" This first graphic program shows the basic process of using the graphic commands. Example 8: "SINCURVE.SHT" This file shows you how to draw a sine curve with the graphic commands. Example 9: "SPLINE.SHT" This file shows you how to draw graph using data stored in your worksheet. Page 3 SHEET User manual By Chor-ming Lung October 9, 1988 Example 1: _________ AMORT.SHT Amortization means killing something bit by bit. That is the way we handle loans -- we pay little by little. Suppose you are trying to borrow $100,000. Your financial company provides two options for this loan. - 30 years with annual interest rate 9% - 20 years with annual interest rate 10.5% Intuitively, you may choose option 1. Well, let us analyze this problem with SHEET and see whether it is a wise choice. First, we have to design our layout. We plan to use column A for labels, column B for option 1 and column C for option 2. Following are the steps for creating the template: Cell entry Cell cursor Menu option/Description --------------------------------------------------------- Find menu title "Sheet" and select "Defaults". Choose Text justification for Center (^) and select OK. B1 To move your cell cursor to B1, either move the mouse cursor to B1 and click the mouse button, or use the arrow keys to move the cell cursor. If you use the mouse to move your cell cursor and if the cell cursor disappears, then you should refer to Appendix F. Option 1 Type "Option 1" and terminate it with key. C1 Press the -> arrow key to move from cell B1 to C1. Option 2 B2 \- Type "\-" and terminate it with key. C2 \- Find menu title "Sheet" and choose "Defaults". Choose Text justification for Right (") A3 Amount borrowed A4 Annual interest rate A5 Total number of terms A6 Terms per year A7 Page 4 SHEET User manual By Chor-ming Lung October 9, 1988 \- B7 \- C7 \- A8 Payment "\-" means to fill up the cell with "-". You should notice that entries on column A occupy some areas of column B and C. We need to enlarge the width of column A to make rooms for entries on column B and C. To widen column A, you need to place the mouse pointer within the gap between column letter A and B. Press the mouse button and drag it to the right hand side. (Alternately, you can select the menu item "Col widths", enter the column name and your desired width.) When you press and drag you mouse, you should see the rubber box expanding. If the rubber box is large enough to cover all the characters of column A, then you can release your mouse button. Every text in column A is right justified now. Well, a template without formulas is not a template at all. The formula we need for this calculation is "LOAN_PAY". It needs four arguments -- amount borrowed, annual interest rate, total number of terms, terms per year. That is "LOAN_PAY(B3,B4,B5,B6)". If you enter the above line into cell B8, SHEET will treat it as a TEXT string instead of formula. That is because the leading character of a cell entry determines its type. A cell starts with "+", "-", digit, "(" or "." is a formula. A cell starts with alpha character is a TEXT string. A '+' symbol before "LOAN_PAY" will tell SHEET to treat it as a formula. (NOTE: If you are used to Lotus 123, you may tempt to add @ symbol before "LOAN_PAY". @ symbol in SHEET has different meaning. All the functions in SHEET does not require @ symbol.) Here is the steps for entering "+loan_pay(b3,b4,b5,b7)" on cell B8 and copy the content of B8 to C8. Entry Mouse Mouse button Editing buffer cursor display -------------------------------------------------------------- +loan_pay( +loan_pay( B3 Mouse click +loan_pay(B3 B4 + Mouse click +loan_pay(B3,B4 B5 + Mouse click +loan_pay(B3,B4,B5 B6 + Mouse click +loan_pay(B3,B4,B5,B6 ) +loan_pay(B3,B4,B5,B6) B8 Page 5 SHEET User manual By Chor-ming Lung October 9, 1988 + Mouse click C8 + Mouse click "+Mouse click" means press the Control key and left mouse button simultaneously. SHEET is case-insensitive. It will treat "loan_pay" as "LOAN_PAY". After those steps, you should see 0.1e+38 on both B8 and C8. Don't panic. You are not suppose to pay that much money even you borrow nothing. This number means calculation error. During calculation, Lotus uses 11 bytes (instead of 8 bytes) to store real number. Special bit pattern is set in these 11 bytes to represent ERR. Unfortunately, I do not have that much energy to create my own math package. So, I choose the largest number to represent error. It seems column B and column C are too close to each other. You should be able to change the column width of column B and C without any difficulty now. How wide should they be? It is up to you. It is time to enter our data to do analysis. Entry Cell cursor ----------------------------------- B3 100000 B4 0.09 B5 360 B6 12 After you have typed 100000, you terminate your input by the down arrow key. This action changes the editing direction to DOWN. Whenever you terminate your input with , the cell cursor will automatically move DOWN. B3 holds the $100,000 you want to borrow. B4 holds the annual interest rate 9%. B5 holds the total number of terms you must pay back to your financial company. It is supposed to be a monthly payment. You have to pay 12 times each year. Hence, the total number is 30*12 or 360. B6 holds the number of terms per year. It is 12. NOTE: if your payment is collected quarterly, then B6 should be 4 and B5 should be 30*4 or 120. It is an exercise for you to enter 100000, 0.105, 240 and 12 into cells C3, C4, C5, and C6 are respectively. The payment for option 1 is 804.622617 and it is 998.379887 for option 2. Probably Page 6 SHEET User manual By Chor-ming Lung October 9, 1988 your financial company will ask you to pay $804.62 and $998.38 respectively. To reflect this fact, you must reformat your display. We need to change B3..C3 and B8..C8 to DOLLAR format and B4..C4 to percent (%) format. Key Mouse button Mouse cursor position ---------------------------------------------------- B3 +Mouse click C3 + Mouse click B8 +Mouse click C8 + Mouse click Select DOLLAR The above operations define two blocks. +mouse click on a cell defines the start of a block and +Mouse click on a cell defines the end of a block. The first block is B3..C3 and the second block is B8..C8. activates the reformat dialog box. Selection DOLLAR will change cells contained in those blocks to DOLLAR format. I think it is an easy exercise for you to change B4..C4 to percent format. The payment just tells us we need to pay more for less terms. That is natural. The hidden truth is: we pay more money in option 1 than option 2. How can we find it? Well, we pay 804.62 each month, and we need to do it 360 times. So, the total amount we pay for option 1 is 360*804.62, and 240*998.38 for option 2. In cell A9, enter "Total payment". In B9, enter 360*b8 and in C9, enter 240*c8. Comparing the results on B9 and C9, you will find you pay $50,000 less in option 2. So, if you can affort $998.38 per month, you should choose option 2. Now, it is up to you whether you want to save your first worksheet. If you think you don't need it any more. Press the window close button and SHEET will give you a last chance to save it. If you want to save it for future reference, you should press and enter the file name in the file selector dialog box. You may even want to print it out to show your spouse that his/her decision is right or wrong. The following steps will print cells A1..C10. Key Mouse button Mouse cursor ---------------------------------------------------- A1 +Mouse click C10 Page 7 SHEET User manual By Chor-ming Lung October 9, 1988 + Mouse click Fill out the Print dialog box After this example, I assume you can operate this program without any difficulty. The tedious description of cell entries and mouse operations will not appear in the following examples. Page 8 SHEET User manual By Chor-ming Lung October 9, 1988 Example 2: _________ ROSES.SHT This is an inventory problem. Assume that you are selling roses. Each dozen costs 4 dollars and you are selling for 12 dollars per dozen. According to your pass experience, you know the probabilities you can sell 10 dozens, 11 dozens, 12 dozens in one day are 0.60, 0.23, 0.17. How many dozens of roses should you order to get the best profit? PROFIT= 12 * actual number of dozens sold - 4 * number of dozens ordered For example, if you order 11 dozens and you sell only 10 dozens of them, then you earn: PROFIT = 12*10-4*11=76 Here we need to create the array of profits for actual calculation. ordered/ 10 11 12 sold 10 80 76 72 11 80 88 84 12 80 88 96 You may ask what is the meaning of ordered 10 and sold 11? It means you sell all the 10 dozens of roses and still there are customers asking for roses. The number of roses sold includes actual number of dozens sold and number of dozens customers asking. If you have sold all the roses you have, you cannot give anything to the late customers. So, your profit is 12*10-4*10=80. If you are using calculator to find out those number, then you are not a good spreadsheet user. To calculate the profit table, you must use formula. Now, we assume that the numbers ordered are stored from C1..E1 and the numbers sold are stored from B3..B5. The function we need is IFF which means IF Function (I choose this name because IF is a reserved key word for BASIC). This function require 3 arguments. The first one is a conditional expression, the second one is an expression returned when the condition is true, the last one is an expression returned when the condition is false. Here is the logic: IF the number of dozens we order is greater than the number of dozens we sell, we use the following formula to calculate PROFIT: PROFIT = 12 * dozens ordered - 4 * dozens sold That is because we can sell no more than we ordered. On the other hand, if the number of dozens we order is not greater than the number of dozens we sell, then we can sell as many as we can. The PROFIT will be: PROFIT = 12 * dozens sold - 4 * dozens ordered Page 9 SHEET User manual By Chor-ming Lung October 9, 1988 So, we enter the following formula in C3: +IFF(C1 key. It should be placed before you restore your screen. SORT will not change the appearance of the updated cells. We need to refresh the screen. EXITGRAPH is a command to refresh the screen. By the way, you can update your screen manually by pressing . One thing you may notice in this program is I/O redirection. Here is a reason why you need to redirect your data to disk file. A user from Canada told me that his JUKI 6100 Daisywheel printer does not respond to the "end of line" character. So, he prints his worksheet to disk and then use 1st Word to print it. If I don't support I/O redirection in SHEET BASIC, he may need to buy another printer or stop using SHEET.. Currently, SHEET BASIC has command to redirect output only. Future version should provide input redirection. Page 14 SHEET User manual By Chor-ming Lung October 9, 1988 Example 5: _________ FTEST.SHT The file name of this example is "FTEST.SHT". A1..A5 is a program testing the output file commands. A7..A13 is a program testing the input file commands. To run it, enter "@run a1" and "@run a7" separately. A1 :! OPEN "o",#1,"test.txt" A2 :! PRINT #1,"123,456 789": PRINT #1 A3 :! PRINT #1,"5623.23,84.38 723.12" A4 :! CLOSE #1 A5 :! END A7 :! GET_FILE "\*.txt", FILENAME$ A8 :! OPEN "i",#1, FILENAME$ A9 :!R=1: SAVE SCREEN A10 :! LOOP A11 :! INPUT #1, BUF$ A12 :! PRINT BUF$ A13 :!R=R+1: ENDLOOP A14 :! END Explanation: Explanation A1: Before we can access a file, we need to open it first. Statement A1 open an output file in channel #1 and the file name is "test.txt". Channel number is in the range of 0~9. A2: Print the string "123,456 789" and a blank line to the file A3: Print another string to the file. You can output not only a string but also numeric data on the same line separated by comma or semicolon. A4: Close channel number 1. A7: Use the file selector to get the file name from user. Note: FILENAME$ is a string variable that is changed whenever you change the content of the file selector dialog box. A8: Open that file as input file on channel #1 A9: R is the counter for line being read. Save our current screen first. A10: Enter a loop to read the file line by line. A11: Read a line from the file to BUF$. BUF$ is a string variable which can be modified by INPUT statement only. A12: Print the line being read to the terminal. A13: Increase the counter of line number and repeat the loop. NOTE, there is no EXIT IF statement. There is no way to detect end of file in current SHEET BASIC. You must let it go forever until the interpreter finds end of file error and stops your program. You may have some extra "Syntax error" errors. It is normal. NOTE: After you are done, you can enter the command "@restore screen" to restore your screen. Also, program A7 can be used to read other text files. You can even read this document by choosing this file and you can stop and resume the print action by and . Page 15 SHEET User manual By Chor-ming Lung October 9, 1988 Example 6: _________ NUMBER.SHT The file name for the following program is "NUMBER.SHT". There are two programs in this spreadsheet. The first program generates data to file "test.txt". It is exactly the same program of Example 5. L7..L16 reads the data from the file "test.txt" and separate each of line into different cells. Subroutine P1..P11 is used to separate each data into different cells. L1 :! OPEN "o",#1,"test.txt" L2 :! PRINT #1,"123,456 789": PRINT #1 L3 :! PRINT #1,"5623.23,84.38 723.12" L4 :! CLOSE #1 L5 :! END L7 :! GET_FILE "*.txt", FILENAME$ L8 :! OPEN "i",#1, FILENAME$ L9 :!R=0 L10 :! LOOP L11 :! INPUT #1, BUF$ L12 :! IF ( ASC( BUF$)<>0) THEN GOSUB P1 L13 :!R=R+1 L14 :! ENDLOOP L15 :! CLOSE #1 L16 :! END P1 :!P=1:C=1 P2 :! LOOP P3 :!Q= POS(" ,", BUF$,P) P4 :! EXIT IF Q=0 P5 :!A1[C,R]= VAL( MID$( BUF$,P,Q-P)) P6 :!C=C+1 P7 :!P=Q+1 P8 :! ENDLOOP P9 :!A1[C,R]= VAL( MID$( BUF$,P,100)) P10 :! RETURN Explanation: Explanation L1~L5: It is the same code for Example 5 A1~A5. L7: Uses the file selector to get the file name L8: Open the file for INPUT. L9: R is a counter for row number. L10: We enter an infinite loop to read data lines. L11: Read a line from the data file. L12: Test whether the line being read is empty or not. If it is not, then we call subroutine P1 to separate the data into cells. L13: Increase our row number by one. L14: Repeat our loop L15 and L16 is never being executed. They are there for decoration only. P1: P is the starting search position and C is the column number. It is zero origin. C=1 means we start from column B. P2: Enter the loop to separate BUF$ into pieces. P3: We assume the number is separate by a blank space or comma. POS is a function that tries to find the position of a blank space or Page 16 SHEET User manual By Chor-ming Lung October 9, 1988 comma from BUF$ starting from P. If POS cannot find it, Q will be zero. P4: Check whether Q is zero. If it is, then we have checked the whole data line for space and comma and that is the end of our loop. P5: Store the value of BUF$ from P up to Q-1. It depends on the value of C and R. If C is 1 and R is 0, then the value will be stored in cell B1. (A1[1,0] is equivalent to B1). P6: Increase our column number by one. P7: Change our starting position for P to Q+1 P8: Repeat from P2. P9: We still have one value after the last space or comma. It is stored from P to the last character of BUF$. If the len of MID$ is larger than the length of BUF$, then it returns a string from P to the last character of BUF$. P10: End our subroutine. Page 17 SHEET User manual By Chor-ming Lung October 9, 1988 Example 7: _________ GRID.SHT This small program shows the setup and exit process for using graphics in SHEET BASIC. It just uses window 1 to draw the x and y- axis then marks them. To run it, enter the command "@run d1" D1 : ! WINDOW 1 D2 : ! SCALE -7,-2,7,2 D3 : ! CLEAR D4 : ! LINE -7,0,7,0: LINE 0,-10,0,10 D5 : ! MARKX 0,-6,6,0.1,0.5 D6 : ! MARKY 0,-2,2,0.2,1 D7 : ! EXITGRAPH D8 : ! END Explanation: Explanation: D1: The first command for graphics is WINDOW/VIEWPORT. WINDOW 1 means that we are using window 1 for output. D2: Setup the scaling factors. The minimum number for X is -7 and the maximum number is 7 and -2, 2 for Y. D3: Clear the window being used. D4: Draws the x-axis and y-axis D5: Making marks on x-axis from -6 to 6 with increment 0.1. A bigger marker on increment of 0.5. D6: Marks y-axis D7: Legal way to exit from graphics commands NOTE: ____ The first step on graphic command is WINDOW/VIEWPORT followed by SCALE. After that, you can enter any graphic commands. After the graph is done, you should use EXITGRAPH to reset the clipping area. Otherwise, your screen may seem to be locked up. Page 18 SHEET User manual By Chor-ming Lung October 9, 1988 Example 8: _________ SINCURVE.SHT This example draws a sine curve on the current window. To run it, enter the command "@run a1". A1 : ! WINDOW A2 : ! SCALE -7,-1.2,7,1.2 A3 : ! CLEAR A4 : ! PLOT -2*PI, SIN(-2*PI) A5 : ! FOR X=-2*PI TO 2*PI+0.1 STEP 0.1 A6 : ! LINE_TO X, SIN(X) A7 : ! NEXT X A8 : ! LINE -7,0,7,0: LINE 0,-1.2,0,1.2 A9 : ! MARKX 0,-7,7,0.5,1 A10 : ! MARKY 0,-1.2,1.2,0.1,0 A11 : ! LABELX 0,-7,7,1,"f0" A12 : ! LABELY 0,-1.2,1.2,0.2,"f2" A13 : ! WAIT 5: EXITGRAPH Explanation: Explanation: A1: WINDOW with no parameter means we are using current window for output. A2: SCALE for our sine curve. A3: Clear our window A4: Plot the first point of our sine curve A5: A FOR loop to generate a sine wave from -2*PI to 2*PI A6: Instead of plotting dots, we want to connect all the dots with lines. A8: Draws the x- and y-axis. A9: Marks x-axis A10: Marks y-axis A11: Draws the labels on x-axis. They are integers (f0). F means the number is displayed in fixed format. 0 means decimal place is 0. So, it has no decimal place or it is integer. A12: Draws the labels on y-axis. They are real number with 2 decimal points. A13: Waits for 5 seconds and then refreshes our window. Page 19 SHEET User manual By Chor-ming Lung October 9, 1988 Example 9: _________ SPLINE.SHT This time, we are going to use PLOT DATA and SPLINE to see how wonderful SPLINE will be. This program has a lot of subroutines. I will explain each. To run it, enter the command "@run c1". F1 : !R=0 F2 : ! FOR X=-2*PI TO 2*PI+1 STEP 2 F3 : !J1[0,R]=X:K1[0,R]= SIN(X) F4 : !R=R+1 F5 : ! NEXT X Subprogram F1 generates 7 data sets for sine curve. The data for x is stored in cells J1..J7 and data for y is stored in cells K1..K7. D1 : ! WINDOW 1 D2 : ! CLEAR : SCALE -7,-2,7,2 D3 : ! PLOT DATA J1..J7,K1..K7,0,1 D4 : ! GOSUB C13 D5 : ! RETURN Subroutine D1 plots the data stored in J1..J7,K1..K7 using no marker and solid line. The command PLOT DATA plots data stored in worksheet. J1..J7 and K1..K7 for x,y respectively. 0 means using dot instead of marker for each point and 1 means using solid line to connect points. D7 : ! WINDOW 1 D8 : ! SCALE -7,-2,7,2 D9 : ! ATTRIBUTE 1,1 D10 : ! SPLINE J1..J7,K1..K7,0.1 D11 : ! RETURN This subroutine draws the spline curve using color 1 (Black). The first argument for ATTRIBUTE sets the color to 1 (Black) and using line width 1. SPLINE uses cubic-spline method to draw smooth curve. J1..J7 and K1..K7 define the value for x,y. 0.1 defines the increment for generating data on the spline-curve. F7 : ! WINDOW 1 F8 : ! SCALE -7,-2,7,2 F9 : !X=-2*PI: PLOT X, SIN(X) F10 : ! ATTRIBUTE 3,1 F11 : ! FOR X=-2*PI TO 2*PI STEP 0.1 F12 : ! LINE_TO X, SIN(X) F13 : ! NEXT X F14 : ! RETURN Subroutine F7 draws the actual sine curve using color 3 (Green). C13 : ! LINE 0,-2,0,2: LINE -7,0,7,0: RETURN Subroutine C13 draws the x- and y-axis Page 20 SHEET User manual By Chor-ming Lung October 9, 1988 C1 : ! GOSUB D1 C2 : ! GOSUB D7 C3 : ! GOSUB F7 C4 : ! WAIT 5 C5 : ! EXITGRAPH C6 : ! END Our main program. First, draws the sine curve with PLOT DATA. Plot the sine curve with cubic spline routine, then draws the actual sine curve. The sine curve using SPLINE is closer to the actual sine curve. Actually, if you change cell F2 with "STEP 1" instead of "STEP 2" and change cells D3 and D10 for ranges J1..J14,K1..K14, then you can hardly tell the difference between the spline-curve and the actual sine-curve. NOTE: the actual sine-curve generates 126 points and spline-curve uses 14 data points! Isn't it wonderful? Page 21 SHEET User manual By Chor-ming Lung October 9, 1988 PART II -- Spreadsheet operations MENU SELECTIONS: Briefly describes all the menu selection and its usage. MOVING AROUND: This session explains how to move the working area to display different part of your worksheet. EDITING: This session discusses about cell entries for formula, constant, text, BASIC statement and command. It shows you how to use the line editor. CUSTOMIZED DISPLAY: This chapter tells you how to change the width of individual column and the display format of individual cell. Block is introduced in this _____ chapter too. Block is important to many worksheet commands. COPY AND MOVE: This chapter shows you how to reorganize your worksheet. You need to know how to select blocks. It also discusses about absolute and relative cell references. ERASE CELLS: This chapter explains to you how to erase the unnecessary cells. INSERT AND DELETE: This session discusses how to delete or insert a row or a column. Column and row selection is different from block. LOAD, SAVE, PRINT: It explains the menu items under menu title "File". Page 22 SHEET User manual By Chor-ming Lung October 9, 1988 MENU SELECTIONS The menu titles are the strings that appear on the menu bar. ____________ Menu items refer to the strings that drop down when a menu title __________ is selected. The menu titles for SHEET looks like this: Desk File Sheet Row/Col Cells Graph Help ______________________________________________ TITLE ITEM Description -------------------------------------------------------------------- Desk Desk About SHEET General information about SHEET. File File For a detail description of the following menu items, please refer to the chapter "LOAD, SAVE, PRINT" New N Open a new worksheet window. The N on the right hand side means that you can activate this menu item by pressing . You have a choice for changing your new worksheet size. The maximal number of rows can be 50 to 9999. Load L Load an old worksheet file from disk drive. You can use to activate this menu item. Load WKS Load files in the format of Lotus 123 release 1A or 2. SHEET is not a Lotus clone, so it will not translate everything. Whenever it encounters problem, the translation process will be terminated. Load PART Load SHEET files saved by using "Save PART". Its file extension is PRT. Save S Save the whole worksheet file into your diskette. Save WKS It is supposed to save your worksheet in Lotus 123 file format. It is not working right now. Save PART Save part of your worksheet into diskette. You need to select blocks of cells before you activate this menu item. Print P Print part of your worksheet to disk/printer. You need to select a block before you activate this menu item. Quit Q Quit to desktop. Sheet Sheet Erase Erase your current worksheet. Defaults Set some default states of SHEET. You can change the recalculation order which is saved with your worksheet, change the TEXT justication, change the current window name, takes 32K back for screen redraw and have a Page 23 SHEET User manual By Chor-ming Lung October 9, 1988 look of the memory remains. Options In the dialog box of Options, you have three choices. Users have suggested for a Global format for each column. "Cell format using previous row" means that whenever you enter a new element for the worksheet, it will try to find the format of previous row on the same column and uses it as default format. So, you do not need to reformat again and again. If you are moving the worksheet with arrow keys, you will find that moving up and down is a lot faster than moving left and right. "Move to left/right Fast" provides an option that you can move left and right faster than before. One drawback for this option is that the new column being displayed is truncated to its own width. Hence you may have problem in displaying BASIC program or text string which spilled over other cells. If you want to redraw the screen to display them properly, you can use the command "@exitgraph" or to redraw the screen. Two monochrome users have suggested me to give an option for using the Small font to display more rows per screen. I have make the option available but it is not working yet. Dates SHEET can display three different Date types. The Dates menu item under menu title Sheet provides you a new way to show your date data. The components for a DATE are day, month and year. Normally, day and year are displayed as digits. Month can be digit, the first three characters of the month or the whole alphabets for that month and some countries put month before day and some put day before month. To overcome these differences, the date format is configurable to your favour. dd and yy represent day and year in digit. mm represents month in digit. mmm represents month in three letters and mmmm represents month in full alphabets. Here are some examples. Assume that the date is March the first, 1988. Date format: Display: ------------------------------------------------- mmddyy 030188 dd-mmm-yy 01-Mar-88 mmmm dd,19yy March 01,1988 dd/mm/yy 01/03/88 mm/dd/yy 03/01/88 NOTE: The file SHEET.CFG holds the defnition of Date1, Date2 and Date3. It is updated whenever Page 24 SHEET User manual By Chor-ming Lung October 9, 1988 you change the Date format. If SHEET.CFG is not there when SHEET is loaded, then the default formats will be used. Row/Col Row/Col Insert I Insert a row or a column. You need to select that particular row/column first. Refer to the chapter "INSERT AND DELETE" for detail description. Delete D Delete a row or a column. You need to select that particular row/column first. Col widths You can change the column width in a more effecient way than just using mouse dragging method. For example, if you want to make the column width from column A to column O to 1,9,1,9,1,9,1,9,1,9,1,9,1,9,1, you can select the menu item "Col widths" from menu title "Row/Col", enter "A" for the leading column and 1,9,1,9,1,9,1,9,1,9,1 in the edit field "widths". Cells Cells NOTE: YOU HAVE TO SELECT BLOCKS BEFORE YOU ACTIVATE THE FOLLOWING MENU ITEMS. Reformat R Changes the format of blocks. For a detail description, please refer to "CUSTOMIZED DISPLAY" Erase E Erase cells on blocks specified. Please refer to "ERASE CELLS". Copy C Copy a block of cells to other block. Please refer to "COPY AND MOVE" Move M Move a block of cells to other place Adjust A Adjust is used for changing TEXT string to BASIC statements or changing TEXT justifications. The first step in using ADJUST is: Select the blocks being ajusted and then press or select this menu item. You MUST not change TEXT that are not supposed to be BASIC statements to BASIC statements. The program may not recognize all your characters and part of them may be truncated with a character #. Graph Graph This selections is not available yet. This selections is not available yet. Selections Layout Legend X-axis lable Data Variable A Data Variable B Data Variable C Data Variable D Data Variable E Data Variable F Page 25 SHEET User manual By Chor-ming Lung October 9, 1988 Help Help Entry Helps for entering formula, text strings, BASIC statements and commands. Formula Helps for formula, especially for calculation priority Built-in Func Helps for built-in functions. Statement Helps for BASIC statements (v1.6) Limits Helps for program limit (accuracy, maximum numbe of variables and so on) Graphic commands Helps for graphic commands for BASIC (v1.7) PTEXT Helps for the graphic command PTEXT (PLOT TEXT) (v1.7) Commands Helps on other new BASIC commands (v2.0) Console commands Helps on screen control and keyboard commands. (v2.0) Page 26 SHEET User manual By Chor-ming Lung October 9, 1988 MOVING AROUND When you start SHEET, your screen is divided into menu bar and worksheet window. The worksheet window can be divided into 5 major areas. The status line is the first line under the window title. It is ___________ used for displaying current cell's content and worksheet status. The editing buffer ,which is the line below status line, is used for ______________ entering or editing the current cell. The column bar is the line below __________ the editing buffer. The row bar is located on the left hand side of the _______ worksheet window. Both column and row bars are used for cell reference. The biggest area is the working area for current worksheet. ____________ Each column is referred by a unique letter (from A to IU). The first column is A, column B after A, C after B and so on. The one following column Z is AA and after AZ comes BA, and so on. Each row numbers are sequentially ordered from 1 to a variety of 50 to 9999 depends on the worksheet size you choose. A cell in the worksheet is referred by column letter followed by row number. For example, at the intersection of column B and row number 2 is cell B2. Each spreadsheet can hold 255 columns and a variable number of rows from 50 to 9999. It means you can access at most 2,549,745 cells. Your screen can display a very small amounts of them. Therefore, you have to view your spreadsheet by moving the working area. The following operations let you move your working area by keyboard entry or mouse button. If current cell cursor is not in row number 1, the cell cursor will be moved one row up. If current cell cursor is not in row number 1280, the cell cursor will be moved one row down. If current cell cursor is not in column A, the cell cursor will be moved one column left. If current cell cursor is not in column IU, the cell cursor will be moved one column right. If possible, the cell cursor will be moved by "page" to the direction the arrow key specified. This key moves the current cell cursor to A1. When you press the function key F5, you can enter the cell reference directly to move the cell cursor to that cell. If the cell specified is not in the working area, then the working area will be changed and the new upper left cell is the cell specified. You can use the slider bars to change the working area quite fast. Also, you can locate a cell with mouse much faster than arrow keys. To locate a cell with mouse: - use vertical and horizontal slider bars to move to the desired working area. - put the mouse cursor over the desired cell and press left button on your mouse. Page 27 SHEET User manual By Chor-ming Lung October 9, 1988 EDITING Types of cell entry: ___________________ There are 4 different types of cell entries in SHEET: numeric constant, formula, text and BASIC statement. BASIC commands will be executed immediately but it will not be stored in any cell. The leading character of each entry will differentiate one from the other. Formula: Input string starts with "+", "-", DIGIT, "." or "(" Formula Constant: Input string starts with "+", "-", DIGIT, "." or Constant "(" Statement: Input string starts with "!" Statement Command: Input string starts with "@" Command Text: Input string starts with double quote, single quote, Text "^", "\" or character other than "+", "-", DIGIT, ".", "(", "!", "@". Constant is an arithmetic expression which does not refer to Constant other cell(s) or variable(s). A constant cell will not be constant recalculated. You may notice that most of your inputs are constants. Making the distinction between formula and constant constants formula constant helps speeding up the spreadsheet recalculation time. Statement and command are for BASIC interpreter. The Statement command differences between statement and command are that command will be statement command command executed immediately but will not be stored in current cell and statement will not be executed but will be stored in the current statement cell. Text is a descriptive string. The first character of each Text text entry has special effect for display. Single quote('): The text is left justified. Double quote("): If the width of the cell is larger than the length of the text, the text is right justified. Circumflex (^): if the width of the cell is larger than the length of the text, the text is centered within the cell width. Back slash(\): The text following back slash will be used repetively filling the cell. If a text string starts with character other than single quote, double quote, circumflex or back slash, then the character selected on "TEXT justification" of menu item "Defaults" under menu title "Sheet" will be used as its leading character. There is a command which you can change the first character from one of (' " ^ \) to one of (' " ^ \ !). It is the menu item "Adjust" under menu title "Cells". Page 28 SHEET User manual By Chor-ming Lung October 9, 1988 Rules for constructing formula: ______________________________ When you are entering formula, the first character of your entry must be "+","-", "0".."9", "." or "(". The following table summarizes all such first characters and their usage. Char Usage Example ----------------------------------------------------------------- + Used when the formula begins with a cell +A1/A2 reference or calling built-in function +SIN(A1) - Used when the formula begins with -A1*20 negative constant value or negative value -12*0.2 of cell reference or negative value of -COS(A1) function call 0-9 Used when the formula begins with a 12.3*20 constant number. . Used when the formula begins with a .05*120 constant number. ( Used when the beginning part of the (A1-A2)/A3 formula's natural order of calculation must be altered through the use of a pair of parentheses. NOTE: 1. ____ Versions before 2.0 of SHEET did not know that .05 is 0.05. 2. If part of the expression returns a string, then it will treat as 0. It means you cannot do string calculation in any cell. But the BASIC interpreter can do string operations. eg. 34+chr$(12) ==>34 "test1"+"test2" ==>0 4+len("some") ==>8 Page 29 SHEET User manual By Chor-ming Lung October 9, 1988 Cell input: __________ Whenever you type some keys, the corresponding characters will be inserted into the editing buffer. You may notice the status is changed from "Ready" to "Input". Your entry must be terminated by the following method to change the status back to "Ready". While you are in "Input" mode, the menu selections and window resize function are not allowed, though you can change your working area by moving the horizontal or vertical slider bars. Whenever you terminate your input, you will get back to the working area before you do editing. Move the editing cursor: Move the editing cursor Moves the editing cursor to left by 1/2 the length of the editing buffer. Moves the editing cursor to right by 1/2 the length of the editing buffer. MOUSE cursor Moves the mouse cursor to the character you want it be and press left button on your mouse, then the editing cursor will move to that character. Modify your input: Modify your input Toggles the INSERT mode to TYPEOVER mode and vice versa. Erases the character on the left hand side of the editing cursor. Erases the character under the editing cursor. Terminate your input: Terminate your input: Terminates input and the cell content is not modified. Terminates input and the cell cursor moves to the direction specified by editing direction (see below). Terminates input and the cell cursor moves one row up if possible. Terminates input and the cell cursor moves one row down if possible. Terminates input and the cell cursor moves one column left if possible. terminates input and the cell cursor moves one column right if possible. Miscellaneous operations: Miscellaneous operations: MOUSE if the mouse cursor is inside the working area and if you press the left button on your mouse, the cell coordinate that the mouse is over will be inserted into the editing buffer. MOUSE It works as above except a comma is inserted before the cell coordinate. MOUSE Instead of comma, a through notation (..) added before the cell coordinate. Sliders You can change the working area by moving the vertical or horizontal slider bars. Any terminating key will resume the display back to the working area before editing. Page 30 SHEET User manual By Chor-ming Lung October 9, 1988 Modify a cell: _____________ To modify a cell, you have to move the cell cursor to the desired cell. Press the function key and use the above method to modify the cell content but you must terminate your change by ______________ . If you change your mind, you can always what you have done. Editing direction: _________________ When you terminate your input by any arrow key, the editing direction will be changed to the direction the arrow key specified. When you press to terminate your input, the cell cursor will move to the editing direction. If you want to change the editing direction to neutral, you have to press before your editing session. e.g. Cell Entry Cell cursor position ------------------------------------------------------------- A1 1 A2 A2 2 A3 The first entry change the editing direction to . The second entry which terminates with is similar to terminating with . Page 31 SHEET User manual By Chor-ming Lung October 9, 1988 CUSTOMIZED DISPLAY SHEET can calculate number accurate up to 17 decimal places. In most case, you need to display 2 decimal points only. For example, your monthly loan payment may be 876.54321. You will not care about the extra digits. You just want it to display as 876.54 or $876.54. That is the situation in which you need to change its default display format. There are two ways to change the appearance of your worksheets-- changing the width of individual column and changing the format of individual cells. Change column width: ___________________ The column width of a cell may affect the display. For numeric result, if the format you specify cannot fit into the width of the cell, then the cell will be filled up with "*". For text, you may not be able to do right justification or center the text. To display the result correctly, you may need to change the width of the column. There are two ways to change the width of individual column. The first way uses the mouse and the second way uses the menu item "Col widths" under menu title "Row/Col". MOUSE: - Move the desired column within the working area - Move the mouse cursor on the white gap after the column letter (e.g. if you want to change the width of column A, move the mouse cursor on the white gap after column letter A) - Press the left mouse button (do not release it) - To expand the column width, drag the mouse to right and release the button - To shrink the column width, drag the mouse to left and release the column Col widths: - Select the menu item "Col widths" under the menu title "Row/Col". - Enter the correct column letter - Enter your desired column width. - You can enter numbers separate by comma. Doing this will change the column widths beginning from the column letter specified. Note: Note if your column width is only 1 character wide, then the column letter will be pushed to the next column. Page 32 SHEET User manual By Chor-ming Lung October 9, 1988 Default display format: ______________________ Text: Text Depends on the first character for justification. Constant and Formula: Constant and Formula The number of digits after the decimal point is six. It is right justified and if possible, a blank space is added at the rightmost place. Column width: 9 characters per column. Column width Display format: _______________ There are 9 different predefined display formats for formula formula and constant types. The first one is the default format mentioned constant above. The others are %, Fixed, Scientific, Dollar, ,(comma), % Fixed Scientific Dollar , Date1, Date2, Date3. Date1 Date2 Date3 Type Usage Example -------------------------------------------------------- Default Display up to 6 decimal points 3.1234 % Use for interest rate, 0.05 will be discount, tax and so on. displayed as 5% Fixed Use for data which have 1.00 different decimal places. (eg 1.50 1,1.5. Most likely, you want it 2.00 to be 1.00, 1.50) Scienti- Use for display data which is a 3.24e12 fic big or small number. Dollar Use for data which are related $1,245.12 to money. , Comma format is similar to 1,245.12 Dollar format excep there is no dollar sign in front of it. Date1 Use for displaying date related 9-Jul-88 value. Its format is dd-MMM-yy. Date2 Its format is dd-mmm. 18-Jul Date3 Its format is mmm-yy. Jul-88 TEXT Display formula instead of its value. HIDE The information on this cell will not appear on the working area Page 33 SHEET User manual By Chor-ming Lung October 9, 1988 Block: _____ Before we reformat some cells, we need to tell SHEET which cells we intend to do that. In SHEET, those cells are called blocks. In Lotus 123, we can define only one block at one time, but SHEET can accomodate up to 18 blocks at one time. A block is part of the spreadsheet. It is a rectangle which holds at least one cell. To define a block, you need to select the starting ________ cell and stopping cell of the block. The process of defining the ____ _____________ starting cell requires mouse button and key, and stopping ______________ ________ cell requires mouse button and key. ____ Here is an example to define cell A5 as a block: - Move the mouse cursor within cell A5 - Press key and the left mouse button simultaneously You may notice that the selected block has been highlighted by reversing its color. Here is another example to show you how to define a block containing cells from B5 to C14 (B5..C14): - Move the mouse cursor within cell B5 - Press key and the left mouse button simultaneously - Move the mouse cursor within cell C14 -Press key and the left mouse button simultaneously. In summary, to define the starting cell, you have to move the _____________ mouse cursor over the first cell and press and left mouse button simultaneously. To define the stopping cell, you have _____________ to move the mouse cursor over the last cell and press and left mouse button simultaneously. If no stopping cell is provided _____________ then the stopping cell is the starting cell. Using above example, if you want to define block B5..B14 instead of B5..C14 - Move the mouse cursor within cell B14 - Press key and the left mouse button simultan- eously. If block B5..C14 is the first block you defined, there is another way to select this block: - move the mouse cursor within the cell B5 - press the left mouse button and drag until you reach cell C14 - release mouse button. De-select blocks: ________________ Selecting any cell by a mouse button click will de-select all the blocks being selected. Page 34 SHEET User manual By Chor-ming Lung October 9, 1988 Change display format: _____________________ To reformat some cells: - select blocks using above method - Press or find menu title "Sheet" and choose menu item "Reformat" under it. - A dialog box will show up. Choose the format you want from it. You can also use the command "@REFORAMT" to reformat some cells. The command should be: - @reformat FORMAT_STRING, block1, block2, .... - FORMAT_STRING consists of two part. The first part is the format being used, and the second part is the decimal places. The format of a cell can be "$", ",", "%", "F", "S" "D1", "D2" and "D3". They represent the format of "DOLLAR", ",", "PERCENT", "FIXED", "SCIENTIFIC", "Date1", "Date2" and "Date3" respectively. For example, if you want to reformat cell B2..C2 and B5..C5 to DOLLAR with decimal place of 2, you can issue the command @reformat "$2",b2..c2,b5..c5 instead of selecting blocks and press . NOTE: NOTE YOU CAN ONLY REFORMAT THOSE CELLS WHICH EXIST. That is because of Sparse Matrix. After a cell has been created, the cell possesses attributes. If you format some empty cells and insert something into those cells after that, those cells appear as default. It is not a bug. You can set the option in menu item "Options" under "Sheet" to use the format of previous row. Then, whenever you enter a new element for the worksheet, it will try to find the format of previous row on the same column and uses it as default format. So, you do not need to reformat again and again. Page 35 SHEET User manual By Chor-ming Lung October 9, 1988 COPY AND MOVE COPY and MOVE commands require two blocks. The first block is the source, and the second one is destination. You have to select those blocks before you activate COPY command by or find the menu title "Cells" and choose menu item "Copy" under it. To activate MOVE command, you press or find menu title "Range" and choose menu item "Move" under it. You can use the command "@COPY source_block, dest_block" or "@MOVE source_block, dest_block" to do the same thing. Here is an example showing you how to copy the content on cell A1 to A2..A5. Assume that the content of A1 is "1+A2". The display of cell A1 should be 1. Key Mouse button Effect Mouse cursor position ----------------------------------------------------------- A1 Control + Mouse click Cell A1 inverted A2 Control + Mouse click Cell A2 inverted A5 Shift + Mouse click Cell A2 through A5 inverted. Control C An alert box display on screen Return or Mouse click on OK. The operations prior to define two blocks. The first block consists of cell A1 only. The second block consists of cells A2 through A5. tells SHEET to copy the content of the first block to the second block. If cell A6 does not contain anything and you set the recalculation mode to Natural, your display should be: A Cell content 1 5 1+A2 2 4 1+A3 3 3 1+A4 4 2 1+A5 5 1 1+A6 Natural recalculation means formulas are calculated in their dependent order. Cell A1 depends on the result of A2 (1+A2). So, the calculation of A1 is postponed until A2 gets its result. A2 depends on A3 and A3 depends on A4 and so on. Thererfore, the first formula being computed is A5. If a formula refers to an empty cell, a zero will be returned. Hence, A5=1+A6=1+0=1, cell A4=1+A5=1+1=2 and A3=1+A4=1+2=3 ... Page 36 SHEET User manual By Chor-ming Lung October 9, 1988 One question you may ask: Why the cell reference of A2 in cell A1 has been changed to A3, A4, A5 and A6 in cell A2, A3, A4 and A5 respectively? The answer is relative cell reference. Relative and Absolue cell reference: ___________________________________ Cell reference can be absolute or relative. By adding a $ symbol before the column letter or/and row number, we designate a part of a cell address as absolute or both parts as absolute. For example: $A2 denotes column (A) is absolute and the row (2) is relative. $A$2 denotes column (A) and row (2) are both absolute. A$2 denotes column (A) is relative and the row (2) is absolute A2 denotes column (A) and row (2) are both relative. During COPY, a formula in source block which contains relative cell references will be modified. If the formula is copied down, then the relative row number will be increased. If the formula is copied to right hand side, then the relative column letter will be increased. If you intend to copy "1+A2" to A2..A5, then you must use formula "1+A$2" instead of "1+A2". NOTE: NOTE: A block can be a single cell (A2), part of a column (A2..A5),part of a row (B20..E20) or a range (A4..C10). The following describes how the program handle different types of block COPY. Source is the first block and destination is the second block. Result is the range size chosen by COPY. Source Destination Result size --------------------------------------------------------- Single cell any kind of block the largest range column reference any kind of block the largest range row reference any kind of block the largest range range reference any kind of block the source range You can use "@COPY" to copy from source to destination too. You can use "@COPY" to copy from source to destination too. Examples: - If you want to COPY C1 to C2..C20 * Control and mouse click on cell C1 * Control and mouse click on cell C2 * Shift and mouse click on cell C20 * OR: "@COPY c1,c2..c20" - If you want to COPY C1..C12 to D1..D12 * Control and mouse click on cell C1 * Shift and mouse click on cell C12 * Control and mouse click on cell D1 * OR: "@COPY c1..c12,d1" Page 37 SHEET User manual By Chor-ming Lung October 9, 1988 ERASE CELLS After some practice, you may find some data in your worksheet are no longer needed. Hence, you need the capability to erase them. Before you activate the ERASE command, you must select the desired cells as blocks. Yes, you can erase more than one block, but the total number of blocks cannot exceed 18. Here is an example showing you how to erase cells C1..D11, E5, F8 at one time. Key Mouse button Effect Mouse cursor Position ---------------------------------------------------------------- C1 Control + Mouse button C1 is inverted D11 Shift + Mouse button C1 through D11 are inverted E5 Control + Mouse button E5 is inverted F8 Control + Mouse button F8 is inverted Control E An alert box for ERASE command on screen Return or Mouse button The screen will be on OK redrawn to show the effect of erase. Note: ____ Instead of using mouse operations, you can use the command "@ERASE" to erase part of your worksheet. To do the same thing as above, you can issue the command: @erase c1..d11,e5,f8 Page 38 SHEET User manual By Chor-ming Lung October 9, 1988 INSERT AND DELETE INSERT and DELETE commands work with row and column only. The first thing before we activate INSERT or DELETE command, we must select a row/column. Row/Column selection: ____________________ Here is an example to show you how to select column D: Mouse cursor position Mouse button ------------------------------------------------------ COLUMN BAR column letter D Click the mouse button After you select column D, the whole column will be inverted. This is an example showing you how to select row 3 Mouse cursor position Mouse button ------------------------------------------------------ ROW BAR row number 3 Click the mouse button After you select row 3, the whole row will be inverted. To delete or insert a row/column, you must first select the row/column by clicking on the column or row bar with desired column letter or row number. After that, you can press or for inserting or deleting. You can choose the menu title "Row/Col" and choose menu item "Insert" or "Delete" to do the same thing. Note: ____ INSERT and DELETE commands work with row and column only. - If you intend to add some blank rows or column but you do not want to add the whole rows/columns, then you must use MOVE command instead. - If you intend to delete some cells, use ERASE instead of DELETE. Page 39 SHEET User manual By Chor-ming Lung October 9, 1988 LOAD, SAVE AND PRINT If you take a look of the menu title "File", you may notice that there are three types of load and save commands. Menu item "Load" will load the ordinary SHEET files (they have a file extension SHT). "Load WKS" will load Lotus 123 release 1A or release 2 files. "Load PART" is similar to Lotus file combine command. It is used for merging other worksheet with current worksheet. The file you "Load" with part should be saved with "Save PART" command and its file extension should be PRT. Menu item "Save" will save the current file using SHT format. "Save WKS" will save file in Lotus WKS format. "Save PART" is similar to Lotus file extract command. It is used for saving some part of the current worksheet. To activate this command, you should select blocks to hold cells being output, and activate this command. Currently, you cannot save your worksheet in Lotus release 1A format. Other than that, every thing is functional. File extension: ______________ Lotus 123 release 1A uses WKS as its file extension. SHEET uses SHT as its file extension. If you enter a file name in your file selector dialog box, you should include the file extension. The file extension for PART is PRT. If you are printing the spreadsheet to disk, the file extension for this file should be PRN. Load: ____ There are three way to load a spreadsheet: - From the menu title, choose "File" Choose menu item "Load" Select your file from the file selector box - . Press and L key simultaneously Select your file from the file selector box - @load (This is a BASIC command). Save: ____ There are three way to save a spreadsheet: - From the menu title, choose "File" Choose menu item "Save" Enter your file in the file selector dialog box - . Press and S key simultaneously Enter your file in the file selector dialog box - @save "filenmae" (This is a BASIC command.) Load PART: _________ You can merge those cells that are saved with "Save PART" into your current worksheet. To load those files, you should select the menu item "Load PART" from the menu title "File". Page 40 SHEET User manual By Chor-ming Lung October 9, 1988 Save Part: _________ You can save part of your worksheet to a file and then merge it with other worksheet. It is especially useful for BASIC programmer. They may create some BASIC routines that are needed from file to file. Save them as PART and load them back for other files. To save part of your worksheet, you need to select those cells as blocks and activate the menu item "Save PART". The file extension for this kind of file is PRT. Lotus files: ___________ In many ways, SHEET is quite similar to Lotus 123. But I must emphasize that I am not writing a Lotus clone. Instead, I am trying to write a BASIC interpreter in a spreadsheet environment. Even though, SHEET can read Lotus files, you may not be lucky enough to convert all your existing Lotus files. SHEET will convert those functions it supports. When SHEET encounters problem in function convertion, it will stop loading the file. It can read Lotus 123 release 1A or 2 files. You may also notice that the converted SHT files are normally bigger than WKS files. There are two reason for that. First, I use a single data type -- double -- for all constant number. Lotus separates number from integer to double. An integer is two bytes long while a double is eight bytes. The second reason is that Lotus stores formula in suffix order while SHEET stores formula in infix order (e.g. 3+4 is in infix order, its suffix order representation is 3 4 +). Suffix order not only saves storage (in suffix order, parenthesis can be discarded and comma between parameters can be discarded too), it also speeds up formula calculation. You may ask me why I use infix order? Well, it is not easy to write a BASIC interpreter using suffix order. Print: _____ You can print your work sheet to a file or to printer. Before Before you print, please specify the block which you want to print. The you print, please specify the block which you want to print print routine will print those columns that fits on the margins. If some columns on the print range are not print on the first pass, they will be printed on later passes. Note: Note The information you enter for the PRINT DIALOG will not be saved with your spreadsheet file. It means you have to type in your format each time you want to print it. Here is an example showing how to print the block A1..E12 Key Mouse button Effect Mouse cursor position ---------------------------------------------------------------- A1 Control + Mouse button A1 is inverted E12 Shift + Mouse button A1 through E12 are inverted Page 41 SHEET User manual By Chor-ming Lung October 9, 1988 PRINT dialog box display Change dialog box contents. or Mouse button on OK. Let us examine what the components of the print dialog box mean. If you want to print your worksheet to disk instead of printer, enter D in the box after "Print to (P)rinter/(D)isk:". Otherwise, enter P. If you want to print the formula (including BASIC statement) of each cell, then enter Y for the question "Print Formula (Y)es/(N)o"". Otherwise, enter N. NOTE: versions before 2.0 prints formula row after row. The new version prints formula column after column. It makes printing BASIC program easier. Right margin--> <---------------------- Page width ---------------------> ^ ******************************************************** | * ^ * | * | Top margin and titles * | * v * | * ************************************** * | * * * * *<------>* * * Page * Left * * * Length * Margin * * * * * * * | * * * * | * * * * | * * * * | * * * * | * * * * | * * * * | * * * * | * * * * | * ************************************** * | * ^ * | * | Bottom margin * | * v * v ******************************************************** If you want to test your output before you actually print it, you can enter the command: @redirect to "prn:" @print_block a1..g4,4,76 The commands above will print row 1 to row 4 from column A to column G using left margin 4 and right margin 76 to your printer. You can check the result before you actually print it. (That is useful because you cannot stop PRINT when it actually prints). Page 42 SHEET User manual By Chor-ming Lung October 9, 1988 PART III -- SHEET BASIC A spreadsheet is a powerful computing tool. It is also a handy environment for handling data. Yon can add, delete and change your data with ease, and the spreadsheet program modifies all the required places to reflect the changes. Let the spreadsheet handle your data and numurical calculations, and let the BASIC interpreter do the rest. SHEET BASIC can do calculations, string operations, graphics and text files. The combination of spreadsheet and BASIC provides an ideal environment for using your computer productively. The following is a brief explanation of each session of PART III. Cells, Variables, String: This chapter introduces the new cell reference method, variables and their naming convention. You cannot assign a string to a variable, but you can assign a string to a cell. The rules for accessing and operating strings are introduced too. Flow control Statements: These statements change the execution sequence of BASIC commands. The commands include: IF .. THEN .. ELSE, GOTO, GOSUB .. RETURN, LOOP .. EXIT IF .. ENDLOOP, FOR .. NEXT, and RUN I/O Statements: REDIRECT TO, PRINT Miscellaneous statements: SAVE SCREEN, RESTORE SCREEN, WAIT, DEFINE, MESSAGE Text files: GET_FILE, OPEN, CLOSE, INPUT, PRINT Worksheet commands: COPY, MOVE, ERASE, REFORMAT, SORT, SWAP Console commands: CLS, CURSORON, CURSOROFF, VIDEOHIGH, VIDEONORM, GOTOXY, INKEY, UNGET, INPUT Graphic commands: Setup commands: WINDOW, VIEWPORT, SCALE, EXITGRAPH, CLEAR Line/Dot commands: ATTRIBUTE, LINE, LINE_TO, PLOT, PLOT DATA, SPLINE Turtlegraphics: FORWARD, TURN, TURN_TO Axis commands: MARKX, MARKY, LABELX, LABELY Text command: PTEXT Miscellaneous: MOUSE_POS Page 43 SHEET User manual By Chor-ming Lung October 9, 1988 Cells and Variables Cell reference with dimension: _____________________________ A cell reference is a combination of column letter and row number. This type of access method is not enough for a programming language. In SHEET, you can use a cell as a BASE and move it horizontally or vertically by appending two numbers which are separated by comma and embraced by "[" and "]". Each column letter has its own value. Column A is 1, B is 2 and so on. For example, A2[3,4]. A2 is the BASE whose coordinate is [1,2] and A2[3,4]=[1,2]+[3,4]=[4,6]=D6. Here are some other examples: C1[4,7] which is equivalent to cell G8. C1 is equal to [3,1] and C1[4,7]=[3,1]+[4,7] = [7,8]. Column G is 7. So the combination of this cell is G8. H123[-2,4] H123=[8,123] and H123[-2,4] = [8,123]+[-2,4] = [6,127] or F127. Variables: _________ Variables must be initialized before being used. You can define up to 150 variables. A variable name starts with an alpha character which is followed by alphanumeric characters. Up to 15 characters are significant. A variable name must not be identical with reserved keywords, built-in functions and cell reference. examples: row=4 correct pi=3.1415 incorrect (PI is a predefined constant) a1=12 incorrect (it is cell A1) 1and2 incorrect (the first character is not letter) There are so many keywords and built-in functions, how can one differentiate them from the other? Keywords are embraced by leading and trailing spaces. For example, IF is a keyword and when it is displayed, it is " IF ". A built-in function has a leading space before the name. For example, SIN(x) will be displayed as " SIN(X)". Variables do not have any leading or trailing space. Cell reference with variables _____________________________ The whole worksheet can be viewed as a two dimensional array. You can access any cell by A1[c,r] where c is the column number and r is the row number. Not only you can access individual cell by using this method, you can also use it for block. For example, assume that you store your square matrix in cells a1..c3 and you want to store the inverse of this matrix to d1..f3. You can use the following program to do this. ! matrix(d1,inverse[a1..c3]) ! end Assume that you need to solve another square matrix in which you Page 44 SHEET User manual By Chor-ming Lung October 9, 1988 store it in cells a1..d4. You can easily change the argument of matrix to: ! matrix(e1,inverse[a1..d4) If you keep using this method to solve square matrix in different sizes, you may find it is clumsy and easy to make mistakes. Here is another program that makes the whole process of changing a lot easier for you and less error prone. !n=3 ! matrix(a1[n,0],invers[a1..a1[n-1,n-1]) ! end n=3 so a1[3,0]=[1,1]+[3,0]=[4,1]=d1 and a1[n-1,n-1]=[1,1][2,2]=[3,3]=c3. All you need to change is the constant n. String ______ You may store numerical data or TEXT string in a cell. To access a cell as a string, you should add the "$" at the end of each cell reference. For example: A1$, A1[i,j]$ String operators for comparisons, concatenation and string functions are added for BASIC users. REMEMBER, string functions and concatenation are available only in BASIC statements and commands. Currently, you can assign string to cells but not the 150 variables. Two predefined string variables are provided for file name and input buffer. They are FILENAME$ and BUF$. You can use them but you cannot assign new value to them. FILENAME$ is changed whenever you use the file item selector and BUF$ is changed whenever you use the command INPUT. For more details, please refer to "Text files" for GET_FILE, INPUT #n and "Console:" for INPUT. If an empty cell is referred as a string, a null string will be returned. If a cell with number is referred as a string, then a string that is exactly being displayed for that cell will be returned. If the cell is a TEXT string, a string excluding the leading character will be returned. The leading character for a TEXT string is one of ' " ^ and \. If you are assigning a string to a cell, and if the string does not contain one of those leading character, then the default character defined in "TEXT justification" of menu item "Defaults" will be used as the leading character for that cell. WARNING: DO NOT MAKE ANY STRING LONGER THAN 249 CHARACTERS. String operators: String operators +: string concatenation. For example "Thi"+"s a test" will return a string "This is a test". =: string assignment or string equal comparison. E.g. !a1$="^123": END. or !if a1$=b1$ then ... Page 45 SHEET User manual By Chor-ming Lung October 9, 1988 <>, >, <, =>, >=, =<, <= string comparisons for not equal, greater, less, equal or greater and equal or less than. Page 46 SHEET User manual By Chor-ming Lung October 9, 1988 Flow control Statements IF .. THEN .. ELSE .. Syntax: IF conditionn THEN statements:ELSE statements Description: This statement is used to make a decision regarding program flow based on the result returned by an expression. Example: IF A1=12 THEN A2=20:ELSE A2=30 NOTE: The following statements are not valid: IF A1=12 THEN G3:ELSE GOTO G4 IF A1=12 GOTO G3 If you have used traditional BASICs before, you would know that you can write "IF A1=12 THEN 120:ELSE GOTO 130" and "IF A1=12 GOTO 120" They are not valid in SHEET. Instead, you must change them to: IF A1=12 THEN GOTO G3:ELSE GOTO G4 IF A1=12 THEN GOTO G3 GOTO Syntax: GOTO cell. Description: This statement is used to branch unconditionally to a specified cell. Example: GOTO A1 GOSUB .. RETURN Syntax: GOSUB cell ... RETURN Description: These statements uses in pair for branching to, and returning from a subroutine. LOOP .. EXIT IF .. ENDLOOP Syntax: LOOP ... EXIT IF condition .... ENDLOOP Description: Those commands are used to execute a series of statements in Page 47 SHEET User manual By Chor-ming Lung October 9, 1988 a loop until some conditions are true. Condition is a logical expression which may be either TRUE or FALSE. Within the LOOP, multiple EXIT IFs are allowed. It is adviced not to use GOTO statement to break the loop. Example: The following example assigns 1 to 20 to cell A1 through A20. R=0 LOOP A1[0,R]=R+1 R = R+1 EXIT IF R=20 ENDLOOP FOR .. NEXT Syntax: FOR assignment TO expression [STEP expression] ... NEXT variable_being_assigned Description: These commands are used to execute a series of statements in a definite number of times. STEP is optional. If no STEP value being provided, it is assumed to be one. In other BASIC interpreters, NEXT I,J means: increases value of I, when the _________ FOR loop for I is done, then increases value of J. You must use NEXT I: NEXT J in SHEET BASIC. The number of nesting FOR _______________ loops is infinite. RUN Syntax: RUN cell Description: RUN is similar to GOTO. The only difference is that RUN will initialize the runtime stack and reset the symbol table. Most of the time, RUN is used to execute BASIC codes starting from a specified cell. If the cell is C1, you should enter "@run c1" in the editing buffer. Page 48 SHEET User manual By Chor-ming Lung October 9, 1988 I/O Statements REDIRECT TO Syntax: REDIRECT TO "file" Description: This statement changes the output path of subsequent PRINT commands to the specified file. There are a few predefined files. prn: is the printer, con: is your monitor ____ ____ and aux: is the rs-232 port. Otherwise, they are disk files. ____ The default file is con:. Except con:, all the file output are buffered. It means you may not get instant response. If you redirect the output to printer and if your printer is not on, an alert box will show up. If you choose "Quit", the BASIC program being executed will be stopped. If you choose "Continue", the subsequent PRINT commands will output to the file prior to REDIRECT TO command. Most likely, it is con:. You should turn your printer on before you choose "Try again". EXAMPLE: REDIRECT TO "prn:" PRINT statement Syntax: PRINT cell/cell$/number/string[,/;...] Description: This command prints numeric or string data to the file specified by REDIRECT TO command. Implicitly, it is your monitor. If no data following the PRINT command, a blank line will be printed. String data must be enclosed by double quotation marks. Each datum can be a cell reference, variable, string or number. If more than one datum is going to print, each of them should be separated by comma or semicolon. If you are using comma to separated two data, the second data will be aligned to the next TAB position. Each TAB is 10 characters wide. If you are printing cell content, the format specified in that cell is used exactly as display. If the cell you intended to print contains TEXT string, you should add "$" at the end of the cell reference. Otherwise, you will print a cell with zero. If a number is being printed, the number will be surrounded by a leading space and a trailing space. The output format of variables is similar to a number.If you are printing string, the text inside the embraced double quotation marks will be outputed. While you are printing, you can use and to suspend and resume your display. BUT don't _________ press to stop your PRINT session. If you do press _____________________________________________________________ while PRINT command is printing on console, YOU _____________________________________________________________ Page 49 SHEET User manual By Chor-ming Lung October 9, 1988 WILL GET back to desktop and you need to reboot the system to _____________________________________________________________ use SHEET. UES to stop your BASIC program. ________________________________________________ Example: PRINT a1,12;b1$;20;"This is a test" Page 50 SHEET User manual By Chor-ming Lung October 9, 1988 Miscellaneous statements If you print to console, your current display may be garbled. Therefore, you need to save your screen before printing and restore it after you are done. SAVE SCREEN Syntax: SAVE SCREEN Description: Save current display to SCREEN reserved area. That is the same area that is used for FAST screen update. If you deny to reserve 32K for fast screen update, you may crash the system when you use this command. RESTORE SCREEN Syntax: RESTORE SCREEN Description: Restore the screen to the old display. WAIT Syntax: WAIT WAIT # Description: This statement suspends the execution of BASIC commands. # means an integer number. If no number follows WAIT, it will print "Press return to continue ..." on console and you must press key to resume its operations. The printed message will damage your display. Hence, you should save your screen before calling WAIT. If WAIT followed by an integer x, then the system will wait for x seconds. Example: WAIT 5 (System suspends for 5 seconds) DEFINE Syntax: DEFINE fname(parameters) = expression Description: This statement let user define its own functions. Fname is the function name. Its naming convention follows the rule for naming variable. You can put up to 9 parameters for each user function. Each parameter is a variable separated by comma. Each DEFINE statement must occupy a single cell and user defined function is useful for numerical calculation only. Page 51 SHEET User manual By Chor-ming Lung October 9, 1988 MESSAGE Syntax: MESSAGE "text" Description: This command uses the GEM alert box to display a message. "text" can be a string of up to 5 lines. Each line is separated by "|" and the length of each line should not be bigger than 40. Example: MESSAGE "Hello|World" EXAMPLE: Address label program EXAMPLE Assume that you store your address data in the current spreadsheet. Column A holds the Last name. Column B holds the First name. Column C holds the address and D,E,F holds City, State and Zip respectively. The data starts from row 3. The following program will print the data in your address sheet to the address label for handling mails. Assume the program stores from cell G1..G10 and you run it by "@run g1". G1 : ! REDIRECT TO "prn:" G2 : !R=0: SAVE SCREEN G3 : ! LOOP G4 : ! EXIT IF ISEMPTY(A3[0,R]): PRINT G5 : ! PRINT A3[1,R]$;A3[0,R]$ G6 : ! PRINT A3[2,R]$ G7 : ! PRINT A3[3,R]$;A3[4,R]$;A3[5,R]$ G8 : ! PRINT : PRINT :R=R+1 G9 : ! ENDLOOP : WAIT G10 : ! RESTORE SCREEN Page 52 SHEET User manual By Chor-ming Lung October 9, 1988 Text files SHEET should be the first spreadsheet which has the commands for you to access any TEXT files. The command is line oriented. These commands are intended for people who need to acquire data from other sources. There are two constant strings that are related to some of the Text file commands. FILENAME$ is a constant string that can be changed by file item selecto dialog box. BUF$ is a constant string that is modified by any INPUT statement. Using the file commands and string functions, you can do some thing that is impossible for user of other spreadsheet programs. GET_FILE Syntax: GET_FILE def_search, name$ Description: Use the file item selector to find the file name. Def_search is the default search string. It can be a string with wild characters. For example "\*.SHT". name$ can be FILENAME$ or a cell reference. Example: GET_FILE "\*.*",filename$ GET_FILE "\*.txt",a1$ OPEN Syntax: OPEN "o",#n,filename$ OPEN "i",#n,a1$ Description: Open a file for input or output. "o" means output and "i" for input. #n means the channel number. N should be a number from 0 to 9. Filename can be FILENAME$ or a cell reference. Example: OPEN "o",#1,FILENAME$ OPEN "i",#2,"test.txt" CLOSE Syntax: CLOSE #n[,#m,...] Description: Close opened files. #n , #m are the channel number. They should be in the range of 0 to 9. If you try to close an unopened file, you will get an error message "Cannot close an unopened file". Example: CLOSE #1 Page 53 SHEET User manual By Chor-ming Lung October 9, 1988 INPUT #n Syntax: INPUT #n,buf$ Description: Read a line from the input file. #n is the channel number. buf$ can be BUF$ or a cell reference. If the channel number being referred is an output file or an unopened file, you will get an error message "Channel number error". Example: INPUT #1, BUF$ INPUT #1, a1$ PRINT #n Syntax: PRINT #n, cell/cell$/string/number[,/;....] Description: PRINT #n is similar to PRINT except that PRINT outputs to the file specified by REDIRECT TO (implicitly your terminal). PRINT #n outputs to the channel number being opened by OPEN command. Example: Assume that cell a1$="This is a test" and b1=12.34 and the widths of column A and B are 9. PRINT #1,a1;b1 ==> " 0 12.34 " to #1 PRINT #1,a1$;b1 ==> "This is a test 12.34 " to #1 Page 54 SHEET User manual By Chor-ming Lung October 9, 1988 Worksheet commands SHEET BASIC is part of SHEET -- the spreadsheet program. It is more convenient if you can use some of those worksheet commands to handle data in your BASIC program. This new version of SHEET provides some of the essential worksheet commands for you to incorporate with your program. They are COPY, MOVE, ERASE, REFORMAT, SORT and SWAP. SORT and SWAP are new commands for SHEET too. Currently, they can be activated by BASIC commands only. They work fine with cells whose formula do not refer to other cells. That is because the cell references in those formula are not updated when you SWAP them. Next release should remove this restriction. COPY Syntax: COPY block1, block2 Description: Copy block1 to block2. A block can be a single cell or a rectangle portion of the worksheet. The COPY command follows the rule for COPYing cells by . Example: COPY a1..b3, c1 MOVE Syntax: MOVE block1, block2 Description: Move block1 to block2. It is similar to the command . Example: MOVE a1..b3, c1 ERASE Syntax: ERASE block1[,block2,...] Description: Erase portion of the worksheet specified by the blocks. Up to 18 blocks can be erased at one time. Example: ERASE a1..b3,c1..d3 REFORMAT Syntax: REFORMAT FORMAT,block1[,block2,...] Description: Reformat portion of the worksheet. Up to 18 blocks can Page 55 SHEET User manual By Chor-ming Lung October 9, 1988 be reformat at one time. FORMAT is a string which consists of two part -- format and decimal places. A format can be "$", ",", "F", "S", "D1", "D2", "D3". "$" for DOLLAR, "," for COMMA, "F" for FIXED, "S" for SCINETIFIC and "D1", "D2", "D3" for DATE1, DATE2, and DATE3 respectively. No decimal places should follow "D1","D2" and "D3". Example: REFORMAT "$2",A1..B3 (reformat a1..b3 for dollar and 2 decimal places) REFORMAT "D1", C1..C12 (reformat C1..c12 for Date1 format) SORT Syntax: SORT "A",block, cell1[,cell2,...] SORT "D",block, cell1[,cell2,...] Description: Sort followed by "A" means sort block in ascending order and "D" for descending order. Only the block will be affected by the sort command. Cell1 points to the first column that is used for comparison. Up to 6 of these cells can be used as criteria for comparisons. In comparison, an empty cell is smaller than a cell with numerical value and a cell with numerical value is smaller than a cell contains TEXT string. If the column being compared consists of BASIC statements, SORT will be stopped. Currently, SORT does not modify the formula on each cell being affected. You may have trouble in sorting cells whose formula refers to other cells. Example: SORT "A",A1..B3,A1 SWAP Syntax: SWAP cell1, cell2 Description: This command swaps cell1 with cell2. It swaps between two cells. If you want to swap a row with other row, you should use a FOR loop to do it one by one. Again, it has problem with cell whose formula refers to other cell. Example: SWAP a1,b2 Page 56 SHEET User manual By Chor-ming Lung October 9, 1988 Console commands ________________ The Atari monitor emulates the VT52 terminal. You can use those VT52 commands to control your output. This version of SHEET provides some of the VT52 commands in the SHEET BASIC. They are CLS, CURSORON, CURSOROFF, VIDEOHIGH, VIDEONORM and GOTOXY. One function and two commands for reading keyboard entries are provided. They are INKEY, UNGET and INPUT. Screen coordinate: (1,1) is the upper left-corner, (80,1) is the upper right-corner, (1,25) is the bottom left-corner and (80,25) is the bottom right-corner. CLS Syntax: CLS Description: Clear the current screen and move the cursor to the position 1,1. CURSORON Syntax: CURSORON Description: Makes the screen cursor visible. CURSOROFF Syntax: CURSOROFF Description: Turns the screen cursor off. VIDEOHIGH Syntax: VIDEOHIGH Description: Makes the subsequent output characters in reverse color VIDEONORM Syntax: VIDEONORM Description: Makes the subsequent output characters in normal color GOTOXY Syntax: GOTOXY x,y Description: Moves the screen cursor to position x,y. x is in the Page 57 SHEET User manual By Chor-ming Lung October 9, 1988 range of 1 to 80 and y is in the range of 1 to 25. INKEY Syntax: a=INKEY Description: It is a function that returns the ASCII number of a key being pressed. If no key or special keys are being pressed -1 will be returned. Arrow keys for Up, Down, Left, Right have value of 129,130,131 and 132 respectively . Arrow keys with Shift keys for Up, Down, Left, Right have values of 133,134,135 and 136. Example: A=INKEY UNGET Syntax: UNGET x Description: Put x back to the input stream for later INPUT uses. Example: A=INKEY:.... :UNGET a INPUT Syntax: INPUT buf$ Description: buf$ can be either BUF$ or a cell reference. This routine calls the standard C function gets to get your input. If you UNGET a character before calling this command, the UNGET character cannot be altered or displayed but it will be the first character in your buf$. Page 58 SHEET User manual By Chor-ming Lung October 9, 1988 Graphic commands ________________ Your monitor can be used to display pictures. A picture is a collection of picture elements (pixels). Some people prefer to call a pixel a dot. Normal graphic commands in other BASIC languages provide you a mean to change each pixel in your display. The addressing method they used are called Raster coordinate. The left top-corner is (0,0) and the right bottom-corner is (639,399), (639,199) or (319,199) for high, medium and low resolution. In real world, we are dealing with Cartesian coordinate instead of Raster coordinate. The left bottom-corner is the minimal point we can use for display and the right top-corner is the maximal point. The graphic commands provided by SHEET BASIC use the Cartesian coordinate and those commands will translate Cartesian points to pixels. SHEET BASIC is not the first BASIC interpreter which provide scientific graphics (at least True BASIC is a few years earlier). But I think it is the first BASIC provides cubic spline curves. Cubic spline is a method scientists use to draw smooth curves. The graphic commands are highly relocatable and they work on any resolution. Hence, it is possible to use 16 colors to plot your graph in low resolution. The graphics commands are separated into the following categories: Setup commands: Setup commands: WINDOW or VIEWPORT, SCALE, EXITGRAPH, CLEAR Line/Dot commands: Line/Dot commands: ATTRIBUTE, LINE, LINE_TO, PLOT, PLOT DATA, SPLINE Turtlegraphics: Turtlegraphics FORWARD, TURN, TURN_TO Axis commands: Axis commands MARKX, MARKY, LABELX, LABELY Text command: Text command: PTEXT Miscellaneous: Miscellaneous: MOUSE_POS CONVENTION: CONVENTION Optional parameters are bracketed. # means a number. (x,y), (x1,y1) and (x2,y2) are measured in Cartesian coordinate. An r added before x or y means the point is in Raster coordinate. Page 59 SHEET User manual By Chor-ming Lung October 9, 1988 Setup commands: ______________ WINDOW or VIEWPORT defines the part of the screen being used for graphic output. They are the only commands whose parameters are in Raster coordinate. SCALE defines the scaling factors that translate the Cartesian corrdinate data into location on your display. CLEAR cleans up the area defined by WINDOW or VIEWPORT. WINDOW and VIEWPORT change the clipping area. The last command after your graph is done should be EXITGRAPH which resets the clipping area to the current window. Otherwise, you may have an illusion that your system is locked. In case you feel your system is locked, you should try first to see whether a BASIC program is running. If the system is still lock-up, enter the command "@EXITGRAPH" in blind and you should have the system back. WINDOW: ______ Syntax: WINDOW [# [,rx1,ry1,rx2,ry2] ] Description: If no parameter provides for this command, then the current window will be used for graphic output. SHEET supports up to 4 windows. Each window has its own number ranging from 1 to 4. This number corresponds to the number you press ALT-1, ALT-2, ALT-3 or ALT-4. So, you can use any window for graphics output. If raster parameters provide, then the window specified will be resized to those coordinates and the work area of that window will be used for graphics output. If a window changes it size, AES will ask SHEET to redraw it. So, you may need to run your program twice to get the graph. If the window number specified has not been opened, then you won't see the graph, but the BASIC interpreter will not stop. Examples: WINDOW using the current window WINDOW 1 using window 1 WINDOW 1,0,20,350,120 reposition and resize window 1 Hints: If your program involves a lot of calculation, and you don't want to run the program twice to get the graph, you can stop your program by key when you see the window has been resized and then enter the same run command again in blind. So, you have better to put your WINDOW command on the first line. Page 60 SHEET User manual By Chor-ming Lung October 9, 1988 VIEWPORT: ________ Syntax: VIEWPORT rx1,ry1,rx2,ry2 Description: WINDOW lets you to borrow the working area of a spreadsheet window. VIEWPORT command lets you put your picture anywhere in your screen. If the raster parameters are 0,0,0,0 then the whole screen will be used for graphic display. It is desirable if you want to test the graph on medium resolution and display the final graph on low resolution. Examples: VIEWPORT 0,0,0,0 VIEWPORT 0,0,350,120 SCALE: _____ Syntax: SCALE xmin, ymin, xmax, ymax Description: SCALE setup the scaling factors which are used for mapping Cartesian coordinate data to Raster coordinate pixels. xmin,ymin specifies the left bottom-corner coordinate and xmax,ymax the right top-corner. You should make the scale a little bit larger than what you want to leave some spaces for texts. CLEAR: _____ Syntax: CLEAR Description: This command clears the screen specified by WINDOW or VIEWPORT commands. EXITGRAPH: _________ Syntax: EXITGRAPH Description: WINDOW and VIEWPORT commands will change the clipping area. EXITGRAPH resets the clipping area to the current window and refreshes it (sometimes your graph may overwrite part of the current window). Page 61 SHEET User manual By Chor-ming Lung October 9, 1988 Line/Dot commands: _________________ Each line posseses some attributes. It has color, width and its type. A line can be a solid, or broken line. This is the type of a line and there are 6 line types defined. A dot has color and width. Instead of dot, sometimes you may want to display your data using markers. There are 6 different markers defined in SHEET BASIC graphic commands. SHEET BASIC is part of the spreadsheet SHEET. You may have your data stored in the spreadsheet. There are two special commands to plot data stored in cells. They are PLOT DATA and SPLINE. ATTRIBUTE: _________ Syntax ATTRIBUTE color#,width Description: ATTRIBUTE command changes the color and width of subsequent graphic commands. The default color is 2 which is red and the default width is 1. LINE: ____ Syntax: LINE x1,y1,x2,y2[,linetype] Description: This command draws a line from (x1,y1) to (x2,y2). If no type specified, it is supposed to be solid line. The parameter type is a number ranging from 1 to 6. linetype: 1: solid line 2: long dashes 3: dots 4: dash-dots 5: dashes 6: dash-dot-dot LINE_TO: _______ Syntax: LINE_TO x1,y1[,type] Description: This command draws a line from the previous position to (x1,y1). The last line/dot commands specified the previous position. If the program is newly executed, the previous position is (0,0). Page 62 SHEET User manual By Chor-ming Lung October 9, 1988 PLOT: ____ Syntax: PLOT x,y[,marker#] Description: If no marker number specified, PLOT command will draw a dot on the position (x,y). The markers chosen for SHEET BASIC are model after Lotus 123. MARKER: 1: square 2: + 3: diamond 4: up-triangle 5: x 6: down-triangle PLOT DATA: _________ Syntax: PLOT DATA xrange, yrange[,marker#, linetype] Description: The data are stored in the spreadsheet. PLOT DATA accesses x,y pairs from the xrange and yrange specified. It will plot data from the first row to the last row in the range. Please refers to PLOT for marker number and LINE for linetype. NOTE: if your data are quite dense, the linetype other than 1 may not break the line the way you specified. SPLINE ______ Syntax: SPLINE xrange, yrange,increment Description: Given n points (knots), mathematicians can find n- 1 different cubic polynomials that pass through the n _________ points. This curve generated by those polynomials is called spline-curve. It is a smooth curve and it may represent the data more closely than connecting the points with lines. Increment should be smaller than any subinterval of the n points. NOTE: To use SPLINE, you NOTE: To use SPLINE, you must provide at least 4 points. must provide at least 4 points. Page 63 SHEET User manual By Chor-ming Lung October 9, 1988 Turtlegraphics: ______________ Turtlegraphics is quite interesting. I add these commands just for fun. Only three commands are provided though. FORWARD: _______ Syntax: FORWARD distance Description: FORWARD command in LOGO uses pixel as measurement for distance. SHEET BASIC uses the scale factors specified by SCALE as measurement for distance. This command moves the drawing pen specified by the parameter distance. Distance can be negative which make FORWARD acts as BACK in LOGO. Example: FORWARD 5.2 TURN, TURN_TO: ____ _______ Syntax: TURN degree TURN_TO degree Description: They are the only commands in SHEET BASIC using degrees instead of radians. Degree 0 points to North. East is 90 degree, South is 180 and West is 270. TURN turns the active turtle by the amount specified in rotation. TURN is similar to LEFT if the degree is positive and RIGHT if the degree is negative. TURN_TO is similar to SETHEADING in LOGO, which sets the direction of the active turtle to a specified angle. Page 64 SHEET User manual By Chor-ming Lung October 9, 1988 Axis command: ____________ It is easy to draw the x-axis and y-axis by using LINE command, but you may also want to know where x=3 on the x-axis. MARKX and MARKY commands help you to mark your axes with measurement. LABELX and LABELY let you add the number 3 under the mark. MARKX/MARKY: ___________ Syntax: MARKX y_origin, xmin, xmax, xinc, xstep MARKY x_origin, ymin, ymax, yinc, ystep Description: An example may help me to explain better. Suppose that you are drawing an x-y graph for your income from 1970 to 1988 and your income ranges from 12,000 to 40,000. SCALE 1969, 0, 1989, 45000 Making the x-axis one year larger on both sides give you some blank spaces. You do not care about amount smaller than 12,000. The following command draws the x-axis. LINE 1969,12000,1989,12000 Your y_origin starts from 12000. xmin is 1970 and xmax is 1988. You want to mark each year and have a bigger mark on each five year. Here is the command: MARKX 12000,1970,1988,1,5 MARKY works similarly. NOTE: xstep should be a multiple of xinc. LABELX/LABELY: _____________ Syntax: LABELX y_origin, xmin, xmax, xinc, format LABELY x_origin, ymin, ymax, yinc, format Description: I am using the same example on MARKX. Now, you want to add the year 1970 to 1988 on your graph. The command to do this is: LABELX 12000,1970,1988,1,"f0" You may find some of the numbers are overlapped. Then you should adjust xinc. Format is a string that specified what kind of format we are going to use for displaying the labels. Currently, there are two formats supported by LABELX and LABELY. They are FIXED and SCIENTIFIC. The string starts with an f or F means the labels are FIXED number. The number following f/F is the decimal place. A scientific format starts with an s/S. The format string ("f0") on the example means that we are displaying integers. Page 65 SHEET User manual By Chor-ming Lung October 9, 1988 Text command: ____________ PTEXT: _____ Syntax: PTEXT x,y,string[,color, effects, rotation, height] Description: PTEXT plots string on the location (x,y). There are some special effects: 1: bold face 2: light 4: italic 8: underline 16: outline 32: shadowed If you want to use more than one special effects, you can do that by adding the number for each effects. For example, if you want to plot the string with bold face and underline, you should use 9 (1+8) for the effects. Rotation changes the direction the characters being displayed. 0: East 900: North 1800: West 2700: South Height of a font may be changed but you may not get a good result. The following height numbers provide the best result for small and normal fonts. Color monitor: small font: 4 normal font: 6 Monochrome monitor: small font: 6 normal font: 13 Page 66 SHEET User manual By Chor-ming Lung October 9, 1988 Miscellaneous: _____________ MOUSE_POS: _________ Syntax: MOUSE_POS x,y Description: x,y should be variables or cell references. MOUSE_POS will wait for you to press the left button on your mouse. If your mouse cursor is within the block specified by WINDOW or VIEWPORT, then the Cartesian of the mouse cursor will be stored on x,y. Otherwise, the Raster coordinate is stored on x,y. This command is useful for finding the value for WINDOW, VIEWPORT or PTEXT. It is also useful to find some data points from your graph. Hints: Mouse click response time varies not in a constant way (refer to problems part). You may better add a WAIT ____ 1 statement after the MOUSE_POS. So, user can press the _ button a little bit longer without causing problem. Also, MOUSE_POS will keep polling until you press your mouse button. It means your system is suspended for mouse click. You cannot break its operation by pressing . You have better add an ALERT or MESSAGE telling user to press the mouse button. Page 67 SHEET User manual By Chor-ming Lung October 9, 1988 Tips on using SHEET BASIC Interrupt: Interrupt You can stop your BASIC program by pressing key. Using previous row format: Using previous row format If your BASIC program generates a lot of data to the worksheet, you should choose "Using previous format" from menu item "Options" to non. It will speed up a little of your BASIC program. Error: Error When the BASIC interpreter encounters error, it will tell you what is wrong and where the offending code is. You can use F2 to edit that line and re-run it again. If your offending cell is for example G12, instead of re-run it, you can use "@goto g12" to continue your program. It may save you some time. Multiple statements: Multiple statements You can enter more than one statement in a cell. Each statement must be separated by colon. Entering multiple statements in one cell will save some memory, but don't enter too many because your editing buffer can hold 250 characters only. Garbled screen: Garbled screen If you output some data to your monitor, your current screen display will be garbled. In case you forget to save your screen before you print, you can rebuild your display by the following steps: - Select the menu bar and choose "About SHEET" - Press return when the dialog shown up. - If the number of windows being opened is less than 4, you can choose "New" to open up a new worksheet and then close it. - If the number is 4, then you must move your window out of the screen and move it back. Adjust: Adjust When I wrote the first SHEET BASIC program, I felt it is clumsy to enter "!" at the beginning of each statement and I forgot to enter "!" in some cells. If you have the same problems, you can use the following technique to enter BASIC statement. As we know, each BASIC statement starts with an alpha letter. SHEET will treat any cell entry starts with an alpha letter as TEXT string. You can enter your BASIC codes as text string and then adjust them to BASIC statements. The steps to adjust codes: - select blocks to cover the desired cells - Press and choose "BASIC (!)" to adjust the text string to BASIC statements. Page 68 SHEET User manual By Chor-ming Lung October 9, 1988 Appendix A BUILT-IN FUNCTIONS _____________________________ Math: ____ General functions: ABS(x): Absolute function of x INT(x): Integer part of x (e.g. +int(12.4) = 12) FRAC(x): Fraction part of x (e.g. +frac(12.4) = 0.4) FACT(x): Factorial of x. X < 34 SQR(x): Square function of x SQRT(x): Square root of x Trigo & logarithm functions: SIN(x): Sine function of x. X is measured in radian COS(x): Cosine function of x. TAN(x): Tangent function of x. ASIN(x): Arc sine function of x ACOS(x): Arc cosine function of x ATAN(x): Arc tangent of x LOG(x): Logarithm base 10 LN(x): Logarithm base e EXP(x): Exponent of x RAD_DEG(x): Radian to degree DEG_RAD(x): Degree to radian DATE functions: DATE(mm,dd,yy): Function for calculating Julian days. DAY(x): Day of x. X is Julian day MONTH(x): Month of x YEAR(x): Year of x NOTE: 1. You can use the following formula to find the weekday of the date: (date(mm,dd,yy)+1) mod 7 0 if Sunday, 1 if Monday and so on. 2. You can use the following formula to find the number of days on a specific month. case 1: mm<12 date(mm+1,1,yy)-date(mm,1,yy) case 2: mm=12 date(1,1,yy+1)-date(mm,1,yy) Matrix functions: A matrix in a worksheet is a block embraced by "[" and "]". For example, to make A1..C3 as a matrix, it should be [A1..C3]. Versions before 2.0 of SHEET restrict individual matrix size within 64K. This limit has been removed. Matrix operators include *,+ and -. There is no priority for those matrix operators. Formula is calculated from left to right. Parenthesis can be used to change the calculation order. There are only two matrix functions. They are INVERSE and TRANSPOSE. Their arguments must be a matrix or a matrix operations grouped by parenthesis. The sequence of activating matrix operations is: Page 69 SHEET User manual By Chor-ming Lung October 9, 1988 MATRIX(cell, matrix_operations) Cell: The cell reference of the MATRIX is the leading location where matrix operations put their result. Matrix_operations: Matrix operations can be matrix functions or combination of matrix operators. For example, to store the inverse of matrix [a1..c3] in matrix [d1..f3], you should store the following formula somewhere: +MATRIX(d1,inverse[a1..c3]) Statistics: __________ AVERAGE( any number of parameters ): Average of parameters. Parameters can be a single cell reference, an expression, a block of cells. MEDIAN( any number of parameters ): Median of parameters. STD( any number of parameters): Standard deviation of parameters. VAR( any number of parameters): Variance of parameters. SQR_SUM( any number of parameters): Square sum of parameters. eg. +AVERAGE(A1..F4,234,12+24.5,H2+34*sin(pi)) Miscellaneous: _____________ MAX(any number of parameters): Maximum value of parameters. MIN(any number of parameters): Minimum value of parameters. SUM(any number of parameters): Sum of all parameters. RAND: Random number between 0 and 1. IFF(condition,true_value, false_value): IF function ISEMPTY(cell): return 1 if cell is empty, otherwise 0 TRUE: it is equal to 1 FALSE: it is equal to 0 ALERT ALERT(default, "[icon#][text][exit]") Uses the alert box to get user's response. icon#: 0: no icon 1: NOTE icon (!) 2: WAIT icon (?) 3: STOP icon (stop sign) text: text string can be up to 5 lines. Each line is separated by "|". You cannot have more than 40 characters per line. If you use icon, then the number of character per line must be lesser than 40. Keep your character per line less than 30 will be safe. exit: Up to 3 exit button can be defined. Each of them is separated by "|". eg. !A3=ALERT(1,"[1][This is a test|Wow][Ok|Cancel]") Page 70 SHEET User manual By Chor-ming Lung October 9, 1988 COL_WIDTH COL_WIDTH(block) Returns the total column widths that holds the block. For example, if column width of A is 8 and B is 12 and C is 4: eg COL_WIDTH(a1) ==> 8 COL_WIDTH(b1..c1) ==> 16 LAST_ROW LAST_ROW(cell,r) Returns the value stores in the last row on the column the cell is. r tells you where the last row is. r is zero origin. For example, if the last row of column B is 12 and cell B12 has a value of 123.45: eg LAST_ROW(b20,row) ==> 123.45 and row=11 You can access the last cell by using B1[0,row] LAST_COL LAST_COL(cell,c) Returns the value stores in the last column on the row the cell is. c tells you where the last column is. c is zero origin. For example, if the last column of row 4 is H and cell H4 has a value of 456.77: eg LAST_COL(a4,col) ==> 456.77 and col=7 You can access the last cell by using a4[col,0] SEARCH SEARCH(STRING,block,r,c) A boolean function to find out whether STRING in the block and r,c tell you where the cell is. r and c are zero origin so you can access the cell by using a1[c,r]. NOTE: the comparison is case insensitive and a cell of the block which has a substring includes STRING, then the location of that cell will be stored in r and c. If search fails, r and c will not be changed. eg. Assume that cell A5$="What",b5$="is",c5$="your", d5$="name?". SEARCH("You",a5..d5,r,c) ==> 1 and r=4, c=2. To access the cell that holds a substring of "YOU", you should use a1[c,r]$ (it is c5$ in this case). Finance: _______ Future growth: GRATE(a time series data): Average growth rate. GBASE(a time series data): Adjusted base value GFUTURE(grate,gbase, predict time): Future values based on grate and gbase A time series data is data being collected in a fixed frequency of time. They may be gathered yearly, quarterly, monthly or daily. For example, you income is a time series data that is increased yearly. For a more detail example, refer to the example file "FG.SHT" Investment: INVEST_PV(final value, interest, compound frequency, terms): Present value of investment Page 71 SHEET User manual By Chor-ming Lung October 9, 1988 INVEST_FV(present value, interest, compound frequency, terms): Future value of investment INVEST_INTEREST(present value, final value, compound frequency, terms):Compound interest of investment INVEST_TERM(present value, final value, interest, compound frequency): Terms of investment. Please refer to the file "INVEST.SHT" for using those functions. Loan: LOAN_PAY(amount borrowed, interest,total terms, number of terms per year): Payment for each term. LOAN_AMOUNT(payment, interest, total terms, terms per year): Amount borrowed. LOAN_TERM(amount borrowed, payment, interest, terms per year): Total number of terms for loan. Please refer to the file "LOAN.SHT" for using those functions. Withdraw: WDRAW_SAVE(amount withdrawn, interest, terms, terms per year): Initial Investment WDRAW_AMOUNT(amount saved, interest, terms, terms per year): Amount withdrawn. WDRAW_TERM(amount saved, amount withdrawn, interest, terms per year): Total terms Please refer to the file "WITHDRAW.SHT" for using those functions. Deposit: DEPOSIT_FV(amount, interest, total terms, terms per year): Final value for deposit DEPOSIT_AMOUNT(final value, interest, total terms, terms per year): Regular deposit DEPOSIT_TERM(amount, final value, interest, terms per year): Total number of terms. Please refer to the file "DEPOSIT.SHT" for using those functions. String functions: _________________ FILENAME$ FILENAME$ is a string variable that can only be modified by file selector dialog box. BUF$ BUF$ is a string variable that can be changed by the command INPUT only. MID$: MID$(STRING,s,len) Returns a substring of STRING, starting from s with Page 72 SHEET User manual By Chor-ming Lung October 9, 1988 length len. If s is larger than the length of the string STRING, then a substring range error occur. If len+s is larger than the length of STRING, then up to the last character will be returned. eg MID$("12345678",4,3) ==> "456" MID$("12345678",14,3) ==> substring range error MID$("12345678",4,20) ==> "45678" LEFT$: LEFT$(STRING,len) Returns a substring of STRING, starting from 1 with length len. If len is larger than the actual length of STRING, the whole STRING will be returned. eg LEFT$("12345678",4) ==> "1234" LEFT$("12345678",20) ==> "12345678" RIGHT$: RIGHT$(STRING,len) Returns a substring of STRING, from the far right portion of STRING with length len. If len is larger than the length of STRING, then substring range error occurs. eg. RIGHT$("12345678",4) ==> "5678" RIGHT$("12345678",20) ==> substring range error SPACE$: SPACE$(num) Returns a string with num of blank spaces. num must be a positive integer. eg. SPACE$(4) ==> " " CHR$: CHR$(num) Returns a string with one character whose ASCII number is equal to num. num is in the range of 0 to 255. eg CHR$(82) ==> "R" STR$: STR$([FORMAT,]value) Changes a number value to a string equivalent to that value. FORMAT is optional. It is a string which consists of three part -- width, format, decimal point. It is equivalent to return a cell string with column width, format and decimal place. eg STR$(1234.5) ==> "1234.5" STR$("12$2",1234.5) ==> " $1234.50" VAL: VAL(STRING) Returns the value of STRING. This function will strip off characters other than "E", digits, "+", "-" and "." first and do the translation later. eg VAL("abc123.45") ==> 123.45 VAL(".23e-2") ==> 0.0023 VAL(".23ae-2") ==> 0.0023 LEN: LEN(STRING) Returns the length of the STRING. An empty string has a len of zero. eg LEN("") ==> 0 LEN("123456") ==> 6 Page 73 SHEET User manual By Chor-ming Lung October 9, 1988 POS: POS(STRING1,STRING2,start) Return the position of any character of STRING1 from STRING2 beginning from start. All the character of STRING1 and STRING2 will be changed to upper case first. A zero will be returned if none has been found or start is larger than the len of STRING2. eg POS(" ", "What is this?",1)==> 5 A space in position 5. POS("ts","What is this?",1)==>4 "t" is in position 4 POS("ts","What is this?",5)==>7 "s" is in position 7 POS("AT","What is this?",1)==>3 "A" is in position 3. Case insensitive. POS(",-","What is this?",1)==>0 Appendix B CONTROL KEYS _______________________ Non-editing: ___________ stops a running BASIC program. moves cell cursor one row up. moves cell cursor one row down. moves cell cursor one column left. moves cell cursor one column right. moves cell cursor to the direction specified by the arrow key by page. changes the editing direction to neutral. adjusts TEXT string to BASIC statements or text justification. creates a new sheet loads a sheet file saves current spreadsheet

prints current spreadsheet exits to desktop inserts a row or a column deletes a row or a column reformats cells erases cells copies cells moves cells defines the starting cell of a block defines the stopping cell of a block <1> switches to the first window <2> switches to the second window <3> switches to the third window <4> switches to the fourth window refreshes the current window. recalculates the spreadsheet by column recalculates the spreadsheet by natural order recalculates the spreadsheet by row Page 74 SHEET User manual By Chor-ming Lung October 9, 1988 modifies the current cell's content Goto a specified cell. Editing: _______ terminates and moves cell cursor one row up. The editing direction changes to up direction. terminates and moves cell cursor one row down. The editing direction changes to down. terminates and moves cell cursor one column left. The editing direction changes to left. terminates and moves cell cursor one column right. The editing direction changes to right. moves editing cursor to left. moves editing cursor to right. terminates and moves cell cursor to the direction specified by editing direction. if the mouse cursor is within working area, the coordinate of the cell under mouse cursor will be inserted into editing buffer; if the mouse cursor is within the editing line, the editing cursor will move to the character under the mouse cursor. if the mouse cursor is within working area, a comma and the coordinate of the cell under mouse cursor will be inserted into editing buffer. if the mouse cursor is within working area, a through symbol ".." and the coordinate of the cell under mouse cursor will be inserted into editing buffer. Appendix C Program specification: ________________________________ SHEET is written in Mark Williams C. The accuracy for real number (double) in Mark Williams C is up to 17 digits (Lotus 123 is up to 15 digits). The range of calculation is -1E-37 to 1E+37. Each spreadsheet can accomdate up to 2,549,745 (255*9999) cells and the spreadsheet is implemented by using Sparse Matrix. Any input to a cell will be tokenized before it is being inserted. This program support up to 4 windows. The BASIC interpreter can be used to handle graphics, string, and ASCII text files. Accuracy: Accuracy up to 17 digits Range: Range -1E-37 to 1E+37 (If you find 1e+37 in a cell, that means the calculation for that cell is not correct) Arithmetic operators: Arithmetic operators "+", "-", "*", "/", "^", MOD Logical operators: Logical operators: AND, OR, NOT, "=", "<>", ">", "<", ">=", "<=", "=>", "=<" Built-In functions: Built-In functions Page 75 SHEET User manual By Chor-ming Lung October 9, 1988 Math: SIN, COS, TAN, ASIN, ACOS, ATAN, LOG, EXP, LN, ABS, INT, FRAC, SQR, SQRT, DATE, FACT, RAD_DEG, DEG_RAD, PI (constant) Statistics: AVERAGE, MEDIAN, STD, VAR, SQR_SUM Miscellaneous: MAX, MIN, SUM, COUNT, TODAY, DAY, MONTH, YEAR, TRUE, FALSE (TRUE and FALSE are constant, TRUE is 1 and FALSE is 0), IFF, ISEMPTY, ALERT, LAST_ROW, LAST_COL, SEARCH. Finance: GRATE, GBASE, GFUTURE, INVEST_PV, INVEST_FV, INVEST_INTEREST, INVEST_TERM, LOAN_PAY, LOAN_AMOUNT, LOAN_TERM, WDRAW_SAVE, WDRAW_AMOUNT, WDRAW_TERM, DEPOSIT_FV, DEPOSIT_AMOUNT, DEPOSIT_TERM String: MID$, CHR$, LEFT$, RIGHT$, SPACE$, STR$, VAL, LEN, ASC, FILENAME$(a string variable used for file item selector) and BUF$(a string variable used by the command INPUT). Matrix: MATRIX, TRANSPOSE, INVERSE, *, +, - Priority: Priority Lowest: - AND, OR - "+", "-" - "*", "/", "^", MOD - number, cell reference, variable, function call, NOT, "+", "-" (unary operator) Highest:- Parenthesis Constants: Constants PI(0.31415926535897932e+01), TODAY(Julian days when you press TODAY), TRUE(1), FALSE(0). Cell reference: A cell can be referred by using its spreadsheet Cell reference coordinate (e.g. D1) If you add a "$" in front of the row number (in this example 1) or in front of the column letter (D) then you make it an absolute reference. If you add "$" at the end of the cell reference, then you are using it as a string. You can use a cell as BASE and move it vertically or horizontally by appending two numbers which are separated by comma and embraced by "[" and "]". The first number increases the BASE horizontally and the second number increases the BASE vertically. For example A2[3,4]. You can separate A2 to [1,2] (Column A has a value of 1, B has 2 and so on). A2[3,4] = [1,2]+[3,4]=[4,6]=D6. Variables: Variables Up to 150 variables can be defined. This number includes the user defined function. It is intended for BASIC usage. For the time being, you cannot use those variables to hold any string. Appendix D Lotus and SHEET __________________________ Page 76 SHEET User manual By Chor-ming Lung October 9, 1988 In Lotus, you must add a '@' symbol before every function name. In SHEET, no special symbol is required to put before the function name. However, if a function name is the first element of your formula, a '+' or '(' is required to differentiate it from TEXT. Lotus requires you to put # symbol to embrace the logical operators. For example , "+A1 #AND# A2". It is invalid in SHEET. Actually, you will get 2 bombs if you enter the above formula. You should avoid adding special character in your formula. The valid form is "+A1 AND A2". Lotus @COUNT function will count any cell containing a numeric value or a label. SHEET will count only numeric value. For example, A _ B _ 1 _ 12 This is a test @COUNT(A1..B1) will return 2 and COUNT(A1..B1) will return 1. Lotus @DATE function asks for arguments (YYYY,MM,DD) and DATE function in SHEET asks for (MM,DD,YY). The first day of @DATE is 1-Jan-1900 while the first day of DATE is 1-Jan-4712 B.C. Here is a list of Lotus functions which have equivalent SHEET functions but using different names. @FV(amount,interest,terms) = DEPOSIT_FV(amount,interest,terms,1) @RATE(fv,pv,term) = INVEST_INTEREST(pv,fv,12,term*12) @TERM(pmt,int,fv) = DEPOSIT_TERM(pmt,fv,int,12) @PMT(amount,interest,terms) = LOAN_PAY(amount,interest,terms,12) Appendix E Recalculation ________________________ Natural: Natural Recalculates each cell in the dependent order. NOTE: some spreadsheet programs use dependent list to find the dependent order and they can quickly recalculate those cells being affected. SHEET uses recursive method to recalculates cells. The effect is that, it blindly recalculate every cell which contains formula and it is slower than using dependent list. The positive side is that, you have more memory to use than those spreadsheet programs. This is a trade-off between speed and space. Row: Row: Recalculate each row in the ascending order. Column: Column: Recalculate each column in the ascending order. Manual: Manual The spreadsheet stops automatic recalculation. User has to press , or for natural, row or column recalculation, where means press key with character key simultaneously. Note: It is no longer true that natural recalculation is slower than row or column recalculation. Page 77 SHEET User manual By Chor-ming Lung October 9, 1988 Appendix F FILE FORMAT ______________________ Header for SHT: Header for SHT - 270 bytes reserved data. Currently, there are a few bytes have been used. - 2 bytes for file version number, current version is 1.4 - 1 bytes for screen type when the file saved. 1 for color monitor and 0 for monochrome monitor. - 2 bytes for maximum row number. - 40 bytes for window title. (39 characters with a null character) - 2 bytes: check-sum of the window title to make sure we are handling the right work sheet. - 256 bytes for width of each columns - 2 bytes for window type (It is useless) - 8 bytes for current window size - 4 bytes for working area top row, top column - 4 bytes for current cell cursor address - 2 bytes for current recalculation mode Cell contents for SHT format: Cell contents for SHT format - 1 bytes for cell type (use this byte with 0x23 to do bit-wise and. If it is not 0, then it is NUMBER). - 2 bytes for length - IF NUMBER: 2 bytes for row #, 1 byte for column #. 2 bytes for attribute and 1 byte for decimal place 8 bytes for calculating result. - IF NOT NUMBER (it may be TEXT or STATEMENT, its decimal place and calculating result are not needed) 2 bytes for row#, 1 byte for column #. 2 bytes for attribute (it may be blank cell) - IF length <> 0, length bytes of tokens. - The last cell type and length are 0 and 0. Header for PRT: Header for PRT - 4 bytes holding "PART" Cell contents for PART format: Cell contents for PART format - 4 bytes for cell row, column - 2 bytes for display attribute - 2 bytes for decimal place - 2 bytes for tokens length - 8 bytes for calculated result - tokens length bytes of actual tokens Appendix G Problems ___________________ A user told me his cell cursor keep flashing ans: It won't happen very often. It seems it happens when the system is newly bootup and you load SHEET right away. In Page 78 SHEET User manual By Chor-ming Lung October 9, 1988 SHEET, you can locate the cell cursor by mouse click. You can also select a block by prolonging your mouse click (drag). When you press your mouse to move the cell cursor, SHEET first moves your cell cursor to that position and inverts the color of that cell. Mouse response time seems a lot faster when the system is newly bootup. Because of the fast response time, SHEET thinks you are still pressing your mouse button. It thinks you are trying to select a block then. That is the reason why your cell cursor on and off. In case you have this problem, you should choose some of the help dialogs. 4 or 5 of them should slow down your mouse response time. Another method found by a user is provided here. After the system is booted, you type a key and then load your SHEET program. Why some of my input characters are being truncated? ans: For versions before 2.0, if you type any illegal character in your formula, you will be bombed out and you must reboot your system to use SHEET. This new version of SHEET prevents this problem but the rest characters after the illegal character will be truncated and a "#" symbol will be added at the end of the illegal formula. You should rewrite your formula. Page 79 SHEET User manual By Chor-ming Lung October 9, 1988 Biorthym program ________________ A German doctor found that our emotional, physical and intellectual conditions change in sine curves with different frequencies. Emotional condition changes in 23 days per cycle, physical 28 days, and intellectual 33 days. They are called biorthym. The following SHEET BASIC program uses the data found to generate the biorthym curves for you. You should enter your name in cell B1 and your birthday in B2 using +DATE(mm,dd,yy), then you can run it by the command "@run e1". E1 : !B3= TODAY E2 : !B4=B3-B2+1:START= DATE( MONTH( TODAY),1, YEAR( TODAY))-B2 E3 : ! IF B3<0 THEN MESSAGE "Date entry error": END E4 : !EMOTION=START MOD 23 E5 : !PHYSICAL=START MOD 28 E6 : !INTELLECTUAL=START MOD 33 E7 : ! WINDOW 1,0,11,417,199 E8 : ! SCALE -4,-1.5,33,1.1 E9 : ! CLEAR :EC=2*PI/23:PC=2*PI/28:IC=2*PI/33 E10 : ! FOR X=1 TO 31 E11 : !Z1=EMOTION*EC:EMOTION=EMOTION+1 E12 : !Z2=PHYSICAL*PC:PHYSICAL=PHYSICAL+1 E13 : !Z3=INTELLECTUAL*IC:INTELLECTUAL=INTELLECTUAL+1 E14 : ! PLOT X, SIN(Z1),1: PLOT X, SIN(Z2),2: PLOT X, SIN(Z3),3 E15 : ! NEXT X E16 : ! LINE 1,0,31,0 E17 : ! LINE 1,-1,1,1 E18 : ! MARKX 0,1,31,1 E19 : ! MARKY 1,-1,1,0.1,0 E20 : ! LABELX 0,2,30,2,"f0" E21 : ! LABELY 1,-1,1,0.1,"f1" E22 : ! PTEXT 11,-1.2,B1,1,1 E23 : ! PLOT -2,-1.35,1: PTEXT -1,-1.4,"Emotional" E24 : ! PLOT 8,-1.35,2: PTEXT 9,-1.4,"Physical" E25 : ! PLOT 20,-1.35,3: PTEXT 21,-1.4,"Intellectual" E26 : ! EXITGRAPH E27 : ! END Explanations: E1: Set the current date on cell B3. E2: B4 holds the days of your life. Variable START holds the days from your birth to the first day of current month. The plus 1 in calculating B4 means that your first day is counted as 1. But biorthym calculates the first day as 0. (Sine curve starts from 0 to 2*PI). E3: This is unnecessary. I just want to show you how to display a message. E4: Since our emotional condition changes in a cycle of 23 days, we can calculate the condition of the first day of current month by using the operator MOD. E5: Similar to E4 E6: Similar to E4 Page 80 SHEET User manual By Chor-ming Lung October 9, 1988 E7: Setup our graphic window E8: Setup the scaling factor. Making the scaling factors a little larger than what they actually are will provide some blank spaces for texts. E9: Clear the window area. EC is emotional constant. Emotional condition changes in 23 days per cycle. Each cycle is 2*PI long. PC for physical condition and IC for intellectual condition. E10: A FOR loop to draw your graph. I just assume each month have 31 days. You may want to change 31 to its actual days. E11~E13: Simple calculation for your conditions. E14: Plot emotional condition with marker 1, physical with marker 2, and intellectual with marker 3 E16~E17: Plot the x-axis and y-axis E18: Draw marks on x-axis E19: Draw marks on y-axis E20: Draw label on x-axis. It is integer. E21: Draw label on y-axis. It is real with 1 decimal place. E22: Plot your name on screen. (B1 holds your name) E23~E25: Plot texts showing the meaning for each marker. E26: Legal way to return to your current window E27: END program. Page 81 SHEET User manual By Chor-ming Lung October 9, 1988 Calendar program ________________ The file name of this program is "CAL.SHT". This program generates a month calendar and leaves some spaces for your monthly activity. Here is the listing. To run it, enter "@run q1" Q1 :!MON=10:YR=88:IBM= FALSE Q2 :!WEEKDAY=( DATE(MON,1,YR)+1) MOD 7 Q3 :! IF (MON<12) THEN TDAY= DATE(MON+1,1,YR)- DATE(MON,1,YR): ELSE TDAY= DATE(1,1,YR+1)- DATE(MON,1,YR) Q4 :! IF IBM THEN GOSUB Q34: ELSE GOSUB Q42 Q5 :! ERASE A1..O64 Q6 :!B3$="^Sun":D3$="^Mon":F3$="^Tue":H3$="^Wed" Q7 :!J3$="^Thu":L3$="^Fri":N3$="^Sat" Q8 :!LINES=(WEEKDAY+TDAY)/7 Q9 :!LINES= INT(LINES)+( FRAC(LINES)>0) Q10 :!WIDTH=60/LINES Q11 :! FOR I=0 TO 62 Q12 :! FOR J=0 TO 14 STEP 2:A2[J,I]$= CHR$(VERT): NEXT J Q13 :! NEXT I Q14 :! FOR I=0 TO 12 Q15 :! IF (I MOD 2)=0 THEN B2[I,0]$="\"+ CHR$(HOR):B4[I,0]$="\"+ CHR$(HOR): ELSE B2[I,0]$="\"+ CHR$(MDOWN):B4[I,0]$="\"+ CHR$(PLUS) Q16 :! NEXT I Q17 :!A2$= CHR$(TLEFT):O2$= CHR$(TRIGHT):A4$= CHR$(PLEFT):O4$= CHR$(PRIGHT) Q18 :! FOR I=1 TO LINES Q19 :!L=I*WIDTH: COPY A4..O4,A4[0,L] Q20 :! NEXT I Q21 :! FOR J=0 TO 12 STEP 2:C64[J,0]$= CHR$(MUP): NEXT J Q22 :!A64$= CHR$(BLEFT):O64$= CHR$(BRIGHT) Q23 :!L=1:J=1:M=WEEKDAY*2 Q24 :! FOR I=WEEKDAY TO WEEKDAY+TDAY+1 Q25 :! IF (I MOD 7)=0 THEN L=L+WIDTH:M=0 Q26 :!B4[M,L]=J:J=J+1:M=M+2: IF J=TDAY THEN I=42 Q27 :! NEXT I Q28 :! SAVE SCREEN : REDIRECT TO "prn:" Q29 :! PRINT SPACE$((80- LEN(V1$))/2);V1$: PRINT Q30 :! PRINT_BLOCK A2..O64,4,76 Q31 :! WAIT 2: RESTORE SCREEN Q32 :! END Q34 :!MDOWN=194:MUP=193 Q35 :!PLEFT=195:PRIGHT=180 Q36 :!PLUS=197:VERT=179:HOR=196 Q37 :!TLEFT=218:TRIGHT=191 Q38 :!BLEFT=192:BRIGHT=217 Q39 :! RETURN Q42 :!MDOWN=45:MUP=45 Q43 :!PLEFT=124:PRIGHT=124 Q44 :!PLUS=43:VERT=124:HOR=45 Q45 :!TLEFT=43:TRIGHT=43 Q46 :!BLEFT=43:BRIGHT=43 Q47 :! RETURN Page 82 SHEET User manual By Chor-ming Lung October 9, 1988 Explanation: Explanation Q1 Set the month and year we interest about in the variable mon and yr. You cannot use month and year for the variable names because they are names for built-in functions. IBM is TRUE if your printer can print the IBM graphic characters. If you change the month and year in cell Q1, please change the string in cell V1 at the same time. Q2: WEEKDAY stores the weekday of the first day of the month. WEEKDAY is 0 if it is Sunday and 1 for Monday and so on. Q3: TDAY is the total number of days in the specified month. It can be calculated easily by subtracting the first date of one month after mon from the first date of mon. Q4: Initializes some constants depend on whether IBM is TRUE or FALSE. Q5: Erase cells from A1 through O64 Q6: Row 2 and 4 holds the frame for the title of weekdays. Row 3 holds the title. Q8: Normally, we need 4, 5 or 6 lines to hold a monthly calendar. To calculate how many lines are need for our current month, we can do it by adding the WEEKDAY and TDAY and divides it by 7. Q9: This is a little bit tricky. FRAC is a function returns the fractional part of a numerical value. In this case, LINES from Q8 is equal to 5.285714. FRAC(LINES) returns 0.285714. It is greater than zero. TRUE is 1 and 0 is FALSE. (FRAC(LINES)>0) returns a value of 1 and the INT(LINES) returns a value of 5. So we need 6 lines to hold our current monthly calendar. Q10: The width for each line is 60/LINES. Since the value of LINES can be 4,5 or 6. So, width is an integer. Q11~Q22 Fills the frame for our calendar. TLEFT, is the TOP left- corner, TRIGHT is the TOP right-corner, BLEFT is the Bottom left- corner and BRIGHT is the Bottom right-corner. VER is a vertical bar, HOR is a horizontal bar. PLEFT is a T character turns -90 degree and PRIGHT is a T character turns 90 degrees. MDOWN is a T character and MUP is a T character turns 180 degrees. They are used to join lines in the edges. PLUS is a + character. Q23~Q27 Fills the Date into the calendar. Q28~Q31 prints the calendar to your printer. Q32: You should always end your program with END. Q34~Q39: Defines constants for IBM=TRUE Q42~Q47: Defines constants for IBM=FALSE Page 83 SHEET User manual By Chor-ming Lung October 9, 1988 Roots _____ ROOTS.SHT is a sample program which finds roots for any single variable functions. There are some techniques which are not covered in other sample programs. You should study it even you have no interest in finding roots for a function. B1 : !XMIN=-5:YMIN=-5:XMAX=5:YMAX=100:ST=0.1:ERR=0.000001 B2 : ! DEFINE F(X)=3*X*X-2 B3 : ! IF ALERT(2,"[0][Which resolution you are in?][Medium|High]")=2 THEN WINDOW 0,0,20,639,399: ELSE WINDOW 0,0,11,639,199 B4 : ! SCALE XMIN,YMIN,XMAX,YMAX B5 : ! CLEAR B6 : ! PLOT XMIN,F(XMIN) B7 : ! FOR X=XMIN TO XMAX STEP ST B8 : ! LINE_TO X,F(X) B9 : ! NEXT X B10 : ! LINE XMIN,0,XMAX,0: LINE 0,YMIN,0,YMAX B11 : !X= ALERT(1,"[0][Can you see any|root in your graph?][Yes|No]") B12 : ! IF X=2 THEN MESSAGE "Change your minimum and|maximum values and try again": EXITGRAPH B13 : ! IF X=2 THEN END B14 : ! MESSAGE "Locate mouse cursor|to the left of a|root in your graph" B15 : ! MOUSE_POS AX,AY B16 : ! MESSAGE "Locate mouse cursor|to the right of a|root in your graph" B17 : ! MOUSE_POS BX,BY B18 : ! LOOP B19 : !AY=F(AX):BY=F(BX) B20 : ! EXIT IF ( ABS(AY) key will not stop the program. B18: A loop using bi-section method to find the root of a function. B24: If we found the root, we put it in cell A1 B25: Normal exit B26 End of the program. Page 85