We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

offset question

Seamus2626
Seamus2626 asked
on
Medium Priority
246 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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?

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I understand! perfect, thanks Rory

Seamus
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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

Author

Commented:
Thanks for the extra info!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.