This section covers the basics of spreadsheet manipulation with OpenOffice-Calc; a program that looks and feels like MS-Excel, but it is free (open source) and can be downloaded from .
Spreadsheets - The Basics
OpenOffice Calc is a spreadsheet program. A spreadsheet is a table with special funtionalities; namely, the cells of the table can be 'programmed' to perform calculations on the values it contains such as sums, counts and averages, and even graphics such as histograms, pie charts, etc.
Go ahead and open Calc on your computer. You should see something similar to the picture below:
Figure 1: Spreadsheet is a table composed of cells; each cell has a location which is defined by its row and its column.
Each cell can be referred to by its column/row location. Rows are named using numbers 1,2,3, etc and Columns are named using letters A, B, C, etc. For example, cell A1, highlighted in the above picture, corresponds to the cell located in row 1, column A. You can tell what cell is highlighted by looking at the lefthand box of the formula toolbar (which is located directly above the column labels).
NOTE: If a particular toolbar is not visible, go to the View > Toolbars pull down menu. In the above picture, the Standard, Formatting, and Forms toolbars are chosen. Click on various cells and notice how the lefthand box reflects the cell location chosen.
Entering Data
To enter data into a spreadsheet you have to type values into the cells of the spreadsheet. The cell you want to place data in is first selected with the mouse, and the data is then typed in, followed by to actually place the value in the selected cell.
To edit a cell, select it first by double-clicking on it; once selected, you can change the content of the cell; press <Enter> at the end of the editing to 'take' the new value into the cell.
To move from one cell to another within the spreadsheet. You can move from the starting cell to other cells by using Tab (across a row) and Enter (down a column). The four arrow keys on the keyboard can also be used to move (i.e. navigate) within the spreadsheet.
Formatting Data
Some cells may contain numbers, other cells may contain names, dates, etc. The application will need to know what type of information each cell is to contain for different purposes such as displaying it on the screen, printing it to paper or to a file, and most importantly for performing calculations with those values.
Note: a default format is assigned to the entire spreadsheet when it is first created and you may not even need to worry about this aspect at all.
Changing the cell format. The Cell Format can be changed by highlighting the cell (or entire column, or entire row) and then using the toolbar buttons.
and then hit Enter. NOTE: A formula must always start with (=). Addition and subtraction use
the (+) and ()
keys, while multiplication and division use (*) and (/). If the computer complains
(beeps and/or error message) then something was typed incorrectly.
Another way to enter cell addresses in a formula is to click in the cell of interest rather than
typing in the cell address. To do the same calculation in C5 again, try:
Figure 2. Spreadsheet formatting. Each cell may contain a different type of value. To specify the cell format you can use menu or toolbar options.
Further formatting (such as specifying the number of decimal points in numbers, etc.) can be done using the Format > Cells pull down menu. In the simple example on the next page, the discount was formatted as a percent and the numbers in the table were formatted as dollars.
The width of the columns can be adjusted by placing the cursor on the vertical dividing line between the column labels (i.e. between A and B), then clicking and dragging with the mouse. Go ahead and recreate Columns A and B of this table. (We will learn how to calculate the sale price in Column C in a moment):
Figure 3. A simple example of entereing data into and formatting a spreadsheet.
Building Formulas
The value that a cell may contain can be typed into the cell directly as illustrated in Figure3. The value can also be assigned to the cell as the result of performing some calculation on the values of other cells; to specify the calculations to be performed, we need to write formulas (i.e. functions).
Entering a formula into a cell is straightforward. A formula must always start with the = symbol; use + and - for addition and subtraction operations; similarly, use * and / for multiplication and division operations.
Consider the above example. The sale is 10% off; that is, the sale price is 10% of the retail price; so for the hiking boots, its retail price is located in cell B5 with value $99.95 and you need to calculate its sale price and place it in cell C5. Select C5 and type:
and then hit Enter.
NOTE: If the computer complains (beeps and/or shows an error message in a popup window) then something was typed incorrectly.
Another way to enter cell addresses in a formula is to click in the cell of interest rather than typing in the cell address. To do the same calculation in C5 again, try:
=(click in B5) - 0.10*( click in B5) |
and then hit Enter. Notice the color-coding when you clicked on cell B5.
Absolute and Relative Cell References
When the spreadsheet copies the contents of one cell, say X, to another cell, say Y; it is important to remember this: if the cell X contains a formula, the spreadsheet will copy the formula of the cell X rather than its value to the new cell Y; as illustrated here in Figure 4.
Figure 4. Copying formulas between cells.
Another thing to remember is that the spreadsheet will copy the formula and will 'adjust' the cell locations depending on the locations between cells X and Y. If you think, that is confusing, you are right.
Suffice it to say that if you don't want the spreadsheet to adjust the formula in that way, you can use the dollar sign $ in front of the column and/or the row that you don't want changed by the spreadsheet.
The cell location without the $ is also known as the relative address of the cell;
Whereas the cell location with the $ is also known as the absolute address of the cell.
So, let's revisit the above example. if you want to copy the formula of cell B2 to cell B3 'as is', then use absolute address thus:
Figure 5. Copying formulas between cells using absolute addressed
Built-In
Functions
Certain formulas are so common that they have been 'prepackaged' and are ready to go; those types of formulas are also called built-in functions. To examine the list of available built-in functions click in any blank cell and then choose Insert > Function from the pull down menu. The Insert Function window that opens has a pull down menu for functions in different categories. There is also a function Search utility. Click on a function in the list and some information about how to use that function is provided in the window:
Figure 6. List of builtin functions
Listed below are some of the most useful functions.
| =AVERAGE(range) |
=EXP(cell or value) |
=COS(cell or value) |
| =LN(cell or value) |
=SQRT(cell or value) |
=SUM(range) |
Ranges are groups of consecutive cells specified by two values; the location of the first cell followed by colon : followed by the location of the last cell. For instance, in the example illustrated in Figure 3, we need to enter in cell C9 the total sale value of the entire purchase. We do that by summing all the consecutive cells between C5 and C8.
One way is by typing in cell C9 this formula:
Another way is by using the built-in function SUM thus:
You should see that we expect to spend $207.53 on our supplies.
Graphing Data
To graph data in Calc, enter the data first, then highlight it, and finally click on the Chart Wizard icon  : from the toolbar.
Notes:
Use a separate column for each variable you want to plot. For example, if we want to produce a chart with the data we have in the spreadsheet of Figure 3; we would highlight with the mouse the area between cells A4 and B9 and then click on the Chart Wizard icon.
The wizard selects the type of chart automatically; however, you can change such selection later
Figure 7. Histogram generated with data of the example given in Figure 3.
Creating another spreadsheet.
For the next part of the unit, create a spreadsheet with the title and column headings shown below:
Figure 8. Contents of the TITRATION spreadsheet
Type in cell A2: "Virtual pH Titration of 1M HF with 0.1M NaOH"
Type in cell B4: "Initial Buret Reading:"
Type in cell A6: "actual buret reading"
Type in cell B6:"corrected buret reading"
Type in cell C6: "avg NaOH"
Type in cell D6: "1st der."
Type in cell E6: "2nd der."
Type in cell F6: "pH"
Saving the spreadsheet
Calc needs to be told to save the spreadsheet into a file. In order to perform this operation go to the menu bar, select File with the mouse, to open that menu window; then select Save, a popup window should open. Select the directory and then type the filename TITRATION then click ok.
Further Documentation
A more extensive and detailed Calc tutorial is available at .
A Excel tutorial is available at as well.
|
