Faculties and centres
When we write a formula in a cell, in general, we refer to the content of other cells. References to these cells can be relative, absolute, or mixed. This difference is significant when you copy the formula from one cell to another adjacent using driver filling (small black square in the lower right corner of the active cell).
In the figure we have a spreadsheet with a formula in cell E5 which is assigned the value:
= B5 * C5
When we copy this formula to cells E6 and E7 using filler controller, Excel does not copy exactly the same formula, but it generates the following:
Cell E6 = B6 * C6
Cell E7 = B7 * C7
It does so because references are used.
The formula of starting refers to the value located three cells to the left and multiplied it by the value located two cells to the left. To copy the formula, these instructions, not the literal content of the cell are copied. Normally, this is what we want to do, because if we copied the formula literally will get the same value as in the origin of the formula cell.
By default, Excel creates cell references.
When we cut or copy a formula, and then paste it into another cell, the cell references do not conform.
Sometimes we want to use in a formula, the reference to a cell specific and this is preserved when using the filler controller, copy the formula to other cells. To achieve this we use absolute references.
In the figure below, the contents of the cell B10 is the discount that we will apply to the total.
The formula of the cell F5 is: = E5 * $B$ 10.
The dollar sign ($) before the column letter and row number indicate the program that we make a reference to a cell and that we want that it remains unchanged when we copy the formula. To copy the formula to the two cells below we obtain:
Cell F6 = E6 * $B$ 10
Cell F7 = E7 * $B$ 10
The relative cell reference has changed E5 E6 and E7, while the absolute reference to B10 is maintained.
An absolute reference uses two symbols dollar ($): one for the letter column and one for the row number. We may also use mixed references in which only the reference to the column or row is absolute. They will be of the form:
$A1 column letter is absolute and the relative row number
$ 1 The column reference is relative and absolute row
We can enter absolute or mixed references manually, by inserting dollar signs in the appropriate places in the formula, either using the F4 key. For example, if we aim or write the reference to the cell A1, press F4 becomes $to$ 1. If we go back to press F4 is transformed into A$ 1. Press once more and get $A1. And the next time returns to A1.
In the following figure is an example in which mixed references are used to calculate various percentages of different amounts
The formula in cell B3 is: $A3 * B$ 2. When we copy this formula, absolute references will remain unchanged. To copy it to the B3:F8 range will obtain, for example:
B5 = $A5 * B$ 2
$A8 = B8 * B$ 2
E5 = A5 $ * AND$ 2
E8 = A8 $ * AND$ 2
In the formulas can use the values of cells of other spreadsheets within the same book of work or even that they are in another different book. To make use of this type of references used the notation that we will see below.
= A1 * Hoja2!A1
If the spreadsheet you referenced has blank spaces in the name, type it between apostrophes. For example:
= A1 * 'Example sheet'!C8
= [NombreLibro] NombreHoja!DireccionCelda
As before, if the name of the book of job, or the spreadsheet includes spaces, put it between apostrophes:
A1 = ' [forecast 2015] personnel expenses '!A1
We can refer to a book of different work cells while it is open. In that case, it should add the full path of the book. For example:
= A2 ' c:\My documentos\Presupuesto\ [budget by 2015] Hoja2'!A.
A circular reference occurs when a formula refers to its own value. For example, we will have a reference circular if we attach to the cell A3 value = A1 A2 A3. A circular reference is usually a result of an error. When the program detects a circular reference, the following error message appears:
The program gives us two options:
To locate a circular reference: on the tab formulas in the formula auditing group, click on the arrow of the pooled error checking button, choose references, and then click the first cell that appears in the submenu.
It is not always easy to locate the references. A formula can refer to another which refers to a third which in turn refers to the first, making it difficult to find the circular reference. In this case we speak of indirect references.
The circular reference toolbar helps us find and resolve circular references.
Sometimes we need to use circular references.
In the following example, a company donates 5% of net profit. At the same time, the donation is considered spending and is subtracted from the net profit.
Here we have the following formulas:
B4 = B5 * 5%
B5 = B2 - B3 - B4, which contain a circular reference.
Normally, Excel will show an error message and will not present the value of the cell.
To make the calculations that we want, we must activate the iteration option:
Click the Microsoft Office button, click Excel Options, and then click the formulas category.
In the calculation options section, select the Enable iterative calculation check box.
As a general rule, this option must be set to off and her activate when you want to use a circular reference.