Calc. The spreadsheet of Openoffice

Posted on October  24th, 2008 by Technology Department 

The spreadsheet component of OpenOffice is called Calc. With this program you can enter numerical data and then run the program to produce certain results, for example, you can calculate how much you spend on your mobile in a month if you have the data: The number of calls, the time of each call and the rate per minute. A major advantage of spreadsheets is that the data is easier to alter. If the mobile company changes the rate per minute, changing just one item of data in your spreadsheet means all the work is done! We'll see it now in some exercises.  

Sheets and cells

A Spreadsheet is made up of a number of sheets ( we will work with just one sheet ) and every sheet contains a block of cells arranged in columns and rows. These cells contain the individual elements, that is, numbers, formulas, text, etc— which make up the data to be displayed and later manipulated. In the example of the mobile, cell1 contains the minutes you have spent talking to your friend, cell2 the hang up rate  and cell3 the rate per minute. The sheet manipulates the data to display the total cost in cell4, that is:

Total cost =  cell2 + ( cell1* cell4 )  

This formula should be placed in cell5

Each spreadsheet can have several sheets and each sheet can contain many cells. In Calc, every sheet can have 65,536 rows and 245 columns, this gives us more that 16 million individual cells per sheet. I have the feeling that we won't need 2 or 3 million cells in our project !  

What does a Sheet look like?

When a spreadsheet is started, the window looks like the next image main calc windows

Let's put in plain words  those concepts:

  • The Title bar shows the name of the current spreadsheet.  When you save a new file, you are prompted to enter a name for your spreadsheet.
  • The Menu bar. When you choose one of the menus, a submenu appears with other options.
  • The Standard toolbar.  It provides a wide range of common commands and functions
  • The Formatting toolbar. Two rectangular areas: Font Name and Font Size The Formula bar.  Rectangular area in which mathematical formulas can be added

Cells are the intersections of columns and rows. They hold the data. Column and row headers. Every cell is identified by a column and row reference.  

Starting new spreadsheets and saving spreadsheets.

Steps to open a new spreadsheet:

1. From the menu bar—Click File and then go for New -> Spreadsheet.

2. After the work is done, it  can be saved. Click File and then go to  Save. A new window will ask you for a name to identify your file.

 Inserting columns and rows

A single column can be added using: Insert-> Columns. Do the same if you want to add a row.

We said that there are lots of columns and rows on a spreadsheet. Adding row and columns is important when you need to add information to a sheet once it is finished.

Multiple rows or columns can be inserted at the same time rather than inserting them one at a time. For example, to add three rows, go to the columns header and highlight three of them by holding down the left mouse button. Then, click on the right mouse button and click on Insert rows.

If you need to delete a single column or row, follow these steps :

1. Select the  row or column.

2. Right-click on the  row or column.

3. Select Delete Row or Delete Columns from the pop-up ( small window )  menu.  

Calc Formulas Tutorial 

Let's make a basic formula in a simple spreadsheet. The formula will calculate how much you pay just for dialing a number on your mobile ( if the person you want to speak to answers your call ) It is necessary to multiply the date of two cells. In our case, these cells are A4 and B4  

What you have to do is:

1. click in the cell you want to insert the formula into

2. Go to formula and select this command

3. Choose in Category -> Mathematical and later Product

The window  will look like this:

Now, click on Next, click on Number 1 and select cell A4, then click on Number 2 and select CellB4. Now, once you have clicked on OK, the formula bar will show PRODUCT(A4;B4). Now, try to insert some data intoeither cell and the data in cellC4 will change when you change either A4 or B4.

Great, we have done our first Spreadsheet! Important. Calc formulas always begin in the cell where you want the answer to appear. If you want to create a new formula without using the formula menu, you ALWAYS start by typing the equals sign. In this case, you may use the following mathematical operator:

The mathematical operators used in Calc formulas are similar to the ones used in maths class.  

  • Plus sign ( + )  for Addition
  • Minus sign ( - )  for Subtraction
  • Asterisk (* )  for Multiplication
  • Forward slash ( / )  for Division
  • Exponentiation -> caret (^ )

If you have a long formula with various type of operators, apply the same techniques as you do in your maths class, so:

Any operation contained in brackets ( () )  will be carried out first. The next priority Operator is the exponents.

After that, multiplication  or division  operations ( equal importance ) . The same goes for the  subtraction and  addition operations. 

Example of Office If you need to create  graphs for class  presentations, OpenOffice Calc  has several graph templates available.

Let's see it in a budget example. Create a new spreadsheet to generate a personal budget by entering data descriptions such as electricity, telephone bill, water supply, groceries, mortgage, holidays, restaurantscar insurance , car Loan,  etc  into column A and then the money spent into Column B. Once you have finished run a formula on column B to Sum all this data. When you click into the simbol SUM ( in the formula bar ) a rectangular area will be highlighted and you will be able to modify the data and sum it. See next image:

home budget with calc

Now, Highlight the areas you would like to include in the chart ( both column A and Column B ) and then click Insert -> Chart. In the new window, choose a chart type and the graphic will appear on the right. You can move it anywhere on the screen. We should have something like this:

bugbet chart 

Exercise 1.

A serial circuit has 4 resistors of 50Ω , 70Ω , 80Ω  and 100 Ω. If those are connected to a 4,5 battery, make a Spreadsheet to calculate voltage across  All resistors, that is, V1, V1, V3 and V4 , the current through the resistors and the power in every resistor.

Be careful. In the format cell, if you select ( in Options ) decimal places to 2 you should have o.o2 for the current (4.5/300), so, if you get a higher number, like 3, you have got it wrong. See the next two images:

decimal places in cell 1

decimal places in cells 2

The final screen ...

serial circuit spreadsheet

  

Dictionary:

Mortgage: A temporary, conditional pledge of property to a creditor as security for performance of an obligation or repayment of a debt. ! This word should be said if yours parents are around ¡

More information 

Posted on 2009