Sep 10 2022 01:23 PM
I am having trouble with offset input/output reference
Sep 11 2022 02:53 PM
Sep 11 2022 03:09 PM
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))