![]() ![]() Note: Excel documentation states height and width can't be negative, but negative values appear to have worked fine since the early 1990's. The OFFSET function in Google Sheets won't allow a negative value for height or width arguments. The OFFSET function can be used to build a dynamic named range for charts or pivot tables, to ensure that source data is always up to date. ![]() The main purpose of OFFSET is to allow formulas to dynamically adjust to available data or to user input. It is common to see OFFSET wrapped in another function that expects a range. Note: width could be omitted, since it will default to 1. To reference C1:C5 from A1, reference is A1, rows is 0, cols is 2, height is 5, and width is 1: =OFFSET(A1,0,2,5,1) // returns reference to C1:C5 ![]() When height and width are omitted, they default to the height and width of reference.įor example, to reference C5 starting at A1, reference is A1, rows is 4 and cols is 2: =OFFSET(A1,4,2) // returns reference to C5 ![]() The height and width arguments are optional and determine the size of the range that is created. The rows and cols arguments are the number of cells to "offset" from the starting point. The starting point (the reference argument) can be one cell or a range of cells. The Excel OFFSET function returns a dynamic range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |