Unit 4.  Formulas and Functions (I)


This unit is one of the most important in the course. To get the basics of Excel you must understand and use the contents of this unit. A worksheet is just a data base that we use with a series of formulas in order to avoid recalculations for each change introduced. That is why this unit is essential to follow the course and for the proper use of Excel.

We are going to go deeper into the use of functions , already defined by Excel2003, to facilitate the creation of worksheets. We are going to to study the syntax of functions and how to use the functions assistant, a very useful tool when we are still unfamiliar with the existing functions and their syntax.

 

Introducing Formulas and Functions

A function is a formula predifined by Excel2003 (or by the user) that operates with one or more values and returns a result that will appear directly in the cell or will be used to calculate the formula it contains.

The syntax of any function is:

name_function(argument1;argument2;...;argumentN)

Functions follow these rules:

- if the function is at the beginning of a formula, it must begin with the sign =.

- Arguments and entry values are always in brackets. Do not leave any spaces after or before each bracket sign.

- Arguments can be constant values (figure or text), formulas or functions.

- Arguments should be separated by semicolons ;.

Example: =SUM(A1:C8)

This is a SUM() function that returns as result the addition of its arguments. The operator ":" identifies a cell range like this A1:C8 and indicates all the cells included between cell A1 and cell C8, so the function will be the same as:

=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8

In this example we can appreciate the advantage in using functions.

Formulas may contain more than one function and functions may appear nested inside a formula.

Example: =SUM(A1:B4)/SUM(C1:D4)

There are many types of functions, according to the type of calculation or operation they perform. So functions can be: mathematical, trigonometrical, estatistic, financial, text functions, date and time functions, data base functions, search and reference functions and information functions.

To introduce a formula you must type it in any cell, in the same way you introduce any text, preceded always by the sign =.

If you wish further informations about most frequently used operators and the priorities of operators visit our basic .

Inserting a function with the assitant

 

A function, as any other piece of data, may be typed straight into the cell if we know its syntax, but Excel2003 offers the help of an assistant to use functions. With the assistant it will be easier for us to use functions.

To introduce a function in a cell:

insertar funcion Go to the cell where you wish to introduce it, unfold the menu Insert.

Choose option Function...

Or click on the button boton formulas on the formulas bar.

The dialoge box Insert function will appear on the right.

With Excel2003 we can search the function we need typing a brief description of the function we need in the text box Search for a function and, then, click on the button boton ir . In this way, we don't have to know all the functions incorporated in Excel because the list shown in the box Select a function: will contain functions related to the description typed. 

To prenvent the list of functions being too long, we can previously select a category in the combine box Or select a category:, then in the list box will only appear functions from the chosen category and the list will be shortened. If you are not sure about which category you want, choose All.

In the list box Select a function: you will have to choose the function you wish clicking on it.

Notice that as we select a function, in the lower part the different arguments will appear together with a brief description of the function. The link Help on this function is also available to obtain a fuller description of a function.

Finally, click on the button OK.

This unit continues in the following page...



 

Pag. 4.1

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.

 

© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.