Functions - Spreadsheet

Functions

 

OpenOffice Calc has more than 350 functions under 11 categories. Functions are predefined formulae already available with Calc. They are used to perform several frequently done calculations. Every function has a unique name and a prototype. Functions are categorized according to their functionality. For example, the functions such as Sum, Average, Sin, Cos etc., are categorized as “Mathematical Functions”.

 

1. Inserting Functions into

 

Worksheet:

 

A  function  can  be  inserted  by

 

·  Direct Insert Method or (ii) Using Function Wizard method.

 

1.1 Direct Insert method:

 

If you know the function name and its syntax, it can be directly typed in any cell of the worksheet.

 

For example, SUM is the most frequently used function to add a set of values. The syntax of the SUM ( ) is

 

=SUM (range 1; range 2; range 3…… range n)

 

If you want to know the sum of the values in A1, A2, A3, A4 and in A5, Place your cell pointer in A5 and directly type the formulae as follows.

 

= SUM (A1:A4)

 

While inserting a function the following points should be kept in mind.

 

a.    A function should begin with an equal sign.

 

b.    Use proper name for the function to be used.

 

c.     Arguments should be given within the brackets as per the syntax. Each function has a unique argument list.

 

d.    Press “Enter” key after typing the function.

1.2 Using Function Wizard method

 

A function can inserted using Function Wizard in Calc. Function Wizard is a dialog box provides the step-by-step procedure to insert a function. Function wizard can be invoked by clicking the Function Wizard icon on the Formula bar (or) Insert -> Function (or) Ctrl + F2. Refer Figure 7.41

 

Function category drop down list box:

 

Function Wizard has two tabs viz. Functions and Shortcuts. In Functions Tab, the list of categories is available in Category drop down list box. In Calc, the functions are categorized into 11 types. They are,

1.    Database

2. Date and Time

3. Financial

4. Information

5. Logical

6. Mathematical

7. Array

8. Statistical

9. Spreadsheet

10. Text

11. Add-in 

If you select any particular category, the Functions list box shows the functions which belongs to the selected category. If “All” is selected under category all functions in calc is displayed in alphabetical order. Refer Figure 7.42

 

Function Description

 

When you select a function, the function wizard shows the function name, syntax and a small description about the function on the right side of the dialog box. Refer Figure 7.43.

 

Inserting a function using Function Wizard: (with Cell Reference)

 

The following steps explains to insert POWER( ) function in a cell.

 

About POWER ( ) function:

 

POWER ( ) is a function which is used to calculate power of an exponent value of a number. This function is categorized as a Mathematical function. There are two inputs needed to find the power value of a number. They are, Base value and exponent value. For example, to find the value of 25 to the power of 2 (25²) where 25 is the base value, 2 is the exponent value.

 

The syntax of POWER( ) is = POWER (Base ; Exponent)

 

Both Base and Exponent are arguments. In Calc, arguments are separated by a semicolon.

 

Inserting POWER( ) in a worksheet:

 

Step 1: In cell A2 type the base value 25

 

Step 2: In cell B2 type the exponent value 2

 

Step 3: Move the cell pointer to C2; in which you want display result.

 

Step 4: Click fx icon from Formula bar (or) choose Insert -> Functions (or) Press Ctrl + F2.

 

Step 5: Pull down category list box, Choose “Mathematical”

 

All function under Mathematical category is displayed in the “Functions” list box

 

Step 6: Scroll the “Functions” list box and select “POWER( )”. The function wizard shows the description about the selected function on the right corner of the dialog box

 

Step 7: Click “Next” command button. Now, Function wizard appears as shown in the Figure 7.44.

 

Step 8: Click on the cell which is contains the base value (A2).

 

Now, the minimized wizard shows the cell address you have selected (A2). Refer Figure 7.45

 

Step 9: Click “Maximize” button to display full wizard. Now, Function wizard appears as shown in Figure 7.46

 

Base box shows the cell address which contain base value.

 

Step 10:      Directly type the cell address which contains exponent, in Exponent box; or repeat steps 7, 8 and 9.

After entering Base and Exponent cell references, the function wizard appears as shown in Figure 7.47

 

The formula box displays the syntax of the function with input values and Result box displays the result value.

 

Step 11: If the result is correct, click “OK” button else click “Back” button to display the previous page of this wizard.

 

Inserting a function using Function Wizard: (with direct values)

 

In the previous example, Cell addresses are used for Base and Exponent values. In Calc, direct values can be used instead of using cell reference (i.e. cell address) to find the same result.

 

In this case, type base and exponent value instead of cell address. Function wizard displays the result in the current cell. Refer Figure 7.48 shown below.