Unit 2. Basic. Formulas

Formulas

A formula is sequence made of unvariable data, reference to other cells, names, functions, or operators.

A formula is a basic tecnique to analyse data. Different operations can be done with with the figures in spreadsheets, like *, +,-, Sen, Cos, etc...

In a formula you can mix constants, names, references to other cells, operators and functions. A formula should be written on the formulas bar and must begin always with the sign =.

The different types of operators that may be used in formulas are:

ARITMETIC OPERATORS used for calculations. Examples are:   +    -    *    /     %    ^

TEXT OPERATOR used to link cells that contain text. For example:  &

RELATIONAL OPERATORS used to compare data and to provide a logical apraisal (true or false) as a result of a comparison. Examples are: <    >   =   <=   >=   <>

REFERENCE OPERATORS indicate that the data produced in the referred cell must be used in the formula. In Excel these can be:

- Range operator indicated by colon (:), is used to indicate cells range. For example: A1:G5

- Union operator indicated by a coma (,), unites data of two or more cells. Example: A1, G5

When there are several operation in the same expression, each part of it is evaluated and a resolution is found in a determined order. That order is called operators priority.

You can use brackets to modify priority order and force the resolution of a part of the expression before the others.

Operations in brackets always have priority over those that are not in brackets. But inside the brackets normal priority of operators is maintained

When there are expressions that contain operators of more than one category, Excel resolves before those with aritmethic operators, next those with comparison operators and finally those with logical operators .

Comparison operators have all the same priority, that means that they are resolved from left to right, in the order they appear. Comparison operators are:

When there are multiplication and division in the same expression, each operation is resolved as it appears,from left to right. In the same way when there are sums and subtractions in the same expression, operations will be resolved in the order that they appear, from left to right.

The chains of characters operator (&) is not really an arithmetic operator but it has priority over all other comparison operators.

FUNCTIONS

A function is a special formula previously written that accepts one or mor values, performs calculations with those values and returns a result.

All functions must follow a syntax and if this is not adhered to Excel will display an error message.

1) Arguments or entry values are always in brackets. Do not leave spaces before or after each brakect.

2) Arguments can be values that are constant (a number or text), formulas or functions.

3) Arguments must be separated by a semicolon;".

Example:

=SUM(A1:B3)  this function equals =A1+A2+A3+B1+B2+B3

Later on in the course we will see how functions are used.