Guys,I am totally confused..* need more explanat...

Discussion in 'General Discussions' started by Maha_3, Jun 26, 2018.

  1. Maha_3

    Maha_3 Customer
    Customer

    Joined:
    Jun 22, 2018
    Messages:
    3
    Likes Received:
    0
    Guys,
    I am totally confused..
    * need more explanation about Index & Offset functions.
    * when to add ($) when to not ?
     
    #1
  2. Shohini_1

    Shohini_1 Well-Known Member
    Simplilearn Support

    Joined:
    Sep 24, 2018
    Messages:
    73
    Likes Received:
    4
    Hi Maha,

    Greetings!

    Please find the explanations below:

    The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

    Example:

    The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The OFFSET function returns a cell because the height and width are both set to 1.

    [​IMG]

    The INDEX array form returns the value of an element in a table or an array based on the row and column numbers you specify.

    Syntax: INDEX( array, row_num, [column_num])
    1. array - is a range of cells, named range, or table.
    2. row_num - is the row number in the array from which to return a value. If row_num is omitted, column_num is required.
    3. column_num - is the column number from which to return a value. If column_num is omitted, row_num is required.
    For example, the formula =INDEX(A1: D6, 4, 3) returns the value at the intersection of the 4throw and 3rd column in the range A1: D6, which is the value in cell C4.



    A dollar sign $ is called a locked cell reference.

    Locked cell references are one of the building blocks of Excel modeling. Without them, you will waste endless amounts of time re-writing formulas.

    Locking cell references will anchor formulas in specific ways so that we can copy and past them without having to rewrite them.

    Here is a lesson on the three types of locked cell references and how you can use them to copy & paste your formulas.

    There are three ways to lock a cell reference:
    • Locked column
    • Locked row
    • Locked column & row
    Example:

    Lock the column: =$D2 -> =$D9

    The column stayed locked as D, but the unlocked row moved down to row 9
    So the output changed to 0 (empty cell)

    Lock the row: =D$2 -> =E$


    The column shifted right to column E, but the row stayed locked at row 2
    So the output changed to 0 (empty cell)

    Lock the cell: =$D$2 -> $D$2

    The locked cell did not change at all
    Output did not change

    Lock the range: =SUM($D$2:$D$6) -> =SUM($D$2:$D$6)

    The locked range did not change at all
    Output did not change



    If you still get stuck somewhere, please let me know.
     
    #2
  3. Waleed Saleem

    Waleed Saleem Member

    Joined:
    Feb 1, 2019
    Messages:
    2
    Likes Received:
    0

    Hi,

    I am a little confused with the use of offset function in lesson 4.10 and lesson 4.12. Why did in one instance we used =offset($B$6, row()-6) and in another instance row()-15?
     
    #3
  4. Shohini_1

    Shohini_1 Well-Known Member
    Simplilearn Support

    Joined:
    Sep 24, 2018
    Messages:
    73
    Likes Received:
    4
    Hi Saleem,


    In Excel ROW() returns the current row number, which will help to specify the data value.
     
    #4

Share This Page