IF formula - positioning difference

[Nm]

Hi

Following is my issue:

I have 1 column of data and I have named the range
$A$2:$A$30 as Data_1.

I have following data in it (sample)

854000A13A0099002
854000A13AV031004

When I put the following formula in A1
=IF(Data_1>0,"yes","")

it gives me #VALUE as result but when I put it anywhere infront of Row
2 to Row 30 it works (any column).

I am sure I am missing something very small but I cant figure it out.

If someone could please tell me what may be the reason.

Thanks,
Naeem

 

[Bob Phillips]

It is because it is an array of data, and if you try to address an array
like that, you have to be in the same relative rows.

Try starting with

=IF(INDEX(Data_1,ROW(A1))>0,"yes","")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nm]

It is because it is an array of data, and if you try to address an array
like that, you have to be in the same relative rows.

Try starting with

=IF(INDEX(Data_1,ROW(A1))>0,"yes","")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)













- Show quoted text -

Thanks Bob, It worked....but could you please explain what does the
INDEX in the formula is doing ? Is it just considering ROW 1 in the
formula ?

Thanks,
Naeem

 

[Bob Phillips]

Basically it is INDEXing into the array, using the ROW number as the index.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

row(A1) returns 1. When you copy it down one cell, it becomes row(A2) and
then it returns 2.

This is used as the first argument of the index function. The index
function takes a range of cells as its first argument. The second argument
is a one based index value in to the rows of that range and the third
argument is a one based index in to the columns of that range. so

=Index($M$11:$Q$20,2,5) would be the cell in the second row of the range
and the 5th column; so it would be Q12 and it would return the value found
in Q12.

Using the row funtion to determine the row of interest, you can see that the
formula refers to the upper left corner of the Data_1 range only when the
second argument is Row(A1), when it is Row(A2) it refers to the second row
of the Data_1 range. The use of A1 is just to use an address that will
return the number 1 when used as the argument to row. $IV1 would work as
well.

 

[Nm]

row(A1) returns 1. When you copy it down one cell, it becomes row(A2) and
then it returns 2.

This is used as the first argument of the index function. The index
function takes a range of cells as its first argument. The second argument
is a one based index value in to the rows of that range and the third
argument is a one based index in to the columns of that range. so

=Index($M$11:$Q$20,2,5) would be the cell in the second row of the range
and the 5th column; so it would be Q12 and it would return the value found
in Q12.

Using the row funtion to determine the row of interest, you can see that the
formula refers to the upper left corner of the Data_1 range only when the
second argument is Row(A1), when it is Row(A2) it refers to the second row
of the Data_1 range. The use of A1 is just to use an address that will
return the number 1 when used as the argument to row. $IV1 would work as
well.

--
Regards,
Tom Ogilvy







- Show quoted text -

Thanks Tom for explaining it in detail.

Naeem