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
   42   43   44   45   46   47   48   49   50   51   52