comparring 65000 values, with conditions
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
