How to valid the values?
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
|