Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric

=SUMPRODUCT(--(A1:A8=B1-D1),--(A1:A8<=B1+D1))0

--
Gary''s Student - gsnu200739


"Eric" wrote:

Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric

On Sat, 25 Aug 2007 03:34:01 -0700, Eric
wrote:

Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric


C1: =OR(ISNUMBER(MATCH(ROW(INDIRECT(B1-D1&":"&B1+D1)),A1:A8,0)))

This formula is an array-formula, so must be confirmed by holding down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula if you entered it correctly.
--ron

On Sat, 25 Aug 2007 03:34:01 -0700, Eric
wrote:

Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric

Minor change:

=OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B1-D1,1)&":"&B1+D1)),A1:A8,0)))


--ron

Thank everyone very much for suggestions

Could you please describe what the symbol "--" do in this formula?
Thank you for any suggestions
Eric

"Gary''s Student" wrote:

=SUMPRODUCT(--(A1:A8=B1-D1),--(A1:A8<=B1+D1))0

--
Gary''s Student - gsnu200739


"Eric" wrote:

Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric

Thank everyone for suggestions

I would like to further provide addition conditions for this matching.

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 4,6,1

After knowing how to valid the values in cell B1, I get more values to be
valided in column B, in this example, there are 4,6,1 in column B, and I
would like to know how many values in column B are valided to be matched with
any value in column A.

The values in column B are 4,6,1, which is valided based on given condition.
There are 3 matched values and return 3 in cell E1.

Does anyone have any suggestions on how to determine the number of matched
values in column B?

Thank everyone very much for any suggesitons
Eric


"Ron Rosenfeld" wrote:

On Sat, 25 Aug 2007 03:34:01 -0700, Eric
wrote:

Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric

Minor change:

=OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B1-D1,1)&":"&B1+D1)),A1:A8,0)))


--ron

Converts TRUE into 1

for example
=(1=1) displays TRUE
but
=--(1=1) displays 1
--
Gary''s Student - gsnu200739


"Eric" wrote:

Thank everyone very much for suggestions

Could you please describe what the symbol "--" do in this formula?
Thank you for any suggestions
Eric

"Gary''s Student" wrote:

=SUMPRODUCT(--(A1:A8=B1-D1),--(A1:A8<=B1+D1))0

--
Gary''s Student - gsnu200739


"Eric" wrote:

Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric

On Sat, 25 Aug 2007 06:20:01 -0700, Eric
wrote:

Thank everyone for suggestions

I would like to further provide addition conditions for this matching.

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 4,6,1

After knowing how to valid the values in cell B1, I get more values to be
valided in column B, in this example, there are 4,6,1 in column B, and I
would like to know how many values in column B are valided to be matched with
any value in column A.

The values in column B are 4,6,1, which is valided based on given condition.
There are 3 matched values and return 3 in cell E1.

Does anyone have any suggestions on how to determine the number of matched
values in column B?

Thank everyone very much for any suggesitons
Eric


Adapting my formula -- **array-entered** with <ctrl<shift<enter:

=OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B1-D1,1)&":"&B1+D1)),A1:A8,0)))+
OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B2-D1,1)&":"&B2+D1)),A1:A8,0)))+
OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B3-D1,1)&":"&B3+D1)),A1:A8,0)))

Adapting Gary's student's formula (probably better):

=(SUMPRODUCT(--(A1:A8=B1-D1),--(A1:A8<=B1+D1))0)+
(SUMPRODUCT(--(A1:A8=B2-D1),--(A1:A8<=B2+D1))0)+
(SUMPRODUCT(--(A1:A8=B3-D1),--(A1:A8<=B3+D1))0)
--ron

Thank eveyone very much for suggestions
Eric

"Ron Rosenfeld" wrote:

On Sat, 25 Aug 2007 06:20:01 -0700, Eric
wrote:

Thank everyone for suggestions

I would like to further provide addition conditions for this matching.

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 4,6,1

After knowing how to valid the values in cell B1, I get more values to be
valided in column B, in this example, there are 4,6,1 in column B, and I
would like to know how many values in column B are valided to be matched with
any value in column A.

The values in column B are 4,6,1, which is valided based on given condition.
There are 3 matched values and return 3 in cell E1.

Does anyone have any suggestions on how to determine the number of matched
values in column B?

Thank everyone very much for any suggesitons
Eric


Adapting my formula -- **array-entered** with <ctrl<shift<enter:

=OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B1-D1,1)&":"&B1+D1)),A1:A8,0)))+
OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B2-D1,1)&":"&B2+D1)),A1:A8,0)))+
OR(ISNUMBER(MATCH(ROW(INDIRECT(MAX(B3-D1,1)&":"&B3+D1)),A1:A8,0)))

Adapting Gary's student's formula (probably better):

=(SUMPRODUCT(--(A1:A8=B1-D1),--(A1:A8<=B1+D1))0)+
(SUMPRODUCT(--(A1:A8=B2-D1),--(A1:A8<=B2+D1))0)+
(SUMPRODUCT(--(A1:A8=B3-D1),--(A1:A8<=B3+D1))0)
--ron

Thank everyone very much for suggestions

Could you please describe how the function SUMPRODUCT works?
I have read the help for this function, but don't fully understand how it
works.
Thank you very much for any suggestions
Eric

"Gary''s Student" wrote:

Converts TRUE into 1

for example
=(1=1) displays TRUE
but
=--(1=1) displays 1
--
Gary''s Student - gsnu200739


"Eric" wrote:

Thank everyone very much for suggestions

Could you please describe what the symbol "--" do in this formula?
Thank you for any suggestions
Eric

"Gary''s Student" wrote:

=SUMPRODUCT(--(A1:A8=B1-D1),--(A1:A8<=B1+D1))0

--
Gary''s Student - gsnu200739


"Eric" wrote:

Does anyone have any suggestions on how to valid the values?

There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true in
cell C1.
Does anyone have any suggestions on how to do it in excel?
Thank everyone very much for any suggestions
Eric

"Eric" wrote...
There is a list of value in column A
1,5,6,8,9,10,3,20
There is a given value in cell B1, which is 4
The value of range is 1 in cell D1
I would like to determine whether the value 4 in cell B1 +/- 1 in cell D1
[value of range] is matching any value in column A.
In this case, the matching values are between 3 [4-1] and 5 [4+1], and 5
exists in column A, then the value 4 in cell B1 is valid, and return true
in cell C1.
....

And, FTHOI, using ABS and avoiding volatile functions.

=SUMPRODUCT(--(ABS(A1:A8-B1)<=D1))