@DATABASE Tutorial.guide @NODE MAIN "TurboCalc by Michael Friedrich" TurboCalc - copyright Michael Friedrich. @{"Main Table of Contents" link TurboCalc.guide/MAIN} of all files @{"Full Index" link Index.guide/MAIN} of all files Tutorial @{"TurboCalc-Sheets" link TurboCalcSheets} @{"Selecting a Cell" link SelectingaCell} @{"Entering a Formula" link EnteringaFormula} @{"Selecting Ranges" link SelectingRanges} @{"Filling a Range" link FillingaRange} @{"Generating a Series" link GeneratingaSeries} @{"Saving your Work" link SavingyourWork} @{"Editing" link Editing} @{"Absolute Cell References" link AbsoluteCellReferences} @{"Copying a block" link Copyingablock} @{"Functions" link Functions} @{"Formatting" link Formatting} This chapter will list up a few practical examples of the possibilities and features of TurboCalc and makes you familiar with them. Step by step an example sheet will be created and you will learn about input, formatting, formula creation and further work steps. It will be useful to sit in front of your Amiga while reading this chapter, so that you can carry out every step directly - by using this method you will have the best possible success with your introductory work. Annotation: In this Tutorial most of the functions are only "touched on" and only a few capabilities are really used. You will find a complete description of each menu item in the menu survey or in the corresponding chapters (input, formula, diagrams...). Additionally, you will find every work step as a TurboCalc-file in the " tutorial" directory on the diskette. So it is possible to leave out different steps and to try a few functions directly, although it is not recommended. (In this case, loading is done just like the below mentioned saving.) Our example sheet, which shall be created as follows, deals with a surplus calculation. By using this example, we have a good possibility to explain some of the most frequently appearing procedures. To give you a rough survey, which solution is expected and what you can do with a minimum effort, please have a look at the sheet shown below: @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut1.iff'"} Now, it is your turn: Please sit down in front of your computer and start TurboCalc. Annotation: Here, it should not be described again how to start TurboCalc. These details you will surely find in your Amiga manual. After a short loading time, the TurboCalc screen should appear with an empty sheet (named "Sheet 1"). @ENDNODE @NODE TurboCalcSheets "TurboCalc-Sheets" TurboCalc-Sheets Before you definitely start working, a short introduction to the menus of TurboCalc and a brief explanation of some expressions is given. This might be a little "exhausting", but will be useful later on for the explanation of sheets and the practical work with them. The respective examples will follow in the next paragraph. @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut2.iff'"} On top of the screen you see the menu bar. Here you can "choose" a menu item with a click on the right mouse button (to perform this, press down the mouse button and (while pressing down the button) move your mousepointer to the desired menu item and release the button there.) This way you can start all operations of TurboCalc. A little bit below you will find the "Toolbar", which represents an other possibility to start operations. To start them, simply move the mouse pointer to the desired symbol, press down the left mouse button and release it again (We will need this operation many times from now on and so we will call it "clicking" shortly). Most of the symbols should be self- explanatory. If not, you will find a short explanation of every symbol in the chapter "Toolbar". The window-title is placed between screenbar and toolbar. Here you can also find the sheetname, the window gadgets (the closing symbol on the left side to close the sheet, as well as the two gadgets on the right side, which you need to place windows "in front" or into the "background") and the following two ranges: Annotation: "Gadget" is an expression of the Amiga world and represents, in most cases, a small square area, which activates a certain operation if you click within this area. Cell Status: This short text field on the left shows the current cursor position, as well as the size of a selected range. Input Field: In the range right of it the cell contents will be entered, shown and edited. Row- and column title: Here you can see the structure of sheets in rows and columns. The rows are numbered beginning with one, the rows receive a letter, e.g. A, B, ... Z, AA, AB, ... . A cell can be located precisely by listing up its row- and column position. TurboCalc expresses this as follows: The first position has got the column letter, which is followed by the row number, e.g. A1 or F7. Such a definition is called "Cell Reference" . Cell cursor: This is the position, on which the next input or formatting should take place. It is displayed within the field "Cell Status" and you will find a colored frame around the respective cell. In a new sheet, the cursor (which is the jargon name of the frame) is located in cell A1 (on the top left). After these explanatory words, let's go back to the example sheet. @ENDNODE @NODE SelectingaCell "Selecting a Cell" Selecting a Cell The first thing to do is to enter the headline. The first text shall be written in cell B4. Therefore the cell cursor, which rests on cell A1, must be moved. To do this, please use the and press it three times and one time to the . With every keystroke the cursor should jump into the appropriate direction and the contents of the field should change accordingly.After this procedure "B4" should be displayed.. Mouse: If you prefer to work with the mouse, move the mouse pointer to cell B4 and click here. Now enter the expression . You can read the text in the "input field" on the top, as well as directly in the cell. If you made a slip you can delete the last letter with (the key above ) and enter again. If you have entered the word correctly, press . The cursor jumps one row down to cell B5. Move the cursor with the mouse or keyboard to cell C4 (one up and right) and enter "Expenditures". Do not press now! Try or (that is the key on the left side of ). The input will be ended and the cursor moves promptly to cell D4, where the next text should be entered. Please enter now, side by side "Surplus", "VAT" (i.e. Value added tax) and " Profit". You should receive the following sheet: @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut3.iff'"} We go on with the input of the date in column A. To increase the effect of learning, only the first two months are entered. The remaining months will be calculated by TurboCalc automatically. At first just move the cell cursor to cell A5 or place the cursor by mouse. Then enter "Jan 94" and press . Now, please move the cell cursor again up and have a look at your just entered text: The cell contents is nearly unchanged (if you have entered " jan" in lower case, it will automatically be changed in "Jan"), but in the "input field" you will see "01.01.1994": The entered text was not a "normal " text as before but a date. This will be saved as day, month and year but can be displayed in different ways. Because it had not been entered with a particular day, it is assumed as 1 and not displayed. To enter a date in TurboCalc is as simple as the input of text and it is possible to use (nearly) all forms of dates (e.g. 30.09.93, 30 Sep 94 or 30/9, 30 Sep...). A survey can be found in chapter "The Input". Please enter "Feb 94"in cell A6 and fill up the cells B5, C5, B6 and C6 with any number (entered like texts): @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut4.iff'"} @ENDNODE @NODE EnteringaFormula "Entering a Formula" Entering a Formula Let's leave the input of text for a while and concentrate on TurboCalc's calculation capabilities. The first formula: We would like to calculate the difference between income and expenditures. We have already entered these numbers, so the computer can work for us. Formulas generally start with an equals sign to differentiate them from normal text. After that, it follows the arithmetical expression, namely B5- C5. That results in the formula: "=B5-C5" (without quotation marks). Please enter it in cell D5 (do not forget to locate the cursor!) and press . If everything is entered correctly, the formula disappears from the cell and the correct result will appear at once. If you locate the cursor again on this cell, you will read the formula in the "input field"! Now enter the second formula in E5. This shall be the value added tax of the sum of D5: "=D5*15%". This should partly be entered with the help of the "mouse". Therefore press the equals sign key and then click the mouse in cell D5 - in the input field "=D5" is displayed now. If you enter a formula (recognizable by the leading "=") followed by a mouseclick on a certain cell, TurboCalc will display the cell reference at the current input position. It is not necessary to remember the cell reference - a simple mouse click upon it is enough. Annotation: In this example it is assumed that tax rates at 15%. If it differs in your country (or in your special application), simply change it (or just ignore the wrong tax-rate in this example, as this should not be too important). Now, please enter the rest "*15%" and then press . Enter the formula " D5-E5" (with or without mouse) in cell F5. The first data line is now completed and the following picture should appear (here together with displayed formulas). @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut5.iff'"} Now there are 11 lines left to be created - but this is the work of TurboCalc. @ENDNODE @NODE SelectingRanges "Selecting Ranges" Selecting Ranges To do this, a range must be marked first. A range (also called a block) is a rectangular area of cells. Most of the time this area is specified by the top left and the cell down on the right (separated by a colon), here it is: D5:F16. Using the keyboard: Move cursor to cell D5, press the key and hold it. Then move the cursor to cell F16 (with -key still pressed). While moving, a colored area develops, which represents the range. If it is chosen completely, the -key can be released. (Attention: If you move the cursor without -key, the block marking is canceled). Selecting a Range with mouse: Press the left mouse key on cell D5 and move the mousepointer, while holding the mouse key, to cell F16. While moving a colored range develops. The range is completely chosen when the mouse key is released. The third alternative is a mixture of the above mentioned methods: Move cursor to cell D5 (e.g. with mouse click), then move mouse pointer to cell F16, press -key and then make a mouse click on this cell while pressing the -key. Now release the -key and the mouse key (no matter in which order). However, a colored range should be seen and "F16 (3*12)" be displayed at the "cell status". @ENDNODE @NODE FillingaRange "Filling a Range" Filling a Range Now choose the menu item or +. This causes TurboCalc to fill every column of the marked range with the contents of each uppermost column. In our case the formulas are copied downwards. If you move the cursor e.g. to cell D6 you will find the formula "B6-C6" and not, as you might expected, "B5-C5" - TurboCalc has changed the cell references automatically during copying. The reason is that these references are treated as "relative cell references", but more about this later. @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut6.iff'"} (To receive the image shown above, please choose from the menu Options the item Display and there >show Formulas instead of Values< to display the formulas and not their results; otherwise only zero-values will appear in the respective columns). Hint: If you have changed the options as mentioned above, do not forget to unselect this flag again. (You can use -<*> for this purpose as well!) @ENDNODE @NODE GeneratingaSeries "Generating a Series" Generating a Series Besides the lacking values of the income and expenditures the date column is still missing. Choose it as you did before (therefore, range A5:A16). A simple filling is not possible now, as the whole range would be filled with "Jan 94", for it is no formula. But there is a function for "filling in with calculating" in TurboCalc as well: "Data Generate Series". Please select this menu item or click on the appropriate symbol of the toolbar (7th of the right, with a small "+"). The following requester appears: @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut7.iff'"} Please change, by a click on the circle in front of the appropriate text or by +, the to "Month", then click on >Ok< or press . The increment (the number, by which the month is increased each time) was "1" by default and therefore need not to be changed. The window disappears and the data row should now be displayed completely. @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut8.iff'"} Finally fill up the cells B7:C16 with numeric input at will (perhaps with " Data-Generate Series" or "Fill" as an exercise) and the result should look as follows: @ENDNODE @NODE SavingyourWork "Saving your Work" Saving your Work After a few work steps you should buffer your sheet, for your data will not be lost in case of a power failure or a serious mistake. For this sheet is included in the delivery range of TurboCalc, saving it to the Ram Disk will be sufficient: Please select menu item point or (for a new sheet both menu items do have the same effect) or click the third symbol on the left of the toolbar. After that a filerequester is opened for a file-name selection or input. From OS2.0 on, it is the standard-ASL- requester which is part of the operating system and is explained there. Here, click on "Drive" and in the displayed list of devices on "Ram Disk". The text cursor should be located behind "File". Please enter a file name just as "Example.TCD". The file tag TCD is a synonym for "TCData" and is useful to find TurboCalc-files easier. Naturally this tag is not obligatory, take it as a suggestion for giving your files expressive names (in the loading requesters of TurboCalc normally files that end with .TCD are displayed unless you change this option) - it prevents that you lose the track too quickly). If you use OS1.3 or lower, a different requester will appear. Click on "RAM: ", and then enter your file name as explained above. A click on >Ok< saves the sheet. After saving, the window title should change from "Sheet1" to "Ram Disk:Example.TCD". From now on the selection of or the appropriate symbol of the toolbar is enough to save the sheet with the current name. If a new name is desired you should use . Attention: Files, which are stored on the Ram Disk will be lost when the computer is switched off. For our example this is intended - for your own (important) files saving on a permanent storage medium is advisable. @ENDNODE @NODE Editing "Editing" Editing Up to now the tax rate is set to 15%, but what happens when this percentage changes ? In that case all formulas using the tax have to be changed as well. The example sheet does not cause much effort to do so, but with bigger projects you might have some problems. Therefore the formula for the calculation of the value added tax has to be modified in a way that the percentage can be changed without much effort. Enter "Percentage of V.A.T." in cell A19 (only as a comment) and "15%" in C19; if the percentage changes, only cell C19 has to be adapted. Therefore the "15%" of the formula in cell E5 is replaced by "$C$19" (why the dollar signs are added now, you will get to know after the change has been executed). Editing: Move the cursor to cell E5 - in the "input field" the previously entered formula is displayed. Now press . This is one of the most important function keys of TurboCalc, as it activates the editing mode: Now the cursor should be located at the end of the "input field". It can be moved with and , (if you press the simultaneously, the cursor will be moved right to the beginning or ending of the line). Now, delete the 15% with and replace it by "$C$19" and finish the input as usual with or . or does not work now as it did before, as it only moves the text cursor within the input field. Mouse: It is also possible to perform all steps with the mouse. Instead of just click on the "input field". The cursor is located automatically on the clicked position. Summary: TurboCalc offers two different input modes. While using the normal mode the cell cursor can be moved with the cursor keys and short texts can be entered or corrected with . A change of existing input is not possible. If you press or click on the "input field" with the mouse, the old contents can be edited with , , , or . To quit this mode use , or . After that TurboCalc switches to the normal mode again. @ENDNODE @NODE AbsoluteCellReferences "Absolute Cell References" Absolute Cell References Now, back to the question why we entered "$C$19" instead of "C19". Remember the filling example; there the formula "=B5-C5" was changed automatically into "=B6-C6" in the next line. The new formula "=D5*$C$19" shall also be copied down in this case, but we will need the formula "=D6*$C$19" in the next line (and not "=D6*C20") - " $C$19" should not be changed through copying. This is the special task of the dollar sign. The component row or column which has a leading dollar sign is not changed during copying or filling; it is an absolute reference. In contrast to that, the instructions without dollar sign are changed with every cell shift. The contents "=B2" in cell A1 therefore means, "take contents of the cell one left and one below A1". Copying the formula to F7 will results in "=G8", as the cell reference is relative. Hint: The dollar sign can also be put in front of only one component, e.g. $C5 or C$5. In this case the component with the dollar sign is absolute, the other relative, e.g. $C5 corresponds to: "Cell in the third column, 5 lines below the current one". In most cases (e.g. the calculating within rows and columns) the normal reference (without dollar sign) should be correct - if a distinct, fixed cell (as in our example) is meant, which should be accessed from different cells the absolute reference is needed. Because you entered the absolute reference the formula can be copied down without problems. @ENDNODE @NODE Copyingablock "Copying a block" Copying a block Select cell E5 and call the menu item or simply click on the " copy symbol" at the tool bar (the camera). The selected range (in our case only one cell) will be buffered and can now be copied to a different place with the help of . Now select block E6:E16 and then call or click the symbol on the right of the camera. The previously copied range will now be inserted at the current place (or block) and - as it is a block - filled. Hint 1: This is naturally not the main purpose of block copying and inserting. The main purpose is to copy cells from one place to another (also to different sheets). If no block is marked before you call , the copied block will be inserted starting at the current cell as the top left corner. Hint 2: Besides you will also find the menu item . That corresponds to with an ensuing - it is very suitable to shift cell contents. @ENDNODE @NODE Functions "Functions" Functions Now only a completion line is missing (besides the correct formatting) in which the columns are summed up. TurboCalc has got a simple function for it: Enter the formula "=sum(B5:B16)" in cell B17. This instructs TurboCalc to add up block B5:B16. Input by mouse: This formula should be entered by mouse: Move the cell cursor to cell B17 with the help of the mouse. Then call menu item or click on "f()" in the toolbar. The following window appears: Now, take the proportional gadget at the right margin (blue rectangle), click upon it and move it down while holding the mousebutton. The text on the left promptly moves in the appropriate direction. If you are in the lowest third, the formulas should start with "S" and the formula "sum(range;...)" should appear. Then release the left mouse button and select the "sum"-formula with a mouse click. Now, close the window with >Ok<. Hint: Instead of >Ok< you can execute a double click on the desired formula. The desired formula is taken over to the input field ("=sum()") and the text cursor is located within the parentheses, because parameters are still missing. The parameter (in this case "B5:B16") can be entered with the mouse as well. Simply select the appropriate block with the mouse. A look at the input field shows that the formula is correct. Now, simply press to take over the formula. With you might abort the input if you made a mistake. As soon as the formula is entered the sum should be displayed. The formula must then be copied to C17:F17 either with or by copying: The result should be the following screen: @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut9.iff'"} @ENDNODE @NODE Formatting "Formatting" Formatting Basically the sheet is finished now, at least concerning the contents. But it does not look very professionally and clearly laid out at the moment. Use the formatting possibilities of TurboCalc to improve the appearance of your sheet: @{"Show Picture" RXS "ADDRESS COMMAND DISPLAY 'Pic/Tut10.iff'"} Alignment While inspecting the headlines of the columns it is obvious that they are not placed correctly above the numbers; they are a little bit displaced. The reason for this is that texts normally begin at the left margin of the cell (left aligned) and numbers are orientated to the right (right aligned). Presumably the simplest possibility to solve this problem, is to place the texts at the right margins as well: Therefore select all texts (here block B4:F4) first and then ; choose >Right< (with a click on the circle in front of it) and then >Ok<. Or even simpler: Click on the symbol "right aligned" (the last one before the color values) of the toolbar. Column Width Secondly it is obvious that the distance from "Jan 94" to the next number is too large (and the column "Expenditures" too small). Therefore reduce the width of column A. For this step it is possible to open a window with in which the width, as amount of characters, can be entered. But it can be done faster by mouse: Just click with the left mouse button on the line between column A and B and move the mouse while keeping the mouse button pressed. A vertical line is displayed instantly, which corresponds to the column width. Move the cursor a little bit to the left until column A has reached the optimal width. Now simply release the mouse button. Hint: If you have set the column too small, "Jan 94" will be printed as "### ". This indicates that the date does not fit into the column. In this case enlarge the column as mentioned above. Frame With the frame function every cell border can receive its own frame. Select A4:F4 and then call . In the appearing window, please click in the field "bottom" (right on the left side) on >middle< (on the circle in front of it). Leave the rest unchanged, because your only aim is to draw a line below the row. Click on >Ok< to finish. Do the same procedure for A17:F17 but now set the top margin on >middle<. The sheet should appear like this: Numeric Format In this example all values were integer by chance. If a value has got decimals, like for example 380.5, the formatting will be invalid. This can be avoided with so called numeric formats, which specify how the numbers should be displayed independent from how many digits after the decimal point really exist. Therefore select all numbers B5:F17 and . In the appearing window you can see on the right a list of all available numeric formats. On the left you find a variety of gadgets by which the list can be moved to the beginning of the appropriate numeric type (normal numeric notation, scientific notation, percentage numbers, currencies, date- and time formats). This allows an easy finding of a special numeric format. Here are a few numeric formats which may be used in our example: 0 Only numbers in front of decimal point are displayed, the sum is rounded up. 0.00 Numbers are only shown with 2 places behind the decimal point , if necessary with (".00"). 0.000 Like "0", but 3 decimal places are separated by a specific sign, that makes sense in case of high amounts. 0.000.00 dito, but now with 2 decimal places behind the decimal point. $0 In analogy to the first 4 formats, but here the currency symbol (e.g. $) is $0,00 added. This makes big number fields (like in our example) illegible $0,000 because to many "$" only confuse. For individual values or columns $0.000,00 ,however, it is often very effective. Remark: The separation sign for thousands, the decimal point and the currency symbol can be defined freely in TurboCalc by using . Details about this can be found in the description of this menu item. Now confirm the desired format with a click on the entry (e.g. "0,000 ") and >Ok< (or double click). Keyboard: This can also be executed by using the keyboard: Select the desired entry with the cursor keys and then press . Font To finish the layout a bigger headline is still missing. Therefore enter the title "Surplus Calculation" in cell B1 and then call "Format-Font..." or click upon "A" in the toolbar. During the first call your hard disk or your diskette drive should shortly start working, because TurboCalc is searching for the available character sets. If necessary you will be asked to insert the FONTS-diskette. After that a window with two registers appears, for the character sets and to the right of it for the available sizes. Fields for "Underline", "Bold" and "Italic" will appear as well. Now select the character set, with a click on the character-set name, and afterwards select its size with a click on the desired size. The font in the example is "Courier" and the size is 18. If you do not have OS2.0 at your disposal, you naturally can use another character set, for example " Diamond/20". Hint: From OS2.0 on, the font's size is freely adjustable due to the new vector fonts. If the appropriate character-set is not available it will be calculated automatically. In most cases the result looses some quality then. To do so simply enter the desired value in the text field below the size list. Now you can also select >Underline< and >Bold< to enforce the title effect. Afterwards click on >Ok<. The character-set will be changed accordingly; for it is bigger now it might not be visible completely. To change that enlarge the row height and the column width accordingly. Now enter the text "for the year 1994" in cell B2 and change the font to "Courier/13". @ENDNODE @NODE Charts "Charts" Charts The sheet looks more professional now, but often does a chart express more than thousand words. Select the desired chart data (in our example the profits F5:F16) and "Data-Generate Chart"; or click on the "chart"-symbol at the toolbar. In the chart window simply click on >Ok<. This step activates another window, where you can define a few parameters for the current chart type. Pass by this requester by clicking on and the chart will be opened. It is now scaleable and moveable to any size and position on the screen. The appearance of the chart can also be changed with the menu items listed under "Chart". Try this and test the other functions of TurboCalc as well - for example the database module which has not been used up to now. For this you will find a few important hints in chapter "Database". Furthermore you should use the opportunity to "experiment" with the enclosed example sheets. You will surely discover some tricks and knacks. @ENDNODE @NODE INDEXNODE "Index" @{"Absolute Cell References" link "AbsoluteCellReferences"} @{"Charts" link "Charts"} @{"Copying a block" link "Copyingablock"} @{"Editing" link "Editing"} @{"Entering a Formula" link "EnteringaFormula"} @{"Filling a Range" link "FillingaRange"} @{"Formatting" link "Formatting"} @{"Functions" link "Functions"} @{"Generating a Series" link "GeneratingaSeries"} @{"Saving your Work" link "SavingyourWork"} @{"Selecting a Cell" link "SelectingaCell"} @{"Selecting Ranges" link "SelectingRanges"} @{"TurboCalc by Michael Friedrich" link "MAIN"} @{"TurboCalc-Sheets" link "TurboCalcSheets"} @endnode