Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 18 - 2617_JSSPS_C-6
P. 18

    CELL REFERENCING IN FORMULAS AND ITS TYPES

                  A cell reference is a cell address that is used in a formula to denote a specific cell. A formula may contain
                  references to other cells. In such a case, the result of a formula depends on the values in the referenced
                  cells. In case you make any changes to 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 press Enter key.

                  In cell A2, the value is 20. If you change the value in cell A1
                  from 10 to any value, the value in cell A2 will also change.
                  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 a cell that is above or below and left or right of a number of
                  rows or columns. Copying a formula with a relative reference from one cell to another also modifies the
                  copied cell's value. To use the relative cell reference, perform the following steps:

                                                                                  2
                           Select another cell in       Click on the Copy button.
                           which you want to copy
                       3   the formula and click
                           on the Paste button.

                            Select the cell that
                            contains the formula
                        1   in which the reference
                            of other cells is used.

                  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 copied, 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, the discount will remain the same
                  for all products. To calculate the amount to be paid, add the dollar ($) sign to the cell that contains the
                  discount percentage. In this case, the discount percentage is given in cell D3. Hence, the formula will
                  be =((B3*C3)-((B3*C3)*($D$3))).
                  When you copy the formula to the next cell, address of the cell D3 will remain same as shown:

                     16     Premium Edition-VI

| 13 14 15 16 17 18 19 20 21 22 23 |