Compare cells within a range for a specific difference?

Waxaholic

Board Regular
Joined
Apr 2, 2002
Messages
72
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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".
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top