How to valid the values?

G

Guest

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
 
R

Ron Rosenfeld

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
Click to expand...


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
 
R

Ron Rosenfeld

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
Click to expand...

Minor change:

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


--ron
 
G

Guest

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 said:
=SUMPRODUCT(--(A1:A8>=B1-D1),--(A1:A8<=B1+D1))>0

--
Gary''s Student - gsnu200739


Eric said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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
Click to expand...

Minor change:

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


--ron
Click to expand...
 
G

Guest

Converts TRUE into 1

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


Eric said:
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 said:
=SUMPRODUCT(--(A1:A8>=B1-D1),--(A1:A8<=B1+D1))>0

--
Gary''s Student - gsnu200739


Eric said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Rosenfeld

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
Click to expand...

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
 
G

Guest

Thank eveyone very much for suggestions
Eric

Ron Rosenfeld said:
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
Click to expand...

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
Click to expand...
 
G

Guest

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 said:
Converts TRUE into 1

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


Eric said:
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 said:
=SUMPRODUCT(--(A1:A8>=B1-D1),--(A1:A8<=B1+D1))>0

--
Gary''s Student - gsnu200739


:

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
Click to expand...
Click to expand...
Click to expand...
 
H

Harlan Grove

Eric said:
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.
Click to expand...
....

And, FTHOI, using ABS and avoiding volatile functions.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
setting cell values based on a 3rd cell 4
COUNTING THE SAME VALUES IN A SHEET 3
How to determine the value? 2
Need Help 4
Look up or Match Function? Excuse my ignornace. This could be simple.Variable Upgrades on Furniture 1
Excel functions 0
Averaging values from a list, based on user input 3

Top