Offset Explanation Please

**tryingtoexcelatexcel** · 12-01-2016, 05:09 AM

Hi All,

So I'm trying to understand offset.

For example if I write =OFFSET(B5,0,2,COUNTA($C$4:$C$1082),1)

And I drag down, it will return the values in C, BUT if I write =OFFSET(B5,0,2,1,3) and try dragging right it wont return anything it just says #Value!

Can someone please explain how offset works? Is it because the range going down (COUNTA) goes all the way to C1082 so if I drag down, Offset will return two rows over from B and even though =OFFSET(B5,0 <-- is 0.

Also I tried to use the same concept to go right --> but when I put =OFFSET(B5,0,2,1,3), the box should be 3 horizontal, 1 vertical. But I get #Value! instead of returning values in the same way (COUNTA) did.

I think I might be not fundamentally understanding Offset, can someone please help?

Also any good combinations to use offset with? I've only used it with average(offset(counta)).

**Glenn Kennedy** · 12-01-2016, 05:36 AM

I think you need to explain what you're trying to do. Currently it al llooks a bit random.

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**bebo021999** · 12-01-2016, 05:49 AM

First OFFSET, returns one value in the result range
Second OFFSET, returns #VALUE
But, note that, both return a range, not single value. Press F2, then F9, to display the result array.

Offset Explanation Please

LinkBack

Thread Tools

Rate This Thread

Display

Offset Explanation Please

Re: Offset Explanation Please

Re: Offset Explanation Please

Thread Information

Users Browsing this Thread

Similar Threads

Indirect explanation

V Look up explanation

[SOLVED] VBA explanation

function similar to offset to offset the offset

VBA explanation

Explanation of formula

Explanation

Bookmarks

Bookmarks

Posting Permissions