Page 46 - TP_Modular_V2.1_Class7
P. 46

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 in case of complex formulas. There are three types of cell
                  references used in Excel: relative, absolute and mixed.
                  Relative References

                  By default, all cell references are relative references. When copied across multiple cells, they
                  change based on the relative position of rows and columns. When a formula that possesses a
                  relative reference is copied from one cell to another cell, the value of the relative references in
                  the copied cell also changes.

                  To use the relative cell reference, perform the following steps:
                  Step 1   Select the cell that contains the formula in which the reference of other cells is used. In
                           this case, we have selected the cell D3 which contains the formula =B3*C3 to calculate
                           the amount to be paid for the product.
                  Step 2  Click on the Copy command form the Clipboard group under the Home tab.

                  Step 3   Select another cell in which you want to copy
                           the formula and click on the  Paste command
                           from the Clipboard group under the Home tab.
                           Notice, in the Formula bar that the cell address
                           that you have used in the formula gets changed
                           accordingly. For example, if you paste the formula
                           in cell D4, the formula will become =B4*C4                   Using relative referencing
                  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))).                   Using absolute referencing

                  When you copy the formula to the next cell, address of the cell D3 will remain same as shown.
                  For example, if you paste the formula in cell D4, the formula will be =((B4*C4)-((B4*C4)*($D$3)))

                   44       Modular (Ver. 2.1)-VII
   41   42   43   44   45   46   47   48   49   50   51