Posted by David on July 31, 2001 7:16 PM

I am looking to compare values in a column in a worksheet

with 65000 rows. Basically, I want to compare each value

with every other one in the column. If their difference

is greater than a given amount, I want a TRUE output. Even

better, I want the output to be the rows that satisfies

my condition, or at least tell me how many rows satisfies

my condition. This is what I've tried so far, which doesn't

work:

=IF(AND(ABS(B1-B$1:B$65000)<1,ABS(C1-C$1:C$65000)<.5),1,0)

and, furthermore:

=SUM(IF(AND(ABS(B1-B$1:B$65000)<1,ABS(C1-C$1:C$65000)<.5),1,0))

any help would be appreciated.

Posted by Aladin Akyurek on July 31, 2001 11:08 PM

Care to provide just 3 rows of your B and C columns along with expected results?

Posted by David on August 01, 2001 1:00 AM

Sure...i know i didn't make it as clear as I could have:

B C

0.648 0.334

-23.65 0.023

1.343 0.239

I want a formula that will take the value in B1,

and subtract B2 from it. If the absolute value of

their difference is less than, in this case, 1, then

I want a true result. But, I only want a true result

if the absolute value of the difference between C1

and C2. So I want to compare B1 and C1 in this way

to all other rows. So it would subtract from B2, and

from B3, seperatly. And both columns must satisfy

conditions simultaneously for a true result. Ideally,

the output would be the rows that satisfy the condition,

but not neccesary.

So, to be less wordy, the above example:

absolute value of(B1-B2) is greater than 1, so it

is out, even though ABS(C1-C2)<.5.

However, B1-B3 is less than 1, and C1-C3 is less

than .5, so the result would be true, and, if

possible, the result would be 3, since that is

the row that satisfies our condition.

Posted by Aladin Akyurek on August 01, 2001 1:31 AM

Are you aware of the sheer number of computations that you want?

B1 with B2 -- C1 with C2

B1 with B3 -- C1 with C3

B2 with B3 -- C2 with C3

& what is it for?

Posted by David on August 01, 2001 11:12 AM

yes, i am aware of the number of calculations

involved. it is for a research project i am working

on. i get the feeling that it is beyond Excel's scope

and more in the range of VB, but figured I would give

it a try. I don't know anything of VBA, so it would

take a while for me to figure it out that way too.

Posted by Aladin Akyurek on August 01, 2001 11:40 AM

At least the formula that you were looking for:

=IF(ISNUMBER(B2),IF(AND(ABS($B$1-B2)<1,ABS($C$1-C2)<0.5),ROW(C2),""),"")

Aladin

Posted by David on August 01, 2001 12:24 PM

Yes, but i want to compare B1 with all B1 through B65000 simultaneously,

but individually, and, at the same time, compare C1 with all C1 through C65000

and if both the B and C column conditions are satisfied, for the same rown number,

then i get a true result.

-David

Posted by Aladin Akyurek on August 01, 2001 12:40 PM

Yes, but i want to compare B1 with all B1 through B65000 simultaneously, but individually, and, at the same time, compare C1 with all C1 through C65000 and if both the B and C column conditions are satisfied, for the same rown number, then i get a true result.

If you put this formula in D1, you'll get the row numbers that satisfy your conditions. This is just for B1 (& C1) of course. You see you'll not have enough columns to do is for every value in B. It would better to have an array-formula that returns an array of row numbers. I didn't look at that (yet).

Aladin

Posted by David on August 01, 2001 1:05 PM

Yes, in D1 I want to know if any rows satisfy the condition when compared to C1 and B1 (out of all 65000)

In D2, I want to know if any rows satisfy the condition compared to C2 and B2, etc, all the way to row 65000.

-David