Forum Discussion

  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 11, 2022

    tonyaterrell73 

     

    1st input: cell reference (a single cell)

    2nd input: number of rows to move up (negative value), down (positive value) or none (zero)

    3rd input: number of columns to move to the right or left or none

    >>these three parameters will output a reference to a single cell offset by the number of rows or columns, if you want a range... use the fourth and fifth inputs.

    4th input: the size of the range in number of rows

    5th input: the size of the range in number of columns.  

     

    For example, if you want to a range which grows and shrinks through time, you can use counta to count the number of rows that contain data as in the example below.  Note the "-1" after "COUNTA(G:G)" is simply to account for the text header.  Lastly, if you want to sum those values just wrap the offset function in a sum function.  

     

    This was a very useful function before excel tables existed, to ensure your formulas will always include the entire range of source data, even if the size of it changed.  

     

    =SUM(OFFSET(G2,0,0,COUNTA(G:G)-1,1))

     

     

  • GeorgieAnne's avatar
    GeorgieAnne
    Iron Contributor
    Sep 11, 2022
    Hello @ tonyaterrell73
    Can you elaborate a bit more of what you are trying to accomplish? Not enough information here.

    Offset is a way to tell MS-Excel what to do with the cell left, right, up or down from the cell you are referencing. For example:
    =OFFSET(A1,9,2) will get the value of the cell C10 cell C10 is 9 rows below cell A1 (1+9=10) and 2 Columns to the Right of cell A1. (A-B-C).
    There are other parameters for the OFFSET function, but for now I need to keep it simple.

    For the "input/output reference" I can't tell what you mean.

    Hope this helps
    Georgie

Resources