micro excel

Occasional Visitor

I am having trouble with offset input/output reference

 

2 Replies
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

@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))

 

DexterG_III_0-1662933868763.png