Thanks:  0
Likes:  0

# Thread: Compare cells within a range for a specific difference?

1. 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 ]

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

3. 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".

4. 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

5. 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.

6. 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 ]

7. 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 ]

8. 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 ]

9. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•