Page 47 - modular4.0
P. 47
Chapter Profile
CELL REFERENCING IN FORMULAS AND ITS TYPES
A cell reference is a cell address that can be used in a formula to denote a specific cell. A formula
may contain reference of other cells. In such case, the result of a formula depends on the values in
the referenced cells. In case you make any change in the referenced cells, the result will automatically
be changed.
To see how this works, enter 10 in cell A1. Now, select cell A2 and type =A1*2, and then press Enter
key. The value in cell A2 is 20. If you change the value in cell A1 from 10 to any value, the value in
cell A2 will also change.
Using cell reference After changing value
Cell references are especially helpful when you create complex formulas. There are three types of
cell references used in Excel: relative, absolute and mixed.
Relative References
In relative reference, you actually refer to cell that is above or below and left or right to a number
of rows or columns. When a formula that possesses a relative reference is copied from one cell to
another cell, the value in the copied cell also changes.
To use the relative cell reference, perform the following steps:
2 Click on the 1 Select the cell that contains the formula
Copy command. in which the reference of other cells is used.
3 Select another cell in
which you want to copy
the formula and click on
the Paste command.
Absolute References
If you wish to maintain the original references as they were, then you should make use of the
absolute references. If you want to make the formula retain the original reference when it is copied,
then you should precede the column reference and the row reference by a Dollar ($) symbol.
For example, if you want to give a discount of 10% on each product then the discount will remain
same for all the products. To calculate the amount to be paid, you should add the Dollar ($) sign with
the cell that contains the discount percentage. In this case, the discount percentage is given in the
cell D3. Hence, the formula will be =((B3*C3)-((B3*C3)*($D$3))).
Formulas and Functions 45

