WHAT'S THIS THING CALLED A SPREADSHEET by 3M Don Sutton This is a very useful overview of what Spreadsheets are and how they can be used - Don gives a long list of possible uses towards the end of the article. This article is aimed at those of you who do not know, or who may be a little fuzzy as to what a spreadsheet is or what it can do for you. Spreadsheets first made their appearance as a business tool on Apple IIs, namely Visicalc, Supercalc then later Multiplan. Compared to modern day products they were slow and did not have all the features of today's examples. However they would run in a 64k machine whereas today's examples probably use this much in the title screens. WHAT IS IT? For those of you who may be newcomers to computers or spreadsheets, imagine a sheet of paper ruled with lines horizontally and vertically. This will leave a series of rectangles called cells into which you could write data. Look at the screen as being a window which can be moved across the cells or down the rows. Each cell can have a label (just any ascii characters such as a name), a number or a formula. The vertical stack of cells is refered to as a column which are normally given letters starting at the left with A and incrementing alphabetically to the right. When the letter Z is reached the next column to the right is given the name AA and this continues with AB,AC etc. The rows of cells are numbered down from the top starting at 1 and increasing by one to the bottom of the spreadsheet. The last column or row is dependent on the brand of spreadsheet - in the case of VIP it's row 8127 and there are 255 columns. This is a small spreadsheet by Amiga standards but large by IBM/clones running MS-DOS. In most cases one would not be able to fill up all the cells as the computer would run out of memory (unless you are lucky enough to have a fully expanded memory). Though VIP or Maxiplan will run on a 512k Amiga you generally have less than 100k for a spreadsheet. This is not as bad as it seems as a home budget spreadsheet could easily be run in 40-60k. The lines dividing the cells are invisible though there are some spreadsheets that will display a grid (MaxiPlan for example). You can insert dashes etc to fill a cell to highlight a cell above or below with most other spreadsheets (not necessary on MaxiPlan). The cursor fills the current cell. The column widths are adjustable from 1 character width to at least 60 with most spreadsheets. The number of cells visible on the screen depends on the widths of the columns and in the case of the Amiga whether or not WB Interlace is set. The usual procedure in setting up a spreadsheet is to plan the format, enter the labels, enter the data and finally the formula. An example would be a tally of your year's expenditure with the leftmost column labeled with the date the next column showing say, the cheque number and the other columns labeled as rent, electricity, gas, petrol, groceries, clothing etc in row 1. A rough example of a section of a spreadsheet showing 3 rows and 12 columns. --------------------------------------------------------------------- | | A |B |C |D |E |F |G |H |I |J |K |L | | 1|______|____|____|____|____|____|____|____|____|____|____|____| | 2|______|____|____|____|____|____|____|____|____|____|____|____| | 3|______|____|____|____|____|____|____|____|____|____|____|____| In the right column after the cheque number column you would enter the amount of the bill under the appropriate column' say $198 under electricity in row labeled with the date 20 - 11 - 89 and so on at the end of this you would add up the rows to give an account for the expenditure for each day (month in our example) and add the columns to give the total expenditure on each item. See the example below. A | B | C | D | E | F | G | H 1| Date Rent Elect. Gas Petrol Food etc Clothing MnthTotal 2|20-11-89 $120.00 $198.00 $35.00 $24.00 $68.00 $33.00 $478.00 3|20-12-89 $120.00 $26.00 $126.00 $80.00 $352.00 4|20-01-90 $125.00 $18.00 $94.00 $237.00 5| 6|------------------------------------------------------------------- 7|Sub 8|Totals $365.00 $198.00 $35.00 $68.00 $288.00 $113.00 9|Total $1,067.00 This would be done by entering the formula @sum(B2:G2) in cell H2 using our example above. You would now simply copy this formula down to the last row of data (ie in H3,H4 etc). The above example is a bit squashed up so I can fit it into this article. Usually a blank row or a row filled with dashes would be inserted between the headings and the data. Note: I have shown the formula above preceded by an ampersand "@". Some spreadsheets require an "=" whilst others require a "+". Read the manual for your particular spreadsheet to determine what is required. The spreadsheet needs some symbol to tell the computer that what is entered into the cell is a FORMULA and not a LABEL. After recalculating the spreadsheet the result of the formula will be displayed in the cell and not the actual formula. If the cursor is placed in this cell then the command line will show the formula. Most spreadsheets can be set to auto or manual recalc. This is neccessary as if you have it set to auto while entering data the entry may be slowed down considerably as the spreadsheet recalcs after each cell entry. Auto recalc would be used if you wished to see what changes would occur if one or two items were to change in value, the spreadsheet would then automaticaly update the results. To complete the above example spreadsheet we would enter a formula under the last data row (it can be a couple of rows down to look neater) we would enter the formula +sum(c2:c#) where # equals the last row number of data. Again we would copy this formula into the other cells in the same row underneath the data. The spreadsheet will automatically increase the row reference if copying downwards or the column letter if copying horizontally. Finaly we would want a grand total. To the left of the last column or below the last row we would enter another formula to either sum the "I" column or sum the subtotal row or sum the entire block of data. An example of the latter would be (assuming we had filled in 30 rows of data) +sum(C2:H31). The computer would add all the columns to end up with a total. Now supposing you forgot or lost a bill then found it. With your paper spreadsheet you are now faced with rewriting it or part of it and then recalculating it. With a computer spreadsheet you just insert a new line at the right place and enter the new data. The spreadsheet will then recalculate the worksheet. If you wished you could add it to the end and issue the commands applicable to your spreadsheet to sort the entries into a specified order. Also if you wished to find out what your bills will come to approximately next year with a projected increase in the cost of living, this can also be accomplished very easily using WHAT-IF features. What I have described is a very simplified idea and actual spreadsheets can be as complex as your imagination and the program allows. WHAT CAN A SPREADSHEET BE USED FOR? Spreadsheets have the ability to be a simple Database and at a pinch a simple wordprocessor. Most have graphing capabilities where selected data can be displayed in a variety of different types such as bar, pie, histograms etc. The power of a spreadsheet is the ability to display data in an easily read and organised format with easy editing. The built in formula commands make manipulation of the data into meaningful results. Add to this the ability to print all or selected parts of the spreadsheet in a variety of type sizes (dependent on printer) and the ability of some spreadsheets to print sideways and you have a very powerful planning tool which can be used in the home as well as business. COMMON USES ARE: Business Accounting - A spreadsheet can be set up to handle all ledger work and invoicing. This can be made easier for other people to enter data by writing Macros so that cell entry is under program control. Comparison Charts - An example you want to buy a new car. Place the brands or models along the top row, enter there features in the left column, then put a yes or no depending on whether the model has this feature under the model heading. Other charts could be comparison of prices between supermarkets, productivity between departments in a business. Conversion Charts - These can be set up to convert anything from one standard format to another. An example - An imperial to metric conversion chart for pressure. Enter PSI in cell A1 and KPA in B1. In the left column use the data fill command to start a list of pressures in psi. starting at or near the pressure of interest and finishing some rows down at the last pressure of interest. In the next column you would enter a formula such as @(A2*6.895) and then copy this formula down column B level with the bottom of the list in column A. There will now be two columns, column A with psi and column B with the equivalent in kpa. The B column would need to be formatted with the format command to reflect the number of decimal places you wish to show. You could also make this chart universal by entering a conversion factor in say cell C2 and then altering the formula to the following: (A2*$C$2) (A symbol not neccessary in this case) Note the "$" in the formula. This prevents the copying process changing the cell of reference containing the conversion factor (makes C2 absolute). A $ sign in front of the letter makes the column absolute and a $ sign in front of the row number makes the row absolute. Some spreadsheets such as Superplan do not allow semi absolute cell references, most however allow either row or column or both absolute. Now when we want to change the conversion chart to something else you would change the title in A1 to the standard you wish to convert from and B1 to the standard you wish to convert to then enter the conversion factor in C2. (See this drawer for a working example in VIP and Maxiplan format; those with Superplan or Logistix can use the load 1-2-3 worksheet format to load the VIP version. The cell references are slightly different to the example above for a better looking layout). In a future issue I intend to cover MACROs and our example above will be automated. Calendar Charts - Using the date functions a calendar can be created of your own layout. (There is an example on one of the Fish disks that will run on VIP). I will do another version when we cover Macros. Fee payments - Put names in column A and head other columns to the right with the dates due. Put the amount paid in the cells below. Maintenance programs - List your machines have a column displaying worked hours and another showing when a service falls due and then a formula in another column displaying whether or not the service was due. An example of a formula would be: @if((b3>=b4),"Due","Not Due") Note: Syntax may have to be changed slightly to suit your spreadsheet. When the "@if" condition is true ie. b3 is equal or greater than b4 the cell will contain the word "Due" else it will contain "Not Due". Payrolls - Can be set up to handle a company payroll, keeping track of the total tax, holiday hours, worked hours etc and printing pay slips. Presentation charts - mainly using the graphing modules. Production Schedules Parts Lists & Inventories Recipes - Industrial style ie. A tyre batch is to be manufactured. The ingredients would be listed in a column, the price of the ingredients in the next, the number of tyres to be manufactured in another cell. Then another column set up to show how much of each ingredient is used per tyre. A further column would then show (using a suitable formula) the total amount of ingredient to be used for the batch per ingredient. Another column would show the total price of each ingredient and at the bottom the total cost of ingredients for the batch. Of course you would probably add labour, transport and other overheads, and from this a retail price would be calculated again using further columns and rows in this spreadsheet. Now in some months' time when the labour rate has risen or the ingredient price has risen (notice I said risen, do they ever go down?) it is simply a matter of adjusting those things that have changed and an updated result will have been attained. Reports - Export portions of the spreadsheet into wordprocessors or databases or create a report within the spreadsheet which may reference certain key cells to produce a regular report such as a monthly sales report. Roster lists Time Sheets - Keep track of hours spent on a job by an employee and hours per job for all employees. Finance - Keep track of shares on the stock market, keep track of morgage repayments, calculate morgage repayments, loan amortisation etc. Quotations - A list of items can be placed in a column with other columns set up to reflect labour time, labour cost and other overheads to arrive at a job cost. Gantt charts (a form of horizontal bar graph that starts and ends each a plot according to cell criteria) can then be used to produce a timing schedule.