offset question

Hi,

I am looking at a spreadsheet and trying to figure exactly hows its working.

There is a formula

=OFFSET(Data!$B300:$B300,0,COUNT('Summary Sheet'!C$30:$O$30))

Can someone explain how an offset works and what the above is saying.

Thanks
Seamus
Seamus2626Asked:
Who is Participating?
 
Rory ArchibaldCommented:
To the right, not left, but yes. :)
0
 
Rory ArchibaldCommented:
The first argument is the start position: Data!$B300:$B300 (i.e. B300 on the Data sheet)
then the formula offsets by 0 rows (in other words still looking at row 300)
and then offsets the column by the the number of entries in C30:O30 on the Summary sheet
so if you had 5 cells filled in C30:O30, you would be referring to the cell 5 columns to the right of B300.

Note the formula would normally be written as just:
=OFFSET(Data!$B300,0,COUNT('Summary Sheet'!C$30:$O$30))

Make sense?
0
 
Seamus2626Author Commented:
What if you had 4 cells filled in C30:O30, would that mean you would be referring to the cell 4 columns to the left of B300?


Thanks
Seamus
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Seamus2626Author Commented:
I understand! perfect, thanks Rory

Seamus
0
 
Rory ArchibaldCommented:
Even though they are not used here, there are also 2 optional arguments to the OFFSET function whereby you can specify the number of rows and columns in the resulting range. So:
=OFFSET(A1,0,0,3,2)
would refer to a range starting 0 rows and 0 columns from A1 (i.e. starting at A1) that is 3 rows high and 2 columns wide - in other words A1:B3
0
 
Seamus2626Author Commented:
Thanks for the extra info!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.