BIL 101 Introduction to Computers and Information Systems

BIL 101, Introduction to Computers and Information Systems Chapter 9 Spreadsheets, Star. Office Spreadsheet, MSExcel The Star. Office part of this chapter is prepared by Neşe Akyüz and Mehpare Songül The snapshots are prepared by Erdal Mutlu Reformatted by Metin Demiralp Istanbul Technical University, Informatics Institute, Maslak { 80626, Istanbul, Türkiye) Version 0. 60

Spreadsheet A spreadsheet is composed of rows and columns such that the rows are usually numbered while the columns are assigned alphabetical letters. The elemantary units which are positioned at the intersections of rows and columns are called cells. Each cell has a distinct and unique cell adress like A 1, B 4, or D 15. You can place a value which is a number, or a label which is a heading or explanatory text. The value may also be a hidden formula that performs a calculation. A formula can involve constants like 3 +5 however the most useful formulas contains cell references like C 10+E 23. The placement of formulas into the cells enables us to create even a complex network of links among the parts of a spreadsheet. After embedding the formulas it is possible to adjust constants and to see how the bottom line changes. The concepts of spreadsheet comes from accountant's worksheet and includes generally a graphic representation. Hence, a spreadsheet usually requires a graphic interface like Windows or X Window. In computer science terminology, a spreadsheet program simulates an accountant's worksheet on screen and has the capability of the performing hidden formulas.

The paper spreadsheets were used for business data and its process long before the invention of the computers. The beginning of the computer age facilitated the creation of these structures. The first computer spreadsheet was called Visi. Calc It was used on Apple II computer at the and of 1970 s. The mos widely used spreadsheet have been Lotus I - III and Excel since they were available for the abundance of personal computers although more powerful spreadsheets became available for Unix and recently Linux operating systems. Now we can itemize the main features about spreadsheets even by repeating some statements above. 1. The data of the spreadsheet is arranged in rows and columns. Columns and rows are labeled by letters and numbers respectively. Each data location in the spreadsheet is called cell. Numbers or letters can be entered in a cell as below

2. When a formula entered the computer automatically evaluates the value defined by the formula and displays it in the cell where the formula was entered. For example we can evaluate the revenue of the sales of cereal in the above spreadsheet by entering the formula +B 2*C 2 where + specifies the entered thing as a formula while * denotes multiplication in cell D 2. The resulting display is as follows.

When a formula is entered the result of the formula will be displayed by the spreadsheet instead of the formula itself. However it is possible to see the content of the formula at the top of the screen when the cell pointer into the cell where the formula entered. This also enables us to edit the formula if necessary.

3. You can copy formula in one cell to the other cells. For example, the formula in cell D 2 in the above spreadsheet can be copied to cells D 3 and D 4. This results in the following spreadsheet display.

When a formula is copied from a cell to other one it is automatically readjusted by the spreadsheet according to the new cell address. For example, in the above example, the formula becomes +B 3*C 3 when it is copied from the cell D 2 to the cell D 3. 4. A formula may contain some built-in functions. For example the function SUM can be used to sum all cells within a range specified in the command. 5. The spreadsheets are important because the formulas are recalculated when a new value is given for one of the numbers. 6. The graphics can be automatically created for a given data by the spreadsheet.

7. MACROs can be used to combine a series of keystrokes into one command. There is possibility of constructing advanced macros which are themselves computer programs to establish interaction between the user and the spreadsheet and to process data. 8. The spreadsheets were originally two dimensional whereas the new versions now in- volves the three dimensional graphic capabilities. In the following sections we deal with the Star. Calc which is a publicly free spreadsheet under Star. Office Suite. It is very similar to MSExcel and covers almost all features of MSExcel and in good agreement with it. Hence, here we emphasize only on Star. Calc. The user which gets an increasing experience on Star. Calc will be able to equivalently use the MSExcel.

Samples of MSExcel tables GOZTEPE yil ay saatlik hava sicakligi C gün 1 2 3 4 5 6 1996 1 1 3, 5 4, 4 5, 2 5, 9 5, 2 1996 1 2 14, 6 14, 4 14, 3 14, 2 14, 1 1996 1 3 9, 9 9, 6 9, 8 9, 9 1996 1 4 5, 1 5 4, 8 4, 6 4, 4 1996 1 5 2, 4 2, 3 2, 2 2, 1 1996 1 6 2, 1 2 2 2 1, 6 1, 5 1996 1 7 2, 9 3 3 3, 1