Compare cells within a range for a specific difference?

[Waxaholic]

Surely this is possible but i am lost. I have tried a few different ways to accomplish this but run into the "Formula is too long" scenario. Here is what i am trying to do:

A1:A16 contains the data. I want to be able to compare each cell within the range to all of the other cells within the same range for a difference of <.630 . For example, compare A1 to A2--A16, A2 to A1--A16, and so on. If the difference is encountered then some sort of flag would be raised whether it be cell formatting or a return value of TRUE/FALSE to an adjacent cell. Any help appreciated.

Waxaholic

[ This Message was edited by: Waxaholic on 2002-04-11 13:28 ]

[Andy Gee]

You can send the sheet or mockup sheet for me to have a look at if you like?
andy.gee@blueyonder.co.uk

[Mark W.]

Enter the array formula...

{=OR(ABS(A1-IF(ROW($A$1:$A$16)<>ROW(A1),$A$1:$A$16))<0.63)}

...into B1 and copy down. Array formulas must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

[Waxaholic]

Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

Waxaholic

[Aladin Akyurek]

On 2002-04-11 20:32, Waxaholic wrote:
Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

Waxaholic

Enter this in a cell

=((ABS(A1:A16-TRANSPOSE(A1:A16)))<0.630)+0

Activate the cell of the formula, select a range of 16 by 16 while in this cell, and hit control+shift+enter. You'll get a matrix of 1 and/or 0's. Not sure though whether it gives what you want.

[Mark W.]

On 2002-04-11 20:32, Waxaholic wrote:
Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

Waxaholic

Provide some representative sample data and the expected results!

[ This Message was edited by: Mark W. on 2002-04-15 15:00 ]

[eliW]

Hi Aladin,
I made a simple model of 5x5 cells and it does not seem to work. Probably I do not understand your instructions:

Enter this in a cell
=((ABS(A1:A16-TRANSPOSE(A1:A16)))<0.630)+0

Is this in any cell?

Activate the cell of the formula, select a range of 16 by 16 while in this cell, and hit control+shift+enter. You'll get a matrix of 1 and/or 0's. Not sure though whether it gives what you want.

How can I activate this cell and in the same time select another range of cells? Is it by pressing ALT ? Any way it is not working.

Thanks in advance

Eli


[ This Message was edited by: eliW on 2002-04-15 09:56 ]

[Aladin Akyurek]

On 2002-04-15 09:55, eliW wrote:
Hi Aladin,
I made a simple model of 5x5 cells and it does not seem to work. Probably I do not understand your instructions:

Enter this in a cell
=((ABS(A1:A16-TRANSPOSE(A1:A16)))<0.630)+0

Is this in any cell?

Yes, a cell outside the data range.

Activate the cell of the formula, select a range of 16 by 16 while in this cell, and hit control+shift+enter. You'll get a matrix of 1 and/or 0's. Not sure though whether it gives what you want.

How can I activate this cell and in the same time select another range of cells? Is it by pressing ALT ? Any way it is not working.

[ This Message was edited by: eliW on 2002-04-15 09:56 ]


Eli,

You just type the formula in the activated/selected cell and hit enter. You'll get a #VALUE! error. Now select an area 16 by 16, starting from this formula cell and hit control+shift+enter to get the matrix of results I mentioned.

I must say that, as I noted, I'm not sure whether this meets the specs. Mark is already asking for more info.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-04-15 10:52 ]

[Mark W.]

On 2002-04-11 20:32, Waxaholic wrote:
Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

Waxaholic

Perhaps this "tweak" will suffice...

{=OR(ABS(A1-IF(ROW($A$1:$A$16)<>ROW(A1),$A$1:$A$16,A1+0.63))<0.63)}

If A1:A16 contains...

{0.1;0.9;2;1.6;3;4;5;6;6.4;8;9;10;11;12;13;14}

...and, this array formula is entered into B1 and copied down to B16 it'll produce...

{FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

B3 and B4 are TRUE because |2-1.6| < 0.63.
B8 and B9 are TRUE because |6-6.4| < 0.63.

All other values in column have an absolute difference greater than 0.63.

BTW, the formula insures that when a value is compared to itself it will NEVER return TRUE; otherwise, the results for the entire list would always be TRUE.

Your problem statement suggested that A1 should be compared to A2:A16 and A2 should be compared to A1:A16. In fact, A1 should be compared to A2:A16, A2 should be compared to A1,A3:A16, etc.

[ This Message was edited by: Mark W. on 2002-04-16 07:16 ]