IF formula - positioning difference
 

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_10,"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

IF formula - positioning difference
 

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" wrote in message
ps.com...
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_10,"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

IF formula - positioning difference
 

On Jul 10, 10:10 am, "Bob Phillips" wrote:
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" wrote in message

ps.com...



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_10,"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- Hide quoted text -

- 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

IF formula - positioning difference
 

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)



"Nm" wrote in message
oups.com...
On Jul 10, 10:10 am, "Bob Phillips" wrote:
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" wrote in message

ps.com...



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_10,"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- Hide quoted text -

- 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

IF formula - positioning difference
 

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


"Nm" wrote:

On Jul 10, 10:10 am, "Bob Phillips" wrote:
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" wrote in message

ps.com...



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_10,"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- Hide quoted text -

- 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

IF formula - positioning difference
 

On Jul 10, 11:04 am, Tom Ogilvy
wrote:
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



"Nm" wrote:
On Jul 10, 10:10 am, "Bob Phillips" wrote:
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" wrote in message

ups.com...

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_10,"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- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

Thanks Tom for explaining it in detail.

Naeem