• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset Function

G

GN0001

Member
I have a range and I need to use an offset function:


C4=Sales

C5=1515

C6=1526

C7=1444

C8=1562

C9=1490

C10=1571

C11=1702

When I enter offset function as below:

=offset(C4,0,0,1,1) it gives me the correct answer

=Offset(C4,0,0,7,1) it gives me #Value! Why? here Height is 7 cells(Rows) and width is 1 (Column).

=Offset(C4,0,0,1,7) it give me the correct answer. Why?

Regards,

Guity
 
Try selecting the formula in the formula bar, and then hit F9. Take a look at what you see.
 
This is what I get from =Offset(C15,0,0,7,1)when I hit F9:

{"Sales";1515;1526;1444;1562;1490;1571}

This gives me an array, So I had to enter Ctrl + Shift + Enter to get an array for answer.

Is this what you would like to result?


Then, this question comes up for me why this function: =Offset(C4,0,0,1,7) is not considered an array? This is the result: {"Sales"," Profit","No. of Customers",0,0,0,0}, This extends across a row.

Thanks,

Guity
 
Guity

Offset by itself cannot return a value, unless it refers to a single cell

eg =Offset(C15,5,0,1,1) will return the value in C20

Offset returns an array of values refered to by the size of the offset parameters


Offset is generally used with a function such as Sum, MIn, Max etc

eg: =Sum(Offset(C15,0,0,5,1)) will add up the values in C15:C20


You can use offset within other functions like Index/Match, Vllokup etc to make them dynamic or responseive to changing range areas
 
Hui,

Thanks a lot, that is a great help.

With what you explained on the top, I totally understand that offset can only return value when it refers only to one cell and it is used inside of other functions.


Can it be used like what I say in underneath?


I take ctrl+shift+enter or when I Press F9 to see the function in formula bar and then I hit enter, I can get the result when it is extended along a column: {"Sales";1515;1526;1444;1562;1490;1571}.

It gives me all the values and each value is coming in an individual cell.


When it is extended across a row: {"Sales"," Profit","No. of Customers",0,0,0,0}

It gives me all the values and each value is coming in an individual cell.


What would you say about these two cases?

----------------------------------------------------------------------------------

Second question:

I have a if function:

=If(B4, C4, NA())

I Would like to control a range when the B4 is true, not only one cell C4.

_____________________________________________________________________________


When I saw you have responded to my question, I filled with joy.

Regards,

Guity
 
Kachiba,


You are completely right. It is very confusing. I shouldn't have presented them in this way. Since I didn't receive any answer, I thought if I break it, I may get some answer.


I have to test what you you are suggesting, if I check the checkbox, then true part of If function should be performed without me being needed to do a CTRL+Shift+Enter.


I will get back to you.


Regards,

Guity
 
Kachiba,


You are completely right. It is very confusing. I shouldn't have presented them in this way. Since I didn't receive any answer, I thought if I break it, I may get some answer.


I have to test what you you are suggesting, if I check the checkbox, then true part of If function should be performed without me being needed to do a CTRL+Shift+Enter.


I will get back to you.


Regards,

Guity
 
You must log in or register to reply here.
Back
Top