Here is a term paper on ‘Spreadsheet’ for class 11 and 12. Find paragraphs, long and short term papers on ‘Spreadsheet’ especially written for school and college students.
Term Paper on Spreadsheet
Term Paper Contents:
- Term Paper on the Introduction to Spreadsheet
- Term Paper on the Structure of Spreadsheets
- Term Paper on the Operating Modes of Spreadsheet
- Term Paper on the Entering Data in Spreadsheet
- Term Paper on the Entering Formulas in Spreadsheet
- Term Paper on the Entering Functions in Spreadsheet
- Term Paper on the Commands Used in Spreadsheet
- Term Paper on the Cell Formatting in Spreadsheet
- Term Paper on the Insertion-Deletion-Erasing in Spreadsheet
- Term Paper on the Data Management in Spreadsheet
- Term Paper on the Graphical Representation of Spreadsheet
- Term Paper on the Printing Operation of Spreadsheet
ADVERTISEMENTS:
Term Paper # 1. Introduction to Spreadsheet:
The data, as you know by now, is the basic input element of a computer package which is manipulated in various ways to extract new knowledge, which is used in decision making. The input data is generally classified into string and numerical values, the latter having a special importance, as different mathematical operations and functions are used to transform them into different forms to make them useful.
In business organisations, where everything is ultimately judged in terms of rupees and paisa, the most common form of numerical data is what is called real numbers, with generally two decimal places in each case, being termed as floating point numbers in computer terminology.
The computation involving floating point numbers is called number crunching, as it calls for special techniques to process them. Generally, number crunching is a relatively slower process, but by using special hardware like mathematical co-processors, the processing speed can be increased to a great extent.
ADVERTISEMENTS:
The data analysis package, which we are going to explore in this term paper, is called an electronic spreadsheet and its operating performance can be greatly improved upon by using co-processors of Intel 87 series, like 8087, 80387, etc., as the case may be. The most popular electronic spreadsheet package appears to be Lotus 1-2-3 of the Lotus Development Corporation.
An electronic spreadsheet is an on-screen data manipulation package where data can be stored, computations carried out using them and the results displayed — everything being done before your eyes, right on the screen. The greatest advantage with an electronic spreadsheet is that once a worksheet is built up defining the input, operations, and output, any change in the input data is automatically processed and its result reflected in the output.
Such kind of instantaneous processing is naturally not possible in manual work without wasting lots of paper and taking considerable time. Sensitivity Analysis, which is a process of assessing how much an output changes, such as the projected profit of a business, if the values of different inputs, like expenses on different items, are changed, can be most conveniently carried out in an electronic spreadsheet — finding out the sensitivity of an output change with respect to the corresponding change in the input. This technique is also known as the what-if technique – what happens if this or that is done.
The spreadsheet uses a tabular format to demarcate clear cut areas for storage of data, data-names, and the results, which are called cells. The cells are formed at each of the intersection of the rows and columns; each cell having a specified storage width. In Lotus 1-2-3 [version 2.01], there are 256 columns identified serially by alphabets as A, B, etc. The total number of rows is 8192 identified serially as 1, 2, 3, etc., — giving a total of 2,097,715 cells, which are identified as A1, A2, B1, etc., and each having a normal width of 9 characters, called default width.
ADVERTISEMENTS:
To activate the spreadsheet, type LOTUS at the DOS prompt followed by a pressing of the Return key with 1-2-3 highlighted in the displayed menu, called Access Menu, or, simply type 123 at the DOS prompt, followed by pressing of the Return key. The spreadsheet loads and displays the working area, which when used and saved in a file is called a worksheet; the default extension of the file is wk1.
Term Paper # 2. Structure of Spreadsheets
:
The working area of a worksheet, also called the active area, is marked by a highlighted bar positioned at the fourth top-row of the display screen as the top limit and a highlighted bar at the first column of the screen being the left limit. The highlighted row displays the names of the columns as A, B, C, etc., and the highlighted column displays the row-identifiers as 1,2, 3, etc.
The space above the highlighted row is called the Control Panel. The first line of the control panel displays the cell number, called Cell Address of the cell where the cursor is positioned, along with its width and format characteristics, if different from the normal or default settings.
ADVERTISEMENTS:
The middle portion called Edit line, displays the contents of the cell where the cursor is and on the extreme right is the Mode Indicator — the spreadsheet system being able to operate in different modes for different purposes. The second line in the control panel is called the Entry Line, where, whatever is typed for entering into a cell are first displayed, as a sort of a line editor.
A sample small worksheet with the first menu and its sub menus displayed is shown below:
When the command menu system is activated, the second line of the control panel shows the main menu items and the second line shows the respective sub-menus or if there are no sub-menus, a brief description of the operation carried out by the concerned command. The bottom-left corner of the worksheet shows the current date and time of the system clock. It also displays the status of different keys like CapsLock, NumLock, etc., at the bottom right corner of the screen, if they are switched on.
ADVERTISEMENTS:
The cursor, when used in the active area of the worksheet, is a rectangular block of single row in reverse video covering the width of the cell where it is positioned and it is called a Cell Pointer, only when in you are in Label, Value, or Point modes, as discussed later. The Cell pointer is moved by the four arrow keys as is usual. In addition, pressing the Home key moves the cell pointer to the first cell, that is A1.
Similarly, pressing the End key followed by an arrow key moves the cell pointer to the last blank or non-blank cell depending on where it was. When the End key is followed by pressing of the Home key, the cell pointer moves to the last entry in the work sheet. Pressing the Break key or End key twice takes you back to the Ready mode, where you were initially after the spreadsheet was loaded.
The PgUp and PgDn keys move the cell pointer by 20 rows at a time up or down. The Tab key or Control+RightArrow moves the cell pointer right by one screen and an opposite effect is caused by Shift+Tab or Control+LeftArrow. The function key F5 works as a GOTO key and anytime F5 followed by a cell address moves the cell pointer to that cell.
Term Paper # 3. The Operating Modes
of Spreadsheet:
When the spreadsheet gets smoothly loaded, without anything being wrong, the system being ready to execute commands or receive entries for the cells, it enters what is called READY mode, as shown by the mode indicator on the top right corner of the screen. At the Ready mode, you can either activate the command menu by pressing the forward-slash key /, enter the edit mode by pressing F2, or you can move the cell pointer to any cell and start typing the contents.
As soon as the command menu is invoked, the mode indicator changes to MENU restricting your operations to the selection of commands only — the cursor now allows you to move from one command to another and not from one cell to the other.
When you start typing, the ready mode changes to VALUE mode, if you are entering numerical data or a formula or a function — in all other cases the mode indicator changes to LABEL, indicating the entry of a string value. Each mode, naturally, has its own facilities and restrictions.
For example, if you are in the Value mode, you cannot enter an alphabet and the contents of a cell in the Value mode is right aligned, whereas, the contents of a cell in the Label mode is by default left aligned. In short, a Label indicates a string value and it can be up to 240 characters long, even if the cell width is only 1 character — the whole string gets displayed if there is no other cell on its right containing anything, covering the total length. But, in the Value mode, you cannot enter numerical digits occupying more space than the cell width specified.
When you make a mistake while entering anything into a cell, the mode changes to EDIT mode, allowing you to make corrections in the characters displayed in the entry line. You can also enter the edit mode from the ready mode, by positioning the cell pointer to any non-blank cell and then pressing the F2 key — the contents will be displayed in the entry line for editing.
Pressing the Return key exits the edit mode with the contents transferred to the cell concerned. When the system is taking some time to process data, the mode changes to WAIT state and if something goes wrong, the mode displays ERROR with the error message displayed at the bottom left corner. Just press the Esc key to get out of the error mode and make the necessary correction.
During execution of many commands, you will have to identify a cell or a group of them, called cell range, or simply a range. In such cases, the mode indicator changes to POINT, allowing you to move the cell pointer, as in the Value or the Label mode, but for allowing you to select a range only. If you use the built-in help facility, which is activated by the F1 key, the mode indicator displays HELP. Press Esc to get out of help mode.
Term Paper # 4. Entering Data
in Spreadsheet:
The cells are the storage places of input and output. While inputs are entered in various ways including direct typing, the outputs are the results obtained after applying a formula or a function to certain input values. It is interesting to note that to get the results of a computation in a cell, we position the cell pointer at the proper location and then type in the expression containing input data and mathematical operators forming a formula, but, the formula is not displayed, we get the results, the value generated by the formulas being displayed in the cell.
Same thing happens with the functions, when they are used. Yet, if you position the cell pointer to a cell containing the result of some computation, the edit line will show you the formula and the cell will show you the result of that formula.
The basic technique of building a worksheet can be broadly divided into four operations:
1. Entering Labels or Values into the cells by typing them at the entry line directly, after positioning the cell pointer.
2. Obtaining results by typing or building up formulas at the entry line.
3. Obtaining results by using predefined functions, which are typed at the entry line in their own style.
4. Executing commands from the menu for different operations as provided by them.
5. Changing the format and or the width of the cells and alignment of labels for better display.
As far as the individual cells are concerned, each, of them can be formatted in different ways to show their contents differently as values, naturally, the options are applicable to numerical values only The width of the cells can also be altered, but all the cells in a column have the same width, varying from 1 to 240 characters.
The Labels, which are character strings, are basically used as titles of rows and columns identifying the data stored. For example, A5, B5, C5, D5, E5, F5, A7 are all labels in the sample worksheet shown earlier. The labels are by default left aligned. If you type April in a cell which is 9 column wide, then the A of April will be aligned with the left side of the cell, leaving 4 blanks spaces after it.
If you notice the edit line, you will find that the Label April has an apostrophe mark before it — the mark stands for left alignment. You have two other options: the ” is for right and ^ is for central alignments. You will have to type the double-quote ” or the caret ^ as the first character while entering a Label to make it right or center aligned.
Of course, you can use commands to change the alignment of labels in some or all the cells. You can also use leading blank spaces in the left aligned labels to align them as per your choice.
How does the Lotus system knows whether you are entering a Label or a Value? It is quite simple. If the first character typed is a number, a dot used as decimal point, + or – sign, (,@, #, or a $ symbol then the mode changes to Value — all other cases being treated as Labels. So, if you want to enter a series of negative (-) signs for underlining, you have to enter an apostrophe first to treat it as a Label. In all cases involving the above symbols as the first character, you will have to use a symbol to make it a Label by yourself.
While entering a Label or a Value in consecutive cells, you can use the four arrow keys to function as the Return key cum direction key. For example, while entering values in the cells B7, C7, D7, etc., position the cell pointer at B7, type in 1234.5 and press the RightArrow key, the value will be entered in B7 and the cell pointer will go to C7 for the next entry — this way you save one key stroke.
To enter a single character repeatedly in a cell as a Label covering the whole width, enter the back-slash symbol \ followed by the character and press the Return key. For example, \= will fill the cell with a series of =.
To delete the contents of a cell, whether it contains a Label or a Value, just press the space bar once and then press the Return key Check the edit line to see whether any hidden character has remained behind. In that case a special command will have to be used.
If you position the cell pointer in a cell which contain a Value or a Label and type anything at the entry line [in Ready mode] followed by the Return key, the new content will go into the cell.
The numerical values of a cell are always right aligned and you cannot change it. If you try to left align it by entering an apostrophe, it will become a string [a Label] and then it cannot be used in any computation — as this is the privilege of the numerical values only.
If the number of numeric digits in a cell exceeds the total width of the cell, a series of asterisks fill up the cell indicating overflow. Just increase the width of the cell. Remember, date values are also treated as numerical values, even if they contain characters like Jan, Feb, etc.
The value of a Label is always treated as a zero. Don’t divide a Value by a Label, you will get an error with ERR displayed in the cell.
Term Paper # 5. Entering Formulas
in Spreadsheet:
Under the system of electronic spreadsheets, the cell addresses function as the names of variables, their contents being the Values assigned to them. For example, if there are two cells A6 and B10 containing 12 and 100 respectively, then with the cell pointer at C1, if we type A6+B10, the value displayed at C1 will be 112. If we type 12+100 with the cell pointer at CI, then also we will have 112 in C1. But if we change the value of A6 to 100, in the former case the value of C1 will become 200; it remaining at 112 in the second case.
The mathematical operators available are the usual +, -, *, /, and ^ for addition, subtraction, multiplication, division, and exponentiation; the priority being higher for multiplication and division than that of addition and subtraction, as in the BODMAS [bracket-division-multiplication-addition-subtraction] you learnt in school. The division sign, when used in the Ready mode as the first character displays the command menu, so it has to be used in the formula after a cell’s name. The presence of a bracket merely alters the priority among operators.
You can execute a formula in two ways. Firstly: just take the cell pointer to the cell where you want the result to be displayed and type the formula at the entry line, starting with the + sign, which tells the system that a formula is being entered.
For example, you can type in say Dl, the formula A1+A2-A3*A4 <R> and -1170 will be displayed in D1, if A1 contains 10, A2 contains 20, A3 contains 30, and A4 contains 40. In the process of computation, A3 is multiplied with A4 to give -2000, to which 30 is added for A1 and A2, to give -1170.
Alternately: take the cell pointer to D1 and type a + at the entry line. Then using the arrow keys, move the cell pointer to A1 and type a + again, the cell pointer will come back to D1. Move the cell pointer to A2, type a – sign, move the cell pointer to A3, type a * sign, move the cell pointer to A4 and press return, the result -1170 will be displayed in D1 — the same formula, as in the previous case, will be built up.
Term Paper # 6. Entering Functions
in Spreadsheet:
The functions are built-in useful utilities which returns values after processing the arguments provided, if required. A function, which is an one word expression is prefixed with the @ sign, and suffixed with a pair of single brackets () if any arguments are to be passed on.
For example, to find the total of the values of a number of consecutive cells, you can use the function SUM, whose syntax is:
where the list gives the addresses of the first and last cells of the series, separated by two dots. To find out the sum of the values in the cells A1, A2, and A3 in D3, just move the cell pointer to D3 and type @SUM( at the entry line.
Now, having done this, you have two options to provide the arguments:
1. You complete the function by typing @SUM(A1.A3) <R> and the result of summation will be displayed in the cell D3.
2. After typing @SUM( move the cell pointer to A1 and type a dot, anchoring the address of the first cell, then move the cell pointer to A3, close the bracket by typing ), and then press return. When you move the cell pointer, the cells selected are highlighted. You can select any contiguous block of cells. The second method is called the pointing method, when the mode indicator changes to POINT.
Note that even though a single dot is typed, the system shows two dots to separate the cell addresses within the bracket.
If different ranges of cells are to be covered, then the arguments containing each range; are separated by commas, such as:
This is generally the pattern followed while entering the arguments of a function. Since the value returned by a function is displayed and not the function, the formula or the function used can occupy larger number of characters than the width of the cell concerned where the result will be displayed.
The functions can always be nested — one function’s return value being the argument of another function, such as:
Term Paper # 7. Commands
Used in Spreadsheet:
At the Ready mode, pressing the / [forward-slash] key invokes the command menu. In the control panel, the second line displays the first row of the commands available separated by blank spaces — each command starting with a unique first character, so that only one specific command is executed by pressing the first letter of a command, like W or R.
The second line of the command menu displays the sub-commands available under that main command which is highlighted by the cursor, or a description of what the command does, if it does not have any menu of subcommands under it. The last command in the chain or tree of commands always executes performing the specified operation. Some of the executable commands can be approached by different branches of the tree of commands, having different effects, as you will see later.
The commands can be executed in two ways- either by pressing the key of the first character of the command or by moving the cursor to highlight the command and pressing the Return key.
When a command is selected, if there are sub-commands, a new menu is displayed containing the sub-commands, or if it is the last command in the chain, it starts execution. You can cancel a command and go back to higher levels by pressing the Esc key — at the last stage, you are returned to the Ready mode. Pressing the Break key takes you back to Ready mode from the Menu mode.
The first line of the ten commands are:
Out of these ten commands, four are directly executed from the main menu itself and the rest six have lower levels of commands.
i. Quit:
It causes the spreadsheet package to return to the DOS system, after getting your confirmation, in terms of Yes or No. You must save your worksheet before quitting, as this is not automatically saved.
ii. System:
It takes you to the DOS operating system, without getting out of the system of the spreadsheet, to allow you to execute DOS commands. You are returned back to your worksheet, when you type EXIT at the DOS prompt.
iii. Copy:
It is the most useful command which is used for copying labels, values, and formulas from one or more cells to one or more cells. When invoked, it prompts you to enter the range to be copied from and after you do that, you are prompted to point to the cells where it is to be copied. The copy command maintains the relative position when -formulas are copied.
For example: if the cell A4 contains A1 + A2, and you want to copy the formula in cell A4 to B4 and C4, invoke the copy command with the cell pointer at A4 and press the Return key to confirm that you want to copy from A4. Then move the cell pointer to B4, anchor it with the dot, move the cell pointer to C4, highlighting both B4 and C4 and press the Return key — the formula at A4 will be copied to B4 and C4 relatively. The cell B4 will contain B1 + B2, and the cell C4 will contain C1 + C2; not A1 + A2. The copy command also copies the format of the cell copied.
There is another provision for copying the Value of a single cell, called referencing. If in a cell, say B8, you type +C4 the contents of the cell C4 will be displayed in B8 and whenever the Value of C4 will change, that of B8 will also change. Instead of typing, you can take the cell pointer to B8, type a + and move the cell pointer to C4 and press Return; referencing will be done.
iv. Move:
It is used for moving labels, values, and formulas from one or more cells to one or more cells; the original being repeated exactly at the new location — but the formulas are adjusted to account for the changed situation. Here also you are prompted to give the range to be moved and the place where it is to be moved.
Term Paper # 8. Cell Formatting
in Spreadsheet:
In order to make a good overall presentation and to display the data and result including date in proper perspective, it is necessary to format the cells — to control how the contents are displayed. Most of the formatting controls naturally apply to numeric values.
There are two approaches to cell formatting: global and range wise — the former applies to the whole of the worksheet covering all the cells, except those which are formatted by using the range command, which overrides the settings provided by the global command. In both the format controls, you can set the numeric, date, and time formats.
In numeric formats, the decimal places can be up to 15 places in each case. For global control, the command chain is /Worksheet Global Format or simply, /WGF and for the localized control, the chain is /Range Format or /RF.
In each case the sub-menu provides a number of choices one of which is selected by the first letter or by positioning the cursor. To reset the format setting of a range to that of the global setting, use /Range Format Reset or /RFR, which is an additional option under the Range command.
The numeric formats applicable are:
Fixed: in the form x.xx fixing the number of decimal places; blanks are filled with zeros.
Scientific: in x.xxE+xx or exponential form, the mantissa as a power of 10.
Currency: in $x,xxx.xx, the currency symbol $ can be changed elsewhere.
, (comma): in xxx,xxx.xx, negative number is enclosed in a bracket.
+/-: represents numbers as a bar of + for positive numbers and a bar of – sign for negative numbers. Decimal positions are not available. Zeros are represented by dots.
Percent: in x.xx%, for displaying the percent values, which are less than one in value.
General: it a free form without any decimal setting. When invoked, it restores all the cells involved to the original default state.
You should always remember that the displayed Value may not be the same as the stored Value, as the system stores values with the accuracy of 15 decimal places. Hence, the computed totals of a range of values may be slightly different from what you will get by totalling the displayed values. To get over the problem, you can use the @ROUND() function to round-off values to the displayed size.
To display dates in different styles, you follow the path /Worksheet Global Format Date or /Range Format Date/as the case may be, and the options available are:
Standard Long Form: DD-MMM-YY the month is in 3 letter word like Jan.
Standard Short Form: DD-MMM the month as above.
Standard Short Form: MMM-YY an alternative.
Long International: MM/DD/YY
Short International: MM/DD
Dates are always right aligned. Overflows, as in the case of numeric values, are indicated by a series of asterisks filling up the cell. To get the proper display, increase the width. Dates are stored as numbers representing number of seconds elapsed from January 1, 1990 to the date specified by the system, which you can see using a numeric format. Only when the date format is selected, the number is shown as a date.
The time of the day can also be displayed by using /Worksheet Global Format Date Time or /Range Format Date Time in four different formats, which are:
Standard Long: HH:MM:SS AM/PM -uses 12 hour display
Standard Short: HH:MM AM/PM
Long International: HH:MM:SS [24 hours] uses 24 hour display
Short International: HH:MM [24 hours]
Some of the functions applicable to date and time are as follows:
@NOW:
It returns the current date and time, and so using the appropriate format you can get the current date and time displayed in a cell. If you use it in a worksheet, then every time you take a printout you will know when it was done as a record. On the date of January 30, 1994, the number returned is 34364, which is to be converted using a date format to see the date.
@DATE(year,month,day):
It returns the number of the date, called date- number corresponding to the year, month, and day entered by you from January 1, 1990 and which can be displayed as a date using appropriate cell format.
@DAY(date-number):
Returns the number of the day, which is between 1 and 31. In our case @day(34364) returns 30.
@MONTH(date-number):
Returns the number of the month [1 -12], In our case, @month(34364) returns 1 for January.
@YEAR(date-number):
Returns the number representing year, which is between 1 and 199. In our case @year(34364) gives 94.
@TIME(hour,minute,second):
Returns the number of seconds in the day from midnight and can be viewed by using the appropriate format.
Under the formatting options, when Text is selected, using /WGF Text or / RF Text, the cells containing formulas display the formulas and not the values. Similarly, to Hide the display of the cells, you can use / WGF Hidden or /RF Hidden. Naturally, with the Range chain you will have to provide the range. To go back to the original state, use /WGF General or /RF General.
As you know, by default, the Labels are always left aligned, unless you enter ” or ^ before the characters typed to make them right or centre aligned respectively. You can also change the alignments of the Labels by using commands in the following chains.
/Worksheet Global Label-Prefix or /WGL is used to set the default alignment of the labels, by appropriately selecting one from the prompt of Left, Right, or Centre. If you want to change the alignment of a few cells, select /Range Label or /RL, which again prompts for Left, Right, or Centre. The changing of alignment does not affect the numeric or date cells. Long labels, which exceed the width of the cell containing it is always left aligned.
As far as the width of the columns are concerned, as usual, there are two chains of commands, one affecting all the columns and the other to alter the width of a specific column. The chain /Worksheet Global Column-Width or /WGC allows you to see the existing column width, as well as to change it. The default width of 9 can be changed from 1 to 240 characters.
After entering, /WGC you can use the pointing method, which is offered first. You use the Right Arrow to widen the column width or the Left Arrow to reduce it; the process terminated by pressing of the Return key. When you use the arrows, the actual width is displayed at the panel. Alternately, you can simply type the required column width and it will affect all the columns, except those which are specifically set with the next command.
To alter the width of a specific column, use / Worksheet Column Set_Width or /WCS after positioning the cell pointer at the required column and follow the steps stated earlier. By using /Worksheet Column Reset-Width or /WCR you return the width to the default level.
In order to practice the commands explained so far, type in the details at each cell address as given below, which is given on the left side of each line, to build up a worksheet. The width of some of the cells have been changed, and this is indicated by the number following the W within the square bracket.
The (D1) indicates date format with the first option selected. The alignment symbols for each Label is indicated. The F inside () indicates the format option selected. The details of where the formulas are first entered and then copied where are given below.
You will notice that in Formulas 2 and 3, the dividers have two $ signs enclosing the column names and this has resulted in the respective cell addresses of the dividers remaining unaltered in each case — this is called absolute cell addressing. The Cost in column E has been computed by dividing the respective amounts in column D by the total quantity computed at B14. When a formula is copied, as you know, the relative cell addresses change, as has happened in case of Formula 1. But in case of Formula 2, we want the respective amounts to be always divided by the value at B14 — to get this the cell address B14 has been frozen by the dollar symbols.
This is achieved by pressing the F4 key, called the absolute key, at the cell address of B14, when it is entered in Formula 2. Same thing has been done to compute the percentage in column F, by freezing the value of E14 in Formula 3. Try experimenting without using the F4 key first.
Then use the F2 key to edit E9, take the cursor under E and press F4, the dollar signs will appear. Press the Return key to enter the new formula in E9. Now copy it again using the copy command, overwriting the previous ones in E10, E11, and E12. The actual display in the worksheet is given after the cell entries.
Enter these labels, values, functions, dates, and formulas and change the widths and alignments as stated: 3-1
Al: [W9] ‘Exercise in Building Up a Worksheet
A2: [W9] ‘________________________________
A3: [W9] ‘Cost of Special Juice Concentrate
A4: [W9] ‘================================
E4: “Output = 75 Kg — right aligned Label
A6: [W9] ‘Elements — left aligned Label
B6: [W9] ‘ Quantity
C6: [W10] “Rate
D6: “Amount
E6: “Cost
F6: [W9] ‘ Percent
A7: [W9] ”
B7: [W9] ^Kg — centre aligned Label
C7: [W10] ‘ Rs/Kg
D7: ^Rupees
E7: ‘ Rs/Kg
F7: [W9] ^%
A9: [W9] “Orange
B9: [W9] 20 — the numericals are input values
C9: (F2) [W10] 12.5
D9: (F2) +B9*C9 — the Formula 1 entered here
E9: (F2) +D9/$B$14 — the Formula 2 entered here
F9: (P2) [W9] +E9/$E$14 — the Formula 3 entered here
A10: [W9] “Mango
B10: [W9] 45
C10: (F2) [W10] 16
D10: (F2) +B10*C10 — Formula 1 copied here
E10: (F2) +D10/$B$14 — Formula 2 copied here
F10: (P2) [W9] +E10/$E$14 — Formula 3 copied here
A11: [W9] “Ginger
B11: [W9] 2
C11: (F2) [W10] 112.6
D11: (F2) +B11*C11 ; — Formula 1 copied here
E11: (F2) +D11/$B$14 — Formula 2 copied here
F11 (P2) [W9] +E11/$E$14 — Formula 3 copied here
A12: [W9] “Sugar
B12: [W9] 8
C12: (F2) [VV10] 7.4
D12: (F2) +B12*C12 — Formula 1 copied here
E12: (F2) +D12/$B$14 — Formula 2 copied here
F12: (P2) [W9] +E12/$E$14 — Formula 3 copied here
B13: [W9] “—
D13: “ ______
E13: “______
F13: [W9] “______
B14: [W9] @SUM(B9..B12) — the Function entered here and
D14: (F2) @SUM(D9..D12) — copied here with copy command
E14: (F2) @SUM(E9..E12) — copied here
F14: (P2) [W9] @SUM(F9..F12) — copied here
B15: [W9] “—
D15: “______
E15: “_____
F15: [W9] “—
A17: [W9] “Prepared on:
C17: (D1) [W10] @DATE(94,1,30) — the Date function used here
The worksheet as displayed:
The above exercise will help you to familiarise yourself with the activities connected with building up a worksheet. You should experiment with different formats and widths to see the changes. After the worksheet is ready if you change any input value in Columns B and or C, the output will immediately change.
It is quite natural for a worksheet to become bigger than the area of the display screen, always keeping a part of it out of your vision, as you move from one cell to the other in your worksheet. Sometimes, it creates lots of inconvenience making you to shift the cell pointer back and forth from one side of the worksheet to another.
To overcome this problem to a large extent, by allowing you to bring different areas of the worksheet together on the screen even if they are far apart, there are two aids called Title and Window facilities.
The /Worksheet Window command allows you to split the display screen containing the worksheet into two parts called windows, either horizontally or vertically from the position where the cell pointer is located, and then bring the required distant portion of the worksheet closer to each other in the two windows by scrolling.
However, only one window is active at a time, allowing you to do whatever you like. Visually, your one worksheet becomes two work sheets, each visible in each window. The function key F6 moves the cell pointer from one window to the other, making that part active.
The sub-commands of /worksheet window are:
Use / WW Horizontal to split the worksheet horizontally and /WW Vertical to do so vertically from the position of the cell pointer. Normally, when you scroll the worksheet in a window, the worksheet shown in the non-active window also scrolls in the similar way, being synchronised. To disable this feature, select / WW Unsync — with /WW Sync causing synchronisation. /WW Clear will take you back to the original position. Any change made in any part of the worksheet in any window is faithfully incorporated in your worksheet.
The /Worksheet Title command, on the other hand, freezes a part of the display on the screen, letting other parts to scroll, horizontally or vertically. Suppose, you want the column headings to remain always on the screen no matter where you take the cell pointer, just position your worksheet in such a manner that the column headings are visible at the top portion of the screen.
Then move the cell pointer to the row’ below the title headings and execute / Worksheet Title Horizontal. The portion above the cell pointer will remain frozen on the screen when you move down along the worksheet or move up. If you move the cell pointe horizontally, the frozen portion will also move, but in no case these frozen rows will go out of the screen and you cannot take the cell pointer to that area, without using the F5, the GOTO key.
The sub-menus of the Title menu are:
With /Worksheet Title Vertical, you freeze columns on the left of the cell pointer and by using /WT Both, you freeze the area above and also on left of the cell pointer. The Clear option takes you back to you original state.
Term Paper # 9. Insertion-Deletion-Erasing
in Spreadsheet:
In order to provide for additional entries in some rows, you may have to move a large part of the worksheet using the Move command. Alternately, you can invoke /Worksheet Insert, then select Columns or Rows to be inserted, and the range of columns or rows. Just remember that it affects the whole of the worksheet. Similarly, you can delete the contents of rows or columns and adjust other rows or columns automatically, by using /Worksheet Delete, the Column or Row option, and then by specifying the range.
The command /Worksheet Erase completely erases the current worksheet being used; the saved file is not affected. To erase only a few ranges, use /Range Erase and specify the range to deleted. In many cases, when you delete the contents of a cell containing a Label, by using the spacebar-return method, the apostrophe remains behind blocking display of long labels. In such cases you will have to use /RE to clear those cells. It is also to be used to delete the page break symbol.
When the size of a worksheet becomes too large involving hundreds of calculations, you may have to wait for quite some time after making any change for the processing to be complete. In such and many other cases, you should switch over to manual operation, by invoking / Worksheet Global Recalculation Manual, to suppress the automatic recalculations.
When this comes into force, you can use the F9 function key to get the recalculation done. To go back to the default mode, use /WGR Automatic. There are other options available, which are: Natural – Columnwise – Rowwise to specify the direction of calculation and Iteration to provide the number of passes.
You can display and change some of the default settings of a number of items using the / Worksheet Global Default command, whose sub-menus are:
The Printer option allows you to set margins, page-length, etc., for the printer you are going to use and the Directory option lets you change the default directory, where you are working. The Status option shows a screen-full of status relating to those you can set within this group of commands. Use the Update option to save the changes you have made, and Quit allows you to go back to the previous menu.
The Other option has further sub-menus of: International – Help – Clock. The Clock option decides the format in which the date and time are displayed at the left bottom corner of the screen. The International option has further sub-menus for: Punctuation – Currency – Date – Time – Quit.
With the Date and Time options, you can set different display format for these in the worksheet, like using DD/MM/YY as against MM/DD/YY. The Currency command is most useful. Invoke /Worksheet Global Default Other International Currency and type Rs in place of $ and select the Prefix option. Go back with Quit twice and use Update to store it. From now onwards, whenever you will use the currency format for values, the numericals will be prefixed with Rs, instead of the $ symbol.
You have a Status sub-command under the Worksheet command, which shows the status of formatting, and other details of your worksheet — invoked with /Worksheet Status. Press any key to clear the screen and go back to you worksheet.
The command /Worksheet Page enters a page-break symbol “::” at the cell where the pointer is, which is used by the printer to start a new page during printing.
The command /Worksheet Zero controls the display of zeros in the worksheet, which you have to select with either Yes or No option. When selected, a zero value is not displayed.
The spreadsheet system provides the facility of assigning names to different ranges and using these whenever a range of cells are required to be given — because this is frequently required to be done.
The sub-commands provided under /Range Name are:
To create a new name or modify the range of an existing name, use the command /Range Name Create. The name can be 14 characters long. When invoked, the existing names are displayed. You can select one of them to modify its range, or type in a new name and specify its range. There after, you can use the name to specify that range, wherever required.
The command /Range Name Delete can be used to delete any existing range name. The /Range Name Reset removes all range names created for the current worksheet.
The command /Range Name Label allows you to assign an existing Label of your worksheet to any of its neighbouring cells on left, right, above, or bottom. So, when executed, after specifying the cell containing the Label, you are to select one of the options of Right, Left, Up, or Down and it is done. It is extensively used in creating macros, which has been discussed later.
You can view the existing range names and their respective ranges displayed in a tabular form by using /Range Name Table. You have to specify a place where the table will be displayed.
If you have entered a text in the worksheet with a maximum of 240 characters per row forming a paragraph, you can justify it by invoking /Range Justify and specifying the range within which the paragraph is to be formatted. The text will be rearranged within the width of the range specified.
Sometimes it may be required to rearrange the contents of a column by converting them to a row, or vice versa. The handy command for this is /Range Transpose, where you have to provide the From- and To-range. This must not be used, if any of the cells contain a formula using relative cell address, as this command does not readjust the cell addresses used in the formulas.
In such cases, you can use the command /Range Value to convert the formulas in cells to their pure values beforehand. You have to provide the range which is to be copied to a new range, with the formulas converted to values. The range from which the conversion is made is not affected.
So far we have discussed all the commands under Worksheet and Range of the main menu.
The summarised tree of these commands are given below:
[The numbers before the commands indicate their levels]
⓿ Worksheet Commands
❶ Global
❷ Format
Fixed, Scientific, Currency,,[Comma], General,+/-,Percent, Date, Text, Hidden : Decimal places except in general, +/-, date, hidden, and reset
❸ Date
1. DD-MMM-YY 2. DD-MMM 3. MMM-YY 4. MM/DD/YY 5. MM/DD Time
❹ Time
1. HH:MM:SS (AM/PM) 2. HH:MM (AM/PM) 3. HH:MM:SS (24 Hr) 4. HH:MM (24 Hr)
❷ Label-Prefix: Left Right Centre
❷ Column Width: [1 – 240]
❷ Recalculation; Natural, Col-wise, Rowwise, Automatic, Manual, Iteration
❷ Default
Printer, Directory, Status, Update, Other, Quit
❸ Printer
Interface, Auto-Lf, Left, Right, Top, Bottom, Pg-Length, Wait, Setup, Name, Quit
❸ Other International, Help, Clock
❹ International’ Punctuation, Currency, Date, Time, Quit
❺ Punctuation: different dots and commas
❺ Currency: symbol: Prefix Suffix
❺ Date
A.MM/DD/YY B.DD/MM/YY C.DD.MM.YY D.YY-MM-DD
❺ Time: HH:MM:SS HH.MM.SS HH,MM,SS HHhMMmSSs
❹ Help : Instant Removable
❹ Clock: Standard International None
❶ Zero
⓿Range Commands
❶ Format
Fixed, Scientific, Currency,,[comma], General, +/-,Percent, Date, Text, Hidden, Reset: Decimal places except in general, +/-, date, hidden, and reset: Range
❷ Date
1. DD-MMM-YY 2. DD-MMM 3. MMM-YY 4. MM/DD/YY5. MM/DD Time
❸ Time
1. HH:MM:SS (AM/PM) 2. HH:MM (AM/PM)3. HH:MM:SS (24 Hr) 4. HH:MM (24 Hr)
Term Paper # 10. Data Management
in Spreadsheet:
There are a number of useful commands for dealing with groups of similar data, as in a data base, grouped under the main command Data, which is invoked with /D.
The different facilities available are:
Among these eight numbers of sub-commands, the Sort, Query, Distribution, and Parse requires a data base specifically. Under the system, a data base stands for one or more columns containing string or numericals as Labels or Values respectively, with a distinct name heading each column, called field-names.
In the data base, the rows contain the records or data about each entity segregated into fields. For example, a data base could be built with field names as NAME, AGE, HEIGHT, and RANK of students in cells A1, B1, C1, and D1 respectively — the NAME containing Labels and the rest contain Values. The successive rows beginning with A2, A3, etc., will contain the data of each individual student. If there are 10 records in the data base concerning 10 students, the range containing the data base will A2..D11.
The values could be obtained from an existing worksheet, by converting the formulas to values with /Range Value command and transposing to columns using /Range Transpose command. Let us now discuss each of the commands one by one.
The /Data Fill command is useful for building up a column of values with a series of numbers, which are in arithmetic progression, that is, their values increase in fixed steps. For example, you may enter the years 1983 to 1992 in 10 cells from A2 to A11. As you invoke /DF, you are prompted to select a fill range for output and you give it as A2..A11.
Then you provide the initial value by selecting the Start option, which is 1983, the Step value which is 1, and the Stop value which is 1992. The column will be “instantly filled — it stops when the Stop value or the end of the range specified is reached. It fills from top to bottom if a column is selected, or from left to right if a row is specified. You can also enter formulas as Start, Step, and Stop values. Labels cannot be built up like this.
The /Data Table command provides an automated what-if analysis in a tabular form, where you enter a formula specifying the inputs which are variables in the formula and different values of that input to get the desired outputs. You can select one or two inputs.
On execution, the command calculates the different output results for each different input. Suppose, you want to find out what will be the amount of DA [Dearness Allowance] if the DA rates increases by different percentage points.
The formula could be:
DA Amount = Basic Pay * (1 + DA Percentage)
The command requires that the inputs and the formula be entered in some specific cells in a fixed pattern. So, you have to prepare a Table containing two adjacent columns — the first column containing the different input values, like DA Percentage and the second column to provide the result.
The top row of the first column is left blank and that of the second column contains the formula. A minimum of two columns and two rows are required. After preparing the table, you start by specifying the Input, which has the option of 1, 2, or Reset [to cancel]. Then you have to specify the Table Range and lastly the address of the cell containing the input.
The /Data Sort command is used for sorting the labels or values of records based on a primary key, which can be any column of the data base, which will be used for rearranging all the records. In our data base, we could sort the Rank, calling it the primary key, in Ascending or Descending order, or the Age, Weight, or Name fields. You can also select a second column as the secondary key, which comes into play when the data of the primary key are identical.
For example, we can use the Name as the secondary key, so that students having the same rank will be sorted on the basis of names. The order of sorting can be specified for each of the two keys separately. When you invoke /DS, you have to specify the data range which is the cell address of the first record of the first column [field] and the last record of the last column. Don’t include the field names.
The next step is to define the primary key, which you do by taking the cell pointer to any of the cells in the column concerned, say Rank, and press the Return key; then you select the sort order as A or D. If you want to have a secondary key, select the option, take the cell pointer to any cell in that column, say Name, and press the Return key. There after you select the sort order of the secondary key. Finally, you execute by selecting the Go option.
The four options of /data sort command are:
It is generally preferred to name the data-range using / Range Name Create command, as say DATASORT and then using it when specifying the data-range. Press F3 to display the range-names and select the appropriate one.
The /Data Query command is used to search out specific records in a data base, under different conditions specified by you.
Its five sub-commands are:
Like before, you have to specify the data-range, which in this case must include the respective field names also. So, create a new range name as, say DATAQ, and include the range from the field name of the first column to the last cell of the last column. In fact, for all these sub-commands you have to specify an Input Range and a Criterion Range, the latter specifying the conditions.
For the Extract command, you also have to specify an Output Range. While the data base forms the Input Range, you have to specify two separate regions in the worksheet for the Criterion and Output Ranges. These, to start with, contain all the field names, of the Input Range in a row, followed by a number of blank rows. Use /Copy command to copy the field-names from the Input Range to the Criterion and Output ranges.
The conditions specified are written in the blank rows of the Criterion Range and when output is generated, it is displayed in the blank rows of the Output Range. It is better to name the ranges of the Criterion and Output Ranges as well. The conditions, which are specified in the Criterion Range, are written under the appropriate field-names in the blank rows, where the wild cards . like * and ? can be used to find matching data.
For example, if you want to get the records of all the students whose name starts with B, enter B* under the fieldname NAME in the Criterion range. The wild card ?, as usual, stands for a single character, You can use a tilde ~ which stands for “not”. For instance, if you use ~B, then all names not starting with B will be selected.
To define comparing conditions, the relational operators are used with priority 3 in hierarchy, which are:
= for equals, > for greater than, < for less than,
>= for greater than or equal to, <= for less than or equal to,
and, <> for not equal to.
Wherever required, the relational operators can be connected with Logical Operators, which are: [NOT has a priority of 2 and the others have a priority of 1, that is executed last]
#AND# indicates true when both the conditions give true result
#OR# indicates true when one of the conditions give true result
#NOT# indicates true when the condition specified gives false result
For developing conditions for comparing, you have to create range names of the field-names as Labels using /Range Name Label Down command, like NAME, WEIGHT etc., so that when you use a Label name in the conditional expression, all the values under the field-name will be evaluated. For example, create a label called WEIGHT and specify all the values below the field-name WEIGHT as its range, so that using WEIGHT as a range-name will cover all the values of the Weight column of the data base.
Then, in the criterion range, under the field-name WEIGHT, enter + WEIGHT > 55 — this will select all the records where the Weight is greater than 55. Here, WEIGHT is used as a Label name and not field-name. You notice that there is a + sign before the conditional statement, it is because this statement is treated as a formula. Let us say, we want the condition for search to be the records, where the WEIGHT is between 55 and 65, and AGE is less than 22.
In the first blank row of the criterion range, you have to enter as below and execute:
The criterion range can be maximum 32 column wide.
The /Data Query Find command allows you to find and modify the records which meet the condition specified in the Criterion Range. On execution, the cell pointer moves to the first record fulfilling the condition. Press the Return or the Esc key to leave the Find mode; Up/Down arrows to get next matched records; Left/Right arrows to go to the next field; and F2 to edit.
The /Data Query Delete command deletes those records which meet the condition specified. You are prompted to select Delete or Cancel, the former deletes ultimately.
The /Data Query Extract command copies the records which meet the condition, in the Output Range specified.
The /Data Query Unique command extracts only those records, as in the previous case but only the unique ones; duplicates are not copied.
The function key F7 is called the query key. If you want to repeat the same query, but using different condition, just change the condition in Ready mode and press F7, and new records will be selected depending on the sub-command used last.
The /Data Distribution command calculates the frequency distribution of a range of values in a column, where you have to specify the range for grouping the data, called Bin Range.
The /Data Matrix command is used for Inverting a matrix or Multiplying two matrices.
The /Data Regression command can be used to carry out regression analysis. You have to specify the X-range, Y-range, Output-range, and Intercept.
The /Data Parse command converts a column of long-labels, that is labels which are longer than the cell width in which they are, into several columns of labels or numbers.
The three items to be specified are:
The Format-line defines the fields of the data base to be created from the long labels. The two options under it are: Create and Edit. The first character in a format-line is I, which is followed by different letters indicating the type of the field to be created.
The allowed letters are:
L — Labels V — Value D — Date T — Time
S — Skip the corresponding character in the input line
> — indicates continuation of the field type specified
* — character undefined but belongs to the current block
With Input-Column specify the range of the input to be parsed containing the long labels. The input- column must start with a format-line. Then you specify the output-range and lastly select Go. Given below an example of long label converted to different Labels and Values. The I sign is not displayed.
Term Paper # 11. Graphical Representation
of Spreadsheet:
The system of spreadsheet provides for visual representation of the data of the worksheet in the form of graphs. There are five different options which allow you to draw Line chart, Bar diagram, X-Y diagram, Stacked-Bar diagram, and Pie chart. The Line chart connects the data points by a line drawn from point to point for a variable.
The Bar diagram represents the value in the form of a bar, where as the Stacked-Bar, gives different values as bars stacked over one another. The X-Y diagram shows the point of intersection of the values of the x and y axis. The Pie chart is a circular diagram of values of a variable. The commands are grouped in the main menu under Graph, which is executed with /G.
The sub-menu provides:
The single alphabets A to F are for declaring the range of values for six different variables, which have to be values. These form the y-axis of the diagrams. The alphabet X is to provide for the values of the X-axis and these can be either Labels or Values. The vertical display is sized automatically by taking into account the maximum and minimum values of the six variables A to F. The labels or values of x-axis are equally spaced by default. You have to select the values first.
The option /Graph Type simply selects the type of graph you want to draw, as you know, in most cases different diagrams can be drawn with the same data. The options provided are: Line Bar X-Y Stacked-Bar Pie. Just select the desired type and then select View to see the graph drawn with values provided. Pressing any key will take you back to the menu.
In the case of Pie chart, the slices of the pie shows the values of the A range only. Numeric codes [0 – 7] are required in the B range to change the colour/shades of the slices or to explode them. To explode a slice, that is to show the slice slightly away from the centre, add 100 to the corresponding number code — normally it is done for one slice only.
If you want to change any existing ranges or all the ranges together, you can just select the appropriate option, press Esc and then select the new range.
Alternately, you can use the option reset whose choices are:
Selecting any of these resets the corresponding parameter, Graph resets all of them.
A graph can be saved in two ways. In one way, you can only view it later under 1-2-3 and in the second way, you can print it using another program called PGRAPH after quitting 1-2-3, but cannot view it later. You cannot print a graph using the Print commands of 1-2-3. If you want to save a graph for viewing later, use the /Graph Name Create command, give a name to it and press the Return key — the graph will be saved under that name for loading and viewing later. No separate file is created for this graph, the settings are -saved along with the worksheet.
The options available under /graph name are:
Invoke /Graph Name Use to select a saved graph for viewing and /Graph Name Delete for deleting a specific name. /Graph Name Reset clears all graphs saved this way.
To print a graph later using the Print Graph Software [PGRAPH], you have to use the /Graph Save command and type the name or update an existing graph file. These files have a .pic extension. It is better to name and also save a graph. While saving you can choose Replace to update the saved graph setting.
The /Graph Option command offers a number of facilities to refine the presentation of the graph created.
The options available are:
The /GO Legend allows you to set the legends of all the six ranges of A to F. The legends are short names of up to 19 characters to identify each range, which are displayed at the bottom below the x-axis. You have to first select the appropriate range and then type the legend for it. You can also use any existing label entry in the worksheet as a legend, if you enter a backslash, followed by the cell address or range name of a label. These are available for bar, stacked-bar, and line graph only.
The /GO Format allows you to choose how the Line or X-Y graph will be displayed, such as with Lines Symbols Both Neither. Before selecting any one of these options, you have to select the appropriate range from A to F, or Graph for all the ranges — the options displayed are Graph A B C D E F Quit If you select Neither, then specify Data-Labels with /GO Data-Labels option or the values will not be displayed.
The /GO Data-Labelsis used to specify labels for Line, Bar, and X-Y data points. The options are A B C D E F Quit The labels can be placed as- Centre Left Above Right Below, with respect to the respective data points. Lastly, you have to specify the range.
/GO Title allows you to add titles. You can have two lines for the heading. The options are First Second X-Axis Y-Axis After selecting any of these, you have to give the text in 39 characters. Here also you can select an existing title by typing a backslash and then the corresponding cell address of the label to be used as the title. The text is automatically centered.
/GO Grid allows you to add horizontal lines, or vertical lines, or both in your graphs as grids in Line, Bar, Stacked-Bar, and X-Y graphs.
The options are:
/GO Scale allows you to set the size of the graph manually, it you want to do so.
The options are Y-Scale X-Scale Quit Then under the Y-Scale or X-Scale the choices are:
Once you select Manual, you have to specify the Upper and Lower limits in terms of values. You can Format the display of the values, the options being same as those under /WG Format command. You can also decide to have an indicator with Yes or No option — this indicator displays the scale of the graph. The same settings are also available for the X-Axis.
/GO Color and /GO B&W can be used to select colour or black and white display, as the case may be with your monitor.
If you do not have the Print-Graph software with comes with the Lotus package, or you want to print the graph as displayed on the screen, go back to DOS prompt and type GRAPHICS, which is a DOS utility. Come back to Lotus and use Shift-Print Screen keys to print the graph.
You can use the function key F10 to redraw a graph without entering the Graph Menu, having gone to Ready- Mode to make changes in some displayed values of your worksheet. The last viewed graph is displayed again.
An example of a Bar Graph drawn using Release 4 of Lotus, can also be drawn with Release 2.1.
Term Paper # 12. Printing Operation of Spreadsheet
:
The printing operation is controlled by the /Print command available in the Main Menu. You can print your current worksheet or a part of it using a printer or can send it to an ASCII file, which can be printed in the DOS environment or used in a word-processing file. When printed to a file, the extension PRN is given to the filename provided by you.
So, the two main options are:
You can choose any one depending on what you want to do. There after a number of other options are displayed, some of which are not applicable to the /P File choice.
The options are:
The Range option, selected either with /P Printer Range or /P File Range allows you to define the range to be printed. If a range is selected, it is highlighted. You can change it by pressing the Esc key and then moving the cell pointer to select the range in the usual manner. If the range is too wide for the margin set up, the extra columns are printed on a separate page. You can use the Backspace key also to reject a range already selected.
With /P Printer, the Line option advances the paper by one line, Page advances the paper to the top of the next page, and Align resets the line counter and the system assumes that it is at the top of the page. Use it when you are manually setting the paper in the printer for a fresh page. Quit naturally takes you back to Ready Mode.
The option Go causes the range to be printed or saved in a file. This is the last step during printing operation. The Clear option, applicable to both, is used to reset some or all of the settings selected.
The choices are:
While All cancels all print settings made, taking you back to the default settings, the Range cancels the range selected. The Borders clears the setting made for the borders. The Format option cancels all the printer settings made for page length, margins, the setup string, etc., which are discussed below.
The / Print Option is extremely useful for making different printing settings which overrides the default settings made by /W Global Default Printer option.
The choices available are:
The Pg-Length sets the number of lines printed per page which can be from 1 to 100, the default being 66 lines. The number of lines selected is reduced by the top and bottom margins, as well as by header and footer texts, to provide the net number of lines for the range specified. With /PP Other Unformatted, the page length becomes infinite with no page break, printing being done continuously. / PPOO Formatted resets the number of lines to what you have selected.
The Margin option offers- four choices as: Left Right Top Bottom to select the respective margins. The left and right margins can be set to a maximum of 240, the minimum being 0. Similarly the top and bottom margins can be 0 – 32. The default settings are: Left Margin = 4, Right Margin = 76. Top Margin = 2 and Bottom Margin = 2. If you select a range with 82 characters, with the default left and right margins, 82 – 72 = 10 columns or more depending on the column width will be printed on a separate page.
To position the text of header and footer at different columns, you have to prefix the text with some special characters. Use | to center the text, || to right justify, and nothing for left aligned text. @ prints current date and # prints page number automatically. A line is provided for header below the top margin and also a line for footer above the bottom margin. Prints blank if header or footer are not provided.
The Border option allows you to specify what are to be repeated on each page. You can select rows or columns or both to be included in each page.
The Setup allows to use different types of fonts for printing. For example, \015 in the setup string results in compressed printing.
The /PP Other Option Cell-Formulas causes the formulas to be printed for each cell, followed by the cell address. /PPOO AS-Displayed cancels the above setting.
